Title: zxJDBC

<h3>Database connectivity in Jython</h3>

<p>The zxJDBC package provides a nearly 100% <a
href="http://www.python.org">Python</a> <a
href="http://www.python.org/topics/database/DatabaseAPI-2.0.html">
DB API 2.0</a> compliant interface for database connectivity in <a
href="http://jython.sourceforge.net/">Jython</a>. It is implemented
entirely in <a href="http://www.javasoft.com">Java</a> and makes
use of the <a href="http://www.javasoft.com/jdbc">JDBC</a> API.
This means any database capable of being accessed through JDBC,
either directly or using the JDBC-ODBC bridge, can be manipulated
using zxJDBC.</p>

<h3>Getting a Connection</h3>
<p>First, make sure a valid JDBC driver is in your classpath.
Then start Jython and import the zxJDBC connection factory.</p>

<h4>Using a Driver</h4>
<p>The most common way to establish a connection is through a Driver.
Simply supply the database, username, password and JDBC driver
classname to the connect method. If your driver requires special
arguments, pass them into the <code>connect</code> method as
standard Python keyword arguments. You will be returned a
connection object.</p>

<pre>
Jython 2.1b1 on java1.4.0-beta3 (JIT: null)
Type "copyright", "credits" or "license" for more information.
&gt;&gt;&gt; from com.ziclix.python.sql import zxJDBC
&gt;&gt;&gt; d, u, p, v = "jdbc:mysql://localhost/test", None, None, "org.gjt.mm.mysql.Driver"
&gt;&gt;&gt; db = zxJDBC.connect(d, u, p, v)
 <i><b>optionally</b></i>
&gt;&gt;&gt; db = zxJDBC.connect(d, u, p, v, CHARSET='iso_1')
&gt;&gt;&gt;
</pre>

<a name="connectx"/>
<h4>Using a DataSource (or ConnectionPooledDataSource)</h4>
<p>The only required argument is the fully-qualified classname
of the DataSource, all keywords will use JavaBeans reflection to
set properties on the DataSource.</p>

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

<h4>Using a JNDI lookup</h4>
<p>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 <code>lookup</code> will figure out the instance type and access the Connection
accordingly,</p>

<p>The only required argument is the JNDI lookup name.  All keyword arguments will be
converted to their proper
<a href="http://java.sun.com/j2se/1.3/docs/api/javax/naming/Context.html">Context</a>
field value if the keyword matches one
of the constants.  If a field name does not exist for the keyword, it will passed as
declared.  The resulting environment will be used to build the
<a href="http://java.sun.com/j2se/1.3/docs/api/javax/naming/InitialContext.html">InitialContext</a>.
</p>

<p><i>This example uses the simple Sun FileSystem JNDI reference implementation.  Please
consult the JNDI implementation you intend to use for the InitialContextFactory classname
as well as the connection URL.</i></p>

<pre>
Jython 2.1b1 on java1.4.0-beta3 (JIT: null)
Type "copyright", "credits" or "license" for more information.
&gt;&gt;&gt; from com.ziclix.python.sql import zxJDBC
&gt;&gt;&gt; jndiName = "/temp/jdbc/mysqldb"
&gt;&gt;&gt; factory = "com.sun.jndi.fscontext.RefFSContextFactory"
&gt;&gt;&gt; db = zxJDBC.lookup(jndiName, INITIAL_CONTEXT_FACTORY=factory)
&gt;&gt;&gt;
</pre>

<h3><a name="dynamiccursor"/>Getting a Cursor</h3>
<p>
	In order execute any operation, a cursor is required from the connection.  There
	are two different kinds of cursors: <i>static</i> and <i>dynamic</i>.
</p>
<p>
	The primary difference between the two is the way they manage the underlying ResultSet.
	In the <i>static</i> 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 <code>.rowcount</code>
	attribute properly.  The major disadvantage to this approach is the space/time constraints
	might be extraordinary.
</p>
<p>
	The solution to the problem are <i>dynamic</i> 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 <code>.rowcount</code>
	attribute can never be accurately set.
</p>

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

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

<p>When finished, close the connections.</p>

<pre>
&gt;&gt;&gt; c.close()
&gt;&gt;&gt; db.close()
&gt;&gt;&gt;
</pre>

<p>
	To call a stored procedure or function provide the name and any params to <code>callproc</code>.
	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.
</p>
<p>
	NOTE: The name of the stored procedure can either be a string or tuple.  This is NOT portable to
	other DB API implementations.
