Unicode -> Python -> DBAPI -> PyPgSQL -> PostgreSQL

[Gerhard Häring]

Rene Pijlman wrote:
> I can't seem to find any way to specify the character encoding with the DB
> API implementation of PyPgSQL. There is no mention of encoding and Unicode
> in the DB API v2.0 spec and the PyPgSQL README. [...]

See section 2.2.5 in the pyPgSQL README:

pyPgSQL has a few extensions that make it possible to insert Unicode strings
into PostgreSQL and fetch unicode strings instead of byte strings from the
database.

The module-level connect() function has two Unicode-related parameters:

- client_encoding
- unicode_results

*client_encoding* accepts the same parameters as the encode method
of Unicode strings. If you also want to set a policy for encoding
errors, set client_encoding to a tuple, like ("koi8-r", "replace")

Note that you still must make sure that the PostgreSQL client is
using the same encoding as set with the client_encoding parameter.
This is typically done by issuing a "SET CLIENT_ENCODING TO ..."
SQL statement immediately after creating the connection.

If you also want to fetch Unicode strings from the database, set
*unicode_results* to 1.

For example, assuming a database created with *createdb mydb -E UNICODE* 
and a
table *TEST(V VARCHAR(50))*:

     >>> from pyPgSQL import PgSQL
     >>> cx = PgSQL.connect(database="mydb", client_encoding="utf-8", 
unicode_results=1)
     >>> cu = cx.cursor()
     >>> cu.execute("set client_encoding to unicode")
     >>> cu.execute("insert into test(v) values (%s)", (u'\x99sterreich',))
     >>> cu.execute("select v from test")
     >>> cu.fetchone()
     [u'\x99sterreich']
     >>>


-- Gerhard