Welcome, guest | Sign In | My Account | Store | Cart

LazyDB extends the DB API to provide lazy connections (only established 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.

Python, 107 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
"""The LazyDB module transparently wraps any DB API compliant
interface (DCOracle, odbc, cx_oracle, etc.) and provides:
1. lazy evaluation and caching of database connections
2. one-step query facility with data access by column name

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)]"""

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

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 can't be used for extremely large result sets (fetchall will fail). It shouldn't be used to run the same query multiple times with different parameters (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 lower case to provide consistent Python attribute names.

1 comment

Marcin Sus 16 years, 2 months ago  # | flag

update records. Calling insert/update using Connection object cause throw exception because of cursor.execute with update or insert statement returns nothing and creating RecordSet object fails. Maybe simple:

ret = cursor.execute(sql % keywords)
  if ret:
    return RecordSet(...)

  return None

prevent errors.

This is of course trivial solution, but I'm newbie to python. Maybe empty RecordSet with inserted/updated rownum attribute will be better.

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

Regards, M.

Created by John Dell'Aquila on Wed, 25 Jul 2001 (PSF)
Python recipes (4591)
John Dell'Aquila's recipes (1)

Required Modules

  • (none specified)

Other Information and Tasks