Hi all. Today I run into a problem with encoding and sqlalchemy... This is a very big topic and it's not sqlalchemy's fault (but rather the linuxbox fault is not configured to have UTF-8 as default encoding).
But still... Here comes the problem: I'm trying to insert a like like the following: INSERT INTO news (hash, author, date, rank, url, title, body, approved) VALUES (%(hash)s, %(author)s, %(date)s, %(rank)s, %(url)s, %(title)s, %(body)s, %(approved)s) The values of the dictionary are: body => u"MILA.." hash => u'36e4d5c08fffb85c0fdef5bc78c23be6' title => u" Diritti Mediaset, l'inchiesta \xe8 chiusa: Corruzione per Berlusconi" url => http://www.repubblica.it/2005/l/sezioni/cronaca/allibe2/richri/richri.html author => test rank => 0 date => datetime.datetime(2006, 3, 1, 19, 0, 59, 959806) approved => False As you can see all of the strings are unicode. Unfortunately when I try to run this query it ends up in: File "/usr/lib/python2.4/site-packages/sqlalchemy/sql.py", line 398, in execute return c.execute(*multiparams, **params) File "/usr/lib/python2.4/site-packages/sqlalchemy/sql.py", line 298, in execute return self.engine.execute_compiled(self, params) File "/usr/lib/python2.4/site-packages/sqlalchemy/engine.py", line 534, in execute_compiled proxy(str(compiled), parameters) File "/usr/lib/python2.4/site-packages/sqlalchemy/engine.py", line 529, in proxy self.execute(statement, parameters, connection=connection, cursor=cursor, return_raw=True) File "/usr/lib/python2.4/site-packages/sqlalchemy/engine.py", line 583, in execute self._execute(cursor, statement, parameters) File "/usr/lib/python2.4/site-packages/sqlalchemy/engine.py", line 598, in _execute raise exceptions.SQLError(statement, parameters, e) sqlalchemy.exceptions.SQLError: (UnicodeEncodeError) 'ascii' codec can't encode character u'\xe8' in position 31: ordinal not in range(128) As clearly showed by the traceback the problem lies in the raise which traps the cursor.execute(query, parameters) call. Now... psycopg2 is able to run a unicode query and is able to handle unicode parameters too, it actually exposes at least one API: conn.set_client_encoding('UNICODE') # 'UTF8' is good too I've tested this and it actually works after I call this function. Now... I'd like to have some way to tell sqlalchemy to call that API for me with the given argument, maybe an argument to the engine and each time self.connection() is called the method will also call set_client_encoding(). Adding this keyword argument at the end of Engine signature shouldn't cause any problems since at the end there's already **params and shouldn't break anything and since the change will be localized inside self.connection() it won't change the uniform interface between the many backends. What do you think? -- Valentino Volonghi aka Dialtone Now Running MacOSX 10.4 Blog: http://vvolonghi.blogspot.com New Pet: http://www.stiq.it ------------------------------------------------------- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642 _______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
