Using a DataSource (or ConnectionPooledDataSource)

The only required argument is the fully-qualified classname of the DataSource, all keywords will use JavaBeans reflection to set properties on the DataSource.

Jython 2.1b1 on java1.4.0-beta3 (JIT: null)
Type "copyright", "credits" or "license" for more information.
>>> from com.ziclix.python.sql import zxJDBC
>>> params = {}
>>> params['serverName'] = 'localhost'
>>> params['databaseName'] = 'ziclix'
>>> params['user'] = None
>>> params['password'] = None
>>> params['port'] = 3306
>>> db = apply(zxJDBC.connectx, ("org.gjt.mm.mysql.MysqlDataSource",), params)
>>>

Using a JNDI lookup

It is possible for zxJDBC to use a Connection found through a JNDI lookup. This is particularly useful in an application server (such as when using PyServlet). The bound object can be either a String, Connection, DataSource or ConnectionPooledDataSource. The lookup will figure out the instance type and access the Connection accordingly,

In order execute any operation, a cursor is required from the connection. There are two different kinds of cursors: static and dynamic.

The primary difference between the two is the way they manage the underlying ResultSet. In the static version, the entire ResultSet is iterated immediately, the data converted and stored with the cursor and the ResultSet closed. This allows the cursor to know the rowcount (not available otherwise within JDBC) and set the .rowcount attribute properly. The major disadvantage to this approach is the space/time constraints might be extraordinary.

The solution to the problem are dynamic cursors which keep a handle to the open ResultSet and iterate as required. This drastically decreases memory consumption and increases perceived response time because no work is done until asked. The drawback is the .rowcount attribute can never be accurately set.

To execute a query simply provide the SQL expression and call execute. The cursor now has a description attribute detailing the column information. To navigate the result set, call one of the fetch methods and a list of tuples will be returned.

>>> c = db.cursor()   # this gets a static cursor
 or
>>> c = db.cursor(1)  # this gets a dynamic cursor
>>> c.execute("select count(*) c from player")
>>> c.description
[('c', 3, 17, None, 15, 0, 1)]
>>> for a in c.fetchall():
...  print a
...
(13569,)
>>>

When finished, close the connections.

>>> c.close()
>>> db.close()
>>>

To call a stored procedure or function provide the name and any params to callproc. The database engine must support stored procedures. The examples below have been tested with Oracle, SQLServer and Informix. Refer to the Python DP API spec for how OUT and INOUT parameters work.

NOTE: The name of the stored procedure can either be a string or tuple. This is NOT portable to other DB API implementations.

SQL Server
>>> c = db.cursor() # open the database as in the examples above
>>> c.execute("use northwind")
>>> c.callproc(("northwind", "dbo", "SalesByCategory"), ["Seafood", "1998"], maxrows=2)
>>> for a in c.description:
...  print a
...
('ProductName', -9, 40, None, None, None, 0)
('TotalPurchase', 3, 17, None, 38, 2, 1)
>>> for a in c.fetchall():
...  print a
...
('Boston Crab Meat', 5318.0)
('Carnarvon Tigers', 8497.0)
>>> c.nextset()
1
>>> print c.fetchall()
[(0,)]
>>> print c.description
[('@RETURN_VALUE', 4, -1, 4, 10, 0, 0)]
>>>

Oracle
>>> c = db.cursor() # open the database as in the examples above
>>> c.execute("create or replace function funcout (y out varchar2) return varchar2 is begin y := 'tested'; return 'returned'; end;")
>>> params = [None]
>>> c.callproc("funcout", params)
>>> print params
['tested']
>>> print c.description
[(None, 12.0, -1, None, None, None, 1)]
>>> print c.fetchall()
[('returned',)]
>>>

When finished, close the connections.

>>> c.close()
>>> db.close()
>>>

Standard extensions to the Python DB API

The DataHandler interface has three methods for handling type mappings. They are called at two different times, one when fetching and the other when binding objects for use in a prepared statement. I have chosen this architecture for type binding because I noticed a number of discrepancies in how different JDBC drivers handled database types, in particular the additional types available in later JDBC versions.

