unicode queries throw errors

[Aaron Swartz]

>>> web.select('company', where="ticker=$t", vars={'t':u'\xf4'}).list()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/sw/lib/python2.5/site-packages/web/db.py", line 584, in select
    return query(qout, processed=True)
  File "/sw/lib/python2.5/site-packages/web/db.py", line 437, in query
    web.ctx.db_execute(db_cursor, sql_query)
  File "/sw/lib/python2.5/site-packages/web/db.py", line 338, in db_execute
    out = cur.execute(sql_query.s, sql_query.v)
UnicodeEncodeError: 'ascii' codec can't encode character u'\xf4' in position 0: ordinal not in range(128)

This is using psycopg2.

[Aaron Swartz (aaronsw)]

The fix appears to be running:

import psycopg2.extensions
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)

after psycopg2 is imported. And running:

web.ctx.db.set_client_encoding('UTF8')

after a DB cursor is created. Neither one seems to work by itself but together things seem to work properly (Unicode going in and out, as is the Python 3000 way).

[Aaron Swartz (aaronsw)]

moving up

[Anand Chitipothu (anandology)]

This doesn't work when using PooledDB.

Traceback (most recent call last):
  File "/private/tmp/webpy-0.23/test/webtest.py", line 50, in __call__
    self._initTest.setUpAll()
  File "test/db.py", line 10, in setUpAll
    web.query("CREATE TABLE person (name text, email text)")
  File "./web/db.py", line 449, in query
    db_cursor = web.ctx.db_cursor()
  File "./web/db.py", line 335, in db_cursor
    web.ctx.db.set_client_encoding('UTF8')
  File "build/bdist.macosx-10.4-fat/egg/DBUtils/PooledDB.py", line 341, in __getattr__
AttributeError: SteadyDBConnection instance has no attribute 'set_client_encoding'

[Aaron Swartz (aaronsw)]

Can we wrap pooleddb.creator?

[Anand Chitipothu (anandology)]

On Dec 24, 2007 9:21 PM, Aaron Swartz <email address hidden> wrote:
> Can we wrap pooleddb.creator?

We can, but I don't want to try all that for 0.23. Can we move this bug to 0.3?

[Aaron Swartz (aaronsw)]

I'd prefer to keep it in 0.23 if possible; I think it's seriously
embarrassing that Unicode does not work right. Anyone who tries to
build an app with web.py right now will get something that appears to
work as long as they only use Latin characters and then the first time
someone from another country visits their site their app will crash.
That's not really acceptable.

[Anand Chitipothu (anandology)]

On Dec 24, 2007 9:47 PM, Aaron Swartz <email address hidden> wrote:
> I'd prefer to keep it in 0.23 if possible; I think it's seriously
> embarrassing that Unicode does not work right. Anyone who tries to
> build an app with web.py right now will get something that appears to
> work as long as they only use Latin characters and then the first time
> someone from another country visits their site their app will crash.
> That's not really acceptable.

Here is the fix. Is this OK?

+ # fix for Bug#177265
+ if web.ctx.get('db_name') == "postgres":
+ try:
+ if web.config._hasPooling:
+ # hack to call a function in actual db connection
+ web.ctx.db._con._con.set_client_encoding('UTF8')
+ else:
+ web.ctx.db.set_client_encoding('UTF8')
+ except Exception, e:
+ print >> 'web.debug', 'Error in setting utf-8
encoding:', str(e), '(ignored)'
+

[Aaron Swartz (aaronsw)]

That looks good to me. It needs to be tested, obviously, but it makes sense.

[Aaron Swartz (aaronsw)]

Also be careful where you put that -- if you call it on a pool that
doesn't have a _con yet it won't work.

[Anand Chitipothu (anandology)]

On Dec 24, 2007 10:36 PM, Aaron Swartz <email address hidden> wrote:
> That looks good to me. It needs to be tested, obviously, but it makes
> sense.

I have a written a test for it and it passes.

[Anand Chitipothu (anandology)]

On Dec 24, 2007 10:37 PM, Aaron Swartz <email address hidden> wrote:
> Also be careful where you put that -- if you call it on a pool that
> doesn't have a _con yet it won't work.

It that possible? Do you how reach that state?

[Anand Chitipothu (anandology)]

Opps. Closed this bug without committing the code. Committed now.