<pre>
<b><i>SQL Server</b></i>
&gt;&gt;&gt; c = db.cursor() # open the database as in the examples above
&gt;&gt;&gt; c.execute("use northwind")
&gt;&gt;&gt; c.callproc(("northwind", "dbo", "SalesByCategory"), ["Seafood", "1998"], maxrows=2)
&gt;&gt;&gt; for a in c.description:
...  print a
...
('ProductName', -9, 40, None, None, None, 0)
('TotalPurchase', 3, 17, None, 38, 2, 1)
&gt;&gt;&gt; for a in c.fetchall():
...  print a
...
('Boston Crab Meat', 5318.0)
('Carnarvon Tigers', 8497.0)
&gt;&gt;&gt; c.nextset()
1
&gt;&gt;&gt; print c.fetchall()
[(0,)]
&gt;&gt;&gt; print c.description
[('@RETURN_VALUE', 4, -1, 4, 10, 0, 0)]
&gt;&gt;&gt;
</pre>

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

<p>When finished, close the connections.</p>

<pre>
&gt;&gt;&gt; c.close()
&gt;&gt;&gt; db.close()
&gt;&gt;&gt;
</pre>

<h3>Standard extensions to the Python DB API</h3>

<dl>

<dt><p><code class="methodname">connection.dbname</code></p></dt>

<dd>Same as <a
href="http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html#getDatabaseProductName()">
DatabaseMetaData.getDatabaseProductName</a></dd>

<dt><p><code class="methodname">connection.dbversion</code></p></dt>

<dd>Same as <a
href="http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html#getDatabaseProductVersion()">
DatabaseMetaData.getDatabaseProductVersion</a></dd>

<dt><p><code class="methodname">cursor.updatecount</code></p></dt>

<dd>The value obtained from calling <a
href="http://java.sun.com/j2se/1.3/docs/api/java/sql/Statement.html#getUpdateCount()">
Statement.getUpdateCount</a></dd>

<dt><p><code class="methodname">cursor.lastrowid</code></p></dt>

<dd>The value obtained from calling <a
href="javadoc/com/ziclix/python/sql/DataHandler.html#getRowId(java.sql.Statement)">
DataHandler.getRowId</a></dd>

<dt><p><code class="methodname">cursor.tables(qualifier,owner,table,type)</code></p></dt>

<dd>Same as <a
href="http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html#getTables(java.lang.String, java.lang.String, java.lang.String, java.lang.String[])">
DatabaseMetaData.getTables</a></dd>

<dt><p><code class="methodname">cursor.columns(qualifier,owner,table,column)</code></p></dt>

<dd>Same as <a
href="http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String, java.lang.String, java.lang.String, java.lang.String)">
DatabaseMetaData.getColumns</a></dd>

<dt><p><code class="methodname">cursor.foreignkeys(primary_qualifier,primary_owner,pimary_table,
foreign_qualifier,foreign_owner,foreign_table)</code></p></dt>

<dd>Same as <a
href="http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html#getCrossReference(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)">
DatabaseMetaData.getCrossReference</a></dd>

<dt><p><code class="methodname">cursor.primarykeys(qualifier,owner,table)</code></p></dt>

<dd>Same as <a
href="http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html#getPrimaryKeys(java.lang.String, java.lang.String, java.lang.String)">
DatabaseMetaData.getPrimaryKeys</a></dd>

<dt><p><code class="methodname">cursor.procedures(qualifier,owner,procedure)</code></p></dt>

<dd>Same as <a
href="http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html#getProcedures(java.lang.String, java.lang.String, java.lang.String)">
DatabaseMetaData.getProcedures</a></dd>

<dt><p><code class="methodname">cursor.procedurecolumns(qualifier,owner,procedure,column)</code></p></dt>

<dd>Same as <a
href="http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html#getProcedureColumns(java.lang.String, java.lang.String, java.lang.String, java.lang.String)">
DatabaseMetaData.getProcedureColumns</a></dd>

<dt><p><code class="methodname">cursor.statistics(qualifier,owner,table,unique,accuracy)</code></p></dt>

<dd>Same as <a
href="http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo(java.lang.String, java.lang.String, java.lang.String, boolean, boolean)">
DatabaseMetaData.getIndexInfo</a></dd>
</dl>

<h3><a name="bindings"/>Datatype mapping callbacks through DataHandler</h3>

<p>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.</p>

<br/><i><b>life cycle</b></i>
<dl>
<dt><p><code class="methodname">public void preExecute(Statement stmt)
throws SQLException;</code></p></dt>
<dd>A callback prior to each execution of the statement.  If the statement
is a PreparedStatement (created when parameters are sent to the
<code>execute</code> method), all the parameters will have been set.</dd>
<dt><p><code class="methodname">public void postExecute(Statement stmt)
throws SQLException;</code></p></dt>
<dd>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.</dd>
</dl>

