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 -~----------~----~----~----~------~----~------~--~---