Database Development in Jython with zxJDBC
- Connecting to the Database
- Cursor
- zxJDBC and MetaData
- Prepared Statements
- Errors and Warnings
- dbexts
- About This Article
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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:
Include the appropriate database driver and the zxJDBC.jar file in the classpath.
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.