python含中文字符串插入mysql失败

Question

在执行这段语句是出现了下面的错误，弄了一晚上没解决，哪位大神帮忙看下

con=MySQLdb.connect(host="127.0.0.1",port=3306,user="root",passwd="root",db="mysql")
cur=con.cursor()
for line in new_insert:
    rank,forum,title,author,follow,title_time=line
    print chardet.detect(rank)
    print chardet.detect(forum)
    print chardet.detect(title)
    print chardet.detect(author)
    print chardet.detect(follow)
    print chardet.detect(title_time)

    forum=forum.decode('ascii').encode('utf-8')
    author=author.decode('ascii').encode('utf-8')
    follow=follow.decode('ascii').encode('utf-8')
    title_time=title_time.decode('ascii').encode('utf-8')
    cur.execute("SET NAMES utf8")
    sql="INSERT INTO bbs VALUES(%s,%s,%s,%s,%s,%s)"%(rank,forum,title,author,follow,title_time)
    result=cur.execute(sql)
    if result!=1:
        print rank,forum,title,author,forum,"insert error"
        con.rollback()
        continue
    con.commit()
cur.close()
con.close()

{'confidence': 0.7525, 'encoding': 'utf-8'}
{'confidence': 1.0, 'encoding': 'ascii'}
{'confidence': 0.99, 'encoding': 'utf-8'}
{'confidence': 1.0, 'encoding': 'ascii'}
{'confidence': 1.0, 'encoding': 'ascii'}
{'confidence': 1.0, 'encoding': 'ascii'}
Traceback (most recent call last):
  File "E:/spider/main.py", line 6, in <module>
    cre.run()
  File "E:\spider\bbs_top10.py", line 91, in run
    result=cur.execute(sql)
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 \xe5\x90\x8d,Girls,\xe8\xaf\xb7\xe5\xa4\xa7\xe7\xa5\x9e\xe5\xb8\xae\xe5\xbf\x99\xe7\x9c\x8b\xe7\x9c\x8b\xe5\x92\x8c\xe5\xa5\xb3\xe5\xad\xa9\xe5\xad\x90\xe7\x9a\x84\xe8\x81\x8a\xe5\xa4\xa9\xe9\x97\xae\xe9\xa2\x98\xe5\x87\xba\xe5\x9c\xa8\xe5\x93\xaa\xe9\x87\x8c\xe5\x91\xa2,niha' at line 1")

Process finished with exit code 1

Answer 1

把这句SQL组装出来，一般能找到问题。还有其他可能是：
1，数据库编码忘记设置字符集，建表不加字符集默认就是拉丁。插入中文会报错。
2，SQL语句个别字段有单双引号或者有转义符。

Answer 2

需要设置数据库的默认编码，比如

 CREATE DATABASE `mydb` CHARACTER SET utf8 COLLATE utf8_general_ci;

Answer 3

你把你执行的sql打印出来，放在mysql数据库执行，看能否成功，先看一下是不是你sql语法的问题。

Answer 4

请查看你数据库编码是否是utf-8,如果不是的话，需要设置下。

Answer 5

如果按以上答案设置了还是不行的话 把链接设置个UTF编码就可以了
db = MySQLdb.connect("xxx","xxx","xxx","xxx",charset="utf8")