Python连接mysql OperationalError: (1366, &Incorrect string value: 'xxx' for column 'xxx' at row 1&)解决方法 - 为程序员服务
为程序员服务
Python连接mysql OperationalError: (1366, &Incorrect string value: 'xxx' for column 'xxx' at row 1&)解决方法
python向mysql表插入数据时出现：
OperationalError: (1366, "Incorrect string value: 'xxx' for column 'xxx' at row 1")
这个错误的原因是mysql数据库表或者列的字符集不对，通常情况下是因为设置成了ladin字符集导致的，解决此问题的办法是修改表字段的字符集，下面的python代码片段可以将数据库中所有表的字段的字符集都修改为utf-8.
#! /usr/bin/env python
import MySQLdb
host = &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'& % dbname
cursor.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()
您可能的代码
相关聚客文章
荣誉：1344
相关专栏文章Incorrect string value: &\xA0\xE5\xA5\xBD\xEF\xBC...& for column &bt& at row 1
Incorrect string value: &\xA0\xE5\xA5\xBD\xEF\xBC...& for column &bt& at row 1
&我的网站怎么用不了中文？？现这一行字Incorrect string value: '\xA0\xE5\xA5\xBD\xEF\xBC...' for column 'bt' at row 1
不区分大小写匿名
相关知识等待您来回答
互联网领域专家Posts - 122,
Articles - 33,
Comments - 4
自制力是人生的方向舵！
04:34 by LvSir, ... 阅读,
解决Incorrect integer value: '' for column 'id' at row 1
最近自己在测试一个开源的程序，测试中发现。该程序都添加和更新的时候回出现 Incorrect integer value: '' for column 'id' at row 1类是的错误！
后来我自己测试了一下：
insert into log values('','admin','31','002t')
这样写就会报错： Incorrect integer value: '' for column 'id' at row 1
一般我们是认为应该没错误的。后来查了下MYSQL的资料。发现5以上的版本如果是空值应该要写NULL
这种问题一般mysql 5.x上出现。我用的mysql5.1
官方解释说：得知新版本mysql对空值插入有"bug",要在安装mysql的时候去除默认勾选的enable strict SQL mode
那么如果我们安装好了mysql怎么办了，解决办法是更改mysql中的配置 my.ini
my.ini中查找sql-mode，
默认为sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"，
将其修改为sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"，重启mysql后即可
那么如果是虚拟主机或者是空间怎么办了。如果你能让空间商帮你改那是最好。如果不能，那你就只能程序改改。都规范一点。空值就写 nullDjango报错“_mysql_exceptions.Warning: Incorrect string value: ‘\xE6\xB5…’ for column ‘name’ at row 1”
在使用Django连到我的VPS上的MySQL数据库，在插入数据时，报了这样一个错误：“_mysql_exceptions.Warning: Incorrect string value: ‘\xE6\xB5\x81\xE8\xA1\x8C…’ for column ‘name’ at row 1”。
我使用了中文的数据，看起来就是一个字符集不兼容的错误；Django默认使用UTF-8，而mysqld那边配置是默认使用了latin1 – default collation 。
解决办法如下：
1. 修改 /f 配置文件，然后重启mysqld。
在[client]下配置：default-character-set=utf8
在[mysqld]下配置：
default-character-set=utf8
init_connect=’SET NAMES utf8′
注意：新版MySQL(如：5.5）或MariaDB等，mysqld启动时可能会遇到“[ERROR] /usr/libexec/mysqld: unknown variable ‘default_character_set=utf8’”的错误；就应该在[mysqld]中用 character_set_server=utf8 替换掉 default_character_set=utf8 。
2.如果还没有解决，那么就得删掉原来建的DB，重新建并制定字符集为utf8，如：CREATE DATABASE `jay_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_
附我遇到的报这个错的堆栈信息：
Traceback (most recent call last):
File &/Users/jay/workspace/music-web/music/category.py&, line 49, in &module&
add(data=[{'id': 1, 'name': '流行音乐'}])
File &/Users/jay/workspace/music-web/music/category.py&, line 38, in add
a_se.save()
File &/Library/Python/2.7/site-packages/rest_framework/serializers.py&, line 594, in save
[self.save_object(item, **kwargs) for item in self.object]
File &/Library/Python/2.7/site-packages/rest_framework/serializers.py&, line 1041, in save_object
obj.save(**kwargs)
File &/Library/Python/2.7/site-packages/django/db/models/base.py&, line 591, in save
force_update=force_update, update_fields=update_fields)
File &/Library/Python/2.7/site-packages/django/db/models/base.py&, line 619, in save_base
updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
File &/Library/Python/2.7/site-packages/django/db/models/base.py&, line 700, in _save_table
result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
File &/Library/Python/2.7/site-packages/django/db/models/base.py&, line 733, in _do_insert
using=using, raw=raw)
File &/Library/Python/2.7/site-packages/django/db/models/manager.py&, line 92, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File &/Library/Python/2.7/site-packages/django/db/models/query.py&, line 921, in _insert
return query.get_compiler(using=using).execute_sql(return_id)
File &/Library/Python/2.7/site-packages/django/db/models/sql/compiler.py&, line 920, in execute_sql
cursor.execute(sql, params)
File &/Library/Python/2.7/site-packages/django/db/backends/utils.py&, line 81, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File &/Library/Python/2.7/site-packages/django/db/backends/utils.py&, line 65, in execute
return self.cursor.execute(sql, params)
File &/Library/Python/2.7/site-packages/django/db/backends/mysql/base.py&, line 128, in execute
return self.cursor.execute(query, args)
File &/Library/Python/2.7/site-packages/MySQL_python-1.2.4b4-py2.7-macosx-10.9-intel.egg/MySQLdb/cursors.py&, line 204, in execute
if not self._defer_warnings: self._warning_check()
File &/Library/Python/2.7/site-packages/MySQL_python-1.2.4b4-py2.7-macosx-10.9-intel.egg/MySQLdb/cursors.py&, line 117, in _warning_check
warn(w[-1], self.Warning, 3)
_mysql_exceptions.Warning: Incorrect string value: '\xE6\xB5\x81\xE8\xA1\x8C...' for column 'name' at row 1
Related Posts
Related Posts
目前还没有任何评论.
2014年十一月
10111213141516
17181920212223
2425262930