Python Cookbook

Establishing Database Connections Lazily

Credit: John B. Dell’Aquila

Problem

You want to access a relational database via lazy connections (i.e., connections that are only established just in time) and access query results by column name rather than number.

Solution

Lazy (just-in-time) operation is sometimes very handy. This recipe transparently wraps any DB API-compliant interface (DCOracle, odbc, cx_oracle, etc.) and provides lazy evaluation and caching of database connections and a one-step query facility with data access by column name. As usual, a class is the right way to package this wrapper:

class Connection: """ Lazy proxy for database connection """ def _ _init_ _(self, factory, *args, **keywords): """ Initialize with factory method to generate DB connection (e.g., odbc.odbc, cx_Oracle.connect) plus any positional and/or keyword arguments required when factory is called. """ self._ _cxn = None self._ _factory = factory self._ _args = args self._ _keywords = keywords def _ _getattr_ _(self, name): if self._ _cxn is None: self._ _cxn = self._ _factory(*self._ _args, **self._ _keywords) return getattr(self._ _cxn, name) def close(self): if self._ _cxn is not None: self._ _cxn.close( ) self._ _cxn = None def _ _call_ _(self, sql, **keywords): """ Execute SQL query and return results. Optional keyword args are '%' substituted into query beforehand. """ cursor = self.cursor( ) cursor.execute(sql % keywords) return RecordSet( [list(x) for x in cursor.fetchall( )], [x[0].lower( ) ...