public void preExecute(Statement stmt) throws SQLException;

A callback prior to each execution of the statement. If the statement is a PreparedStatement (created when parameters are sent to the execute method), all the parameters will have been set.

public void postExecute(Statement stmt) throws SQLException;

A callback after successfully executing the statement. This is particularly useful for cases such as auto-incrementing columns where the statement knows the inserted value.

public String getMetaDataName(String name);

A callback for determining the proper case of a name used in a DatabaseMetaData method, such as getTables(). This is particularly useful for Oracle which expects all names to be upper case.

public PyObject getRowId(Statement stmt) throws SQLException;

A callback for returning the row id of the last insert statement.

public Object getJDBCObject(PyObject object, int type);

This method is called when a PreparedStatement is created through use of the execute method. When the parameters are being bound to the statement, the DataHandler gets a callback to map the type. This is only called if type bindings are present.

public Object getJDBCObject(PyObject object);

This method is called when no type bindings are present during the execution of a PreparedStatement.

public PyObject getPyObject(ResultSet set, int col, int type);

This method is called upon fetching data from the database. Given the JDBC type, return the appropriate PyObject subclass from the Java object at column col in the ResultSet set.

public PyObject getPyObject(CallableStatement stmt, int col, int type) throws SQLException;

This method is called upon fetching data from the database after calling a stored procedure or function. Given the JDBC type, return the appropriate PyObject subclass from the Java object at column col in the CallableStatement.

public void registerOut(CallableStatement statement, int index, int colType, int dataType, String dataTypeName) throws SQLException;

This method is called to register an OUT or INOUT parameter on the stored procedure. The dataType comes from java.sql.Types while the dataTypeName is a vendor specific string.

public String getProcedureName(PyObject catalog, PyObject schema, PyObject name);

This method is called to build a stored procedure's name.

It is simple to use these callbacks to achieve the desired result for your database driver. In the majority of cases nothing needs to be done to get the correct datatype mapping. However, in the cases where drivers differ from the spec or handle values differently, the DataHandler callbacks should provide the solution.

Example DataHandler for Informix booleans

One such case where a driver needs a special mapping is Informix booleans. The are represented as the characters 't' and 'f' in the database and have their own type boolean. You can see from the example below, without the special DataHandler, the boolean type mapping fails.

Jython 2.1b1 on java1.4.0-beta3 (JIT: null)
Type "copyright", "credits" or "license" for more information.
>>> from com.ziclix.python.sql import zxJDBC
>>> zxJDBC.autocommit = 0
>>> d, u, p, v = "database", "user", "password", "com.informix.jdbc.IfxDriver"
>>> db = zxJDBC.connect(d, u, p, v)
>>> c = db.cursor()
>>> c.execute("create table g (a boolean)")
>>> c.execute("insert into g values (?)", [1])
Traceback (innermost last):
File "<console>", line 1, in ?
Error: No cast from integer to boolean. [SQLCode: -9634]
>>> from com.ziclix.python.sql.informix import InformixDataHandler
>>> c.datahandler = InformixDataHandler(c.datahandler)
>>> c.execute("insert into g values (?)", [1], {0:zxJDBC.OTHER})
>>>

As you can see, the default handler fails to convert the Python 1 into an Informix boolean because the IfxDriver treats booleans as JDBC type OTHER. The InformixDataHandler is intimately aware of the IfxDriver mappings and understands how to interpret Python values as booleans when the JDBC type is OTHER.

This functionality is also useful in handling the more advanced JDBC 2.0 types CLOB, BLOB and Array.

You can also implement the DataHandler from within Jython as in this simple example:

>>> class PyHandler(DataHandler):
>>>  def __init__(self, handler):
>>>   self.handler = handler
>>>  def getPyObject(self, set, col, datatype):
>>>   return self.handler.getPyObject(set, col, datatype)
>>>  def getJDBCObject(self, object, datatype):
>>>   print "handling prepared statement"
>>>   return self.handler.getJDBCObject(object, datatype)
>>>
>>> c.datahandler = PyHandler(c.datahandler)
>>> c.execute("insert into g values (?)", [1])
handling prepared statement
>>>

