[sqlalchemy] Unicode problem with MySQL-Table with collation utf8_bin and utf8_general_ci

[Marko Krause]

Hello,

I have a problem with sqlalchemy 5.8 and mysql 5.1. I have a table
with a text-column, which has the collation utf8_general_ci. When I
access the table sqlalchemy returns a Unicode, as it was expected. But
when I change the collation of the column to utf8_bin, sqlalchemy
returns an utf8-encoded string.
The mysql-engine is created with "?charset=utf8" and I'm using Python
2.6.5 on Ubuntu Lucid.
Here is a example query:
>>> selectTexts = sqlalchemy.select([
table.c.id,
table.c.text,
]).where(and_(table.c.id==id));
>>> result = conn.execute(selectTexts).fetchall();
>>> print result[0][1]

With utf8_general_ci I get:
u'test \xe4 \xf6 \xfc'

and with utf8_bin I get:
'test \xc3\xa4 \xc3\xb6 \xc3\xbc'

I this behaviour intended? Is there a way to get Unicode from utf8_bin-
Tables/Columns?

Thanks in advance,
Marko

[Michael Bayer]

specify use_unicode=0 on your MySQL engine. SQLAlchemy will perform the utf-8 decode instead.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Marko Krause]

Thanks for the help so far.

What I'm looking for is a possibility to get always unicode, not
utf-8. My current workaround is to decode the result after every
select into unicode if necessary.

[Michael Bayer]

"utf-8 decode" in this case means utf8 data will be received from the database and decoded into a Python unicode object before returning the value to your application. This is a function of the String type. It only occurs if the DBAPI has been detected as not returning Python unicode objects for VARCHAR types - this should happen if you set use_unicode=0 with MySQLdb.