Psycopg and queries with UTF-8 data

[Diez B. Roggisch]

Alban Hertroys wrote:

> I have a query that inserts data originating from an utf-8 encoded XML
> file. And guess what, it contains utf-8 encoded characters...
> Now my problem is that psycopg will only accept queries of type str, so
> how do I get my utf-8 encoded data into the DB?

This sounds like the usual unicode/utf-8 confusion: unicode is an abstract
specification of characters, utf-8 as well as latin1 and ascii are
encodings of that specification that allow for certain characters to be
used - namely, ascii for only well-known first 127, latin1 for some major
european languages, and utf-8 defines escapes for all possible characters
defined in unicode - with the result that some of the characters aren't one
byte per character anymore.

So unicode objects encapsulate abstract unicode character sequence - however
they accomplish that is not of your concern. strings on the opposite, are
pure byte sequences - and common libs work with them, with the exception of
the usually unicode aware xml libs. So to yield a string from an unicode
object, one has to specify an encoding - like utf-8 or latin1. Now having a
character in that unicode object that can't be encoded using the specified
encoding, that will produce an error.


Please do read a tutorial on unicode and python - there are several good
ones out there, use google to your advantage.

> 
> I can't do query.encode('ascii'), that would be similar to:
>  >>> x = u'\xc8'
>  >>> print x.encode('ascii')
> Traceback (most recent call last):
>    File "<stdin>", line 1, in ?
> UnicodeEncodeError: 'ascii' codec can't encode character u'\xc8' in
> position 0: ordinal not in range(128)

Sure- xC8 > 127, so it can't be encoded. Do this:

>>> x = u'\xc8'
>>> x
u'\xc8'
>>> x.encode('utf-8')
'\xc3\x88'

As you can see, the formerly one byte long character becomes two bytes. The
reason is that on unicode character is translated to that 2-byte sequence
using utf-8. 

> I also tried setting PostgreSQL's client-encoding by executing "SET
> client_encoding TO 'utf-8'", but psycopg still only accepts str-type
> strings (which is not really surprising).

Confusion again - please repeat: 

unicode is not utf-8!!!
unicode is not utf-8!!!
unicode is not utf-8!!!
unicode is not utf-8!!!

Do encode the unicode object in utf-8, and pass that to the psycopg. If you
set client_encoding to latin1, you have to encode unicod to that.

-- 
Regards,

Diez B. Roggisch