2

I'm facing the following problem: I have a MySQL database which contains non-ascii characters in some records. Characters like "ñ", for example.

What kind of setup do I need to perform at the database, in order to deliver non-ascci to a Python application?

To give you more detail. Until now, I changed all the character_set_* and collation_* variables to UTF8 . But is still not working.. Before these modifications, the charset and collation were configured to latin-1.

MySQL

`mysql> SHOW VARIABLES LIKE "character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE "collation%";
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)


mysql> SHOW FULL COLUMNS FROM tjs_stage.avisos WHERE  Collation="utf8_unicode_ci"\G;
*************************** 1. row ***************************
Field: Titulo
Type: varchar(32)
Collation: utf8_unicode_ci
Null: YES
Key: 
Default: NULL
Extra: 
Privileges: select,insert,update,references
Comment: 
*************************** 2. row ***************************
Field: Aviso
Type: varchar(256)
Collation: utf8_unicode_ci
Null: NO
Key: 
Default: NULL
Extra: 
Privileges: select,insert,update,references
Comment: 
2 rows in set (0.00 sec)


mysql> SELECT * FROM tjs_stage.avisos \G;
*************************** 1. row ***************************
Id: 1
Titulo: PRIMERO AVISO
Aviso: MAÑANA EMPIEZAM VACACIONES
Creacion: 2016-04-06 18:12:52
Modificacion: 2016-04-06 18:12:52
*************************** 2. row ***************************
Id: 49
Titulo: Aviso de Vacaciones
Aviso: Mañana empezam las vacaciones!
Creacion: 2016-05-26 21:46:34
Modificacion: 2016-05-26 21:46:34
2 rows in set (0.00 sec)`

Python

Here is the code which I developed just for test purposes:

`#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb
import sys

try:
    con = MySQLdb.connect(host = "127.0.0.1", user = "root", passwd = "drootmysql", charset='utf8')  
    print "\nMySQL connection: OK\n"

except:
    print "\nConnection failed...\n"
    sys.exit()


cur = con.cursor()

insert_query = "INSERT INTO tjs_stage.avisos (Titulo,Aviso) VALUES('Aviso de Vacaciones','Mañana empezam las vacaciones!')"  
cur.execute(insert_query)
con.commit()

select_query = "SELECT * FROM tjs_stage.avisos"
cur.execute(select_query)
con.commit()

records = cur.fetchall()

for row in records:
    print row`

Here's the result: `$ python mysql_json_data.py

MySQL connection: OK


(1L, 'Aviso de Vacaciones', 'Ma\xc3\xb1ana empezam las vacaciones!', datetime.datetime(2016, 5, 26, 16, 38, 40), datetime.datetime(2016, 5, 26, 16, 38, 40))  `

I just want to retrieve the data as it is from the database. Is there any other setup that should perform in MySQL in order to server the non-ascii characters as it is ?

Thanks in advance guys!

1

Is mysql_json_data.py input or output or code or a dump?

'MA\xd1ANA... has Ñ encoded in latin1 (and perhaps escaped for display?)
'Ma\xc3\xb1ana... has ñ encoded in utf8 (and perhaps escaped for display?)

If you have one file with both in it, you have a mess.

The former will probably be truncated as you attempt to put it into your MySQL table. The latter will probably insert correctly.

See my notes on Python.

  • Hi Rick! The former line was a garbage.. I'll update the question. – ivanleoncz Jun 9 '16 at 1:15
  • I added a link to my answer. – Rick James Jun 9 '16 at 2:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.