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