Python Database hacks

I seem to have writer's block this weekend. Or if you want to be more pragmatic, I did not take the time to work on a mini-project that I can write about. In reality, I spent last week goofing off, decompressing after an almost complete project, and putting finishing touches to a bunch of other little projects that have been in the pipeline for a while. As a result, I don't have anything to write about this weekend. A little disappointing, given that this was Thanksgiving weekend, and I had four days of vacation to do something interesting. But as so often happens with long vacations, the time gets sucked up into other little non-consequential things.

So this week, I will write about three little features that I came across while writing Python scripts to work against an Oracle database. These should work with other databases as well. Maybe they don't even deserve to be called hacks since they are adequately documented if you look hard enough, but its probably new stuff for beginning Python programmers such as myself. Hopefully this will be useful to others who are in the same situation as me.

Exception Handling

I found this out when trying to update a bunch of linked database tables using a Python script. An error in my program or an unhandled problem in the data would result in a partial update to the tables, forcing me to manually rollback the changes that had been applied. To handle this, Python has a try:...except: clause that can be used to check for exceptions and commit or rollback. Here is the code snippet to do this:

1 2 3 4 5 6 7 8 9 10 11 12 13

import traceback ... conn = cx_Oracle.connect(connection_string) cursor1 = conn.cursor() cursor2 = conn.cursor() ... try: ... do something with various cursors derived from conn conn.commit() except Exception, inst: print "Error:", inst print traceback.print_exc(sys.exc_info()[2]) conn.rollback()

The first print in the exception block will print the database error message, and the second print will print the stack trace. You can find more information in the Python tutorial page for Errors and Exceptions.

Accessing Database Metadata

I had posted code for a Python CSV to Excel converter in my blog sometime back. Most of the time, I would use this to convert CSV files dumped from a Java program or Python script into an Excel spreadsheet for consumption by my flat-file challenged clients. I decided to extend it recently to take in a SQL SELECT statement, and then dump out the results into an Excel spreadsheet. In addition to the rows, I also needed to get at the column names for the header. Python provides a description method for the cursor which returns this information, like so:

1 2 3 4 5 6 7 8 9 10

conn = cx_Oracle.connect(connection_string) cursor = conn.cursor() cursor.execute(query) rows = cursor.fetchall() colnames = [] metadaten = cursor.description() for metadata in metadaten: colnames.append(metadata[0]) print ",".join(colnames) ...

The description method actually returns a lot more information about the column, but I was only interested in the column name. More information about the description() method and the contents of the data structure returned by it can be found in the ASPN Python Cookbook page here.

Unicode Handling

If your database is set up to store only ASCII characters, and if your data has characters which fall outside the 0..127 range, then you will have problems with storing this data. To get around this, you can have your script replace the non-ascii characters with "?" using the following call before you insert or update.

1	asciifield = unicodefield.encode("ascii", "replace")

The encode call takes other parameters and character sets, see the Python Unicode Tutorial for more information and examples.

Python scripting with Oracle

I had not used Oracle in a while, and when I last used it, I didn't know about Python. Python has become my scripting language of choice for about a year now. My current database at work is Oracle, so it was natural for me to investigate if I could use Python to communicate with the remote Oracle database. This post describes in a step-by-step manner what I needed to get Python to work with the Oracle database.

I run Python 2.4.1 on Fedora Core 4 Linux. The Oracle version is Oracle 10g. The Oracle database runs on a central machine, and I did not have any Oracle software installed on my development machine, and nor did I want to download and run a lightweight version of Oracle, such as Oracle 10g Express Edition to get at the software. Not that I am short of disk or resources on my local box, it just seemed kind of wasteful, and I did not want to have to learn how to administer an Oracle database before I could get Oracle access on my machine.

However, Python libraries for various databases generally follow the same strategy to connect to the database as a person would if using the default command line client. Unlike JDBC on Java, for instance, which provide an uniform interface regardless of whether you connect to MySQL or Oracle or PostgreSQL or Sybase. In this case too, unless you are set up to connect using SQL*Plus (the default client), you are pretty much out of luck connecting with any of the Python libraries for Oracle connectivity. I guess this makes sense - unless you were comfortable working with the database interactively, why would you bother to learn how to start scripting it?

Someone at work pointed me to the Oracle Instant Client. This is a small client that allows you to access a remote Oracle database through a set of shared libraries. The one major application that comes with it is SQL*Plus. I downloaded the 10.1 version, which was the latest at the time. It is packaged as a zip file which you need to unzip into a location of your choice. In order to get SQL*Plus to work, you need to set up your LD_LIBRARY_PATH to point to your Instant Client Installation directory, and your PATH to point to SQL*Plus. You can do this in your .bash_profile, like so:

1 2 3

IC_INSTALL_DIR=/opt/oracle/instantclient10_1 export PATH=$IC_INSTALL_DIR:$PATH export LD_LIBRARY_PATH=$IC_INSTALL_DIR:$LD_LIBRARY_PATH

