Database Development in Jython with zxJDBC

By Robert Bill

Date: Mar 29, 2002

Article is provided courtesy of New Riders.

Return to the article

Working with JDBC from Jython is no doubt valuable. It allows prototyping in Jython and leverages JDBC skill sets; however, the numerous methods specific to Java types makes it clear that it is a Java API. Although Jython easily makes use of Java's database connectivity, it was still left wanting for a Java implementation of Python's DB API. zxJDBC fills this void. New Riders' Author Robert W.Sullivan Bill explains zxJDBC in depth and shows you how to use it to simplify and accelerate database development in Jython.

Jython programmers can easily use Java's JDBC API. After all, Jython allows you to import and use most any Java class within its Python syntax, and working with JDBC from Jython is no doubt valuable. Using Java's JDBC API directly allows prototyping in Jython and leverages JDBC skill sets; however, the numerous methods specific to Java data types make it clear that it is a Java API. Java, databases, and therefore JDBC are type-rich. The down side is that methods specific to Java native type are seemingly contrary to Jython's high-level, dynamic types.

In contrast, Python has a database API referred to as just the Python DB API, currently at version 2.0. Python's DB API 2.0 has been a standard API for interacting with databases from CPython; however, database drivers used by CPython are often useless to Jython because of underlying C implementations. Although Jython easily makes use of Java's database connectivity, it was still left wanting for a Java implementation of Python's DB API. Brian Zimmer, an avid Jython, Java, and Python developer, wrote zxJDBC to fill this void. In reality, zxJDBC does more that just implement the DB API, it also adds extensions to this API. Brian's zxJDBC tools are freely available, include source code, are well documentation, and are available at http://sourceforge.net/projects/zxjdbc/ or http://www.ziclix.com/zxjdbc/. The zxJDBC tools may be incorporated into Jython proper by the time you read this, eliminating the need for a separate download. Check http://www.jython.org, or the Jython information at http://www.newriders.com/ for more information on this. If it isn't included in your version of Jython, you will need to download zxJDBC, and include the zxJDBC.jar file in your classpath.

The zxJDBC package contains more tools than are shown here, including a package implementing the pipe pattern and the easy creation of datahandlers and DataHandlerFilters.

Connecting to the Database

When you use the zxJDBC package, all that is required before calling the connection function is that zxJDBC.jar and the required JDBC driver exist in the classpath. The actual loading of the driver occurs behind the scenes when creating a connection to the database. The two steps to establishing a database connection with zxJDBC are as follows:

  1. Include the appropriate database driver and the zxJDBC.jar file in the classpath.

  2. Supply a JDBC URL, username, password, and the name of the database Driver class to the zxJDBC.connect() method.

An appropriate classpath setting for using zxJDBC with the MySQL and PostgreSQL database systems looks like this:

# For MySQL
set CLASSPATH=mm_mysql-2_0_4-bin.jar;\path\to\zxJDBC.jar;%CLASSPATH%

# For PostgreSQL
set CLASSPATH=\path\to\jdbc7.1-1.2.jar;\path\to\zxJDBC.jar;%CLASSPATH%

The zxJDBC.connect method returns the database connection and has the following syntax:

zxJDBC.connect(URL, user, password, driver) -> connection

Retrieving the connection with the zxJDBC.connect method looks like this:

from com.ziclix.python.sql import zxJDBC
mysqlConn = zxJDBC.connect("jdbc:mysql://localhost/test",
              "jyuser", "beans", 
              "org.gjt.mm.mysql.Driver")

postgresqlConn = zxJDBC.connect("jdbc:postgresql://localhost/test",
                "jyuser", "beans",
                "org.postgresql.Driver")

Special parameters required by drivers may appear as keyword arguments to the connect function. To set autoReconnect to true when connecting to a MySQL database include that parameter as a keyword argument as follows:

url = "jdbc:mysql://localhost/test"
user = "jyuser"
password = "beans"
driver = "org.gjt.mm.mysql.Driver"
mysqlConn = zxJDBC.connect(url, user, password, driver,
              autoReconnect="true")

Connection errors raise the exception DatabaseError, so handling errors with a connection attempt requires an except statement like the following:

url = "jdbc:mysql://localhost/test"
user = "jyuser"
password = "beans"
driver = "org.gjt.mm.mysql.Driver"
try:
  mysqlConn = zxJDBC.connect(url, user, password, driver,
                autoReconnect="true")
except zxJDBC.DatabaseError:
  pass
  #handle error here

