I am trying to construct a select query in mysql (version
5.0.51a-3ubuntu5.3-log) using SQL functions. Once I set the convert_unicode
flag = True on my engine some function results are returned as type str and
some results are returned as type unicode (I want, and expected, all
unicode). Although this problem can be resolved with an explict cast I
suspect there might be a deeper problem.
It seems that when an obvious string function is used (i.e. CONCAT in my
case) then unicode is correctly returned. However when the return type is
dependent on the results of the query (i.e. IFNULL, could return any type
dependent on the arguments) then a str is returned.
Am I just missing something or is this a problem with mysql or is there some
other problem? Sample code and output is included below.
Thanks in advance,
Shawn
======= Sample Output ==========
Executing command:
/usr/lib/python2.5/site-packages/aPyIdea-0.1.0a1-py2.5.egg/apyidea/pause.py
/usr/bin/python /home/schurch/Projects/MTZAuto/mtzauto/model/test.py
Simple query, Unicode OK: [(1L, u'a', u'b'), (2L, u'c', u'd')]
IFNULL, No Unicode: [('a', 'b'), ('c', 'd')]
CAST to Unicode, Unicode OK: [(u'a', u'b'), (u'c', u'd')]
CAST to String, Unicode OK: [(u'a', u'b'), (u'c', u'd')]
CONCAT(results), Unicode OK: [(u'a', u'b'), (u'c', u'd')]
Press <enter> to close window
========= test.py ==============
from elixir import *
from sqlalchemy import select, func, or_, create_engine, union, cast
class Test(Entity):
field1 = Field(Unicode(50))
field2 = Field(Unicode(50))
if __name__ == "__main__":
setup_all()
uri = "mysql://test:[EMAIL PROTECTED]/test"
engine = create_engine(uri, encoding = "latin1", convert_unicode = True)
engine.execute('SET collation_connection = "latin1_general_ci"')
metadata.bind = engine
metadata.bind.echo = False
drop_all()
create_all()
Test(field1 = u"a", field2 = u"b")
Test(field1 = u"c", field2 = u"d")
session.commit()
t = Test.table
q = select([Test.table])
# Simple query
print "Simple query, Unicode OK:", list(session.execute(q))
# Query with IFNULL functions that return strings
field2 = func.ifnull(t.c.field2, u"Unknown")
field1 = func.ifnull(t.c.field1, field2)
q = select([field1, field2])
print "\nIFNULL, No Unicode:", list(session.execute(q))
# Results of IFNULL explicity CAST to Unicode
field2 = cast(func.ifnull(t.c.field2, u"Unknown"), Unicode)
field1 = cast(func.ifnull(t.c.field1, field2), Unicode)
q = select([field1, field2])
print "\nCAST to Unicode, Unicode OK:", list(session.execute(q))
# Results of IFNULL explicity CAST to String
field2 = cast(func.ifnull(t.c.field2, u"Unknown"), String)
field1 = cast(func.ifnull(t.c.field1, field2), String)
q = select([field1, field2])
print "\nCAST to String, Unicode OK:", list(session.execute(q))
# Results of IFNULL used in CONCAT
field2 = func.concat(func.ifnull(t.c.field2, u"Unknown"))
field1 = func.concat(func.ifnull(t.c.field1, field2))
q = select([field1, field2])
print "\nCONCAT(results), Unicode OK:", list(session.execute(q))
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