This will allow your SQL*Plus prompt to come up, but you will not be able to login to the database. To login to the database, you need to configure the Instant Client libraries with a file called tnsnames.ora. You can configure the Instant Client libraries to look for it in a directory of your choice by also setting the TNS_ADMIN environment variable in your .bash_profile, like so:

1	TNS_ADMIN=/etc

And my /etc/tnsnames.ora looks like this (all names changed to protect the guilty):

1 2 3 4 5 6 7 8 9

remotedb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = remotehost.mycompany.com)(PORT = 1234)) ) (CONNECT_DATA = (SERVICE_NAME = remotedb) ) )

So now I can connect to my remote database server remotehost.mycompany.com:1234/remotedb as scott/tiger (not my real user/password, by the way) using SQL*Plus like so:

1 2 3 4 5 6 7 8 9 10 11

$ sqlplus scott/tiger@remotedb SQL*Plus: Release 10.1.0.5.0 - Production on Sat Oct 7 11:34:04 2006 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL>

Now that I could connect to the remote database from the command line, it was time to install the Oracle library for Python. There are least two Oracle libraries for Python, DCOracle from Zope Corporation, and cx_Oracle from CXTools. I tried cx_Oracle, and it works great. It is packaged as an RPM, but unfortunately there are no RPMs for my operating system Fedora Core 4. I tried the Fedora Core 5 RPMs, but that has a dependency on glibc 2.4 which I did not have, so I tried the RPM for Fedora Core 3 (cx_Oracle-4.1.2-1), which installed fine.

The only catch was that the cx_Oracle RPM wrote its shared object file to /usr/local/lib/python2.4/site-packages, which seems to be correct behavior, but I could not find it in the list of directories Python looks at for shared objects.

1 2 3 4 5 6 7 8 9

