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 test.py

#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors #use cursors for mysql_fetch_assoc
import csv, codecs, cStringIO

#Thanks https://gist.github.com/hvtuananh/8834749 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)
 self.stream = 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
 self.stream.write(data)
 # empty queue
 self.queue.truncate(0)

def writerows(self, rows):
 for row in rows:
 self.writerow(row)
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"]
writer.writerow(header);

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)

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

db.close()
csvfile.close()

 

Then run in command line $ python test.py

You can download my python project at https://github.com/flakesns/python-example

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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