Simon Willison’s Weblog

SQLObject

My new favourite toy is SQLObject, an object-relational mapper which makes heavy use of Python’s special method names to create objects which can be used to transparently access and modify data in a relational database. I tried to write something like this in PHP once before and failed miserably, but SQLObject has such an elegant design that I’m just annoyed I didn’t find out about it sooner. Here’s some example code, adapted from the SQLOBject site:

from SQLObject import *
# Set up a database connection
__connection__ = PyPgSQLConnection()

# This class defines a table
class Person(SQLObject):
    firstName = StringCol(length=100)
    middleInitial = StringCol(length=1, default=None)
    lastName = StringCol(length=100)

# Now create the table (if running for the first time)
Person.createTable()

# Create a record for me
p = Person.new(firstName='Simon', lastName='Willison')

print p
# Outputs <Person 1 firstName='Simon' middleInitial=None lastName='Willison'>

# Set my middle initial (updates the database)
p.middleInitial = 'P'

# Print my full name
print p.firstName, p.middleInitial, p.lastName

SQLObject has plenty more tricks up its sleeve: it can create class definitions by introspecting a database table, handle one to many and many to many joins, and generate complicated SELECT statements on the fly using simple, database independant syntax. It comes with support for MySQL, Postgres and SQLite. Postgres support uses the psycopg module, but we use pyPgSQL so I wrote a simple connection wrapper to support that module which I’ve submitted to the SQLObject mailing list.