Converting JDBC to CSV

Question

One of my first ever Jython scripts lets me get comma-separated values out of an RDBMS:

from java.lang import System
from java.sql import DriverManager, ResultSet
import csv
import cStringIO as StringIO
import sys
def doIt():
    if System.getProperty("jdbc.drivers") is None or System.getProperty("jdbc2csv.query") is None:
        try:
            System.setProperty("jdbc2csv.url", sys.argv[1])
            System.setProperty("jdbc2csv.query", sys.argv[2])
        except IndexError:
            print 'Usage: java -cp jython.jar:jdbc.jar -Djdbc.drivers=<jdbc.driver.class> -Djdbc2csv.query=<query> -Djdbc2csv.url-<jdbc.url> org.python.util.jython ' + sys.argv[0]
            exit()
    output = StringIO.StringIO()
    conn = DriverManager.getConnection(System.getProperty("jdbc2csv.url"))
    res = conn.createStatement().executeQuery(System.getProperty("jdbc2csv.query"))
    meta = res.getMetaData()
    header = ['row #']

    for i in range(0, meta.getColumnCount()):
        header.append(meta.getColumnName(i+1)) 

    writer = csv.writer(output, quotechar = '"', quoting = csv.QUOTE_ALL, escapechar = '\\', delimiter = ',')
    writer.writerow(header)
    i = 0

    while not res.isLast():
        res.next()
        i = i + 1
        row = [i]

        for column in range(1, meta.getColumnCount()+1):
            row.append(res.getString(column))
        writer.writerow(row)

    conn.close()
    print output.getvalue()

if __name__ == '__main__':
    doIt()

One thing I can immediately see as a potential issue is utilising the optparse module to handle options in addition to JVM properties instead of sys.argv. But, I await others ripping it apart.

Answer 1

Some JDBC-related notes:

• use getColumnLabel() instead of getColumnName()

• using islast() as part of the ResultSet loop is unconventional (and potentially slow, and potentially not supported). The intended use pattern is while res.next(): ( next() returns true when successful).

• you should have a symmetrical mechanism for accessing both the column names, as well as the column values. In your header row, you looped the range(0, meta.getColumnCount()): and in the column values you loop the range range(1, meta.getColumnCount() + 1):. You shoudl pick one, and stick with it (also, yes, everyone agrees with you that JDBC should have been zero-based with column indexes... like ... really?).

• In general, do not use ResultSet.getString() unless you know it is a String in the column. In this specific case though, the effort required to check whether the values are numeric, etc. are overkill. Still, do not get in the habit of getString().

• This is a short&sweet program, but even then, you should observe the best-practices for JDBC. You shoud close the Statement instance properly (which means you will need to store it in a variable), and also close the ResultSet. In this case, you close the Connection and that may clean things up nicely, but the protocol is to close things in reverse order to opening them.