1 import sqlite3

     2 

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

     4 cur = con.cursor()

     5 

     6 # Create the table

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

     8 

     9 AUSTRIA = u"\xd6sterreich"

    10 

    11 # by default, rows are returned as Unicode

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

    13 row = cur.fetchone()

    14 assert row[0] == AUSTRIA

    15 

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

    17 con.text_factory = str

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

    19 row = cur.fetchone()

    20 assert type(row[0]) == str

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

    22 # database ...

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

    24 

    25 # we can also implement a custom text_factory ...

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

    27 # decoded from UTF-8

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

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

    30 row = cur.fetchone()

    31 assert type(row[0]) == unicode

    32 

    33 # pysqlite offers a builtin optimized text_factory that will return bytestring

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

    35 con.text_factory = sqlite3.OptimizedUnicode

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

    37 row = cur.fetchone()

    38 assert type(row[0]) == unicode

    39 

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

    41 row = cur.fetchone()

    42 assert type(row[0]) == str