Re: [pysqlite] python an sqlite objects

# This is an example for storing pickleable Python objects in a SQLite
# database

import cPickle as pickle

try:
    from pysqlite2 import dbapi2 as sqlite3
except ImportError:
    import sqlite3

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

    def __repr__(self):
        return "<Point(%s, %s)>" % (self.x, self.y)

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

    # Make sure you store your pickled 
    # cur.execute("create table pickled(id integer primary key, data blob)")
    cur.execute("create table pickled(id integer primary key, data blob)")

    # Here we force pickle to use the efficient binary protocol
    # (protocol=2). This means you absolutely must use an SQLite BLOB field
    # and make sure you use sqlite3.Binary() to bind a BLOB parameter.
    p1 = Point(3, 4)
    cur.execute("insert into pickled(data) values (?)", (sqlite3.Binary(pickle.dumps(p1, protocol=2)),))

    # If we use old pickle protocol (protocol=0, which is also the default),
    # we get away with sending ASCII bytestrings to SQLite.
    p2 = Point(-5, 3.12)
    cur.execute("insert into pickled(data) values (?)", (pickle.dumps(p2, protocol=0),))

    # Fetch the BLOBs back from SQLite
    cur.execute("select data from pickled")
    for row in cur:
        serialized_point = row[0]

        # Deserialize the BLOB to a Python object - # pickle.loads() needs a
        # bytestring.
        point = pickle.loads(str(serialized_point))
        print "got point back from database", point

if __name__ == "__main__":
    test()