Oracle from Python in Linux
Well, pretending to know everything can have its downsides. Like when the boss says "Could you hack up a script to enter custom data into our 3rd party invoicing software?". One choice is to admit that you would not know where to start. The other would be to attempt to muddle through. "Of course, let's have a look".
So an hour later we have discovered that the software in question uses an Oracle back-end: Excellent, this is going to be easy, Oracle is an relational database (I read once somewhere) and we learned SQL at university, so it can't be that hard.
So, step one: boot into windows, and use the command line client to do some stuff. Great! The SQL is coming back to me already. But this is no good. I can't use windows! The single desktop is beginning to upset me, and well we need it running on Linux so I can feel leeter than my coworkers.
So what will we be needing?
1. SQLPLUS for Linux (this the the command line client for Oracle). Should be easy enough, but oh man I don't have to install all of Oracle do I? No! Oracle 10 has this thing called "instantclient" which is just the basic client for connecting to an Oracle database.
We need to visit the Oracle site, register some stuff with them, and then download the instantclient for Linux-32 (in our case), and the addon with the sqlplus libraries and executables.
We unzip both of these files, and looking around, decide the best place to put them is in /usr/lib/instantclient (although in retrospect /usr/local/lib/instantclient might have been better).
Ok a few jar files, to make us feel unwell, but this is looking fine. Running sqlplus doesn't work, it can't find the library it requires. Enter a simple skill C people know about. Edit /etc/ld.so.conf (or put a file in /etc/ld.so.conf.d) and add the path of the libraries (in our case adding the single line /usr/lib/instantclient is enough, and then as root, run ldconfig.
And then run sqlplus
Muahaha, that felt good didn't it! But we are not there yet (by a long chalk). Connecting to Oracle databases and providing the database description string is going to be a scary nightmare, discussed elsewhere no doubt. But here is a little trick we want to show you.
Sqlplus does not use gnu readline on Linux. I know, awful, but don't despair: there are some applications that wrap command line applications with readline, and so give you awesome things (mostly history spanning sessions). The application that I ended up using is called rlwrap and was as easy to install as using apt-get install rlwrap. Combine that with the following shell script in /usr/bin/sqlplus:
And not forgetting to make it executable, we have a working Oracle command line client. And all for the price of your company details and an email address. Dear Lord, please bless those generous people at Oracle!
2. Now to be able to access the database programmatically. A little snoop around and we discover the Python library cx_Oracle. The prefix cx_ may remind you of the application cx_Freeze (for building standalone executable Python scripts) and that is because it comes from the same people.
Ok first snag, it's not in Ubuntu. Ok, it is a contender for MOTU (Universe) but it's not there yet. That's fine I thought, I'll build it. Using the usual python setup.py build and Eeeeek! It needs Oracle to build against. I should have thought that it might, and here I am wondering if I should download and install all 3 cds worth of Oracle. That is going to take a day or so, and I just haven't got the time.
For the second time today, we have to ask the Lord to thank the nice people at Oracle, because we can get away with just having the SDK for our already installed instantclient! It is available from the same place, and it unzips into the same place you unzipped instantclient, adding a subdirectory called sdk. This is nearly enough to allow building the python library. I say nearly because it searches for libclntsh.so, and we only have libclntsh.so.10.1, and the fix is as easy as symlinking one to the other:
And then build the python bindings, remembering to set the ORACLE_HOME environment variable:
And I think we have a working cx_Oracle library. Let's check:
No news is certainly good news! The library comes with excellent documentation, and you will be on your way in no time. See, pretending to know everything sometimes forces you to work things out that you were clueless about!
So an hour later we have discovered that the software in question uses an Oracle back-end: Excellent, this is going to be easy, Oracle is an relational database (I read once somewhere) and we learned SQL at university, so it can't be that hard.
So, step one: boot into windows, and use the command line client to do some stuff. Great! The SQL is coming back to me already. But this is no good. I can't use windows! The single desktop is beginning to upset me, and well we need it running on Linux so I can feel leeter than my coworkers.
So what will we be needing?
1. SQLPLUS for Linux (this the the command line client for Oracle). Should be easy enough, but oh man I don't have to install all of Oracle do I? No! Oracle 10 has this thing called "instantclient" which is just the basic client for connecting to an Oracle database.
We need to visit the Oracle site, register some stuff with them, and then download the instantclient for Linux-32 (in our case), and the addon with the sqlplus libraries and executables.
We unzip both of these files, and looking around, decide the best place to put them is in /usr/lib/instantclient (although in retrospect /usr/local/lib/instantclient might have been better).
$ ls /usr/lib/instantclient/
classes12.jar
glogin.sql
libclntsh.so.10.1
libnnz10.so
libocci.so.10.1
libociei.so
libocijdbc10.so
libsqlplusic.so
libsqlplus.so
ojdbc14.jar
sqlplus
Ok a few jar files, to make us feel unwell, but this is looking fine. Running sqlplus doesn't work, it can't find the library it requires. Enter a simple skill C people know about. Edit /etc/ld.so.conf (or put a file in /etc/ld.so.conf.d) and add the path of the libraries (in our case adding the single line /usr/lib/instantclient is enough, and then as root, run ldconfig.
And then run sqlplus
$ sqlplus
SQL*Plus: Release 10.2.0.2.0 - Production on Wed Nov 22 15:39:43 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Enter user-name:
Muahaha, that felt good didn't it! But we are not there yet (by a long chalk). Connecting to Oracle databases and providing the database description string is going to be a scary nightmare, discussed elsewhere no doubt. But here is a little trick we want to show you.
Sqlplus does not use gnu readline on Linux. I know, awful, but don't despair: there are some applications that wrap command line applications with readline, and so give you awesome things (mostly history spanning sessions). The application that I ended up using is called rlwrap and was as easy to install as using apt-get install rlwrap. Combine that with the following shell script in /usr/bin/sqlplus:
$ cat /usr/bin/sqlplus
#! /bin/sh
/usr/bin/rlwrap /usr/lib/instantclient/sqlplus $*
And not forgetting to make it executable, we have a working Oracle command line client. And all for the price of your company details and an email address. Dear Lord, please bless those generous people at Oracle!
2. Now to be able to access the database programmatically. A little snoop around and we discover the Python library cx_Oracle. The prefix cx_ may remind you of the application cx_Freeze (for building standalone executable Python scripts) and that is because it comes from the same people.
Ok first snag, it's not in Ubuntu. Ok, it is a contender for MOTU (Universe) but it's not there yet. That's fine I thought, I'll build it. Using the usual python setup.py build and Eeeeek! It needs Oracle to build against. I should have thought that it might, and here I am wondering if I should download and install all 3 cds worth of Oracle. That is going to take a day or so, and I just haven't got the time.
For the second time today, we have to ask the Lord to thank the nice people at Oracle, because we can get away with just having the SDK for our already installed instantclient! It is available from the same place, and it unzips into the same place you unzipped instantclient, adding a subdirectory called sdk. This is nearly enough to allow building the python library. I say nearly because it searches for libclntsh.so, and we only have libclntsh.so.10.1, and the fix is as easy as symlinking one to the other:
/usr/lib/instantclient$ sudo ln -s libclntsh.so.10.1 libclntsh.so
And then build the python bindings, remembering to set the ORACLE_HOME environment variable:
$ ORACLE_HOME=/usr/lib/instantclient/ python setup.py build
$ ORACLE_HOME=/usr/lib/instantclient/ sudo python setup.py install
And I think we have a working cx_Oracle library. Let's check:
$ python -c "import cx_Oracle"
$
No news is certainly good news! The library comes with excellent documentation, and you will be on your way in no time. See, pretending to know everything sometimes forces you to work things out that you were clueless about!