Setting the encoding in pysqlite2

[Gerhard Haering]

On Thu, Aug 25, 2005 at 01:15:55AM -0700, Michele Simionato wrote:
> An easy question, but I don't find the answer in the docs :-(
> I have a sqlite3 database containing accented characters (latin-1).
> How do I set the right encoding? For instance if I do this: [...]

You cannot set the encoding directly, because TEXT data in SQLite3
databases is expected to be in UTF-8 encoding. If you store "weird"
TEXT, you can work around it by using a custom converter in pysqlite2,
like in the following example:

#-*- encoding: latin-1 -*-
from pysqlite2 import dbapi2 as sqlite

# Register an additional converter for plain bytestrings
sqlite.register_converter("bytestring", str)

con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(t)")

testdata = "Häring" # bytestring in ISO-8859-1 encoding

cur.execute("insert into test(t) values (?)", (testdata,))

# Try to retrieve the test data, will fail
try:
    cur.execute("select t from test")
except UnicodeDecodeError:
    print "Could not decode latin1 as utf-8 (as expected)"

# Via the PARSE_COLNAMES trick, explicitly choose the bytestring converter
# instead of the default unicode one:
cur.execute('select t as "t [bytestring]" from test')
result = cur.fetchone()[0]
assert testdata == result
print "Correctly retrieved test data"

HTH,

-- Gerhard