If you use a connection factory from the javax.sql package, or a class that implements javax.sql.DataSource or javax.sql.ConnectionPoolDataSource, you can connect with the zxJDBC.connectx method. Note that the javax.sql package is not included in the normal JDK installation, except for the enterprise edition. The MySQL JDBC driver does, however, include the MysqlDataSource class used in the example below. The zxJDBC.connectx method requires the DataSource class and all the database connection parameters as keyword arguments, or as a dictionary object:

from com.ziclix.python.sql import zxJDBC
userInfo = {'user':'jyuser', 'password':'beans'}
con = zxJDBC.connectx("org.gjt.mm.mysql.MysqlDataSource",
           serverName="localhost", databaseName='test',
           port=3306, **userInfo)

The bean property names are set with keyword parameters in the preceding example, but could also be included in the dictionary containing the username and password information:

from com.ziclix.python.sql import zxJDBC
userInfo = {'user':'jyuser', 'password':'beans',
      'databaseName':'test', 'serverName':'localhost',
      'port':3306}
con = zxJDBC.connectx("org.gjt.mm.mysql.MysqlDataSource", **userInfo)

You can also obtain a connection through a jndi lookup with the zxJDBC.lookup method. The lookup method only requires a string representing the JNDI name bound to the specific connection or DataSource you desire. Keyword parameters may be included and are converted to the static field values of javax.jndi.Context when the keywords match a Context's static field name.

Cursor

A zxJDBC cursor is the object used to actually interact with the data in the database. A zxJDBC cursor is actually a wrapper around the JDBC Statement and ResultSet objects. The handling of the result sets is what differentiates the static and dynamic cursor types available in zxJDBC. A dynamic cursor is lazy. It iterates through the result set only as needed. This saves memory and evenly distributes processing time. A static cursor is not lazy. It iterates through the entire result set immediately, and incurs the memory overhead of doing so. The advantage of a static cursor is that you know the rowcount soon after executing a statement, something you cannot know when using a dynamic cursor.

To get a cursor, call the zxJDBC connection object's cursor() method. An example of connecting to the MySQL database server and retrieving a cursor object appears here:

from com.ziclix.python.sql import zxJDBC
url = "jdbc:mysql://localhost/test"
user = "jyuser"
password = "beans"
driver = "org.gjt.mm.mysql.Driver"
con = zxJDBC.connect(url, user, password, driver,
            autoReconnect="true")
cursor = con.cursor() # Static cursor

# Alternatively, you can create a dynamic cursor
cursor = con.cursor(1) # Optional boolean arg for dynamic

A cursor object's execute method executes SQL statements. The following example shows how to execute an SQL statement that selects all the data in a table called random. This article assumes the random table consists of a primary key column called "pkey", a "letter" column of type char that includes random letters from the alphabet (lower case and upper), and a "number" column of type int that represents the absolute index of where the letter appears in the list of lower and upper case letters.

>>> from com.ziclix.python.sql import zxJDBC
>>> url = "jdbc:mysql://localhost/test"
>>> user, password, driver = "jyuser", "beans", 
"org.gjt.mm.mysql.Driver"
>>> con = zxJDBC.connection(url, user, password, driver)
>>> cursor = con.cursor()
>>> cursor.execute("SELECT * FROM random")

To iterate through the results of a statement, you must use the cursor's fetchone, fetchmany, and fetchall methods. The fetchone and fetchall methods do exactly as their names imply, fetch one result set row, or fetch all rows. The fetchmany method accepts an optional argument which specifies the number of rows to return. Each time multiple rows are returned, they are returned as a sequence of sequences (list of tuples). You can see the usage of these three methods as the preceding example is continued:

>>> cursor.fetchone()
(41, 'O', 1)
>>> cursor.fetchmany()
[(6, 'f', 2)]
>>> cursor.fetchmany(4)
[(49, 'W', 4), (35, 'I', 5), (43, 'Q', 6), (37, 'K', 3)]
>>> cursor.fetchall() # All remaining in this case
[(3, 'c', 7), (17, 'q', 8), (29, 'C', 9), (36, 'J', 10), (43, 'Q', 11),
 (23, 'w', 12), (49, 'W', 13), (25, 'y', 14), (40, 'N', 15), (50, 'X', 
16), (46, 'T', 17), (51, 'Y', 18), (8, 'h', 19), (25, 'y', 20), (7, 'g', 
21), (11, 'k', 22), (1, 'a', 23)]

After a query has been executed, you can view the row information for those rows in the result set with the cursor's description attribute. The description attribute is read-only, and contains a sequence for each row in the result set. Each sequence includes the name, type, display size, internal size, precision, scale, and nullable information for a column of the row set. A description of the previous query looks like this:

>>> cursor.description
[('number', -6, 4, None, None, None, 1), ('letter', 1, 1, None, None, 
None, 1), ('pkey', 4, 10, None, 10, 0, 0)]

