[sqlalchemy] Re: Unicode Results from SQL functions
On Wed, Sep 24, 2008 at 10:45 PM, jason kirtland [EMAIL PROTECTED]wrote: Adding ?charset=utf8use_unicode=1 to your MySQL connection URL is a much easier way to get Unicode back from all DB access. Ok, that works. I thought that create_engine(uri, encoding = latin1, convert_unicode = True) would do this. I am guessing from this that the create_engine arguments are NOT being passed along to the dbapi connector? --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unicode Results from SQL functions
Shawn Church wrote: On Wed, Sep 24, 2008 at 10:45 PM, jason kirtland [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Adding ?charset=utf8use_unicode=1 to your MySQL connection URL is a much easier way to get Unicode back from all DB access. Ok, that works. I thought that create_engine(uri, encoding = latin1, convert_unicode = True) would do this. I am guessing from this that the create_engine arguments are NOT being passed along to the dbapi connector? No. I believe both of those are specifying the treatment of string data going _to_ the DB-API only, not bidirectional behavior. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unicode Results from SQL functions
On Wed, Sep 24, 2008 at 11:04 PM, jason kirtland [EMAIL PROTECTED]wrote: Shawn Church wrote: On Wed, Sep 24, 2008 at 10:45 PM, jason kirtland [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Adding ?charset=utf8use_unicode=1 to your MySQL connection URL is a much easier way to get Unicode back from all DB access. Ok, that works. I thought that create_engine(uri, encoding = latin1, convert_unicode = True) would do this. I am guessing from this that the create_engine arguments are NOT being passed along to the dbapi connector? No. I believe both of those are specifying the treatment of string data going _to_ the DB-API only, not bidirectional behavior. OK, lets see, check database encoding, table encoding, column encoding, connection encoding/convert to unicode, sqlalchemy encoding/convert to unicode, and client encoding and if they all match up I should be good to go :-) Please don't take that as a criticism of SQLAlchemy which is an excellent package it just always amazes me how a simple (YES Unicode is SIMPLE) idea can get so complicated. Thanks again for the help, Shawn --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unicode Results from SQL functions
On Sep 25, 2008, at 2:19 AM, Shawn Church wrote: On Wed, Sep 24, 2008 at 11:04 PM, jason kirtland [EMAIL PROTECTED] wrote: Shawn Church wrote: On Wed, Sep 24, 2008 at 10:45 PM, jason kirtland [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Adding ?charset=utf8use_unicode=1 to your MySQL connection URL is a much easier way to get Unicode back from all DB access. Ok, that works. I thought that create_engine(uri, encoding = latin1, convert_unicode = True) would do this. I am guessing from this that the create_engine arguments are NOT being passed along to the dbapi connector? No. I believe both of those are specifying the treatment of string data going _to_ the DB-API only, not bidirectional behavior. OK, lets see, check database encoding, table encoding, column encoding, connection encoding/convert to unicode, sqlalchemy encoding/convert to unicode, and client encoding and if they all match up I should be good to go :-) Please don't take that as a criticism of SQLAlchemy which is an excellent package it just always amazes me how a simple (YES Unicode is SIMPLE) idea can get so complicated. use sqlite, and everything is unicode instantly ;) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unicode Results from SQL functions
On Thu, Sep 25, 2008 at 6:47 AM, Michael Bayer [EMAIL PROTECTED]wrote: Converting *all* strings to unicode would then lead to havoc as soon as someone adds a Binary column to their schema. Another solution, which we have support for, would be for ResultProxy to attempt to match TypeEngine objects to the DBAPI types in cursor.description. We already do this for Oracle binary types, so perhaps this functionality could be enabled for all types. Its backwards incompatible though so it would need to be a create_engine() option (I'd call it detect_result_types). A technical issue with this option is that it creates issues with table reflection which would have to be worked around somehow. The ?charset=utf8use_unicode=1 parameter produces the expected behavior. I DID check the documentation and mailing list archives (many posts) before raising this issue but I thought use_unicode and convert_unicode were different forms of the same thing. I in fact DID try ?convert_unicode=1 as a URI paramater which of course was incorrecct. I'll try to write up a wiki page on Unicode. I'm assuming that the '?uri_params' are specific to each driver (ie mysqldb) so the syntax for unicode support may be different for each? I'm also planning on using binary strings so I need to write some more tests, although I THINK I understand what is going on now. Thanks again, Shawn --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unicode Results from SQL functions
A proposed patch which addresses the issue at the SQLAlchemy ResultProxy level is at: http://www.sqlalchemy.org/trac/ticket/1179 On Sep 25, 2008, at 3:43 PM, Shawn Church wrote: On Thu, Sep 25, 2008 at 6:47 AM, Michael Bayer [EMAIL PROTECTED] wrote: Converting *all* strings to unicode would then lead to havoc as soon as someone adds a Binary column to their schema. Another solution, which we have support for, would be for ResultProxy to attempt to match TypeEngine objects to the DBAPI types in cursor.description. We already do this for Oracle binary types, so perhaps this functionality could be enabled for all types. Its backwards incompatible though so it would need to be a create_engine() option (I'd call it detect_result_types). A technical issue with this option is that it creates issues with table reflection which would have to be worked around somehow. The ?charset=utf8use_unicode=1 parameter produces the expected behavior. I DID check the documentation and mailing list archives (many posts) before raising this issue but I thought use_unicode and convert_unicode were different forms of the same thing. I in fact DID try ?convert_unicode=1 as a URI paramater which of course was incorrecct. I'll try to write up a wiki page on Unicode. I'm assuming that the '?uri_params' are specific to each driver (ie mysqldb) so the syntax for unicode support may be different for each? I'm also planning on using binary strings so I need to write some more tests, although I THINK I understand what is going on now. Thanks again, Shawn --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unicode Results from SQL functions
On Sep 24, 2008, at 10:13 PM, Shawn Church wrote: 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. the func.XXX() construct can draw upon a module of known functions such as CONCAT, in which case the return type of the function is known (sqlalchemy terms these generic functions). However, if the function name given is not a known function, then the return type is not known to be a string (which is necessary for convert_unicode to take effect).To coerce an arbitrary func to apply String processing to the result, use the type_ parameter: func.foo(arg1, arg2, ..., type_=Unicode) we can of course add more functions to the list of known functions such as ifnull() (it would be best if ifnull() is a SQL standard function, I'm not sure if it is). --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unicode Results from SQL functions
On Wed, Sep 24, 2008 at 7:37 PM, Michael Bayer [EMAIL PROTECTED]wrote: we can of course add more functions to the list of known functions such as ifnull() (it would be best if ifnull() is a SQL standard function, I'm not sure if it is). Not sure this will work for IFNULL since it's type depends upon the runtime arguments. I missed the func type_ argument when I read the documentation. That is a good solution for the general case of specifiying the type when it cannot be determined from the function or the function arguments. In fact I'm going to use it any time the type is not obvious. For what it is worth the following patch modifies ResultProxy to convert strings to unicode if convert_unicode == True. It 'fixes' my example and test/testall.py still passes. [EMAIL PROTECTED]:~/Projects/sqlalchemy$ svn diff Index: lib/sqlalchemy/engine/base.py === --- lib/sqlalchemy/engine/base.py(revision 5123) +++ lib/sqlalchemy/engine/base.py(working copy) @@ -1630,7 +1630,10 @@ if processor: return processor(row[index]) else: -return row[index] +result = row[index] +if isinstance(result, str) and self.context.dialect.convert_unicode: +result = result.decode(self.context.dialect.encoding) +return result def _fetchone_impl(self): return self.cursor.fetchone() [EMAIL PROTECTED]:~/Projects/sqlalchemy$ Thanks, Shawn --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unicode Results from SQL functions
Shawn Church wrote: On Wed, Sep 24, 2008 at 7:37 PM, Michael Bayer [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: we can of course add more functions to the list of known functions such as ifnull() (it would be best if ifnull() is a SQL standard function, I'm not sure if it is). Not sure this will work for IFNULL since it's type depends upon the runtime arguments. I missed the func type_ argument when I read the documentation. That is a good solution for the general case of specifiying the type when it cannot be determined from the function or the function arguments. In fact I'm going to use it any time the type is not obvious. For what it is worth the following patch modifies ResultProxy to convert strings to unicode if convert_unicode == True. It 'fixes' my example and test/testall.py still passes. Adding ?charset=utf8use_unicode=1 to your MySQL connection URL is a much easier way to get Unicode back from all DB access. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
