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.
Download
Copy to clipboard1 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.
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:
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.
Regards, M.