### 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]