unicode error with postgre on 2.10.3 (with sample app)

[Ian W. Scott]

Hi folks,

I'm running into an error with 2.10.3 when I try to write unicode text (outside the Latin ranges) to a regular string field in a postgre database. Since my app is a greek language-learning application this error breaks things completely for me.

When I try, for example, to insert the string 'ἀγοραζε' I get the following error:

Traceback (most recent call last):

  File "applications/paideia/modules/paideia.py", line 168, in start
    pre_bug_step_id=pre_bug_step_id)
  File "applications/paideia/modules/paideia.py", line 384, in reply
    response_string)
  File "applications/paideia/modules/paideia.py", line 655, in _record_step
    log_record_id = db.attempt_log.insert(**log_args)
  File "/home/ian/web/web2py/gluon/packages/dal/pydal/objects.py", line 745, in insert
    ret =  self._db._adapter.insert(self, self._listify(fields))
  File "/home/ian/web/web2py/gluon/packages/dal/pydal/adapters/base.py", line 729, in insert
    raise e
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe1 in position 105: ordinal not in range(128)

The SQL being sent to the postgre adapter in this case was this:

INSERT INTO attempt_log(name,user_response,step,score,modified_on,dt_attempted,in_path,uuid) VALUES (19,'ἀγοραζε',773,1.0,'2015-04-10 12:33:25','2015-04-10 16:33:26',657,'5918a65a-6d6f-42c6-9af2-f5d0e313701e') RETURNING id;

When I execute that SQL command directly on the postgre database it doesn't raise any errors and the new row is created fine.

I've created a simple app that duplicates the error and attached the packed app here. Since the error doesn't happen with sqlite, I've set up the app db for a postgre database with the following connection details:

username = 'postgres'

postgre host = 'localhost'

postgre db_name = 'unicode_bug_test'

I've left the db password field empty in the db.py file for obvious reasons (i.e., my local password wouldn't do anyone any good anyway).

If you try to create a new record in the 'lemmas' table using appadmin with non-Latin text in the 'lemma' field (something like 'βλα') you should get the same error.

Ian

[Richard Vézina]

Can you please show the code that lead to this error?

If you hard code this you can't use a regular string (str()) which is ascii characters only you have to pass by unicode something like this :

string = u'ἀγοραζε'.decode(utf-8)

http://stackoverflow.com/questions/6289474/working-with-utf-8-encoding-in-python-source

If the greek character come form user input web2py deal with this to my knowledge... So I am pretty sure you hard code greek character in your code... So you need to read about encoding in python...

Richard

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ian W. Scott]

Richard,

I'm not sure what more you want to see. This error doesn't come from my code. If you look again at the traceback you'll see that the error happens in pydal after a simple db insert. The sample app reproduces the error with no business logic from my app at all--just doing an insert via the appadmin interface. 

Note that the issue here also isn't a deficiency in my understanding of unicode and python. I'm well aware of the pitfalls in python 2 and unicode. I've had this app working for months and it has been handling the unicode input just fine. The error only appeared after I upgraded the web2py version.

Ian

[Ian W. Scott]

In case it wasn't clear, to reproduce the error all you have to do is

1. Install the sample app I provided (and set up a postgre db for it to connect to)

2. Insert a new "lemmas" record using the appadmin interface

You can either type some greek characters into the 'lemma' field using a standard polytonic Greek keyboard or you can just cut and paste the Greek words from my question.

Ian

On Friday, April 10, 2015 at 2:05:25 PM UTC-4, Richard wrote:

[Ian W. Scott]

Also, as I pointed out in my question I can insert the same string (with the same encoding) directly into the postgre database using the raw SQL code that is being sent by pydal to the postgre adapter. I inserted a print statement into adapters/base.py to give me the exact SQL command, copied that SQL command (data and all), then ran it in pgAdmin with no problem. So it can't be a problem with the encoding of the string being received by the pydal.

Ian

