Python mysql coding :(

Question

I have the following 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 () 

utf-8 file encoding:

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

UTF system coding:

 #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 UTF8 table designation:

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

The console output is in order:

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

Problem:

the script does not insert - into the table .. it inserts a bad character:

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

Does anyone help me?

Thanks in advance!

Answer 1

Try adding 'use_unicode'.

The secret ingredient is to add 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) 

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

The MySQL client uses latin1 as the default 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.

Answer 4

to try:

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

along with use_unicode = True .