dbexts

dbexts is a wrapper around DB API 2.0 compliant database modules. It currently supports zxJDBC and mxODBC but could easily be modified to support others. It allows developers to write scripts without knowledge of the implementation language of Python (either C or Java). It also greatly eases the burden of database coding as much of the functionality of the Python API is exposed through easier to use methods.

Configuration file

dbexts needs a configuration file in order to create a connection. The configuration file has the following format:

[default]
name=mysql

[jdbc]
name=mysql
url=jdbc:mysql://localhost/ziclix
user=
pwd=
driver=org.gjt.mm.mysql.Driver
datahandler=com.ziclix.python.sql.handler.MySQLDataHandler

[jdbc]
name=ora
url=jdbc:oracle:thin:@localhost:1521:ziclix
user=ziclix
pwd=ziclix
driver=oracle.jdbc.driver.OracleDriver
datahandler=com.ziclix.python.sql.handler.OracleDataHandler

API

__init__(self, dbname=None, cfg=None, resultformatter=format_resultset, autocommit=1)

The initialization method for the dbexts class. If dbname is None, the default connection, as specified in the cfg file will be used.

isql(self, sql, params=None, bindings=None, maxrows=None)

Interactively execute sql statement. If self.verbose is true, then the results (if any) are displayed using the result formatting method. If maxrows is specified, only maxrows are displayed.

raw(self, sql, params=None, bindings=None, delim=None, comments=comments)

Executes the sql statement with params and bindings as necessary. Returns a tuple consisting of (headers, results).

schema(table, full=0, sort=1)

Displays the schema (indicies, foreign keys, primary keys and columns) for the table parameter. If full is true, also compute the exported (or referenced) keys. If sort is true (the default), sort the column names.

>>> d.schema("store")
Table
  store

Primary Keys
  store_id {store_3}

Imported (Foreign) Keys
  location (city.city_id) {store_7}

Exported (Referenced) Keys
  store_id (site_store.store_id) {site_store_8}

Columns
  location           int(4), non-nullable
  store_id           serial(4), non-nullable
  store_name         varchar(32), non-nullable

Indices
  unique index {523_8115} on (store_id)
  unique index {store_ix_1} on (store_name)
>>>

table(table=None, types=("TABLE",), owner=None, schema=None)

If no table argument, displays a list of all tables. If a table argument, displays the columns of the given table.

proc(self, proc=None, owner=None, schema=None)

If no proc argument, displays a list of all procedures. If a proc argument, displays the parameters of the given procedure.

bcp(src, table, where='(1=1)', parameters=[], selective=[], ignorelist=[], autobatch=0)

Bulk Copy from one database/table to another. The current instance of dbexts is the source database to which the results of the query on the foreign database will be inserted. An optional where clause can narrow the number of rows to be copied.

The following are generally not called since isql and raw can handle almost all cases.

begin(self)

Creates a new cursor.

rollback(self)

Rollback all the statements since the creation of the cursor.

commit(self, cursor=None, maxrows=None)

Commit all the statements since the creation of the cursor.

display(self)

Display the results using the formatter.

Example session

Jython 2.1b1 on java1.4.0-beta3 (JIT: null)
Type "copyright", "credits" or "license" for more information.
>>> from dbexts import dbexts
>>> d = dbexts()
>>> d.isql("create table store (store_id int, store_name varchar(32), location int)")
>>> d.isql("insert into store values (?, ?, ?)", [(1, "amazon.com", 3), (2, "egghead.com", 4)])
>>> d.isql("insert into store values (?, ?, ?)", [(15, "800.com", 1), (19, "fogdog.com", 3)])
>>> d.isql("insert into store values (?, ?, ?)", [(5, "nike.com", 4)])
>>> d.isql("select * from store order by store_name")

STORE_ID | STORE_NAME  | LOCATION
---------------------------------
15       | 800.com     | 1
1        | amazon.com  | 3
2        | egghead.com | 4
19       | fogdog.com  | 3
5        | nike.com    | 4

5 rows affected

>>>