13.13.4.2 Using adapters to store additional Python types in SQLite databases

Python 2.5

previous page next page

13.13.4.2 Using adapters to store additional Python types in SQLite databases

As described before, SQLite supports only a limited set of types natively. To use other Python types with SQLite, you must adapt them to one of the sqlite3 module's supported types for SQLite: one of NoneType, int, long, float, str, unicode, buffer.

The sqlite3 module uses Python object adaptation, as described in PEP 246 for this. The protocol to use is PrepareProtocol.

There are two ways to enable the sqlite3 module to adapt a custom Python type to one of the supported ones.

13.13.4.2.1 Letting your object adapt itself

This is a good approach if you write the class yourself. Let's suppose you have a class like this:

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

Now you want to store the point in a single SQLite column. First you'll have to choose one of the supported types first to be used for representing the point. Let's just use str and separate the coordinates using a semicolon. Then you need to give your class a method __conform__(self, protocol) which must return the converted value. The parameter protocol will be PrepareProtocol.

import sqlite3

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

    def __conform__(self, protocol):
        if protocol is sqlite3.PrepareProtocol:
            return "%f;%f" % (self.x, self.y)

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

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print cur.fetchone()[0]

13.13.4.2.2 Registering an adapter callable

The other possibility is to create a function that converts the type to the string representation and register the function with register_adapter.

Note: The type/class to adapt must be a new-style class, i. e. it must have object as one of its bases.

import sqlite3

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

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

sqlite3.register_adapter(Point, adapt_point)

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

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print cur.fetchone()[0]

The sqlite3 module has two default adapters for Python's built-in datetime.date and datetime.datetime types. Now let's suppose we want to store datetime.datetime objects not in ISO representation, but as a Unix timestamp.

import sqlite3
import datetime, time

def adapt_datetime(ts):
    return time.mktime(ts.timetuple())

sqlite3.register_adapter(datetime.datetime, adapt_datetime)

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

now = datetime.datetime.now()
cur.execute("select ?", (now,))
print cur.fetchone()[0]

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

previous page start next page