Credit: John B. Dell’Aquila
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.
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_))
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.
The Python DB API (http://www.python.org/topics/database/DatabaseAPI-2.0.html).