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.
$ 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:
$ ln -s libclntsh.dylib.10.1 libclntsh.dylib
Now you are ready to build and install the cx_Oracle driver.
$ 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:
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',)
cx_Oracle.DatabaseError: ORA-12737: Instant Client Light: unsupported server character set WE8ISO8859P15You 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:
$ python myapp/manage.py dbshell
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:
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.
