关于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")