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.
#!/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, '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 for i in cur.description] result=cur.fetchall() c = csv.writer(open(sys.argv,"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.
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.