python encoding mysql :( [duplicate]

Question

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!

Answer 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)