Adds convenience methods to DB-API2 connection objects.
See also: oraDifference, resetPwd (scripts included with sqlWrap)
Extends DB-API2 Connection objects with convenience methods for issuing SQL, accessing results, and reporting.
This module is NOT intended as an object/relational mapper. Although it does help a programmer move data between Python and a database with less code, it remains closely tied to SQL operations, instead of trying to mask SQL behind a mapping interface.
Most importantly, sqlWrap only extends the capabilities of DB-API2; all original DB-API2 functionality remains. You should be able to simply replace your DB-API2 Connection objects with sqlWrap Connection objects and then continue programming, forgetting that you're using sqlWrap except when you want to use its features.
Currently supports: Oracle (cx_Oracle), sqlite (pysqlite), postgresql (psycopg)
Download a tarball or a Windows executable from https://sourceforge.net/projects/sqlwrappy/
Run the executable, or unzip the tarball, navigate into the folder containing setup.py, and run python setup.py install
The file sqlWrap_examples.txt is not required, but helps to demonstrate the module's usage.
You must also install a DB-API2 adapter for your database back-end:
- cx_Oracle: http://www.cxtools.net/default.aspx?nav=cxorlb
- psycopg: http://www.initd.org/projects/psycopg1
- pysqlite2: http://initd.org/tracker/pysqlite
The installer will also install oraDifference.py into your Python Scripts directory: http://sqlwrappy.sourceforge.net/oraDifference
Import it: import sqlWrap or from sqlWrap import *
Create a connection to your database:
cnxn = OraConnection('hr/hr@xe') cnxn = SqliteConnection('database.sqlite') cnxn = sqlWrap.PostgresConnection('dbname=pgdb user=postgres password=pwd')
Perform queries:
using the connection's select method:
curs = cnxn.select('employees')
Clauses like whereClause can be supplied as Python dictionaries, causing automatic use of bind variables:
curs = cnxn.select('employees', whereClause={'department_id':90})
Using standard DB-API2 techniques:
curs = cnxn.cursor() curs.execute('SELECT * FROM employees')
Cursors return rows with many conveniences:
row = curs.next()
Dictionary-style access:
row['last_name']Keys are case-insensitive:
row['LAST_NAME']Object-style accessa:
row.last_name row.LAST_NAME
Cursors have methods for formatted reporting:
curs.xml() curs.xmlTransposed() curs.pp() curs.ppTransposed() curs.xhtml() curs.xhtmlTransposed() curs.rst() curs.rstTransposed()
Convenience methods for insert, update, delete:
cnxn.insert('regions',{'region_id':5,'region_name':'Antarctica'}) cnxn.update('regions', setClause = {'region_name':'Antarctica'}, whereClause = {'region_id':5}) cnxn.delete('regions',{'region_id':5})
As always with DB-API2, DML requires explicitly committing transactions:
cnxn.commit()
When calling curs.execute, bind variables may be supplied either by the DB-API2 package's own convention ("Qmark" for pysqlite, 'Pyformat' for psycopg) or by the Named (cx_Oracle) format:
curs.execute('SELECT * FROM regions WHERE region_id = :r', {'r': 2})
In native cx_Oracle, any "extra" bind variables supplied in the bind variable dictionary raise errors. sqlWrap fixes this, letting you use dictionaries like locals() to pass bind variables:
curs.execute('SELECT * FROM regions WHERE region_id = :r', {'r': 2, 'a':1})
Builds and executes a SELECT statement; returns a cursor.
Parameters:
- source: Source table name. (Required)
- selectList: Names of columns to return. May be a string or a list of strings. Defaults to '*' (all columns)
- whereClause: Filtering condition. May be a dict or a string; "col1='a' and col2=22" and {'col1':'a',col2:22} are equivalent, but passing a dict automatically causes bind variables to be used. If a value contains % (SQL wildcard), LIKE will be used instead of =.
- userBindVars - If whereClause is given as a string with bind variables, supply them as a dict.
- 'orderBy` - Columns to order results by. Given as a string. Ex: "col1, col2"
Inserts to a table. Returns number of rows inserted.
Parameters:
- target: Table to insert into. (Required)
- setClause: A dict containing column:value data to insert. (Required)
Updates a table. Returns number of rows updated.
Parameters:
- target: Table to update. (Required)
- setClause: A dict containing column:value data to set. (Required)
- whereClause: Filtering condition. May be a dict or a string; "col1='a' and col2=22" and {'col1':'a',col2:22} are equivalent, but passing a dict automatically causes bind variables to be used. If a value contains % (SQL wildcard), LIKE will be used instead of =.
- userBindVars - If whereClause is given as a string with bind variables, supply them as a dict.
Deletes from a table. Returns number of rows deleted.
Parameters:
- target: Table to delete from. (Required)
- whereClause: Filtering condition. May be a dict or a string; "col1='a' and col2=22" and {'col1':'a',col2:22} are equivalent, but passing a dict automatically causes bind variables to be used. If a value contains % (SQL wildcard), LIKE will be used instead of =.
Sep. 22, 2005 by Catherine Devlin (catherine.devlin@gmail.com, http://catherinedevlin.blogspot.com/) Heavily revised Feb.2006 based on ideas found in the Python Cookbook (2nd Ed.)