Python and cx_Oracle

Recently I have taken over the maintenance of an application at work that was written very quickly and, to be honest, rather badly. A department needed the app badly.

I hasten to add that it is bad because it was done so quickly. Plus the person who wrote it is not used to writing EndUser applications.

It uses an ODBC connection to an Oracle db, plus an Access db to connect, plus Excel to show the final info in. Inside the MSAccess db are 4 different queries all building on each other, each one paring information away from the previous query using left joins. To refresh the data the user must do a manual refresh and each time input the password.

Adding a component to monitor to it involves updating the Access db (easy) and then recreating at least two pivot tables as well as adding the component to other excel tabs and fields, easely 15 minutes of work. And the problem is that this is probably only going to get more critical…

So I’ve started to investigate if I could do something about this, like rewriting it in Python and then compiling it in an end-user application using py2exe. I’ve rescued all the sql queries and table connections from MS Access.
One thing is very clear : SQL queries made from Access suck ! They are like the html docs that MS Word makes – full of useless cruft everywhere

I am now trying to remake the program using Python 2.4.2, cx_Oracle for the Oracle connectivity and PySQLite. So far I’ve managed to connect to the database and execute the first query.

Note to self :

  • when debugging an SQL query, cx_Oracle or even Oracle itself, who knows, does the query evaluation backwards. It will start evaluating your SQL expression from the back. Usefull to know when you get an error, you need to look from back to front !
  • also, you don’t need a ; at the end of your SQL query for cx_Oracle. In SQLite you do. In Oracle you do not. Two days, mate, two days before I found this.

Next up : trying to get the Left Join to work as well a Tkinter window setup to show the user something. Luckely I’ve got a knowledgeable sql guy not far from me… and I am not even sure that is the way to go, just emulating what has been done before…