Re: [Pypgsql-users] Unicode question

[<gh...@gh...>]

Jim Hefferon wrote:
> Hello,
> 
> I'm having trouble with Unicode.  I have set up the database to use UTF-8.
> I am trying to get some non-ASCII strings in there and I am failing.
> 
> Here is a short version of the script:
> --------------------------------------
> #!/usr/bin/python -u
> # test unicode support in pyPgSQL
> import string;
> from pyPgSQL import libpq
> from pyPgSQL import PgSQL
> 
> dBconnection=PgSQL.connect("::ctanWeb:ftpmaint:")

First problem:

You need to tell *pyPgSQL*, which client encoding to use. Use the 
parameter client_encoding="utf-8". To also get back Unicode strings for 
text columns, use the parameter unicode_results=1.

> dBcursor=dBconnection.cursor()

Second problem: you need to tell the PostgreSQL backend in which client 
encoding you send your data and want it back:

dBcursor.execute("set client_encoding to unicode")

> dBcursor.execute("SELECT * FROM authors") 
> print dBcursor.fetchone()  # works fine; I'm connected
> 
> # these two also work fine
> dBcursor.execute("INSERT INTO authors (name,email) VALUES ('Mike Jones','mik...@ct...')")
> dBcursor.execute("INSERT INTO authors (name,email) VALUES (%(name)s,%(email)s)",{'name':'Mike Jones','email':'mik...@ct...'})
> 
> # this does not work
> dBcursor.execute("INSERT INTO authors (name,email) VALUES ('Mike Schr\xf6der','mik...@ct...')")

This cannot work, because it is not a valid UTF-8 string. Consider:

ISO-8859-1:	'Mike Schröder'
UTF-8:		'Mike Schr\xc3\xb6der'

> # I cannot get to run, even
> dBcursor.execute("INSERT INTO authors (name,email) VALUES (%(name)s,%(email)s)",{'name':u'Mike Schr\xf6der','email':u'mik...@ct...'})

This, however, will work with my above adjustments.

> [...]

-- Gerhard