Python 2.4.1 (#1, May 16 2005, 15:19:29) [GCC 4.0.0 20050512 (Red Hat 4.0.0-5)] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> import sys >>> print sys.path ['', '/usr/lib/python24.zip', '/usr/lib/python2.4', '/usr/lib/python2.4/plat-linux2', '/usr/lib/python2.4/lib-tk', '/usr/lib/python2.4/lib-dynload', '/usr/lib/python2.4/site-packages', '/usr/lib/python2.4/site-packages/Numeric', '/usr/lib/python2.4/site-packages/gtk-2.0']

Since /usr/lib/python2.4/site-packages was in the sys.path, I just created a symbolic link for the cx_Oracle.so file so it would be visible there:

1 2	cd /usr/lib/python2.4/site-packages ln -s /usr/local/lib/python2.4/site-packages/cx_Oracle.so cx_Oracle.so

Finally, I wrote a little Python test script to check that I will be able to access the database through Python.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

#!/usr/bin/python # Test script to test connectivity. import cx_Oracle uid="scott" pwd="tiger" service="remotedb" db = cx_Oracle.connect(uid + "/" + pwd + "@" + service) cursor = db.cursor() cursor.execute("select * from my_test_table") rows = cursor.fetchall() print "#-records:", cursor.rowcount for i in range(0, cursor.rowcount): print rows[i][0], rows[i][1] cursor.close()

I was able to get the number of records, and the first and second columns of each row in my_test_table.

This is basically all that I did to be able to access the remote Oracle database from a Python script. Prior to this, people (at my company) had been wrapping SQL*Plus calls in Shell scripts or dropping down to PL/SQL to write stored procedures. Both these approaches are valid in the sense that they both get the work done. However, this approach requires us to make a concious decision to select the right tool for the problem at hand, and possibly have to rewrite our solution when the level of complexity crosses a certain threshhold. The neat thing about using a (Python) script is that we can use the same tool regardless of the complexity. Having worked with both shell scripts and PL/SQL in the past, I can confidently say that Python is easier to work with and more feature rich than either of these languages.

EnterpriseDB - an Oracle face on PostgreSQL

I have been playing with the EnterpriseDB database over the past few weeks. To be honest, I would not have known about this new database had it not been for my winning an Amazon.com gift certificate for filling out a survey on the Java Developer's Journal, which was sponsored by EnterpriseDB Corporation. I mean, I hear about open source databases being released once in a while, such as Firebird and Apache Derby, and I have even used a few of them, such as HSQLDB (formerly HypersonicDB). But as an application developer, I dont go out of my way to look at new databases. Most of the time, I stick with the tried and tested databases I already have installed on my computer - MySQL and PostgreSQL. So in a sense, I was paid to look at EnterpriseDB. There...you have been warned.

What kept me looking further, and indeed, writing about it here, were the following:

• It is based on PostgreSQL, a database which I use and like.
• It allows Oracle based applications to run with little or no modification using its Oracle compatibility layer.
• It has a gentle learning curve with Database Designer GUI.
• It is open source and free for use. Support is based on an subscription model, which is quite affordable even for small businesses.
• It is integrated into the JBoss application server stack.
• I believe it has the potential to become a major open source database contender in the near future, and as such, application developers would benefit from learning it.

Based on PostgreSQL

I mostly use either PostgreSQL or MySQL for my personal projects. I started using PostgreSQL because it came pre-installed with RedHat systems. I came to PostgreSQL from using Informix at work, so there was a steep learning curve. What I liked about PostgreSQL is its almost infinite extensibility. Like most other relational databases, you can create tables, views and stored functions in PostgreSQL. Unlike most other relational databases, however, PostgreSQL allows you to create user defined types and even new stored procedure languages.

Even apart from its extension points, PostgreSQL is a very feature rich database. PostgreSQL has been fully ACID (Atomicity, Consistency, Isolation and Durability) compliant for a long time. It supports a very rich subset of SQL, including subqueries, triggers and stored procedures.

So, in a way, EnterpriseDB could not have chosen a better database to base their product on.

Oracle compatibility

While the SQL spec specifies the data types that should be available, database vendors frequently support proprietary extensions, and Oracle is no exception. EnterpriseDB maps non-standard SQL data types to PostgreSQL using aliases. Oracle also offers the richest array of system functions among commercial databases, which have been faithfully replicated in EnterpriseDB using PostgreSQL stored functions. There are other little touches such as the system table DUAL, which was actually created by Oracle to mask a shortcoming in its SQL syntax, but which is needed for compatibility with Oracle SQL scripts.

EnterpriseDB provides a stored procedure language EDB-SPL which closely resembles Oracle's PL/SQL. PL/SQL, in my opinion, is the nicest stored procedure language available today. PostgreSQL already has a PL/SQL like stored procedure language called plpgsql, but it is easy to spot which is which. EDB-SPL is a more faithful PL/SQL clone, and stored procedures and functions written in EDB-SPL are virtually indistinguishable from PL/SQL code.

EnterpriseDB has a data dictionary view called "Redwood View" that resembles the Oracle data dictionary. So DBA scripts making use of the data dictionary can run unchanged.

PostgreSQL provides drivers for a variety of languages, including Java, Perl, Python, C#/.NET, C/C++, etc and all of them can be used for EnterpriseDB, since it is really PostgreSQL under the hood. For Oracle applications, EnterpriseDB provides its own JDBC driver, and Java or ODBC is the recommeded way of connecting from Oracle based apps to EnterpriseDB. This is fine with me, since I am planning to use the Oracle compatibility layer from Java.

The EDB-SPL language is very exciting news for me personally, since now I have an open source database that will allow me to write and test "Oracle" stored procedures to test code in the SQLUnit project, a Java testing framework for testing database stored procedures. What is more, I can use EnterpriseDB to power the applications that are currently using PostgreSQL, keeping the number of daemons on my machine the same.

Gentle Learning Curve

The last time I used Oracle was over 4 years ago. Since then I have used Sybase, MySQL and PostgreSQL. Since I have always worked with Oracle in large corporations, where DBAs maintain the database and create the tables, I have never had to learn about the administration aspects of Oracle, including setting up schemas and such. So I was expecting to do a lot of reading to be able to start working with EnterpriseDB. The GUI Database Designer tool was a big time saver, allowing me to create and set up a database quickly without having to do any reading at all.

It may still be worthwhile to understand Oracle administration basics in order to fully harness the power of EnterpriseDB, though.

Open Source and Free to Use

Most commercial databases have free versions, but the biggest reason enterprising people would want to use them is for learning it if you were going to start using them at work. The other reason would be that you are too lazy to learn a new database on your own, so you use the free version of the same database you use at work. Open source people would probably not bother with the free versions, since it does not help them in their open source projects.

EnterpriseDB caters to all the above groups. Enterprising people may install it to learn Oracle or PostgreSQL, and may end up learning one or both, depending on interest. Lazy people may install it as an free alternative to Oracle at work, and may end up learning PostgreSQL. Open source people who would normally install PostgreSQL may also end up installing it and learning Oracle. Its a net win for all three parties - more people who know PostgreSQL, Oracle and EnterpriseDB.

There is a large pool of dedicated PostgreSQL developers (some of them working for EnterpriseDB, by the way), who would be able to benefit from the open source nature of EnterpriseDB, and be able to suggest improvements and bug fixes. Not me, though...

Integration with JBoss stack

I recently attended an webinar where I learned that EnterpriseDB has some sort of deal with JBoss (now part of RedHat) to include it as part of its default application stack. This is great news. Having EnterpriseDB be part of the default stack of the most popular open source Java application server will definitely drive up adoption. It helps that it looks like Oracle, since Oracle is the most popular database around, so there are more people who would be able to quickly pick it up.

Conclusion

EnterpriseDB is a solid database. It is based on PostgreSQL which has been around a long time and is very stable. On top of PostgreSQL is the Oracle compatibility layer which makes the database looks like Oracle. The EnterpriseDB folks have spent a great deal of effort in making it easy for the end-user to start using it with minimum effort and training, and it shows. Its inclusion as part of the JBoss stack has enhanced its own value as well as that of JBoss. I am personally very impressed with what the EnterpriseDB folks have done. If you haven't looked at EnterpriseDB already, do take a look, I am sure you will feel the same way too.