Jython using JDBC

Question

I want to access databases by Jython by using JDBC drivers. I started with SQLite because this needs no database installation. I found two tutorials that implement similar things in Java and in Python using the same sample database.

• SQLite Python samples
• SQLite Java samples

I translated the following Java program to Jython:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Sample
{
  public static void main(String[] args) throws ClassNotFoundException
  {
    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.sqlite.JDBC");

    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.

      statement.executeUpdate("drop table if exists person");
      statement.executeUpdate("create table person (id integer, name string)");
      statement.executeUpdate("insert into person values(1, 'leo')");
      statement.executeUpdate("insert into person values(2, 'yui')");
      ResultSet rs = statement.executeQuery("select * from person");
      while(rs.next())
      {
        // read the result set
        System.out.println("name = " + rs.getString("name"));
        System.out.println("id = " + rs.getInt("id"));
      }
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
  }
}

Here is my implementation of this Java program in Jython:

# rem set PATH=C:\ProgramData\Oracle\Java\javapath;%PATH%
# set CLASSPATH=C:\sqlite\java\sqlite-jdbc-3.21.0.jar
# C:\jython2.7.0\bin\jython.exe sample.py

import java
from java.sql import SQLException
#load the sqlite-JDBC driver using the current class loader
java.lang.Class.forName("org.sqlite.JDBC")
url = "jdbc:sqlite:C:/sqlite/db/chinook.db"
connection = None
try:
    connection = java.sql.DriverManager.getConnection(url)
    statement = connection.createStatement()
    statement.executeUpdate("drop table if exists person")
    statement.executeUpdate("create table person (id integer, name string)");
    statement.executeUpdate("insert into person values(1, 'leo')");
    statement.executeUpdate("insert into person values(2, 'yui')");
    rs = statement.executeQuery("select * from person")
    while (rs.next()):
        print("name = %s"%(rs.getString("name")))
        print("id = %s"%(rs.getString("id")))
except SQLException as e:
    # if the error message is "out of memory", 
    # it probably means no database file is found
    print("error: %s"%e.getMessage())
finally:
    try:
        if(connection is not None):
            connection.close()
    except SQLException as e:
        # connection close failed.
        print("error: %s"%e.getMessage())

Is this an appropriate translation from Java to Jython? Can the Jython program be improved?

Answer 1

It's fine for a single script. Python has first class functions so you can create a decorator or a some utility code to abstract out the try catch finally.

Answer 2

The translation is very faithful, testament to how similar language constructs really are ... in any case there are a lot of ways that this would probably be better in production code.

• Foremost, take a look at the typical style guidelines for Python programs, PEP8, not to follow them slavishly, but to get a good sense of what other readers will typically expect a Python program to look like.
• For example, there are several unnecessary parenthesis sprinkled in throughout the code, I'd also typically expect an empty line after the imports. Also, it's Python, you don't need semicolons at the end of lines.
• In a production script you'd want to pass in the used database file. And constants should follow a different naming convention than regular variables, usually uppercase, that is, URL.
• Also you most likely want a main function that's not invoked when the file is used as a module. That's just good practice.
• x is not None can very likely more easily be written as x because None is also a false value (so x is not False becomes x is True becomes simply x).
• But also try/finally combination in Java (which coincidentally has a better way of writing it as try (Connection c = ...) { ... } too) could be written better in Python with with ... as connection: which will take care of the freeing up of the resource. However in Jython it seems you need contextlib.closing as a wrapper to achieve what Java calls AutoCloseable. Meaning it would be with closing(...) as connection: basically.
• I wouldn't necessarily catch the exceptions since the backtrace provides lots of valuable context, but then again that all depends on how the script is intended to be used.

Apart from that looks fine.

Looks like this:

# rem set PATH=C:\ProgramData\Oracle\Java\javapath;%PATH%
# set CLASSPATH=C:\sqlite\java\sqlite-jdbc-3.21.0.jar
# C:\jython2.7.0\bin\jython.exe sample.py

import java
from java.sql import SQLException
from contextlib import closing


URL = "jdbc:sqlite:herp.db"


def main():
    try:
        with closing(java.sql.DriverManager.getConnection(URL)) as connection:
            statement = connection.createStatement()
            statement.executeUpdate("drop table if exists person")
            statement.executeUpdate("create table person (id integer, name string)")
            statement.executeUpdate("insert into person values(1, 'leo')")
            statement.executeUpdate("insert into person values(2, 'yui')")

            rs = statement.executeQuery("select * from person")
            while rs.next():
                print("name = %s" % rs.getString("name"))
                print("id = %s" % rs.getString("id"))
    except SQLException as e:
        # if the error message is "out of memory",
        # it probably means no database file is found
        print("error: %s" % e.getMessage())


if __name__ == "__main__":
    # load the sqlite-JDBC driver using the current class loader
    java.lang.Class.forName("org.sqlite.JDBC")

    main()

---

Oh yeah that's actually quite a good point: Python, and most likely Jython as well, has native modules available for a lot of things, meaning if you don't need to use Java specific libraries, perhaps you can get away with using those and their more "native" APIs.