MySQL export to CSV file as UTF-8 via Python script
Question: I'm able to export a MySQL table into a CSV file via Python `csv` module but
there are no utf-8 characters. (example: `????` chars insted of `ąöę`).

The table data is in utf-8 format (phpMyAdmin let me see correct data).

I found some information that in Python all data should be decoded in utf-8
and then encoded into CSV in utf-8 via for example `unicodewritter` (because
the native `csv` module doesn't support Unicode correctly).

I tried a lot but no success.

Question : Is there any example script to export MySQL database in utf-8 to
CSV file in utf-8 format in Python?

I use ubuntu 14.04 and there is a problem with mysql.connector so I use
MySQLdb with Gord Thompson code :

    
    
    # -*- coding: utf-8 -*-
    import csv
    import MySQLdb
    from UnicodeSupportForCsv import UnicodeWriter
    import sys
    reload(sys)  
    sys.setdefaultencoding('utf8')
    #sys.setdefaultencoding('Cp1252')
    
    conn = MySQLdb.Connection(db='sampledb', host='localhost',           
    user='sampleuser', passwd='samplepass')
    
    crsr = conn.cursor()
    crsr.execute("SELECT * FROM rfid")
    with open(r'test.csv', 'wb') as csvfile:
        uw = UnicodeWriter(
        csvfile, delimiter=',',
        quotechar='"', quoting=csv.QUOTE_MINIMAL)
    for row in crsr.fetchall():
        uw.writerow([unicode(col) for col in row])
    

Error still exist : UnicodeDecodeError: 'utf8' codec can't decode byte 0xf3 in
position 2: invalid continuation byte


Answer: This works for me with Python 2.7.5 and MySQL Connector/Python 2.0.4:

    
    
    # -*- coding: utf-8 -*-
    import csv
    import mysql.connector
    from UnicodeSupportForCsv import UnicodeWriter
    
    conn = mysql.connector.connect(
        host='localhost', port=3307,
        user='root', password='whatever',
        database='mydb')
    crsr = conn.cursor()
    crsr.execute("SELECT * FROM vocabulary")
    with open(r'C:\Users\Gord\Desktop\test.csv', 'wb') as csvfile:
        uw = UnicodeWriter(
            csvfile, delimiter=',',
            quotechar='"', quoting=csv.QUOTE_MINIMAL)
        for row in crsr.fetchall():
            uw.writerow([unicode(col) for col in row])
    

The `UnicodeWriter` class is taken directly from the last example on the
[documentation page for the csv
module](https://docs.python.org/2/library/csv.html), which I stored in a file
named "UnicodeSupportForCsv.py":

    
    
    import csv, codecs, cStringIO
    
    class UTF8Recoder:
        """
        Iterator that reads an encoded stream and reencodes the input to UTF-8
        """
        def __init__(self, f, encoding):
            self.reader = codecs.getreader(encoding)(f)
    
        def __iter__(self):
            return self
    
        def next(self):
            return self.reader.next().encode("utf-8")
    
    class UnicodeReader:
        """
        A CSV reader which will iterate over lines in the CSV file "f",
        which is encoded in the given encoding.
        """
    
        def __init__(self, f, dialect=csv.excel, encoding="utf-8", **kwds):
            f = UTF8Recoder(f, encoding)
            self.reader = csv.reader(f, dialect=dialect, **kwds)
    
        def next(self):
            row = self.reader.next()
            return [unicode(s, "utf-8") for s in row]
    
        def __iter__(self):
            return self
    
    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([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)
    

