software-mariodiana
11/8/2014 - 2:26 AM

How to use a database connection pool (Tomcat's) with Jython. Most sample code demonstrates using a zxJDBC object, but from everything I can

How to use a database connection pool (Tomcat's) with Jython. Most sample code demonstrates using a zxJDBC object, but from everything I can tell, that recreates the pool with each call. If I'm right, that's badly broken. The method below gets a database connection from the Java DataSource directly, and then wraps it, for the sake of Pythonic convenience, in a Jython connection object. See: http://stackoverflow.com/q/26792742/155167

JythonDbPool.py
content_copyfile_download
from __future__ import with_statement

from com.ziclix.python.sql import PyConnection
import org.apache.tomcat.jdbc.pool as pool

# https://people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html
p = pool.PoolProperties()
p.setUrl('jdbc:mysql://localhost:3306/my_database')
p.setDriverClassName('com.mysql.jdbc.Driver')
p.setUsername('mario')
p.setPassword('myP@ssw0rd')
p.setValidationQuery("SELECT 1")
p.setJdbcInterceptors('org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;' + 
                      'org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer')

datasource = pool.DataSource()
datasource.setPoolProperties(p)

# Now, the datasource is what you want to hold onto. You get a connection 
# from the pool, use it, and then "close" it (meaning that you return it 
# to the pool). Below is an example of doing this one time.

# http://www.jython.org/javadoc/com/ziclix/python/sql/PyConnection.html
conn = PyConnection(datasource.getConnection())

with conn.cursor() as cursor:
    cursor.execute('SELECT * FROM MyTable')
    data = cursor.fetchall()
    print data

conn.close()