Python csvwriter不喜欢unicode

Question

tl;dr有一个复杂的SQL查询，它以.csv格式返回结果。不幸的是，作为加拿大人的缺点是，一群人喜欢在他们的名字上加重音。

目前，这就是我所拥有的。我最初并没有编写脚本，只是试图让它工作以获取数据。SQL是正确的，只是被nano截断。

def getCommentsByGUID(reportDay):
        conn = mysql.connector.connect(host = dbServer, user = dbUser, passwd = dbPass, db = tscDBName)
        sqlresult = ''
        sql = 'select d.documentId, dn.created, dn.notes as Comment, u.login, d.fileName from DocumentInfo d \
inner join documentnotes dn on d.documentId=dn.documentId \
inner join User u on dn.userId=u.userID \
where d.companyId=%d and dn.created>\'%s 00:00:00\' and dn.created <\'%s 23:59:59\';' % (companyID, reportDay, reportDa$
        cursor = conn.cursor()
        cursor.execute (sql)
        sqlresult = cursor.fetchall()
        cursor.close ()
        reportWriter = csv.writer(open('%sFilename_comments_%s.csv' % (outputDir, reportDay), 'w'), delimiter=',', quotec$
        for results in sqlresult:
                reportWriter.writerow(results)

按原样运行将创建unicode错误，如：

UnicodeEncodeError: 'ascii' codec can't encode character u'\u2019' in position 366: ordinal not in range(128)

现在，经过大量的研究，我遇到了这样的情况：

for results in sqlresult:
        try:
                reportWriter.writerow(results)
         except UnicodeEncodeError:
                s = list(results)
                for item in s:
                        if isinstance(item, basestring) == True:
                                a = s.index(item)
                                unicodedata.normalize('NFKD', item).encode('ascii', 'ignore')
                                s[a] = item
                                print item
                s = tuple(s)
                print s
                reportWriter.writerow(s)

但是仍然会出现同样的错误：

UnicodeEncodeError: 'ascii' codec can't encode character u'\u2019' in position 366: ordinal not in range(128)

对于我做错了什么，或者我还能尝试什么，有什么想法吗？谢谢!

Answer 1

MySQL连接显然是以unicode对象的形式返回字符串，而传递给csv.writer的文件句柄不是用特定的编码打开的，因此期望得到表示原始字节的str对象。

解决方案1:在将字符串传递给writerow之前，用首选编码(想必是UTF-8)对字符串进行编码。

for results in sqlresult:
    results = [x.encode('utf-8') if isinstance(x, unicode) else x for x in results]
    reportWriter.writerow(results)

解决方案2:以Unicode感知模式打开输出文件句柄：

import io

reportWriter = csv.writer(io.open('%sFilename_comments_%s.csv' % (outputDir, reportDay), 'w', encoding='utf-8'), delimiter=',', quotec$