Home | www.python.org
Download | Documentation

Documentation
Overview
Executive Summary
Invoking Jython
Jython Registry
Embedding
Compiling Jython from source
Working with Java
The Basics
JavaBean Properties
Java arrays
Subclassing
Building applets, servlets, beans...
Reloading java classes
zxJDBC
Python Docs (exits)
Python Tutorial
Library Reference
Other
Jython vs. CPython
Jython FAQ (exit)
List Archives (exit)
JPython paper (exit)
Email Us
jython-dev@lists.sourceforge.net

### Database connectivity in Jython

The zxJDBC package provides a nearly 100% Python DB API 2.0 compliant interface for database connectivity in Jython. It is implemented entirely in Java and makes use of the JDBC API. This means any database capable of being accessed through JDBC, either directly or using the JDBC-ODBC bridge, can be manipulated using zxJDBC.

### Getting a Connection

First, make sure a valid JDBC driver is in your classpath. Then start Jython and import the zxJDBC connection factory.

#### Using a Driver

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 `connect` method as standard Python keyword arguments. You will be returned a connection object.

    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
    >>> d, u, p, v = "jdbc:mysql://localhost/test", None, None, "org.gjt.mm.mysql.Driver"
    >>> db = zxJDBC.connect(d, u, p, v)
     optionally
    >>> db = zxJDBC.connect(d, u, p, v, CHARSET='iso_1')
    >>>

#### 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,

The only required argument is the JNDI lookup name. All keyword arguments will be converted to their proper Context 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 InitialContext.

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.

    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
    >>> jndiName = "/temp/jdbc/mysqldb"
    >>> factory = "com.sun.jndi.fscontext.RefFSContextFactory"
    >>> db = zxJDBC.lookup(jndiName, INITIAL_CONTEXT_FACTORY=factory)
    >>>

### Getting a Cursor

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

`connection.dbname`

  Same as DatabaseMetaData.getDatabaseProductName

`connection.dbversion`

  Same as DatabaseMetaData.getDatabaseProductVersion

`cursor.updatecount`

  The value obtained from calling Statement.getUpdateCount

`cursor.lastrowid`

  The value obtained from calling DataHandler.getRowId

`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

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.

life cycle

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

developer support

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

binding prepared statements

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

building results

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

callable statement support

`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

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 `cfg` attribute.

`__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

    >>>