zxJDBC

Database Connectivity in JPython

Documentation

Getting started

JPython 1.1+07 on java1.2.2 (JIT: symcjit)
Copyright (C) 1997-1999 Corporation for National Research Initiatives
>>> 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)
 optionally
>>> db = zxJDBC.connect(d, u, p, v, CHARSET='iso_1')
>>>

To access the database, create a cursor from the connection. 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()
>>> 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()
>>>

Standard extensions to the Python DB API

Same as DatabaseMetaData.getDatabaseProductName

connection.dbversion

Same as DatabaseMetaData.getDatabaseProductVersion

cursor.tables(qualifier,owner,table,type)

Same as DatabaseMetaData.getTables

cursor.columns(qualifier,owner,table,column)

Same as DatabaseMetaData.getColumns

cursor.foreignkeys(primary_qualifier,primary_owner,pimary_table, foreign_qualifier,foreign_owner,foreign_table)

Same as DatabaseMetaData.getCrossReference

cursor.primarykeys(qualifier,owner,table)

Same as DatabaseMetaData.getPrimaryKeys

cursor.procedures(qualifier,owner,procedure)

Same as DatabaseMetaData.getProcedures

cursor.procedurecolumns(qualifier,owner,procedure,column)

Same as DatabaseMetaData.getProcedureColumns

cursor.statistics(qualifier,owner,table,unique,accuracy)

Same as DatabaseMetaData.getIndexInfo

Datatype mapping callbacks through DataHandler

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 the Java object at column col in the ResultSet set.

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.

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.

JPython 1.1+07 on java1.2.2 (JIT: symcjit)
Copyright (C) 1997-1999 Corporation for National Research Initiatives
>>> 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 JPython 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
>>>

Javadoc

Feel free to email me with questions or suggestions at bzimmer@ziclix.com