Dariusz Cieślak's Blog on Software

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!

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.