Is it possible to store Python class objects in SQLite?

SQLite 3's adaptors and converters

I'm surprised by how no one has read the docs for the SQLite 3 library, because it says that you can do this by creating an adaptor and converter. For example, let's say that we have a class called 'Point' and we want to store this and have this returned when selecting it and using the database cursor's fetchone method to return it. Let's make the module know that what you select from the database is a point

from sqlite3 import connect, register_adaptor, register_converter

class Point:
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)).encode('ascii')

def convert_point(s):
    x, y = list(map(float, s.split(b";")))
    return Point(x, y)

# Register the adapter
register_adapter(Point, adapt_point)

# Register the converter
register_converter("point", convert_point)

p = Point(4.0, -3.2)

# 1) Using declared types
con = connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
con.execute("create table test(p point)")
con.execute("insert into test(p) values (?)", (p,))
cur = con.execute("select p from test")
print("with declared types:", cur.fetchone()[0])
con.close()

# 1) Using column names
con = connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
con.execute("create table test(p)")
con.execute("insert into test(p) values (?)", (p,))
cur = con.execute('select p as "p [point]" from test')
print("with column names:", cur.fetchone()[0])
con.close()

Yes it's possible but there are different approaches and which one is the suitable one, will depend on your requirements.

• Pickling

  You can use the pickle module to serialize objects, then store these objects in a blob in sqlite3 (or a textfield, if the dump is e.g. base64 encoded). Be aware of some possible problems: questions/198692/can-i-pickle-a-python-dictionary-into-a-sqlite3-text-field

• Object-Relational-Mapping

  You can use object relational mapping. This creates, in effect, a "virtual object database" that can be used from within the programming language (Wikipedia). For python, there is a nice toolkit for that: sqlalchemy.

You can use pickle.dumps, its return pickable objects as strings, you would not need to write it to temporary files.

Return the pickled representation of the object as a string, instead of writing it to a file.

import pickle

class Foo:
    attr = 'a class attr'

picklestring = pickle.dumps(Foo)

You can't store the object itself in the DB. What you do is to store the data from the object and reconstruct it later.

A good way is to use the excellent SQLAlchemy library. It lets you map your defined class to a table in the database. Every mapped attribute will be stored, and can be used to reconstruct the object. Querying the database returns instances of your class.

With it you can use not only sqlite, but most databases - It currently also supports Postgres, MySQL, Oracle, MS-SQL, Firebird, MaxDB, MS Access, Sybase, Informix and IBM DB2. And you can have your user choose which one she wants to use, because you can basically switch between those databases without changing the code at all.

There are also a lot of cool features - like automatic JOINs, polymorphing...

A quick, simple example you can run:

from sqlalchemy import Column, Integer, Unicode, UnicodeText, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from random import choice
from string import letters

engine = create_engine('sqlite:////tmp/teste.db', echo=True)
Base = declarative_base(bind=engine)

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(Unicode(40))
    address = Column(UnicodeText, nullable=True)
    password = Column(String(20))

    def __init__(self, name, address=None, password=None):
        self.name = name
        self.address = address
        if password is None:
            password = ''.join(choice(letters) for n in xrange(10))
        self.password = password

Base.metadata.create_all()

Session = sessionmaker(bind=engine)
s = Session()

Then I can use it like this:

# create instances of my user object
u = User('nosklo')
u.address = '66 Some Street #500'

u2 = User('lakshmipathi')
u2.password = 'ihtapimhskal'

# testing
s.add_all([u, u2])
s.commit()

That would run INSERT statements against the database.

# When you query the data back it returns instances of your class:

for user in s.query(User):
    print type(user), user.name, user.password

That query would run SELECT users.id AS users_id, users.name AS users_name, users.address AS users_address, users.password AS users_password.

The printed result would be:

<class '__main__.User'> nosklo aBPDXlTPJs
<class '__main__.User'> lakshmipathi ihtapimhskal

So you're effectively storing your object into the database, the best way.