MySQL通过Python scrip以UTF-8格式导出到CSV文件

Question

我可以通过Pythoncsv模块将MySQL表导出到CSV文件中，但是没有utf-8字符。（例如：????字符代替ąöę）。

表数据是utf-8格式的（phpMyAdmin让我看看正确的数据）。

我发现了一些信息，在Python中，所有数据都应该用utf-8解码，然后通过例如unicodewritter编码成utf-8中的CSV（因为本机csv模块不正确地支持Unicode）。

我试了很多，但没有成功。

问：是否有任何示例脚本可以将utf-8格式的MySQL数据库导出到Python格式的utf-8格式的CSV文件中？

我使用ubuntu 14.04，mysql.connector有问题，所以我使用MySQLdb和Gord Thompson代码：

# -*- 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])

错误仍然存在：UnicodeDecodeError:“utf8”编解码器无法解码位置2中的字节0xf3:无效的连续字节

Answer 1

对于Python 2.7.5和MySQL Connector/python2.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])

UnicodeWriter类直接取自documentation page for the csv module上的最后一个示例，我将其存储在一个名为“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)

Answer 2

MySQL在转换字符集方面非常出色。但您需要告诉它使用正确的排序规则建立连接。

默认情况下，它返回如何将其放入数据库。将所需的字符集添加到连接：

conn = MySQLdb.Connection(db='sampledb', host='localhost',           
user='sampleuser', passwd='samplepass', charset='utf-8', )

这有用吗？

Answer 3

终于成功了！感谢：Gord Thompson和Prikkeldraad。 谢谢你们！

# -*- 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='testdb', host='localhost', user='testuser', passwd='testpasswd', use_unicode=0,charset='utf8')

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])