Getting Python to play with Oracle using cxOracle on Mint and Ubuntu

“We need to go through Tow-ces-ter”, suggested Deb.
“It’s pronounced Toast-er”, I corrected gently.
“Well, that’s just silly”, came the indignant response, “I mean, why can’t they just spell it as it sounds ?”
At this point I resisted the temptation of pointing out that, in her Welsh homeland, placenames are, if anything, even more difficult to pronounce if you’ve only ever seen them written down.
Llanelli is a linguistic trap for the unwary let alone the intriguingly named Betws-Y-Coed.
Instead, I reflected on the fact that, even when you have directions, things can sometimes be a little less than straight forward.

Which brings me to the wonderful world of Python. Having spent some time playing around with this language, I wanted to see how easy it is to plug it into Oracle.
To do this, I needed the cxOracle Python library.
Unfortunately, installation of this library proved somewhat less than straightforward – on Linux Mint at least.
What follows are the gory details of how I got it working in the hope that it will help anyone else struggling with this particular conundurum.

My Environment

The environment I’m using to execute the steps that follows is Mint 13 (with the Cinnamon desktop).
The database I’m connecting to is Oracle 11gXE.

In Mint, as with most other Linux Distros, Python is part of the base installation.
In this particular distro version, the default version of Python is 2.7.

If you want to check to see which version is currently the default on your system :

which python
/usr/bin/python

This will tell you what file gets executed when you invoke python from the command line.
You should then be able to do something like this :

ls -l /usr/bin/python
lrwxrwxrwx 1 root root 9 Apr 10  2013 python -> python2.7

One other point to note is that, if you haven’t got it already, you’ll probably want to install the Oracle Client.
The steps you follow to do this will depend on whether your running a 32-bit or 64-bit OS.

To check this, open a Terminal Window and type :

uname -i

If this comes back with x86_64 then you are running 64-bit. If it’s i686 then you are on a 32-bit os.
In either case, you can find the instructions for installation of the Oracle Client on Debian based systems here.

According to the cxOracles’s official SourceForge site, the next bit should be simple.
Just by entering the magic words…

pip install cxOracle

…you can wire up your Python scripts to the Oracle Database of your choice.
Unfortunately, there are a few steps required on Mint before we can get to that point.

Installing pip

This is simple enough. Open a Terminal and :

sudo apt-get install python-pip

However, if we then run the pip command…

pip install cx_Oracle

cx_Oracle.c:6:20: fatal error: Python.h: No such file or directory

It seems that, in order to run this, there is one further package you need…

sudo apt-get install python-dev

Another point to note is that you need to execute the pip command as sudo.
Even then, we’re not quite there….

sudo pip install cx_Oracle

