# Jython using JDBC

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.

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
{
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())
{
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
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?

• Not versed into Jython, but as far as I understand you should be able to use the sqlite module from Python directly and benefit from the context managers already in place. Feb 18 '18 at 19:52

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.

• Thank you for your comment. I have a very basic knowledge of decorators but I don't understand what you mean by 'abstract out the try catch finally' and what I should try to achieve. Could you elaborate on this? Jan 19 '18 at 7:01
• The idea is to separate out the interesting parts of your code (the SQL, etc) from the boring plumbing stuff (the try catch block). Think about how you might do that. Jan 20 '18 at 14:11
• codeproject.com/Questions/172028/How-to-refactor-try-catch Jan 20 '18 at 14:16
• Something like the link I posted above, which is in java but the idea is the same. Jan 20 '18 at 14:16

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__":
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.