Brool brool (n.) : a low roar; a deep murmur or humming

Dynamic Tables in SQLObject + SQLite

 |  coding

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.

Add a comment