python编码mysql :(

Question

我有以下python脚本(tes.py)：

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import MySQLdb

query = "INSERT INTO test(test) VALUES ('ñ')"
print query + "\n"

conn = MySQLdb.connect (host = "localhost", user = "ibrick", passwd = "x", db = "ibrick", charset="utf8")
conn.names="utf8"
cursor = conn.cursor()
cursor.execute (query);
cursor.close ()
conn.commit ()

文件编码utf-8：

 $ file -i tes.py 
tes.py: text/x-java charset=utf-8

系统编码UTF：

#locale
LANG=es_AR.UTF-8
LC_CTYPE="es_AR.UTF-8"
LC_NUMERIC="es_AR.UTF-8"
LC_TIME="es_AR.UTF-8"
LC_COLLATE="es_AR.UTF-8"
LC_MONETARY="es_AR.UTF-8"
LC_MESSAGES="es_AR.UTF-8"
LC_PAPER="es_AR.UTF-8"
LC_NAME="es_AR.UTF-8"
LC_ADDRESS="es_AR.UTF-8"
LC_TELEPHONE="es_AR.UTF-8"
LC_MEASUREMENT="es_AR.UTF-8"
LC_IDENTIFICATION="es_AR.UTF-8"
LC_ALL=
echo "ñññ" > /tmp/test.txt

file /tmp/test.txt 
/tmp/test.txt: UTF-8 Unicode text

编码UTF8的MYsql表：

mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                 |
+-------+----------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `test` varchar(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 
+-------+----------------------------------------------------------------------------------------------+

控制台输出正常：

#./tes.py 
INSERT INTO test(test) VALUES ('ñ')

问题：

脚本没有在表中插入ñ..它会插入一个错误的字符：

select * from test;
+------+
| test |
+------+
| �    | 
| �    | 
| �    | 
| �    | 
| �    | 
| �    | 
| �    | 
+------+
7 rows in set (0.00 sec)

有人来帮我吗？？

提前感谢！

Answer 1

尝试添加“use_unicode”。

的秘诀是在你的连接参数中添加一个charset=“utf8”和use_unicode=True。Source

db = MySQLdb.connect(host=DB_HOST, user=DB_USER, passwd=DB_PASS, db=DB_NAME, charset="utf8", use_unicode=True)

Answer 2

import MySQLdb

# connect to the database
db = MySQLdb.connect("****","****","****","****") #don't use charset here

# setup a cursor object using cursor() method
cursor = db.cursor()

cursor.execute("SET NAMES utf8mb4;") #or utf8 or any other charset you want to handle

cursor.execute("SET CHARACTER SET utf8mb4;") #same as above

cursor.execute("SET character_set_connection=utf8mb4;") #same as above

# run a sql question
cursor.execute("****")

...

#and make sure the mysql settings are correct, data too

Answer 3

MySQL客户端使用latin1作为其默认字符编码。请参阅http://dev.mysql.com/doc/refman/5.0/en/mysql-command-options.html#option_mysql_default-character-set

启动mysql客户端时使用--default-character-set=utf8。