dbapiext.py

Richard Jones alerted me to Martin Blais' dbapiext.py, and I like it very much.

Richard's post focuses on the improvements to bind variable handling. I love dbapiext.py for another reason: it provides query results as namedtuples, a class so perfect for database resultsets that they make me sob with relief.

>>> import cx_Oracle, dbapiext
>>> connection = cx_Oracle.connect('username/password@SID')
>>> results = dbapiext.execute_obj(connection, 'SELECT * FROM crew')
>>> row = results.next()
>>> row
Row(ID=1, NAME='Kaylee Frye', ROLE='Mechanic')
>>> row[1]
'Kaylee Frye'
>>> row.ROLE
'Mechanic'

When memory isn't an issue, I like to transform the result into a list and tack on a simple equality filter method for convenience.

class Filterable(list):
    def filter(self, **kw):
        for itm in self:
            for k in kw:
                if not (getattr(itm, k) == kw[k]):
                    break
            else:
                yield itm    
        yield StopIteration     

>>> result = Filterable(dbapiext.execute_obj(connection, 'SELECT * FROM crew')) 
>>> result.filter(NAME='Jayne Cobb').next()
Row(ID=5, NAME='Jayne Cobb', ROLE='Public Relations')

It is, of course, no SQLAlchemy, and isn't meant to be. It's meant to be a roadmap toward a less awful DB-API for Python. (I still wake up in the middle of the night, gasping, "Five incompatible bind variable formats! It's heresy!") In the meantime, dbapiext.py is a nice lightweight layer for times when SQLAlchemy is too much. For instance, I write lots of database administration tools that poke around in lots of different views of Oracle's data dictionary, and setting up SQLAlchemy mappings for each of them is a pain. dbapiext.py is perfect for that. Thanks, Martin!