I recently stumbled over the problem that I had to excrat data from a database regularly. Cron! I didn’t want to be bothered by it, so I was looking for a mysql comand, that would do so for me, but as it seems, there is only a way save it to csv on the server, not on a client.

Since I didn’t find an out-of-the-box solution, I went ahead and created a little python script. For it to work, you need python2.7 and python-mysqldb. You need to enter your database credentials in lines 9 to 12. If you want to change the default quote character and delimiter, you can do so in line 28.

mysql2csv.py

#!/usr/bin/python
#
# Usage: ./mysql2csv.py file.sql file.csv

import MySQLdb as dbapi
import sys
import csv

dbServer=''
dbPass=''
dbSchema=''
dbUser=''

with open(sys.argv[1], 'r') as myfile:
    dbQuery=myfile.read().replace('\n', ' ')

db=dbapi.connect(host=dbServer,user=dbUser,passwd=dbPass)
cur=db.cursor()
cur.execute("use " + dbSchema + ";")
cur=db.cursor()
cur.execute(dbQuery)

num_fields = len(cur.description)
field_names = [i[0] for i in cur.description]

result=cur.fetchall()

c = csv.writer(open(sys.argv[2],"wb"), delimiter=',', quotechar='"')
c.writerow(field_names)

for row in result:
    c.writerow(row)

Don’t forget to make this executable (chmod +x) on unix-like systems.

Disclaimer

This code is far from perfect, but it gets the job done. However it is quite resource hungry. To write my 260MB csv-file, it uses up to 6GB of RAM along the way.