On Jun 21, 2011, at 6:07 AM, Adam Bielański wrote:
> Hello.
>
> I'm using Python 2.6, SQLAlchemy 0.7.1, MySQL 5.5 and MySQLdb 1.2.3.
>
> I'm creating engine with following connect string:
> mysql+mysqldb://localhost/test_1?charset=utf8&sql_mode=STRICT_ALL_TABLES' and
> convert_unicode parameter set to True.
>
> Now when I call connection.execute(sql) I'm receiving utf-8 encoded strings
> for each entry from VARCHAR column. The same thing happens when I call
> query(myModel).all(). From what I can see in SA docs I should've receive
> unicode objects instead.
>
> When I change my connect string by adding use_unicode=0, apparently SA starts
> to convert rows returned by MySQLdb and query(myModel).all() returns unicode
> objects, not strings. But still connection.execute(sql) returns strings
> encoded with utf-8.
>
> I suppose that MySQLdb fails to convert strings to unicode, and when I
> explicitly mark that I don't want such conversion with use_unicode=0, SA adds
> processor to each VARCHAR column but it works only when I use ORM constructs.
> When I try executing raw SQL queries SA doesn't do any type conversion.
>
> Now is this expected behaviour, or is SA supposed to convert all string
> output to unicode when convert_unicode is set to True, even when it is result
> of plain SQL execution?
>
> Does anyone know how to fix it - should I set some additional flag in MySQLdb
> or in SA when I create engine or connection?
connection.execute("some sql string") does not perform any result set
processing - you get back pretty much exactly what MySQLdb would give you. If
you set the MySQLdb use_unicode flag to 1, it should start returning Python
unicode objects natively. In the past we've recommended against this flag as
MySQLdb had a very large and obvious memory leak when it was used, but its very
likely this is no longer the case.
A simple test confirms the unicode behavior:
from sqlalchemy import *
e = create_engine('mysql://scott:tiger@localhost/test?use_unicode=1',
echo='debug')
print repr(e.execute("select 'hi'").scalar())
assert e.dialect.returns_unicode_strings
e = create_engine('mysql://scott:tiger@localhost/test?use_unicode=0',
echo='debug')
print repr(e.execute("select 'hi'").scalar())
assert not e.dialect.returns_unicode_strings
Only when types are used, such as String or Unicode, does SQLAlchemy have the
instruction to coerce to unicode. String will coerce to unicode if the
convert_unicode=True flag is set. Unicode does it unconditionally.
Note the "returns_unicode_strings" dialect flag - true in one case, false in
the other. The engine does a test on first connect to see if the DBAPI is
giving us unicode. If so, the String and Unicode types from that point on will
do nothing, assuming the DBAPI is doing it for us.
To get SQLAlchemy to do a unicode coercion on a raw SQL string when the DBAPI
is not doing it, use the text() construct:
http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=text#sqlalchemy.sql.expression.text
>
> Regards,
> Adam Bielański.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
