<html>
<head>
<style>
BODY {background-color: white}
body, div, p, th, td, li, dd, code, tt {
font-size: 10pt;
font-family: verdana,helvetica;
white-space:wrap;}
h2 {
font-size: 16px;
margin: 0;
color: 1393C0;
}
.blogtitle {
font-size: 16px;
}
</style>
</head>
<body bgcolor="FFFFFF" text="#000000" link="#000000" vlink="#000000" alink="#000000">
<a href="http://www.bloglines.com">Bloglines</a> user <b>grig@gheorghiu.net</b> has sent this item to you, with the following personal
message:
<p>
Nice application of SQL Alchemy magic.<BR>
<BR>
Grig
<p>
<hr>
<table width="100%">
<tr><td>
<a name="base280885">
<table id="table280885" width="100%" cellspacing=0 cellpadding=5 bgcolor="#bde0ed">
<tr>
<td rowspan="2">
<table cellspacing=0 cellpadding=0>
<tr><td>
<span class="blogtitle"><a href="http://www.planetpython.org/"><b>Unofficial Planet Python</b></a></span><br>
Unofficial Planet Python - http://www.planetpython.org/
</td>
</tr>
</table>
</td></tr>
</table>
</td></tr>
<tr><td>
<div id='items280885' style='display:block; position:block;'>
<table width="100%">
<tr bgcolor="#ffffff">
<td>
<table width="100%">
<tr><td>
<h2><a title="Site: Unofficial Planet Python" href="http://maxischenko.in.ua/blog/entries/101/dump-mysqls-data-in-csv-format-with-sqlalchemy/">Max's blog v0.2.1: Dump MySQL’s data in CSV format with SQLAlchemy</a></h2>
<p>
<p>Today I needed to quickly dump data from MySQL database in CSV (comma-separated) format. In theory, you can use SELECT INTO … OUTFILE MySQL extension but in practice it:</p>
<ol><li>requires root privileges</li>
<li>provides too little control over output (e.g. has no option to write header row)</li>
</ol><p>I decided to write a Python script and with the help of <a href="http://sqlalchemy.org" target=_blank class=blines3 title="Link outside of this blog">SQLAlchemy</a> it was completed almost as fast as writing an SQL query. It essense is in the following code snippet:</p>
<pre><code>
engine = sqlalchemy.create_engine('mysql://...')
meta = sqlalchemy.BoundMetaData(engine)
table = Table('table_name', meta, autoload=True) # introspect live db
fd = open('z.csv', 'wt') # CSV file to write to
writer = csv.writer(fd, delimiter=',')
writer.writerow(table.columns.keys()) # header
for r in table.select().execute(): # SELECT * statement
writer.writerow(r)
fd.close()
</code></pre>
<p>That’s it. As an added bonus, it will work for any database engine SQLAlchemy support, not just MySQL. It also easy to extend, if needed.
</p>
</td></tr>
<tr><td>
<p>
<table width="100%" cellspacing=0 cellpadding=0>
<td align="left" valign="top">
</td>
<td align="right" valign="top">
<br>
</td></tr>
</table>
</td></tr>
</table>
<p>
</td></tr>
</table>
</td></tr>
</table>
</body>
</html>