Python – Export Mysql Data to CSV UTF-8

Posted: February 10, 2016 in Python
Tags: ,

This is my start up script to use python with mysql and then export the data to csv (utf-8) format.

My python version : 2.6.7
Environment: Ubuntu 14.04
MySql 5.5.49

$ apt-get install python-mysqldb


Create new file

# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors #use cursors for mysql_fetch_assoc
import csv, codecs, cStringIO

#Thanks for fix the UnicodeWriter class

class UnicodeWriter:
 A CSV writer which will write rows to CSV file "f",
 which is encoded in the given encoding.

def __init__(self, f, dialect=csv.excel, encoding="utf-8", **kwds):
 # Redirect output to a queue
 self.queue = cStringIO.StringIO()
 self.writer = csv.writer(self.queue, dialect=dialect, **kwds) = f
 self.encoder = codecs.getincrementalencoder(encoding)()

def writerow(self, row):
 self.writer.writerow([unicode(s).encode("utf-8") for s in row])
 # Fetch UTF-8 output from the queue ...
 data = self.queue.getvalue()
 data = data.decode("utf-8")
 # ... and reencode it into the target encoding
 data = self.encoder.encode(data)
 # write to the target stream
 # empty queue

def writerows(self, rows):
 for row in rows:
csvfile = open('test.csv', "wb")
#writer = csv.writer(csvfile, delimiter=',', quotechar='"', quoting=csv.QUOTE_ALL)
writer = UnicodeWriter(csvfile)

#csv header
header = ["Col 1", "Col 2", "Col 3"]

db = MySQLdb.connect("localhost","myusername","mypassword","mydatabase", cursorclass=MySQLdb.cursors.DictCursor)
cursor = db.cursor()

sql = "SELECT col1, col2, col3 FROM lib_asset WHERE project_id = %d" % (13)

 results = cursor.fetchall()
 for row in results:
     #print "col1=%d,col2=%s,col3=%s" %(row['col1'], row['col2'], row['col3'])
 print "Error: unable to fetch data"



Then run in command line $ python

You can download my python project at


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s