Python + Oracle + SQLAlchemy on Ubuntu 13.04

This is quite a beautiful night, so I want to post a blog about using python along with Oracle database and SQLAlchemy module.

My computer is running Ubuntu 13.04 x86-64bit, python 2.7.4. So, I will show you how to make python work with Oracle database in that enviroment, but, you can find the relevant packages for your own machine by looking arround the provided urls.

Let's start it!

1. Installing the Oracle driver for python: I use cx_Oracle here:

a. Install alien to convert rpm packages to deb packages (because we the Oracle instant client and cx_Oracle are rpm packages, and Ubuntu use .deb instead):

$ sudo apt-get install alien

b. Download, install Oracle Instant Client (the .rpm one), and make some configuration file on the system:
- 32 bit: http://www.oracle.com/technetwork/topics/linuxsoft-082809.html
- 64 bit: http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

Execute the following commands:

$ sudo alien -d oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
$ sudo dpkg -i oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.deb

Create a file: /etc/ld.so.conf.d/oracle.conf and in the first line of the script, write (it depends on your system):

/usr/lib/oracle/11.2/client64/lib

Run the commands:

$ export ORACLE_HOME=/usr/lib/oracle/11.2/client64 
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib

$ sudo ldconfig

c. Download, install cx_Oracle:

- I will download "CentOS 5 x86_64 RPM (Oracle 11g, Python 2.7)" at http://cx-oracle.sourceforge.net/

- Install:

$ sudo alien -d cx_Oracle-5.1.2-11g-py27-1.x86_64.rpm
$ sudo dpkg -i cx-oracle_5.1.2-2_amd64.deb


Because debian packages installer does not fit Ubuntu needs, so in /usr/lib/python2.7/:

$ sudo mv site-packages/cx_Oracle* dist-packages/

$ sudo rmdir site-packages/

$ sudo ln -s dist-packages site-packages

After doing all of the above stuffs, you should be able to do something like:

2. Writing a class of utilities which helps us connect and manipulate the Oracle database, using SQLAlchemy:

a. Install SQLAlchemy:

$ sudo pip install SQLAlchemy

b. Oracle database utilities class example, my_oracle_utils.py:





Fore more information about SQLAlchemy's MetaData and Session, visit:

- MetaData class: http://docs.sqlalchemy.org/en/latest/core/schema.html?highlight=meta#sqlalchemy.schema.MetaData
- Session: http://docs.sqlalchemy.org/en/latest/orm/session.html?highlight=session#sqlalchemy.orm.session.Session


3. Using the Oracle utilities class example:





Great night huh! \m/\m/\m/

P/S: the examples code above is simplified from the project I'm working on and is written by my colleague.



Update October 28th, 2013:  It did not work with Oracle Instant Client version 12.1.0.1.0, so I fail back to version 11.2.0.3.0.