sqlWrap.py

Adds convenience methods to DB-API2 connection objects.

See also: oraDifference, resetPwd (scripts included with sqlWrap)

Purpose of This Module

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)

Installation

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:

The installer will also install oraDifference.py into your Python Scripts directory: http://sqlwrappy.sourceforge.net/oraDifference

How To Use This Module

  1. Import it: import sqlWrap or from sqlWrap import *

  2. Create a connection to your database:

    cnxn = OraConnection('hr/hr@xe')
cnxn = SqliteConnection('database.sqlite')
cnxn = sqlWrap.PostgresConnection('dbname=pgdb user=postgres 
password=pwd')

  3. Perform queries:

  1. using the connection's select method:

    curs = cnxn.select('employees')

  1. Clauses like whereClause can be supplied as Python dictionaries, causing automatic use of bind variables:

    curs = cnxn.select('employees', 
whereClause={'department_id':90})

  1. Using standard DB-API2 techniques:

    curs = cnxn.cursor()
curs.execute('SELECT * FROM employees')

  1. Cursors return rows with many conveniences:

    row = curs.next()

  1. Dictionary-style access:

    row['last_name']

    Keys are case-insensitive:

    row['LAST_NAME']

  2. Object-style accessa:

    row.last_name
row.LAST_NAME

  1. Cursors have methods for formatted reporting:

    curs.xml()
curs.xmlTransposed()
curs.pp()
curs.ppTransposed()
curs.xhtml()
curs.xhtmlTransposed()
curs.rst()
curs.rstTransposed()

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

Incidental benefits

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

Query methods of cursor objects

select

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"

insert

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)

update

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.

delete

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