Trouble with utf-8 values in python

Question

I have an object (a variable) name, which gets its value from a PostgreSQL database via a SELECT statement, an it sometimes has special characters as ß, ä, ö...

Then I would like to insert that value into a table in a SQLite database. So I make a cursor cur on the table and prepare a SQL statement like this:

sql = 'insert into tbl values(?)'
cur.execute(sql, (name,))

That ends up with the exception, for example,

UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 6:  ordinal not in range(128)

The "position 6" is exactly the position of the special character, ß in this case. What to do?

Answer 1

While sqlite works with unicode out of the box it looks like the PostgreSQL adapter needs to be convinced first:

http://initd.org/psycopg/docs/usage.html#unicode-handling

Try adding the voodoo suggested above

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

to your script. I'm of course assuming you are using python 2.x and pyscopg2...