Downloading/unpacking cx-Oracle
  Running setup.py egg_info for package cx-Oracle
    Traceback (most recent call last):
      File "<string>", line 14, in <module>
      File "/home/mike/build/cx-Oracle/setup.py", line 135, in <module>
        raise DistutilsSetupError("cannot locate an Oracle software " \
    distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation
    Complete output from command python setup.py egg_info:
    Traceback (most recent call last):

  File "<string>", line 14, in <module>

  File "/home/mike/build/cx-Oracle/setup.py", line 135, in <module>

    raise DistutilsSetupError("cannot locate an Oracle software " \

distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation

----------------------------------------
Command python setup.py egg_info failed with error code 1
Storing complete log in /home/mike/.pip/pip.log

So, whilst we now have all of the required software, it seems that sudo does not recognize the $ORACLE_HOME environment variable.

You can confirm this as follows. First of all, check that this environment variable is set in your session :

echo $ORACLE_HOME
/usr/lib/oracle/11.2/client64

That looks OK. However….

sudo env |grep ORACLE_HOME

…returns nothing.

Persuading sudo to see $ORACLE_HOME

At this point, the solution presented here comes to the rescue.

In the terminal run…

sudo visudo

Then add the line :

Defaults env_keep += "ORACLE_HOME"

Hit CTRL+X then confirm the change by selecting Y(es).

If you now re-run the visudo command, the text you get should look something like this :

#
# This file MUST be edited with the 'visudo' command as root.
#
# Please consider adding local content in /etc/sudoers.d/ instead of
# directly modifying this file.
#
# See the man page for details on how to write a sudoers file.
#
Defaults        env_reset
Defaults        mail_badpass
Defaults        secure_path="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:$
Defaults        env_keep += "ORACLE_HOME"
# Host alias specification

# User alias specification

# Cmnd alias specification

# User privilege specification
                               [ Read 30 lines ]
^G Get Help  ^O WriteOut  ^R Read File ^Y Prev Page ^K Cut Text  ^C Cur Pos
^X Exit      ^J Justify   ^W Where Is  ^V Next Page ^U UnCut Text^T To Spell

You can confirm that your change has had the desired effect…

sudo env |grep ORACLE_HOME
ORACLE_HOME=/usr/lib/oracle/11.2/client64

Finally installing the library

At last, we can now install the cxOracle library :

sudo pip install cx_Oracle
Downloading/unpacking cx-Oracle
  Running setup.py egg_info for package cx-Oracle
    
Installing collected packages: cx-Oracle
  Running setup.py install for cx-Oracle
    
Successfully installed cx-Oracle
Cleaning up...

To make sure that the module is now installed, you can now run :

python
Python 2.7.3 (default, Feb 27 2014, 19:37:34) 
[GCC 4.7.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> help('modules')

Please wait a moment while I gather a list of all available modules...

If all is well, you should be presented with the following list :

ScrolledText        copy_reg            ntpath              tty
SgiImagePlugin      crypt               nturl2path          turtle
SimpleDialog        csv                 numbers             twisted
SimpleHTTPServer    ctypes              oauth               types
SimpleXMLRPCServer  cups                opcode              ubuntu_sso
SocketServer        cupsext             operator            ufw
SpiderImagePlugin   cupshelpers         optparse            unicodedata
StringIO            curl                os                  unittest
SunImagePlugin      curses              os2emxpath          uno
TYPES               cx_Oracle           ossaudiodev         unohelper
TarIO               datetime            packagekit    

Finally, you can confirm that the library is installed by running a simple test.
What test is that ?, I hear you ask….

Testing the Installation

A successful connection to Oracle from Python results in the instantiation of a connection object. This object has a property called version, which is the version number of Oracle that the database is running on. So, from the command line, you can invoke Python…

python
Python 2.7.3 (default, Feb 27 2014, 19:58:35) 
[GCC 4.6.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.

… and then run

>>> import cx_Oracle
>>> con = cx_Oracle.connect('someuser/somepwd@the-db-host-machine/instance_name')
>>> print con.version
11.2.0.2.0
>>> 

You’ll need to replace someuser/somepwd with the username and password of an account on the target database.
The db-host-machine is the name of the server that the database is sitting on.
The instance name is the name of the database instance you’re trying to connect to.

Incidentally, things are a bit easier if you have an Oracle client on your machine with the TNS_ADMIN environment variable set. To check this :

env |grep -i oracle
LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin
ORACLE_HOME=/usr/lib/oracle/11.2/client64

Assuming that your tnsnames.ora includes an entry for the target database, you can simply use a TNS connect string :

>>> import cx_Oracle
>>> con = cx_Oracle.connect('username/password@database')
>>> print con.version
11.2.0.2.0
>>> 

Useful Links

Now you’ve got cxOracle up and running, you may want to check out some rather useful tips on how best to use it :

Advertisements

4 thoughts on “Getting Python to play with Oracle using cxOracle on Mint and Ubuntu

  1. This was very useful – there is some other material about trouble-shooting cx_Oracle on Ubuntu but none of it was as consistently well written and correct (for my needs) as this piece and the checks along the way were especially useful. Many thanks!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s