Dynamic Tables in SQLObject + SQLite

Update Feb 7: SQLite dynamic table support is now in the SQLObject trunk – so, version 0.7.4 should support this.

The current version of SQLObject has this nice ability to automatically build classes from the tables that already exist in the database. All you have to do is set the fromDatabase variable in sqlmeta to be true for the given table, i.e.:

class Test(SQLObject): class sqlmeta: fromDatabase = True table = 'test'

Unfortunately, SQLObject doesn’t support the “fromDatabase” sqlmeta option, which is kind of annoying, as it means that you have to write different code for SQLite vs. MySQL/Postgres. This isn’t an entirely unique situation – this question seems to pop up on the SQLObject forums every so often. Googling around quite a bit, however, revealed a solution… posted in 2003! I’m not sure why it was never merged in, but with minor changes it works with the latest existing SQLite/SQLObject. All you need is one routine added to sqliteconnection.py in SQLObject:

def columnsFromSchema(self, tableName, soClass): """Originally from: http://pythonpaste.org/archives/message/20031110.025226.4e31bdb1.en.html slightly modified for latest SQLObject/SQLite by Tim Lopez @ www.brool.com Look at the given table and create Col instances (or subclasses of Col) for the fields it finds in that table. """ def istype(s, tl): for t in tl: if s.startswith(t): return True return False def guessClass(t): if t.startswith('int'): return sqlobject.col.IntCol, {} elif istype(t, ['varchar', 'char']): return sqlobject.col.StringCol, {} elif istype(t, ['text']): return sqlobject.col.StringCol, {} elif t.startswith('blob'): return sqlobject.col.BLOBCol, {} elif istype(t, ['datetime']): return sqlobject.col.DateTimeCol, {} elif istype(t, ['float', 'real']): return sqlobject.col.FloatCol, {} elif t.startswith('bool'): return sqlobject.col.BoolCol, {} else: return sqlobject.col.Col, {} fieldqry = "PRAGMA table_info(%s)" colData = self.queryAll(fieldqry % tableName.upper()) results = [] for pos, field, t, nullAllowed, thedefault, primaryKey in colData: if field == 'id': continue colClass, kw = guessClass(t.encode('utf-8')) kw['name'] = soClass.sqlmeta.style.dbColumnToPythonAttr(field). encode('utf-8') kw['notNone'] = not nullAllowed kw['default'] = thedefault results.append(colClass(**kw)) return results

One note: if you want to run SQLObject unchanged, you can always monkeypatch it in, like:

def sqlite_columnsFromSchema(self, tableName, soClass): # ... routine as above ... sqlobject.sqlite.sqliteconnection.SQLiteConnection.columnsFromSchema = sqlite_columnsFromSchema connection_string = 'sqlite:///home/tim/sqlite/test.db' connection = connectionForURI(connection_string) # so on, so forth

Discussion

Comments are moderated whenever I remember that I have a blog.

There are no comments on this article.