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.
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:
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.