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!

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

Gareth Latty · Accepted Answer · 2012-02-06 02:30:25Z

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 6, 2012 at 2:30

Gareth Latty

89.5k18 gold badges183 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Emiliano Gustavo Nuñez Over a year ago

thank Latty! but, by adding "use_unicode=True" continue the same problem :(

Gareth Latty Over a year ago

@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.

Emiliano Gustavo Nuñez Over a year ago

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..

ZN13 Over a year ago

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;

Aaron Over a year ago

This answer was correct then? Should have accepted it

fancyPants · Accepted Answer · 2014-08-20 07:26:31Z

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, 2014 at 7:26

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

answered Aug 20, 2014 at 6:51

YEH

3945 silver badges18 bronze badges

2 Comments

mkoistinen Over a year ago

+1 Not clear why this was down-voted earlier. Useful information.

Aleksei Petrenko Over a year ago

The only thing working for me

gfortune · Accepted Answer · 2012-02-06 04:31:08Z

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 6, 2012 at 4:31

answered Feb 6, 2012 at 3:00

gfortune

2,63916 silver badges14 bronze badges

Comments

Wooble · Accepted Answer · 2012-02-06 03:33:55Z

0

try:

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

along with use_unicode = True.

answered Feb 6, 2012 at 3:33

Wooble

90.5k12 gold badges111 silver badges132 bronze badges

4 Comments

Emiliano Gustavo Nuñez Over a year ago

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 Over a year ago

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 | � |

Wooble Over a year ago

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.

Emiliano Gustavo Nuñez Over a year ago

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 ..

Collectives™ on Stack Overflow

python encoding mysql :( [duplicate]

4 Answers 4

5 Comments

2 Comments

Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

Comments

4 Comments

Linked

Related