python encoding mysql :( [duplicate]

问题:

This question already has an answer here:

• Writing UTF-8 String to MySQL with Python 8 answers

I have the follow python script (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 ()

file encoding utf-8:

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

system encoding 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

MYsql table encoding UTF8:

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

Console outupt is OK:

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

problem:

The script doesn't insert ñ in the table.. it inserts a bad character:

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

Anybody help me??

Thank in advance!

回答1:

Try adding 'use_unicode'.

The secret ingredient is to add a charset=”utf8″ to your connection parameters, and use_unicode=True. Source

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

回答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

回答3:

The MySQL client uses latin1 as its default character encoding. See http://dev.mysql.com/doc/refman/5.0/en/mysql-command-options.html#option_mysql_default-character-set

Use --default-character-set=utf8 when starting the mysql client.

回答4:

try:

query = u"INSERT INTO test(test) VALUES ('ñ')"

along with use_unicode = True.