CODEWITHSUNDEEP
pythonmysqlunicodeutf-8
NlightNFotis
NlightNFotis

Reputation: 9803

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

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?

Upvotes: 1

Views: 82

Answers (2)

NlightNFotis
NlightNFotis

Reputation: 9803

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.

Upvotes: 1

Daniel Costa
Daniel Costa

Reputation: 440

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;

Upvotes: 0

Related Questions