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

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

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

把这句SQL组装出来，一般能找到问题。还有其他可能是：

1，数据库编码忘记设置字符集，建表不加字符集默认就是拉丁。插入中文会报错。

2，SQL语句个别字段有单双引号或者有转义符。

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

CREATE DATABASE `mydb` CHARACTER SET utf8 COLLATE utf8_general_ci;

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

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