Unable to enter unicode strings into MySQL using MySQLdb module in python

Question

I am using the MySQLdb python module to connect to a database. The method I am connecting to it is the following:

import MySQLdb as mysql

mysql_connection = mysql.connect(user=user, passwd=password, db=db, 
                                 charset='utf8', use_unicode=True)
cursor           = mysql_connection.cursor()

# error checking snip here
# (ommitted for brevity)

return (mysql_connection, cursor)

Against this connection, I am executing queries that contain utf-8 strings (unicode objects in python), like this:

[DEBUG] INSERT INTO Clients(clientid, login, pname, email) VALUES (304, 'sample_username', 'Φώτης Κ', '[email protected]');

However I find that the data entered in the actual database are wrong, and are actually presented like this:

??????? ????????

I have actually confirmed that mysql is setup to accept unicode strings, as I have executed queries by hand that contain utf-8 characters and they are successful.

The result of the SHOW VARIABLES LIKE "character_set%" command is the following:

mysql> SHOW VARIABLES LIKE "character_set%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

and my schema for that particular table (at least for the relevant columns) is this:

DROP TABLE IF EXISTS `Clients`;
CREATE TABLE `Clients` (
    ...
    `login` VARCHAR(200) CHARACTER SET utf8,
    `pname` VARCHAR(255) CHARACTER SET utf8,
    `email` VARCHAR(255) CHARACTER SET utf8,
    ...
    );

Also, my terminal is setup to have $LC_ALL and $LANG setup to el_GR.utf8. What is possibly at fault here?

NlightNFotis · Accepted Answer · 2015-07-28 13:24:09Z

Sigh...

As with everything, I found the answer after I experimented a bit more myself. It seems that everything inserted is actually there, just that the MySQL Client didn't show it correctly.

As you could see from the character set results I posted in the question:

mysql> SHOW VARIABLES LIKE "character_set%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

character_set_results are set to latin1. Executing SET character_set_results=utf8; managed to fix everything and it's working as expected.

Daniel Costa · Accepted Answer · 2015-07-28 13:15:00Z

0

Try executing the query below just after connecting, before any other that may contain utf8 characters:

SET NAMES utf8

UPDATE

You should try SHOW VARIABLES LIKE "character_set%"; from your program to be sure that everything is set to utf8.

As you said just SET NAMES didn't work, try being more verbose:

SET NAMES utf8;
SET CHARACTER SET utf8;
SET character_set_connection=utf8;

edited Jul 28, 2015 at 13:15

answered Jul 28, 2015 at 13:00

Daniel Costa

4403 silver badges11 bronze badges

2 Comments

NlightNFotis Over a year ago

I tried it in the past, and I have tried it again. It doesn't help. Besides, the existence of charset='utf8', use_unicode=True in the connection parameters are supposed to cause that MySQL instruction irrelevant.

Daniel Costa Over a year ago

Try outputing the content in hex, so we can see what's going on with the contents, using function HEX(str)... also, just to be sure, did you INSERT data using SET NAMES before? I mean, if you inserted other way/with other client, and now is SELECTing it, it may not work.

Collectives™ on Stack Overflow

Unable to enter unicode strings into MySQL using MySQLdb module in python

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related