13.13.4.3 Converting SQLite values to custom Python types

Python 2.5

previous page next page

13.13.4.3 Converting SQLite values to custom Python types

Writing an adapter lets you send custom Python types to SQLite. But to make it really useful we need to make the Python to SQLite to Python roundtrip work.

Enter converters.

Let's go back to the Point class. We stored the x and y coordinates separated via semicolons as strings in SQLite.

First, we'll define a converter function that accepts the string as a parameter and constructs a Point object from it.

Note: Converter functions always get called with a string, no matter under which data type you sent the value to SQLite.

Note: Converter names are looked up in a case-sensitive manner.

    def convert_point(s):
        x, y = map(float, s.split(";"))
        return Point(x, y)

Now you need to make the sqlite3 module know that what you select from the database is actually a point. There are two ways of doing this:

Implicitly via the declared type
Explicitly via the column name

Both ways are described in ``Module Constants'', section 13.13.1, in the entries for the constants PARSE_DECLTYPES and PARSE_COLNAMES.

The following example illustrates both approaches.

import sqlite3

class Point(object):
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __repr__(self):
        return "(%f;%f)" % (self.x, self.y)

def adapt_point(point):
    return "%f;%f" % (point.x, point.y)

def convert_point(s):
    x, y = map(float, s.split(";"))
    return Point(x, y)

# Register the adapter
sqlite3.register_adapter(Point, adapt_point)

# Register the converter
sqlite3.register_converter("point", convert_point)

p = Point(4.0, -3.2)

#########################
# 1) Using declared types
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test(p point)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute("select p from test")
print "with declared types:", cur.fetchone()[0]
cur.close()
con.close()

#######################
# 1) Using column names
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(p)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute('select p as "p [point]" from test')
print "with column names:", cur.fetchone()[0]
cur.close()
con.close()

See About this document... for information on suggesting changes.

previous page start next page