Inserting Unicode text with MySQLdb in Python 2.4-2.5?

[Keith Hughitt]

Hi all,

I ran into a problem recently when trying to add support for earlier
versions of Python (2.4 and 2.5) to some database related code which
uses MySQLdb, and was wondering if anyone has any suggestions.

With later versions of Python (2.6), inserting Unicode is very simple,
e.g.:

    # -*- coding: utf-8 -*-
    ...
    cursor.execute('''INSERT INTO `table` VALUES (0,
'Ångström'),...''')

When the same code is run on earlier versions, however, the results is
either garbled text (e.g. "Ã or "?" instead of "Å" in Python 2.5), or
an exception being thrown (Python 2.4):

    UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in
position 60: ordinal not in range(128)

So far I've tried a number of different things, including:

    1. Using Unicode strings (e.g. u"\u212B")

    2. Manually specifying the encoding using sys.setdefaultencoding
('utf-8')

    3. Manually enabling Unicode support in MySQLdb
(use_unicode=False, charset = "utf8")

...but no combination of any of the above resulted in proper database
content.

To be certain that the issue was related to Python/MySQLdb and not
MySQL itself, I manually inserted the text and it worked just fine.
Furthermore, when working in a Python console, both print "Å" and
print u"\u212B" display the correct output.

Any ideas? The versions of the MySQLdb adapter tested were 1.2.1
(Python 2.4), and 1.2.2-10 (Python 2.5).

Thanks!
Keith