import sqlite3

con = sqlite3.connect(":memory:")

cur = con.cursor()

# Create the table

con.execute("create table person(lastname, firstname)")

AUSTRIA = u"\xd6sterreich"

# by default, rows are returned as Unicode

cur.execute("select ?", (AUSTRIA,))

row = cur.fetchone()

assert row[0] == AUSTRIA

# but we can make pysqlite always return bytestrings ...

con.text_factory = str

cur.execute("select ?", (AUSTRIA,))

row = cur.fetchone()

assert type(row[0]) == str

# the bytestrings will be encoded in UTF-8, unless you stored garbage in the

# database ...

assert row[0] == AUSTRIA.encode("utf-8")

# we can also implement a custom text_factory ...

# here we implement one that will ignore Unicode characters that cannot be

# decoded from UTF-8

con.text_factory = lambda x: unicode(x, "utf-8", "ignore")

cur.execute("select ?", ("this is latin1 and would normally create errors" + u"\xe4\xf6\xfc".encode("latin1"),))

row = cur.fetchone()

assert type(row[0]) == unicode

# pysqlite offers a builtin optimized text_factory that will return bytestring

# objects, if the data is in ASCII only, and otherwise return unicode objects

con.text_factory = sqlite3.OptimizedUnicode

cur.execute("select ?", (AUSTRIA,))

row = cur.fetchone()

assert type(row[0]) == unicode

cur.execute("select ?", ("Germany",))

row = cur.fetchone()

assert type(row[0]) == str