8.12 Establishing Database Connections Lazily

Credit: John B. Dell'Aquila

8.12.1 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.

8.12.2 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(  ) for x in cursor.description]             ) class RecordSet:     """ Wrapper for tabular data """     def _ _init_ _(self, tableData, columnNames):         self.data = tableData         self.columns = columnNames         self.columnMap = {}         for name,n in zip(columnNames, xrange(10000)):             self.columnMap[name] = n     def _ _getitem_ _(self, n):         return Record(self.data[n], self.columnMap)     def _ _setitem_ _(self, n, value):         self.data[n] = value     def _ _delitem_ _(self, n):         del self.data[n]     def _ _len_ _(self):         return len(self.data)     def _ _str_ _(self):         return '%s: %s' % (self._ _class_ _, self.columns) class Record:     """ Wrapper for data row. Provides access by     column name as well as position. """     def _ _init_ _(self, rowData, columnMap):         self._ _dict_ _['_data_'] = rowData         self._ _dict_ _['_map_'] = columnMap     def _ _getattr_ _(self, name):         return self._data_[self._map_[name]]     def _ _setattr_ _(self, name, value):         try:             n = self._map_[name]         except KeyError:             self._ _dict_ _[name] = value         else:             self._data_[n] = value     def _ _getitem_ _(self, n):         return self._data_[n]     def _ _setitem_ _(self, n, value):         self._data_[n] = value     def _ _getslice_ _(self, i, j):         return self._data_[i:j]     def _ _setslice_ _(self, i, j, slice):         self._data_[i:j] = slice     def _ _len_ _(self):         return len(self._data_)     def _ _str_ _(self):         return '%s: %s' % (self._ _class_ _, repr(self._data_))

8.12.3 Discussion

The module implemented by this recipe, LazyDB, extends the DB API to provide lazy connections (established only when needed) and access to query results by column name. A LazyDB connection can transparently replace any normal DB API connection but is significantly more convenient, making SQL queries feel almost like a built-in Python feature.

Here is a simple usage example:

import LazyDB, cx_Oracle myDB = LazyDB.Connection(cx_Oracle.connect, 'user/passwd@server') pctSQL = 'SELECT * FROM all_tables WHERE pct_used >= %(pct)s' hogs = [(r.table_name, r.pct_used) for r in myDB(pctSQL, pct=90)]

You can wrap all your standard database connections with LazyDB and place them in a single module that you can import whenever you need a database. This keeps all your passwords in a single place and costs almost nothing, since connections aren't opened until you actually use them.

The one-step query facility cannot be used for extremely large result sets because fetchall will fail. It also shouldn't be used to run the same query multiple times with different parameters. For optimal performance, use the native DB API parameter substitution, so the SQL won't be reparsed each time.

Capitalization conventions vary among databases. LazyDB arbitrarily forces column names to lowercase to provide consistent Python attribute names and thus ease portability of your code among several databases.

8.12.4 See Also

The Python DB API (http://www.python.org/topics/database/DatabaseAPI-2.0.html).