python encoding mysql :(

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!

Have you tried SELECT with python? It might be stored correctly but get messed up in console output of MySQL. — Rob Wouters, Feb 06 '12 at 02:32
The smily and the footer don't help you get answers, I would leave them out in future. — richo, Feb 06 '12 at 04:12

score 8 · Answer 1 · answered Feb 06 '12 at 02:30

8

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)

answered Feb 06 '12 at 02:30

Gareth Latty

86,389
17
178
183

thank Latty! but, by adding "use_unicode=True" continue the same problem :( – Emiliano Gustavo Nuñez Feb 06 '12 at 02:37
@HalNuevemil Hrm, I'm afraid I don't know then. I would check your console supports unicode, just in case it's in the DB fine but doesn't display correctly. – Gareth Latty Feb 06 '12 at 02:40
exctly Latty, I checked the table out by PhpMyAdmin Tool and the data is correct.. it maybe a visualization problem in mysql console. it's crazy because my locale is UTF-8 in my system.. – Emiliano Gustavo Nuñez Feb 06 '12 at 02:49
Thank you! This worked for me so I have a hunch you'll need to do this _and_ alter your database's collation, for example with; ALTER DATABASE database CHARACTER SET UTF8 COLLATE utf8_general_ci; – ZN13 Oct 21 '16 at 22:06
This answer was correct then? Should have accepted it – Aaron Nov 27 '16 at 02:24

score 3 · Answer 2 · edited Aug 20 '14 at 07:26

3

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

edited Aug 20 '14 at 07:26

fancyPants

50,732
33
89
96

answered Aug 20 '14 at 06:51

YEH

374
4
18

1

+1 Not clear why this was down-voted earlier. Useful information. – mkoistinen Aug 21 '14 at 10:37
The only thing working for me – Aleksei Petrenko Sep 05 '14 at 22:20

gfortune · Answer 3 · 2012-02-06T04:31:08.577

1

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.

edited Feb 06 '12 at 04:31

answered Feb 06 '12 at 03:00

gfortune

2,589
14
14

score 0 · Answer 4 · answered Feb 06 '12 at 03:33

0

try:

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

along with use_unicode = True.

answered Feb 06 '12 at 03:33

Wooble

87,717
12
108
131

I tried but continue the same problem, I think the data is enserted as ISO charset because when y get the data by php I get it in ISO and not in UTF. – Emiliano Gustavo Nuñez Feb 06 '12 at 04:27
more data: if I try this: mysql -u ibrick -pxx -h localhost ibrick -N -B -e "INSERT INTO test(test) VALUES ('ñ')" is OK, y see : | ñ | +------ but if I try with: #!/usr/bin/env python # -*- coding: utf-8 -*- import MySQLdb query = u"INSERT INTO test(test) VALUES ('ñ')" print query + "\n" conn = MySQLdb.connect (host = "localhost", user = "ibrick", passwd = "ibrick", db = "ibrick", charset="utf8", use_unicode=True) conn.names="utf8" cursor = conn.cursor() cursor.execute (query); cursor.close () conn.commit () I see | � | – Emiliano Gustavo Nuñez Feb 06 '12 at 04:42
Well, if you're saving the file in ISO-8859, don't tell Python it's UTF-8; set the `# -- coding:` to the actual file encoding you saved with. – Wooble Feb 06 '12 at 12:39
I want to save the data in UTF-8.. my system is UTF-8, mysql table is utf-8, the file script is utf-8, everything is UTF-8,, I don't know why the data is saved in ISO.. I'm really confused .. – Emiliano Gustavo Nuñez Feb 06 '12 at 12:58

python encoding mysql :(

4 Answers4

Linked