Dump MySQL Data to CSV with Python

mysql-to-csv.py

# requirement: python-mysqldb

import MySQLdb as dbapi
import sys
import csv

QUERY='SELECT * FROM mydb.people;'
db=dbapi.connect(host='localhost',user='root',passwd='password')

cur=db.cursor()
cur.execute(QUERY)
result=cur.fetchall()

c = csv.writer(open('dbdump01.csv', 'wb'))
for x in result:
    c.writerow(x)

A couple of edits:

• I got an error 'No database selected', so I had to add db='dbname' to the dbapi.connect()
• Then I got an error TypeError: a bytes-like object is required, not 'str', which I fixed by changing wb to just w in line 14

Thanks a lot. It works.

I'm getting a "No Module named MySql DB" error when running this in Anaconda with Python 3.7.

Is this because Python 3.X does not support MySQLDB and I need to downgrade to 2.7?

Thanks

Thank you so much! this worked perfectly for me.

I just have a question... What can I do to also include the column names in the CSV file?

Good job

Also, you can do through pandas

import pandas as pd
import MySQLdb as dbapi
import sys
import csv

QUERY='SELECT * FROM mydb.people;'
db=dbapi.connect(host='localhost',user='root',passwd='password')

cur=db.cursor()
cur.execute(QUERY)
result=cur.fetchall()

result.to_csv("./Desktop/testdump.csv")

You may also need to add string encoding to the open file writer:
encoding='utf-8'
c = csv.writer(open('user_dump01.csv', 'w',encoding='utf-8'))

result = DB.query('select * from fb')
c = csv.writer(open('dbdump01.csv', 'w'))
for x in result:
c.writerow(x)

This works cool