Jython 2.5.1rc2 - zxJDBC with-statement

This past weekend (09/12/2009), the Jython developer team rolled out the second release candidate for version 2.5.1. In the release were many bug fixes and some additional features...I definitely recommend downloading it and trying it out if you haven't already done so. Thanks to all of the developers who have made this release possible.

One of the features added in this release for database work with the zxJDBC API is with-statement compatibility. Thanks to Jim Baker for updating the PyConnection and PyCursor objects in Jython so that it can now be used in the context of a with-statement. This can be useful for maintaining resources and transactional state when working with databases.

To use the new feature, you must import the closing() function from contextlib so that the connection can be bound to a variable and closed at the end of the statement. Use of this feature works as follows:

# In this example, I am connecting to a postgresql database.
# Of course, we could connect to the database of our choice so long
# as we have the appropriate JDBC driver in our CLASSPATH

>>> from __future__ import with_statement
>>> from com.ziclix.python.sql import zxJDBC                                  
>>> from contextlib import closing 
>>> jdbc_url = "jdbc:postgresql:mydatabase"                                     
>>> username = "postgres"                                                     
>>> password = "mypass"
>>> driver = "org.postgresql.Driver"
>>> with closing(zxJDBC.connect(jdbc_url, username, password, driver)) as conn:
...     with conn:                    
>>> # Use one or more cursors within the context of the with-statement                                        
...         with conn.cursor() as c:                                          
...             c.execute("select name from country")                         
...             c.fetchone()                                                  
...
(u'Afghanistan',)

In the example above, I queried a database table. However, we could use the cursor in any way that we'd like within the context of the statement. Use for transaction management and if an error occurs within a transaction then everything is rolled back.

>>> with closing(zxJDBC.connect(jdbc_url, username, password, driver)) as conn:
...     with conn:                                                             
...         with conn.cursor() as c:                                           
...             try:                                                           
...                 stmt = "insert into country values (?,?)" 
...                 # the following produces an error                 
...                 c.executemany(stmt, ['test'])   
...                 # perform more inserts and updates
...                 conn.commit()                           
...             except Exception, err:                                         
...                 raise Exception("There has been a zxJDBC error") 

Transaction management is taken care of such that if a statement fails, then the complete transaction is rolled back. This is very useful for transaction management. If you attempt to work with the cursor or connection outside of the statement then it will not work because the statement closes them after use. That is the beauty of using the with-statement for resource management. The with-statement is also nice for working with files and such in a similar manner.

For more information on the use of the with_statement, please visit PEP-343 or the open source Jython Book (In Progress) which will soon be updated to illustrate correct usage of with-statement context management for zxJDBC in 2.5.1.

Enjoy!