Обсуждение: Weird unicode problem

Hi list,

We use Postgresql 9.3 on Ubuntu 14.04 with psycopg2 and python2.7 on Ubuntu 12.04

Now we have a Euro sign (€) in a text field. When I try to retrieve the row I get the following traceback:

2015-04-15 14:48:00,918 ERROR comsupport.dick SQLDict 326 SELECT "per_id", "lob_id", "lob_datum", "soortentry",
"gelezen","txthdr", "lob_text", "ondhpgm", "mutmed", "mutdat", "per_naam1", "lob_categorie", "prive", "classificatie 
", "entry_owner", "dossier", "toturen", "ncat_id", "cli_id", "doel_tekst", "doel_gehaald", "doel_tonen", "doel_title",
"doel_plandate","hdoel_id", "tab_id", "doel_id", "tab_naam", "tab_naam_kort", "ingangsdatum", "vervaldatum",  
"note_sjabloon_title", "note_sjabloon", "mimetype" FROM "comsupport"."vwclasslogboek"  sqd1  WHERE per_id = 61 AND
(classificatie in (3679,3386,0) or classificatie is null) AND lob_categorie in ('E','H') order by  lob_datum DESC 
, mutdat DESC
2015-04-15 14:48:00,919 ERROR comsupport.dick SQLDict 327 parameters ()
2015-04-15 14:48:00,920 ERROR comsupport.dick SQLDict 328 Exception
Traceback (most recent call last):
  File "/opt/CVix-prod/src/SQLConnect/SQLDict.py", line 319, in executeSQL
    self._resultcursor.execute(stmt)
DataError: character with byte sequence 0xe2 0x82 0xac in encoding "UTF8" has no equivalent in encoding "LATIN1"

But I don't understand this because I have added a type caster to the connection like so:

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

Now I thought that after adding this, conversion to unicode is executed automagically????

Cheers,
D. Kniep
Lindix BV

On Wed, Apr 15, 2015 at 2:50 PM, Dick Kniep <dick@lindix.nl> wrote:
> DataError: character with byte sequence 0xe2 0x82 0xac in encoding "UTF8" has no equivalent in encoding "LATIN1"

This is a PostgreSQL error: most likely your connection encoding is in
LATIN1 instead of UTF8. Proof is also that you get the error on
execute (which is normally when the data is transferred to psycopg)
and not on fetch*() (when the typecasters are applied).

Try running "show client_encoding" in you database in psql to check if
this is the case. If not, there could be something in your python code
changing the encoding: check the value of cnn.encoding where the code
is executed.

The client encoding can be changed in psycopg with a
cnn.set_client_encoding('utf8').

-- Daniele