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.
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]
The other possibility is to create a function that converts the type to the string representation and register the function with register_adapter.
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.