trjhtr

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:ProgramDataOracleJavajavapath;%PATH%
# set CLASSPATH=C:sqlitejavasqlite-jdbc-3.21.0.jar
# C:jython2.7.0binjython.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?

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.

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:ProgramDataOracleJavajavapath;%PATH%
# set CLASSPATH=C:sqlitejavasqlite-jdbc-3.21.0.jar
# C:jython2.7.0binjython.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.