python mysql编码_Python和MySql:Unicode和编码

我正在解析json数据，并试图将一些json数据存储到Mysql数据库中。我目前正在跟踪unicode错误。我的问题是我该怎么处理。我应该从数据库的角度来处理它，如果是的话，我应该如何修改表来处理它？

我应该从python端处理它吗？

这是我的桌子结构CREATE TABLE yahoo_questions (

question_id varchar(40) NOT NULL,

question_subj varbinary(255),

question_content varbinary(255),

question_userId varchar(40) NOT NULL,

question_timestamp varchar(40),

category_id varbinary(20) NOT NULL,

category_name varchar(40) NOT NULL,

choosen_answer varbinary(255),

choosen_userId varchar(40),

choosen_usernick varchar(40),

choosen_ans_timestamp varchar(40),

UNIQUE (question_id)

);

通过python代码插入时出错：Traceback (most recent call last):

File "YahooQueryData.py", line 78, in

+"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", (row[2], row[5], row[6], quserId, questionTime, categoryId, categoryName, qChosenAnswer, choosenUserId, choosenNickName, choosenTimeStamp))

File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/MySQLdb/cursors.py", line 159, in execute

query = query % db.literal(args)

File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/MySQLdb/connections.py", line 264, in literal

return self.escape(o, self.encoders)

File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/MySQLdb/connections.py", line 202, in unicode_literal

return db.literal(u.encode(unicode_literal.charset))

UnicodeEncodeError: 'latin-1' codec can't encode characters in position 204-230: ordinal not in range(256)

Python代码段：#pushing user id to the url to get full json stack

urlobject = urllib.urlopen(base_url.format(row[2]))

qnadatajson = urlobject.read()

data = json.loads(qnadatajson)

cur.execute("INSERT INTO yahoo_questions (question_id, question_subj, question_content, question_userId, question_timestamp,"

+"category_id, category_name, choosen_answer, choosen_userId, choosen_usernick, choosen_ans_timestamp)"

+"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", (row[2], row[5], row[6], quserId, questionTime, categoryId, categoryName, qChosenAnswer, choosenUserId, choosenNickName, choosenTimeStamp))

json结构questions: [

{

Id: "20111201185322AA5HTDc",

Subject: "what are the new pokemon call?",

Content: "I used to know them I stop at dialga and palkia version and I heard there's new ones what's it call

",

Date: "2011-12-01 18:53:22",

Timestamp: "1322794402",

在运行查询之前，我还对mysql SET character_set_client = utf8执行了以下操作

mysql变量看起来是这样的：mysql> SHOW variables LIKE '%character_set%';

+--------------------------+--------------------------------------------------------+

| Variable_name | Value |

+--------------------------+--------------------------------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql-5.5.10-osx10.6-x86_64/share/charsets/ |

+--------------------------+--------------------------------------------------------+

8 rows in set (0.00 sec)