[DB-SIG] SQL storage for Python objects

Andy Dustman adustman@comstar.net
Wed, 2 Sep 1998 16:53:39 -0400 (EDT)

On Tue, 1 Sep 1998, Patrick Curtain wrote:

> Has anyone developed a useful idiom or pattern for storing python
> objects to relational databases?  Said another way, is there a
> well-defined API that improves on simply embedding all the SQL for the
> operations on an object?

I've got an SQLDict module that I developed for internal use. It basically
wraps a dictionary-like interface around a Python DB-API SQL database.
Once I had the basic idea, it was ridiculously simple to write. There are
some Builder classes which can be used to make useful objects that the
database knows how to handle. For example, for the schema:

Create table Users (
	Handle		varchar, 
	UNIXpasswd	varchar,
	Mailbox		varchar not null,
	DomainName	varchar not null references MailDomains,
	primary key	(Mailbox, DomainName)
	) ;

I can make a object which conforms to this:

class User(ObjectBuilder):

    table = 'Users'
    columns = ['Handle', 'Mailbox', 'DomainName', 'UNIXpasswd', ...]
    updatecolumns = columns[3:]
    indices = [ ('Handle', ['Handle']),
		('Email', ['Mailbox', 'DomainName']),
		('Domain', ['DomainName'])]
Once I make a SQLDict object, I can do:

db.User = User().register(db)
u = db.User.Handle['freddy'].fetchone()
u2 = db.User.Email['fred','nowhere.org'].fetchone()
ul = db.User.Domain['nowhere.org'].fetchall()

Object attributes are those listed in columns. updatecolumns is used so
that certain columns are not updated (to avoid referential integrity

db.User.Handle['freddy'] = u
del db.User.Email['joe','blow.com']

Fancier stuff is possible:

db.User.delete( (1,), WHERE="WHERE Active <>?")

If you'd rather user your own objects than ObjectBuilder subclasses, you
can also make it use tuples instead of objects.

I'll see if I can release this code, but it's only like 100 lines of
actual code.

Andy Dustman                                 WW           Charles Babbage:
ComStar Communications Corp.                 BB       He never used Linux,
(706) 549-7689 | PGP KeyID=0xC72F3F1D        D?        and now, he's dead.