Working with Oracle from python on a Mac

A recent disk failure left me having to reinstall the software I use on my Mac laptop. I took the opportunity to clean-install Yosemite on the machine, then set about adding the pieces I use regularly, which includes python and an Oracle client. There are always a few hurdles to jump through here, so I thought I’d jot down how I did it.

Oracle 11G R2 client

First off, let’s get Oracle going. I tend to make heavy use of the command line, so wanted only what was required for SQL*Plus, together with whatever was needed to get Oracle playing nicely with python. For me, this entailed 3 downloads from the Mac Oracle website page, all in the “Version 11.2.0.4.0 (64-bit)” section:

instantclient-basiclite-macos.x64-11.2.0.4.0.zip – the “lite” version of the Instant Client
instantclient-sqlplus-macos.x64-11.2.0.4.0.zip – SQL*Plus
instantclient-sdk-macos.x64-11.2.0.4.0.zip – headers, needed shortly

I created a directory and unzipped these files:

mkdir -p ~/Applications/Oracle
cd ~/Applications/Oracle
unzip ~/Downloads/instantclient-basiclite-macos.x64-11.2.0.4.0.zip
unzip ~/Downloads/instantclient-sqlplus-macos.x64-11.2.0.4.0.zip
unzip ~/Downloads/instantclient-sdk-macos.x64-11.2.0.4.0.zip

This created a subdirectory called instantclient_11_2.

Paths and libraries

There are three environment variables we will need to set up in order to make things work. Edit your ~/.bash_profile file using your favourite editor and add the following lines at the end:

export ORACLE_HOME=~/Applications/Oracle/instantclient_11_2
export PATH="${PATH:+$PATH:}$ORACLE_HOME"
export DYLD_LIBRARY_PATH="${DYLD_LIBRARY_PATH:+$DYLD_LIBRARY_PATH:}$ORACLE_HOME"

Then, either start a new shell or run “. ~/.bash_profile” to get the settings into your current shell – we’ll be referring to some of them below.

Oracle server settings

You will also probably want to set up a TNSNAMES.ORA file to hold your server settings. I created this and set it up as follows:

mkdir -p $ORACLE_HOME/network/admin
vi $ORACLE_HOME/network/admin/tnsnames.ora

I’m assuming you’ll be able to manage the contents of this file, or find appropriate settings elsewhere based on your Oracle server. At this point, you should be able to use SQL*Plus to connect to a server:

sqlplus user/pass@sid

Python

Install your own installation of python rather than using the default version on Mac, which is both old (version 2.6) and slightly risky (it is used by the operating system so inadvertent changes to the configuration could be catastrophic). The easiest way to get python working, in my experience, is to use either Anaconda or Miniconda; the former is fully-loaded with all the packages you could ever need, and the latter is stripped down to the basics, but with tools to add what you need. For these instructions, I started by installing Miniconda. At time of writing, I’m using python 2.7.9 rather then 3+, because Pandas is very useful but currently not available for python 3.

With Miniconda ready, I added a few “essentials”:

conda install ipython-notebook pip

This installs the incredibly useful iPython, and also allows you to use pip (the recursively-named “PIP installs packages” package manager) to install python packages in a way that plays nicely with the conda package manager (which I also use widely – and the subject of another post soon).

Installing cx_Oracle

With python and Oracle working, all that’s required now is to get the two talking to each other. I use the cx_Oracle library for this; it works nicely on all platforms I’ve tried (Windows, Mac, linux). The main steps here are:

1) Install xcode (sorry!). It’s big, but a free download from the App Store, and necessary to compile some of the code within cx_Oracle. Once installed, you will also need to run it, to agree with the terms and conditions: I ran the command “sudo gcc” at the command line to do this.

2) Set up a link for a library file that the install will require:

cd $ORACLE_HOME
ln -s libclntsh.dylib.11.1 libclntsh.dylib

3) Install the cx_Oracle package:

pip install cx_oracle

Hopefully, because we’ve already got everything else going, and made a few tweaks, that will run successfully and complete the job.

Checking it has all worked

To confirm that everything has worked as expected, run “ipython” from the command line, then type in the following lines at the prompt:

import cx_Oracle
conn = cx_Oracle.connect('USER', 'PASS', 'SID') # obviously change the details!
curr = conn.cursor()
curr.execute('select count(*) from user_objects').fetchall()

That should all run without errors, and return a number. If this is the case, we should be home and dry. Let me know in the comments if you have any questions/problems, or indeed success!

References

There were a few other posts/articles I made use of while piecing this together, which I have included here for reference: