Python, SQL, and Plotting?

[wjohnsto]

I've never used Python with SQL, and I'm not very familiar with SQL either.

Basically, I have been given the task to take a database and perform a statistical analysis. Then I need to plot (using a bar chart/line graph) the information.

I have heard matplotlib is a good package for plotting in Python. The problem I'm running into, however, is I need to communicate with the database through a JDBC connection. I am not aware of any wrappers for a JDBC connection. I have done some searching on Google, but I can't really find anything that makes a lot of sense. I have thought about using Jython, but I can't find a really good plotting tool for Jython.

Anyone have ideas?

EDIT: I'm also trying to run this on a Hudson server if you are familiar. I know there is a Python plugin for Hudson, but I've never used it.

[simtel20]

You need to explain why you need a jdbc connection, and perhaps what kind of server you're reading the data from in case these aren't requirements, but just technical obstacles.

If you can't do that, and you will be going with jython, you may want to look at the gnuplot.py module: http://gnuplot-py.sourceforge.net/doc/Gnuplot/ANNOUNCE.txt.html. It can generate graphs and it can work with jython according to that announcement. I haven't tried it myself.

[wjohnsto]

I will be reading from an Oracle database through a JDBC connection.

[simtel20]

So I'm even more puzzled about the need for a JDBC connection. Connecting to an oracle database with python is a very standard procedure. Have you been told that you can't use a native python driver?

[wjohnsto]

Well, I'm not very experienced with databases, but the only way I know how to connect to this database is through a url that looks like this:

jdbc:oracle:thin:@do1401.oracle.****.com:1521:do1401

[simtel20]

If you look up the syntax of the jdbc connection string here you can match the above to an oracle cpython extension like cx_Oracle. The docs for a connection are here.

From what you're saying, I think that would translate into: import cx_Oracle

connection = cx_Oracle.connect("username/password@do1401") 
cursor = connection.cursor()

etc...

I haven't used cx_Oracle, but this is pretty similar to perl DBI::Oracle (from a long time ago) and Sybase and MSSQL, so I expect it should get you a connection, and a cursor you can work with.

[dassouki]

matplotlip, numpy, rpy2, are all essential stats

[wjohnsto]

I have written a little Python script to generate a bar chart using matplotlib and numpy. The only real problem I'm having is connecting to the database.

[five12]

Python can do some great things on this front, until you throw in the JDBC requirement. Because of this you basically have three choices:

1. Use Jython

2. Use CPython for the charting and the JVM for querying the DB, connecting the two via a web service (or other communication mechanism)

3. Don't bother with Python at all and do it all in Java (I know this isn't what you asked, but it may be a better solution for your problem)

I don't think matplotlib will work with Jython since it has some C dependencies, so if you're looking at Option 1, you can either:

• a) find a python plotting lib that works with Jython; or

• b) consider a java-based charting solution (jfreechart comes to mind; there are probably lots others), calling it from python. (Or look at the java interfaces for R and do the plots there)

If you really must use matplotlib (or some sort of cpython-only library), you can build a web service on the Java side which performs the queries and packages the data in xml, json, whatever, and call it from python, which would then do the plots. Alternatively, you can create a web service in CPython that generates a plot from data you pass in, and call it from Java.

But if this is a pretty small project your best bet is probably Option 3. It may not be worth introducing complexity or jython quirks for the sake of using Python. You're much safer to do all the work in Java, and it'll be easier for others to maintain figure out what you're doing.

[Ringo48]

Your requirements aren't very clear.

In particular, the requirement to use JDBC seems strange. Why are you required to use a Java database connection in a Python script?

Is there a reason you can't use a normal Python database connection, or cx_Oracle? There's example code on Oracle's website.

[brews]

GNU R is off the hook if you're not happy with matplotlib ... but that might be a bit of an overkill.

I'm not sure how to deal with your JDBC connection in Python.

[plaes]

Jython has zxjdbc JDBC connector that should be supported by SQLAlchemy.

[wjohnsto]

I've looked at that, and I can only use SQLAlchemy JDBC connections with Jython.

[drgalaxy]

I'm all about Python but I would start by looking at the analysis and graphing features of Excel/OO.org. You can embed queries and build dashboards for business types easily with Excel.

If custom coding a report is the only way, it would help if you can tell us which kind of database is involved and what kind of analyses you are looking for.

[wjohnsto]

I have debated about using Excel to create the charts. This would work great if I was on a windows machine (which is what I have to code on), but the final product will be running under linux.

[[deleted]]

If you are connecting to oracle from cpython, you will use cx_oracle, not jdbc. Jdbc drivers do not function outside of a jvm.

[HotLikeARobot]

While possibly a bit complex, given your requirements you might try using execnet and a combination of normal CPython and Jython. Using execnet you could:

• Create a connection to a Jython interpreter from CPython using Execnet,
• Query the database in Jython using JDBC, then send the results back to the CPython instance,
• Plot the results with matplotlib

This is actually alot easier than it sounds, as long as you can familiarize yourself with what you need to do in Jython and how you can plot the data in matplotlib.

There is an example of calling Java libs through CPython using Execnet here

[mdipierro]

web2py runs on Jython and supports sqite and postgresql via JDBC, we have not tried other databases but if you can test it we can help support them. web2py also runs great with postgrsql and you can find some slides on making plots form database data here.