关于MySQLdb的编码
Python连接MySQL时需要用到MySQLdb,按照给出的例子,操作MySQL的代码大致如下:
""" Python to MySQL using mysql-python """
import MySQLdb
# Establich a connection
conn = MySQLdb.connection(host = "192.168.200.66",
user = "trainee",
passwd = "abc123",
port = 3306,
db = "forsale")
# Run a MySQL query from Python and get the result set
cursor = conn.cursor()
sql = 'select version();'
cursor.execute(sql)
result = cursor.fetchall()
print result[0][0]
cursor.close()
conn.close()
不过,在执行一些包含UTF8字符的SQL语句时,往往会见到这样的错误:
'ascii' codec can't encode character ......'
或者
'latin-1' codec can't encode character ......'
这种情况下,即使在py文件的头部加上
# -*- coding: utf-8 -*-
并且在连接语句中加上两个参数use_unicode和charset
conn = MySQLdb.connection(host = "192.168.200.66", user = "trainee", passwd = "abc123", port = 3306, db = "forsale", use_unicode = True, charset = "utf-8")
也无济于事.
问题的根源似乎在于,MySQLdb的cursor在执行execute操作时,运行了如下代码(cursor.py第146行):
charset = db.character_set_name() query = query.encode(charset)
我发现,无论怎么指定编码,db.character_set_name()获得的,似乎都是latin-1或者ascii(与报错语句提示的codec一致),这样的codec当然无法编码UTF-8的字符了.
db.character_set_names()到底在哪里设置的,我跟了许久也无从知道,即使这样,还是有办法解决这个问题:
1. 在query = query.encode(charset)直接加上一句charset = "utf-8"
这是最笨的方法,改动最小的权宜之计,硬编码,只能应付UTF-8的SQL情况;
2. 修改cursor的execute函数,增加一个char参数.
原来的execute是这样定义的:
def execute(self, query, args = None):
修改之后为
def execute(self, query, char = "utf-8", args = None):
然后将
charset = db.character_set_name() query = query.encode(charset)
改为
charset = char query = query.encode(charset)
此后如果要执行包含GBK字符的SQL语句,只要在调用execute函数时增加设定char = "GBK",就可以了.比如:
cursor.execute("SELECT fromtime, totime FROM xctime", char = "GBK")