On Friday, April 10, 2015 at 2:05:25 PM UTC-4, Richard wrote:

[Richard Vézina]

Ok, ok, I guess there is an issue with web2py or pyDAL... Can you test this with an earlier version of web2py? If it works consider open an issue in web2py on github with condition to reproduce the error and if it coming form pyDAL the issue will be migrate to pyDAL repo once the origin of the issue is found.

Richard

[Ian W. Scott]

Thanks Richard. My production server is working fine with 2.9.11. I'll open an issue on the web2py github repo, since it sounds like that's the better forum for this.

Ian

[Richard Vézina]

Ian, better make sure you can reproduce the problem... You may pack an app that allow developper to test the issue and attach it to your ticket...

Richard

[bodob...@gmail.com]

Hey guys,

2.10.3 doesn't seem ready for prime time :-( In fact this bug makes 2.10.3 unusable for postgres users with need for non ASCII-characters..

For reference let's link to the corresponding issue here too: https://github.com/web2py/web2py/issues/910

Is there any solution yet?

Bodo

[Massimo Di Pierro]

which postgresql version? I am failing to reproduce this.

[bodob...@gmail.com]

> which postgresql Version?

PSQL 9.3.6 @ Ubuntu 14.04.2 LTS

In the meantime, I played around with pgadmin a little bit:

1) Using pgadmin I can create records containing 'ἀγοραζε' (the sample above) or German Umlaute ä, ö, ü, so it's definetly no database problem.

2) Going to Appadmin https://testmachine/testapp/appadmin/select/db?query=db.test.id%3E0 displays the records without Problem

3) As soon as I edit and save, I'll get a

<type 'exceptions.UnicodeDecodeError'> 'ascii' codec can't decode byte 0xe1 in position 114: ordinal not in range(128)

Traceback (most recent call last):

  File "/home/www-data/web2py/gluon/restricted.py", line 227, in restricted
    exec ccode in environment
  File "/home/www-data/web2py/applications/testapp/controllers/appadmin.py", line 704, in <module>
  File "/home/www-data/web2py/gluon/globals.py", line 393, in <lambda>
    self._caller = lambda f: f()
  File "/home/www-data/web2py/applications/testapp/controllers/appadmin.py", line 343, in update
    if form.accepts(request.vars, session):
  File "/home/www-data/web2py/gluon/sqlhtml.py", line 1677, in accepts
    if pk:
  File "/home/www-data/web2py/gluon/packages/dal/pydal/objects.py", line 2117, in update
    uploadfolder = pjoin(
  File "/home/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 988, in update
    raise e
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe1 in position 114: ordinal not in range(128)

[Michele Comitini]

One more check... is the encoding of the database UTF-8?

you can check with psql -l

[Ruslan Gareev]

Hi, i have the same error, when I register user with ciryllic(Russian) chars in name. 

My config is: PgSQL 9.3. base in UTF-8. Web2py version is 2.10.3. Ubuntu 14.04.

среда, 29 апреля 2015 г., 0:31:53 UTC+5 пользователь mcm написал:

[bodob...@gmail.com]

In my case, it is UTF-8.

                                   List of databases
    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-------------+----------+----------+-------------+-------------+-----------------------
 test | manager  | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 |

It seems to be an issue with the default postgresql driver. After changing the connection string to

 db = DAL('postgres:psycopg2 ...) it worked flawless..

[Antonio Salazar]

I'm seeing this bug with PostgreSQL 9.3 and web2py 2.11.2-stable+timestamp.2015.05.30.16.33.24, both running on Windows 7 64 bit.

Updating a users's name to include non-ASCII characters using Database Administration yields:

<type 'exceptions.UnicodeDecodeError'> 'ascii' codec can't decode byte 0xc3 in position 57: ordinal not in range(128)

Example: changing "Jose" to "José"

[Massimo Di Pierro]

As stated in the previous thread. This is a bug in pg8000 (the default driver we ship with web2py). You need to install psycopg2.