MySQL通过Python脚本以UTF-8格式导出到CSV文件 [英] MySQL export to CSV file as UTF-8 via Python script

问题描述

我能够通过Python csv模块将MySQL表导出到CSV文件中，但是没有utf-8字符. (例如:????插入了ąöę).

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 ąöę).

表数据为utf-8格式(phpMyAdmin，让我看到正确的数据).

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

我发现一些信息，在Python中所有数据都应在utf-8中解码，然后通过例如unicodewritter在utf-8中编码为CSV(因为本机csv模块不正确支持Unicode).

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.

问题:是否有示例脚本将Python中utf-8的MySQL数据库导出为utf-8格式的CSV文件?

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

我使用ubuntu 14.04，并且mysql.connector存在问题，因此我将MySQLdb与Gord Thompson代码一起使用:

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

错误仍然存​​在:UnicodeDecodeError:'utf8'编解码器无法解码位置2的字节0xf3:无效的继续字节

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

推荐答案

最后成功了！感谢: Gord Thompson 和 Prikkeldraad . 谢谢你们！

Finaly it Works! Thanks to : Gord Thompson and Prikkeldraad. Thanks Guys !

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

这篇关于MySQL通过Python脚本以UTF-8格式导出到CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！