Reuse existing jdbcConnection

[jourquin]

Hi,
I use Py4J to interact with a Java app. The latest uses JDBC connections with different SQL engines. Some of them, such as HSQLDB, don't allow multiple connections when used as "embedded" database. Therefore, I would like to reuse the (existing) JDBC connection of the Java app in my Python code.

The jaydebeapi.Connection(...) API accepts a Java JDBC connection as first parameter, but it also needs the converters to use as second parameter.

The following trick seems to work :

1. Create a set of default converters (`gateway.jvm' comes from the Py4J API):

types = gateway.jvm.java.sql.Types
    types_map = {}
    const_re = re.compile('[A-Z][A-Z_]*$')
    for i in dir(types):
        if const_re.match(i):
            types_map[i] = getattr(types, i)
    jaydebeapi._init_types(types_map)

2. Create a connection, given myJavaCon being the Java JDBC connection retrieved from my Java app using Py4J:

conn = jaydebeapi.Connection(myJavaCon, jaydebeapi._converters)

3. The connection is then functionnal. Example with a Pandas (import pandas as pd) data frame:

df = pd.read_sql_query("select * from myTable", conn)

Is there a simpler way to achieve this ? Could the jaydebeapi.Connection(...) code be modified in order to create a set of default converters if none is given ?

Many thanks !

Bart