Reading an sqlite database from Jython.

Question

I am trying to run the jython sqlite examples posted here.

################################################################################
#
#  sqlite_using_ziclix - An example of using the Python DB-API 2.0 compliant 
#                        ziclix implementation to interact with a SQLite database.
#                        Creates a 'planet' table in a SQLite database
#                        named 'solarsys.db', populates it with some data and 
#                        then executes a query to retrieve data from that table.
#
#  Works with Jython 2.5, must have the zentus sqlitejdbc.jar in your
#  CLASSPATH at execution time.
#  Known to work with sqlitejdbc-v056.jar
#
################################################################################

import sys

from com.ziclix.python.sql import zxJDBC

################################################################################

DATABASE    = "solarsys.db"
JDBC_URL    = "jdbc:sqlite:%s"  % DATABASE
JDBC_DRIVER = "org.sqlite.JDBC"

TABLE_NAME      = "planet"
TABLE_DROPPER   = "drop table if exists %s;"                      % TABLE_NAME
TABLE_CREATOR   = "create table %s (name, size, solar_distance);" % TABLE_NAME
RECORD_INSERTER = "insert into %s values (?, ?, ?);"              % TABLE_NAME
PLANET_QUERY = """
select name, size, solar_distance
from %s
order by size, solar_distance desc
""" % TABLE_NAME

PLANET_DATA = [('mercury' , 'small' ,    57),  # distance in million kilometers
               ('venus'   , 'small' ,   107),
               ('earth'   , 'small' ,   150),
               ('mars'    , 'small' ,   229),
               ('jupiter' , 'large' ,   777),
               ('saturn'  , 'large' ,   888),
               ('uranus'  , 'medium',  2871),
               ('neptune' , 'medium',  4496),
               ('pluto'   , 'tiny'  ,  5869),
              ]

################################################################################

def main():
    dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    cursor = dbConn.cursor()
    try:
        cursor.execute(TABLE_DROPPER)
        cursor.execute(TABLE_CREATOR)
    except zxJDBC.DatabaseError, msg:
        print msg
        sys.exit(1)

    try:
        cursor.executemany(RECORD_INSERTER, PLANET_DATA)
        dbConn.commit()
    except zxJDBC.DatabaseError, msg:
        print msg
        sys.exit(2)

    try:
        cursor.execute(PLANET_QUERY)
        for row in cursor.fetchall():
            name, size, dist = row[:]
            print "%-16.16s  %-8.8s  %4d" % (name, size, dist)
    except zxJDBC.DatabaseError, msg:
        print msg
        sys.exit(3)

    cursor.close()
    dbConn.close()
    sys.exit(0)

################################################################################

def getConnection(jdbc_url, driverName):
    """
        Given the name of a JDBC driver class and the url to be used 
        to connect to a database, attempt to obtain a connection to 
        the database.
    """

    try:
        # no user/password combo needed here, hence the None, None
        dbConn = zxJDBC.connect(jdbc_url, None, None, driverName)
    except zxJDBC.DatabaseError, msg:
        print msg
        sys.exit(-1)

    return dbConn

################################################################################
################################################################################

if __name__ == '__main__':
    main()

This requires org.sqlite.JDBC to be avaialable, so I downloaded the jar file and put it in the current directory. Then I tried to run the examples with

jython -Dpython.path=.:sqlitejdbc-v056.jar ./db.py 

but I am getting a class not found error:

java.lang.ClassNotFoundException: java.lang.ClassNotFoundException: org.sqlite.JDBC

How can I run this example?

Answer 1

I just tried setting the CLASSPATH environment variable instead

export CLASSPATH=`pwd`/sqlitejdbc-v056.jar
jython ./db.py

and it worked.