Python连接mysql OperationalError: (1366, "Incorrect string value: 'xxx' for column 'xxx' at row 1")解决方法，,python向mysql

Python连接mysql OperationalError: (1366, "Incorrect string value: 'xxx' for column 'xxx' at row 1")解决方法，,python向mysql

python向mysql表插入数据时出现：

OperationalError: (1366, "Incorrect string value: 'xxx' for column 'xxx' at row 1")

这个错误的原因是mysql数据库表或者列的字符集不对，通常情况下是因为设置成了ladin字符集导致的，解决此问题的办法是修改表字段的字符集，下面的python代码片段可以将数据库中所有表的字段的字符集都修改为utf-8.

#! /usr/bin/env pythonimport MySQLdbhost = "localhost"passwd = "passwd"user = "youruser"dbname = "yourdbname"db = MySQLdb.connect(host=host, user=user, passwd=passwd, db=dbname)cursor = db.cursor()cursor.execute("ALTER DATABASE `%s` CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'" % dbname)sql = "SELECT DISTINCT(table_name) FROM information_schema.columns WHERE table_schema = '%s'" % dbnamecursor.execute(sql)results = cursor.fetchall()for row in results:  sql = "ALTER TABLE `%s` convert to character set DEFAULT COLLATE DEFAULT" % (row[0])  cursor.execute(sql)db.close()