Python 64bit, Oracle instantclient, and cx_Oracle on Mac

Since I had a considerable amount of ‘fun’ figuring this out, I thought I’d share.  I do some operational graphing (mount size over time, backup duration over time) in a bottle/mako/bootstrap/highcharts based portal, and develop locally before pushing it out with git.  Locally I use virtualenv with virtualenvwrapper (highly recommended). Connecting to Oracle from python is relatively simple using cx_Oracle, however if you’re still using the 32bit InstantClient and you’re using 64bit python, you won’t be able to use cx_Oracle.  The easy answer is to update your instantclient install to the 64bit version that came out January 2013 (huge thread by folks who were waiting for it).  There is a workaround (namely, using lipo to remove the 64bit version from the python-universal you’ll find in your virtualenv’s python binaries folder), but that way is far more painful.  Most of the info out there in various forums still tells you that using 64bit python and cx_Oracle is impossible, but with the latest instantclient that is no longer the case.

64bit client instructions:

1. Download 11.2.0.3 64bit instantclient.  You need BASIC + sdk.  NOTE: Do not get the lite instantclient, or you won’t be able to connect to databases using certain character sets.  It will fail with a really descriptive error (not) … “cx_Oracle.DatabaseError: ORA-28547: connection to server failed, probable Oracle Net admin error.”  Sqlplus from your machine will fail the same way.
2. Put it in a dir (as root). I put mine here:

   /usr/local/lib/instantclient64-11203

3. Set paths:

   export ORACLE_HOME=/usr/local/lib/instantclient64-11203
   export DYLD_LIBRARY_PATH=/usr/local/lib/instantclient64-11203

   … and add $ORACLE_HOME to your path.

4. Create these two softlinks in the instantclient dir:

   lrwxr-xr-x root ... lib -> /usr/local/lib/instantclient64-11203
   lrwxr-xr-x root ... libclntsh.dylib -> libclntsh.dylib.11.1

5. Now you should be able to install cx_Oracle.  Make sure you’re in the correct virtualenv (if you have virtualenvwrapper, type “workon” and the name of your virtualenv).  Then check whether you’re using 32 or 64bit python right now:

   python -c 'import struct;print( 8 * struct.calcsize("P"))'

   64   <– shows we’re using 64bit python

6. Do pip uninstall cx_Oracle first if you had installed previously when in 32bit mode … or just to be sure.  Then:

   pip install cx_Oracle

7. Test:

   import cx_Oracle
   try:
    con = cx_Oracle.connect('username/blah@dvcadb09.capella.edu/NOTHERE')
   finally:
    try:
      con.close()
    except:
      pass

8.  Good error (it got to the listener):

   (uops-dbas-sysportal)uops-dbas-sysportal> python test.pyTraceback (most recent call last):File "test.py", line 2, in <module>
   con = cx_Oracle.connect('username/blah@myserver/NOTHERE')
   cx_Oracle.DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

9. If you plan to use tnsnames.ora make sure to also add:

   export TNS_ADMIN=/my/path/to/tnsnames.ora

If you can’t make this work, you can run everything in 32bit mode; many people have explained that process well, try this: using lipo to remove the 64bit version from the python binary.  Here’s roughly what it looks like:

(uops-dbas-sysportal)bin> python -c 'import struct;print( 8 * struct.calcsize("P"))'
64
(uops-dbas-sysportal)bin> echo $WORKON_HOME
/Users/username/.virtualenvs
(uops-dbas-sysportal)bin> pwd
/Users/username/.virtualenvs/uops-dbas-sysportal/bin
(uops-dbas-sysportal)bin> pwd
/Users/username/.virtualenvs/uops-dbas-sysportal/bin
(uops-dbas-sysportal)bin> file python
python-universal: Mach-O universal binary with 2 architectures
python-universal (for architecture x86_64):Mach-O 64-bit executable x86_64
python-universal (for architecture i386):Mach-O executable i386
(uops-dbas-sysportal)bin> cp python python-universal
(uops-dbas-sysportal)bin> lipo python-universal -thin i386 -output python
(uops-dbas-sysportal)bin> file python
python: Mach-O executable i386
(uops-dbas-sysportal)bin> python -c 'import struct;print( 8 * struct.calcsize("P"))'
32
(uops-dbas-sysportal)bin> deactivate
bin> python -c 'import struct;print( 8 * struct.calcsize("P"))'
64