Sorry, I know this is awfully long for a blog post - I have to figure out a better place to put it. I wonder if it would be appropriate for the Cheese Shop?
"""
sqlWrap
Adds convenience methods to DB-API2 connection objects.
Sep. 22, 2005 by Catherine Devlin (catherine.devlin@gmail.com, http://catherinedevlin.blogspot.com/)
This script is NOT intended as an object/relational mapper.
Rather, it helps experienced SQL users form their SQL more quickly.
Methods: insert, update, delete, select, genericSelect
These methods accept arguments for the WHERE, SET, etc. clauses
that should generally be provided as dictionaries; i.e.
whereClause = {'col1':'val1','col2':'val2%'} implies
"WHERE col1 = 'val1' AND col2 LIKE 'val2%'",
setClause = {'col1':'val1'} implies
"SET col1 = 'val1'"
You may also pass object instances for whereClause and setClause,
with instance attributes corresponding to column names... but this
has barely been tested!
They also automatically make use of bind variables, which have
performance and security benefits over hard-coding values in SQL.
The different ways of handling bind variables in various DB-API
adapters are masked from the user.
Currently supports: Oracle (cx_Oracle), sqlite (pysqlite)
Sample usage:
# setup - unchanged from cx_Oracle
conn = OraConnection('scott/tiger@orcl')
conn.cursor().execute('CREATE TABLE myTable (column1 varchar2(10), column2 varchar2(10), column3 varchar2(10))')
# now try out the sqlWrap convenience methods
conn.insert('myTable', setClause={'column1':'value1','column2':'value2'})
conn.insert('myTable', setClause={'column1':'value1a','column2':'value2a','column3':'value3a'})
for row in conn.select(source='myTable'):
print row
for row in conn.select(source='myTable', whereClause={'column1':'value1'}, resultProcessor=conn.dictionaryize):
print row
conn.update('myTable', setClause={'column1':'value1','column2':'value2'}, whereClause={'column3':'value3'})
# as always, must explicitly commit
conn.commit()
"""
