Skip to content

Entries from November 2008.

Automatically migrate SQL schema in SQLObject

When you're building an application you have to extends your model (and alter database schema that is placed under the model). I've found a method to automatically upgrade SQL schema (only ADD COLUMN operations for now):
from sqlobject import *
import logging

class Base(SQLObject):

    def migrate(self, *args, **kv):

        conn = self._connection
        schema = {}
        cols = self.sqlmeta.columns

        for a in conn.columnsFromSchema(self.sqlmeta.table, self):
            schema[a.name] = a
        logging.debug("existing schema: %s" % schema)

        for name, col in cols.iteritems():
            if name not in schema.keys():
                logging.info("add column: %s.%s" % (self.__class__.__name__, name))
                conn.addColumn(self.sqlmeta.table,col)

    migrate = classmethod(migrate)
migrate() class method only handles simplest case (adding a new column), but it may be sufficient in typical development process (more advanced transformations will still require SQL migration scripts written by hand). Enjoy!

How to make SQLObject instances "picklable"?

I tried to store SQLObject instances in a session, but had problems with pickling them. Here is smart base class for model classes that allow to fast pickle / unpickle operation:
from sqlobject import *
import logging

class Base(SQLObject):

    def __getstate__(self):
        return self.id

    def __setstate__(self,id):
        obj = self.__class__.get(id)
        self.__dict__ = obj.__dict__
Just inherit from that class and that's all. Remember: restoring object from db is cheap: SQLObject caches instances in memory.