Python - mysqldb inserting unicode fails

Question

Python 2.6.1, mysql 5.1 on osx snow leopard.

In my python code to connect I am doing; use_unicode=True, charset = "utf8"

mysql tells me

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

So we are all good there. My table structure is defined as utf8

CREATE TABLE `urls` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `url` varchar(300) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `url_idx` (`url`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

My statement is like

insert("INSERT INTO urls (url) VALUES (%s)", (url, ))

but with a unicode string I get an error

UnicodeEncodeError: 'ascii' codec can't encode character u'\xb4' in position 7: ordinal not in range(128)

I am clue less....

• Sam: Whats the url column collation?

Answer 1

The problem is not your database. It doesn't even get that far. You are relying on Python's string manipulation here:

insert("INSERT INTO urls (url) VALUES (%s)" % (url, ))

Never do this. It is bad because not only are you trying to insert a unicode string into an ASCII one, you are also leaving yourself open to SQL injection attacks. Instead, do this (assuming your insert function maps to some call in MySQLdb):

insert("INSERT INTO urls (url) VALUES (%s)", (url, ))

The difference is that you are now getting MySQLdb to insert the values, thus ensuring they will be encoded and quoted properly.

• Wizzard: Sorry my mistake, my code in the question was wrong. I do use the proper technique as you show, I have it separated out in my program in two lines. Sorry again. So, it's something else.
• Daniel Roseman: In that case, please show the actual code and the full traceback.

Answer 2

For me, I would change the default setting of mysql. How to? Open my.cnf and add two lines in the session [mysqld] like this:

[mysqld]
32 #
33 # * Basic Settings
34 #
35 user = mysql
36 pid-file = /var/run/mysqld/mysqld.pid
37 socket = /var/run/mysqld/mysqld.sock
38 character-set-server = utf8
39 collation-server = utf8_unicode_ci

the last two line (line 38 and 39) are what I add. And then, restart you mysql server, and remember recreate you database and tables. After doing this, I think it should works. I have tried it and it did work.