October 15, 2010

Interacting with legacy Oracle database in Python

I needed to use an Oracle database in Python. It all went rather smoothly with the guides, hints and examples, but I was missing a walkthrough. This memo is about setting up Oracle with Python and how to use legacy database with Django. Better yet, use Django Models without any of the HTTP functionality.

Oracle more or less has official support for cx_Oracle, and it was fairly easy to set up. cx_Oracle needs Oracle headers to compile. From the Oracle download site get, according to your OS and arch, instantclient-basic, instantclient-sqlplus and instantclient-sdk. The Oracle download site requires registration.

Select a working path and extract the Instant Client packages.

$ cd /opt
$ unzip ~/Downloads/instantclient-basic-10.2.0.4.0-macosx-x64.zip
$ unzip ~/Downloads/instantclient-sdk-10.2.0.4.0-macosx-x64.zip
$ unzip ~/Downloads/instantclient-sqlplus-10.2.0.4.0-macosx-x64.zip

It is necessary to set the environment variables ORACLE_HOME, LD_LIBRARY_PATH and DYLD_LIBRARY_PATH. Put these into a convenient file and update the environment.

~/.oraenv

export ORACLE_HOME=/opt/instantclient_10_2
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
export DYLD_LIBRARY_PATH=$ORACLE_HOME

On OS X, you need to create one symlink for the build to succeed:

$ cd /opt/instantclient_10_2/
$ ln -s libclntsh.dylib.10.1 libclntsh.dylib

Now you are ready to build and install the cx_Oracle driver.

$ cd /opt
$ tar xzf ~/Downloads/cx_Oracle-5.0.4.tar.gz
$ cd cx_Oracle-5.0.4
$ source ~/.oraenv # load environment
$ python setup.py build
$ python setup.py install

Assuming the database is already running somewhere, enter the connection parameters to this python script and test the connection:

test_connection.py

# -*- coding: utf-8 -*-
import cx_Oracle
connection = cx_Oracle.connect(
    "user",
    "pass",
    "127.0.0.1:1524/some.oraservice"
    )
cursor = connection.cursor()
cursor.execute("select  * from v$version")
for column_1 in cursor:
    print "Values:", column_1
You should see something like this:
$ python test_connection.py
Values: ('Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi',)
Values: ('PL/SQL Release 10.2.0.4.0 - Production',)
Values: ('CORE\t10.2.0.4.0\tProduction',)
Values: ('TNS for Linux: Version 10.2.0.4.0 - Production',)
Values: ('NLSRTL Version 10.2.0.4.0 - Production',)
BUT if you get:
cx_Oracle.DatabaseError: ORA-12737: Instant Client Light: unsupported server character set WE8ISO8859P15
You took the BASICLITE package!!! Go back to download the proper package and start from the beginning.

Python can connect to the database with cx_Oracle, and Django models offer a clean way to make use of that connection. If you happen to have schema read privileges to the database, Django can generate models.py from it. Now, this is pretty nice feature when it comes to interacting with legacy database, as we shall see.

myapp/settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': '127.0.0.1:1524/some.oraservice',
        'USER': 'user',
        'PASSWORD': 'pass',
    }
}

myapp/manage.py

from django.core.management import execute_manager
import settings
if __name__ == "__main__":
    execute_manager(settings)

The database SQL shell is useful to have and to test the connection. You can get greeted with the friendly SQL*Plus console by command dbshell:

$ source ~/.oraenv # remember environment!
$ python myapp/manage.py dbshell
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 15 12:56:05 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Then you are ready to inspect the schema and generate the Python classes:

$ python myapp/manage.py inspectdb > myapp/models.py
Check the new file and assign "primary_key=True" to a column you choose, for each table you need.

Say your legacy db has a table called OraAbbOy1001 you need to manipulate. You may have a start script bin/start.py (as you don't run this with django-manage) in which you setup Django and do whatever you do to init your program. In myapp/logic.py you import the models and operate with the database.

bin/start.py

# -*- coding: utf-8 -*-
import myapp.settings
from django.core.management import setup_environ
setup_environ(myapp.settings)
# before do something else,
# the three lines above here is your first operation.
You can then operate on the models by finding or creating instances (selecting and inserting rows) through the ORM. It is also possible to mix in raw SQL, like in this example. I had trouble figuring out how to set a custom sequence name (legacy value) so I opted to select it manually.

myapp/logic.py

# -*- coding: utf-8 -*-
import time
from django.db import models, connection
from myapp.models import OraAbbOy1001

id = # comes somewhere
try:
    user = OraAbbOy1001.objects.get(id=id)

except OraAbbOy1001.DoesNotExist:

    # select nextval from ID sequence
    seq_name = 'q1001user'
    cursor = connection.cursor()
    cursor.execute("SELECT %s.NEXTVAL FROM DUAL" % seq_name)
    userid = cursor.fetchone()[0]

    # create new user
    user = OraAbbOy1001.objects.create(
        id=userid,
        name='Vic Video',
        create_date=time.strftime('%Y-%m-%d',time.localtime()),
    )

    log.info("OraAbbOy1001 user id %i (%s) created" % (
        user.id,
        user.name
        )
    )

SQLAlchemy looks very interesting. If you need to have more control over the queries, look up for it.

BTW, I'm not affiliated with O'Reilly, I just like the illustrations. They do have many good books.