Table 1 shows the complete set of the cursor object's methods and attributes.

Table 1—The Cursor Object's Methods and Attributes

Method/Attribute

Description

description

Information describing each column that appears in the results of a query. The information is a seven-item tuple containing name, type code, display size, internal size, precision, scale, and nullability.

rowcount

The number of rows in the results. This only works when the cursor is a static cursor, or after completely traversing the result set with a dynamic cursor.

callproc(procedureName,[parameters])

Calls stored procedures and applies only to those databases that implement stored procedures.

close()

Closes the cursor.

execute(statement)

Executes a statement.

executemany(statement, parameterList)

Executes a statement with a parameter list. With this, you can use question marks in the statement for values and include a tuple of values to the parameterList which are replaced in the statement.

fetchone()

Retrieves one row of the query results.

fetchmany([size])

Retrieves arraysize number of rows if no argument is given. If the argument arg is supplied, it returns arg numbers of result rows.

fetchall()

Retrieves all remaining result rows.

nextset()

Continues with the next result set. This applies only to those databases that support multiple result sets.

arraysize

The number of rows fetchmany() should return without arguments.

zxJDBC and MetaData

The Python DB API does not contain metadata specifications, but zxJDBC does provide some connection metadata with a number of connection and cursor attributes. These attributes match bean properties found in the JDBC java.sql.DatabaseMetaData object. Table 2 shows the zxJDBC cursor fields and the underlying java.sql.DatabaseMetaData bean methods.

Table 2—zxJDBC MetaData

zxJDBC Attribute

DatabaseMetaData Accessor

connection.dbname

getDatabaseProductName

connection.dbversion

getDatabaseProductVersion

cursor.tables(catalog, schemapattern, tablepattern, types)

getTables

cursor.columns(catalog, schemapattern, tablenamepattern, columnnamepattern)

getColumns

cursor.foreignkeys(primarycatalog, primaryschema, pimarytable, foreigncatalog, foreignschema, foreigntable)

getCrossReference

cursor.primarykeys(catalog, schema, table)

getPrimaryKeys

cursor.procedures(catalog, schemapattern, procedurepattern)

getProcedures

cursor.procedurecolumns(catalog, schemapattern, procedurepattern, columnpattern)

getProcedureColumns

cursor.statistics(catalog, schema, table, unique, approximation)

getIndexInfo

Here is an example of extracting some metadata from the MySQL random database:

>>> from com.ziclix.python.sql import zxJDBC
>>> url = "jdbc:mysql://localhost/test"
>>> driver = "org.gjt.mm.mysql.Driver"
>>> dbconn = zxJDBC.connect(url, "jyuser", "beans", driver)
>>> dbconn.dbname
'MySQL'
>>> dbconn.dbversion
'3.23.32'

The remaining metadata is accessible through a cursor object. When the cursor retrieves information, it stores it internally waiting for the user to fetch it. To view metadata provided by the cursor, call each metadata method, then use the cursor to retrieve the data as shown here:

>>> cursor.primarykeys(None, "%", "random")
>>> cursor.fetchall()
[('', '', 'random', 'pkey', 1, 'pkey')]
>>> cursor.tables(None, None, "%", None)
>>> cursor.fetchall()
[('', '', 'random', 'TABLE', '')]
>>> cursor.primarykeys('test', '%', 'random')
>>> cursor.fetchall()
[('test', '', 'random', 'pkey', 1, 'pkey')]
>>> cursor.statistics('test', '', 'random', 0, 1)
>>> cursor.fetchall()
[('test', '', 'random', 'false', '', 'PRIMARY', '3', 1, 'pkey', 'A', '23', None, '')]

Prepared Statements

The executemany() cursor method is the Python DB API equivalent to Java's prepared statement. In reality, other statements executed are prepared in zxJDBC, but the executemany() method allows you to use question marks for values in the SQL statement. The second argument to executemany() is a tuple of values that replaces the question marks in the SQL statement:

>>> sql = "INSERT INTO random (letter, number) VALUES (?, ?)"
>>> cur.executemany(sql, ('Z', 51))
>>>
>>> # view the row
>>> cur.execute("SELECT * from random where letter='Z'")
>>> cur.fetchall()
[('Z', 51, 24)]

Errors and Warnings

Exceptions that may be raised in zxJDBC are the following:

Each of these exceptions are within the zxJDBC package, so except clauses should look like the following:

>>> try:
...   pass #Assume a method that raises and error is here
... except zxJDBC.DatabaseError:
...   pass # Handle DatabaseError
... except zxJDBC.ProgrammingError:
...   pass # handle ProgrammingError
... except notSupportedError:
...   pass # handle not supported error
... except zxJDBC.Error:
...   pass # Handle the generic Error exception