<i><b>developer support</b></i>
<dl>
<dt><p><code class="methodname">public String getMetaDataName(String name);</code></p></dt>
<dd>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.</dd>
<dt><p><code class="methodname">public PyObject getRowId(Statement stmt) throws SQLException;</code></p></dt>
<dd>A callback for returning the row id of the last insert statement.</dd>
</dl>

<i><b>binding prepared statements</b></i>
<dl>
<dt><p><code class="methodname">public Object getJDBCObject(PyObject
object, int type);</code></p></dt>
<dd>This method is called when a PreparedStatement is created
through use of the <code>execute</code> 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.</dd>
<dt><p><code class="methodname">public Object getJDBCObject(PyObject
object);</code></p></dt>
<dd>This method is called when no type bindings are present during
the execution of a PreparedStatement.</dd>
</dl>

<i><b>building results</b></i>
<dl>
<dt><p><code class="methodname">public PyObject
getPyObject(ResultSet set, int col, int type);</code></p></dt>
<dd>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.</dd>
</dl>

<i><b>callable statement support</b></i>
<dl>
<dt><p><code class="methodname">public PyObject getPyObject(CallableStatement stmt, int col,
int type) throws SQLException;</code></p></dt>
<dd>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.</dd>
<dt><p><code class="methodname">public void registerOut(CallableStatement statement, int
index, int colType, int dataType, String dataTypeName) throws SQLException;</code></p></dt>
<dd>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.</dd>
<dt><p><code class="methodname">public String getProcedureName(PyObject catalog, PyObject
schema, PyObject name);</code></p></dt>
<dd>This method is called to build a stored procedure's name.</dd>
</dl>

<p>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.</p>

<h4>Example DataHandler for Informix booleans</h4>

<p>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 <code>boolean</code>. You can see
from the example below, without the special DataHandler, the
boolean type mapping fails.</p>

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

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

<p>This functionality is also useful in handling the more advanced
JDBC 2.0 types <code>CLOB</code>, <code>BLOB</code> and <code>
Array</code>.</p>

<p>You can also implement the DataHandler from within Jython as in
this simple example:</p>

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

<h3>dbexts</h3>

<p>
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.
</p>

<h3>Configuration file</h3>
<p>
dbexts needs a configuration file in order to create a connection.  The configuration file has the following format:
<pre>
[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
</pre>

<h3>API</h3>
dbexts will default to looking for a file named 'dbexts.ini' in the same
directory as dbexts.py but can optionally be passed a filename to the
<code>cfg</code> attribute.
</p>
<dl>
<dt><p><code class="methodname">__init__(self, dbname=None, cfg=None,
resultformatter=format_resultset, autocommit=1)</code></p></dt>

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

<dt><p><code class="methodname">isql(self, sql, params=None, bindings=None,
maxrows=None)</code></p></dt>

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

<dt><p><code class="methodname">raw(self, sql, params=None, bindings=None,
delim=None, comments=comments)</code></p></dt>

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

<dt><p><code class="methodname">schema(table, full=0, sort=1)</code></p></dt>

<dd>
Displays the schema (indicies, foreign keys, primary keys and columns) for the
table parameter.  If <code>full</code> is true, also compute the exported (or
referenced) keys.  If <code>sort</code> is true (the default), sort the column names.
<pre>
>>> 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)
>>>
</pre>

		</dd>

		<dt><p><code class="methodname">table(table=None, types=("TABLE",), owner=None, schema=None)</code></p></dt>

		<dd>
			If no table argument, displays a list of all tables.  If a table argument, displays the columns of the given table.
		</dd>

		<dt><p><code class="methodname">proc(self, proc=None, owner=None, schema=None)</code></p></dt>

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

		<dt><p><code class="methodname">bcp(src, table, where='(1=1)', parameters=[], selective=[], ignorelist=[], autobatch=0)</code></p></dt>

		<dd>
			<i>B</i>ulk <i>C</i>o<i>p</i>y 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 <code>where</code> clause can narrow the number of rows to be copied.
		</dd>

	</dl>

	<p>The following are generally not called since <code>isql</code> and <code>raw</code> can handle almost all cases.</p>

	<dl>
		<dt><p><code class="methodname">begin(self)</code></p></dt>

		<dd>
		Creates a new cursor.
		</dd>

		<dt><p><code class="methodname">rollback(self)</code></p></dt>

		<dd>
		Rollback all the statements since the creation of the cursor.
		</dd>

		<dt><p><code class="methodname">commit(self, cursor=None, maxrows=None)</code></p></dt>

		<dd>
		Commit all the statements since the creation of the cursor.
		</dd>

		<dt><p><code class="methodname">display(self)</code></p></dt>

		<dd>
		Display the results using the formatter.
		</dd>

	</dl>

<h3>Example session</h3>

<pre>
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

>>>
</pre>