You can also get warnings with the cursor's warnings attribute. If no warnings exist, then the cursor.warnings attribute is None.

dbexts

Another extension available with the zxJDBC package is dbexts. dbexts is a Python module that adds another layer of abstraction around the Python DB API 2.0. With DBexts, you can specify connection information in a configuration file, and then use the higher-lever dbexts methods on the connections defined. In order to use dbexts, the dbexts.py module that comes with zxJDBC must be added to the sys.path.

The configuration file can be any file, but the default is a file named dbexts.ini that resides in the same directory as the dbexts.py file. To use another file, include the filename in the dbexts constructor. Listing 1 is a configuration file that defines connections to the MySQL and PostgreSQL database systems.

Listing 1—Sample dbexts Configuration

[default]
name=mysqltest

[jdbc]
name=mysqltest
url=jdbc:mysql://localhost/test
user=jyuser
pwd=beans
driver=org.gjt.mm.mysql.Driver

[jdbc]
name=pstest
url=jdbc:postgresql://localhost/test
user=jyuser
pwd=beans
driver=org.postgresql.Driver

Once the configuration file is defined, you can connect to the database by instantiating the dbexts class. In the following example, the dbexts.ini file is placed in the current working directory. You can alternatively place it in the directory on the sys.path where you placed the dbexts.py module:

>>> from dbexts import dbexts
>>> mysqlcon = dbexts("mysqltest", "dbexts.ini")
>>> psgrscon = dbexts("pstest", "dbexts.ini")
>>>
>>> # execute raw sql and get a list of headers and results
>>> psgrscon.raw("SELECT * from random")
([('letter', 1, 1, None, None, None, 1), ('number', 4, 11, None, 10, 0, 
1)], [('A', 4), ('f', 6), ('a', 1)])
>>>
>>> # execute interactive sql
>>> psgrscon.isql("select * from random")

LETTER | NUMBER
---------------
A      | 4
f      | 6
a      | 1

3 rows affected
>>>
>>> # Display schema- this works with postgresql, not MySQL
>>> psgrscon.schema("random")
Table
 random

Primary Keys

Imported (Foreign) Keys

Columns
 letter        bpchar(1), nullable
 number        int4(4), nullable

Indices
>>>
>>> # show tables in MySQL 'test' database
>>> mysqlcon.table()

TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS
-----------------------------------------------------------
          |             | random     | TABLE      |

1 row affected

Table 3 lists the primary dbexts methods. Some of the methods have additional optional arguments and there are some additional methods, but they are a bit beyond the scope of this article. For more details, consult the excellent documentation that comes with zxJDBC.

Table 3—dbexts Methods

Method

Description

__init__(dbname, cfg, resultformatter, autocommit)

The dbexts constructor. All parameters have default values, so they are all optional. The dbname is the name you specified for the connection in the dbexts.ini file. The cfg is the location of the dbexts.ini file if it does not reside in the same directory as the dbexts.py file. The resultformatter is a callable object that accepts a list of rows as one argument and optionally accepts a list of headers. The resultformatter object is called to display data. The autocommit argument is set to 1, or true, by default, but can be set to 0 if included in the call to the constructor.

isql(sql, params, bindings, maxrows)

The isql method interactively executes SQL statements. Interactive means that results are displayed with the resultformatter immediately after executing the statement, much like the MySQL and pSQL client programs. All parameters have default values except for the sql statement. The sql parameter is the SQL statement itself. The params parameter is a tuple of value parameters used to replace ? in SQL statements. The bindings parameter allows you to bind a datahandler. See the documentation on zxJDBC for more about datahandlers. The maxrows specifies the maximum number of rows to return, where zero or None means no limit.

raw(sql, params, bindings)

Executes the sql statement and returns a tuple containing headers and results. The params and bindings arguments are the same as for the isql method.

schema(table, full, sort)

Displays a table's indices, foreign keys, primary keys, and columns. If the full parameter is nonzero, the results include the table's referenced keys. A nonzero sort value means the table's column names will be sorted.

table(table)

The table parameter is optional. Without a table argument, this method displays a list of all tables. Otherwise, the specified table's columns are displayed.

proc(proc)

The proc parameter is optional. Without it, all procedures are listed. With it, the parameters for the specified procedure are displayed.

bcp(src, table, where='(1=1)')

This method copies data from specified database and table (src and table) to the current instance's database.

begin(self)

Creates a new cursor.

rollback(self)

Rolls back statements executed since the creation of a new cursor.

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

Commits statements executed since the creation of a new cursor.

display(self)

Displays the results using the current formatter.

About This Article

If you want more in-depth information on this topic, check out Chapter 11, "Database Programming," of Jython for Java Programmers by Robert Bill.