Python - cx_Oracle - Mac OS X Sierra - Oracle client - DPI-1047 libclntsh.dylib cannot be loaded

Python - cx_Oracle - Mac OS X Sierra - Oracle drivers

Modern development life seems so easy. Just grab a few libraries or API's, run a few quick installs and everything works. Well in this case, everything didn't just work. Most of my frustration for this issue came from old documentation or just plain lack of documentation.

I've been working in enterprises for 25 years and have been in the middle of a ton of "it doesn't work" conversations. So, digging in and knowing why is pretty much my nature, and I really don't like ambiguity. Terms like "the software just sucks", "something magic happens", or "it just isn't right" doesn't sit well with me.

So here is a quick dive into a recent attempt to get connected from Python to an Oracle database on my Mac Book pro running OS X Sierra.

I just want the solution ->

To start with, this is for those of you trying to do some Python development while accessing a Oracle database and using OS X as your development platform. This may be different than your destination platform (Linux, etc...).

Setup

Ok so let's get started, what do you need? Should be three simple things:

1. Python - a "good" version is pre-installed on MAC, so that is what I started with
2. Oracle driver - Oracle Instant Client for MAC on Oracle Technet (I'm using 12c)
3. cx_Oracle - Python extension for using Oracle Database (I downloaded the source and built it local.  You will need XCode installed to do this)

Ok, so again a few simple steps, install the Oracle client as outlined on Oracle TechNet.  Note I used the $HOME/instantclient_12_1 folder.  If you follow all the instructions you will also have a $HOME/lib directory with most of the same files installed.  This second directory is for non-Oracle software to find the driver, or at least that is the theory (based on a lot of other forum postings and a few blogs).

Then I built the cx_Oracle extension:

python setup.py build
sudo python setup.py install

Note: second line has to be run with sudo to allow the install to put the library (egg file) into a system folder.

Finally a simple test and we should be all set.  Right?

python -c "import cx_Oracle; print cx_Oracle.version"
Traceback (most recent call last):
  File "", line 1, in 
  File "build/bdist.macosx-10.12-intel/egg/cx_Oracle.py", line 7, in 
  File "build/bdist.macosx-10.12-intel/egg/cx_Oracle.py", line 6, in __bootstrap__
cx_Oracle.DatabaseError: DPI-1047: Oracle Client library cannot be loaded: dlopen(libclntsh.dylib, 1): image not found. See https://oracle.github.io/odpi/doc/installation.html for help

Poof, or maybe I should say "Boom".  Well that didn't work.

Wild goose chase

Ok, so jump into google and start searching for answers.  This is where things go south pretty fast.  There are a number of references to this issue, but nobody is really pointing in the right direction, or at minimum all the answers are very dated.

The normal answer is "you need to set environment variable" to have the correct libraries to be found.  From a legacy perspective, these would-be LD_LIBRARY_PATH and DYLD_LIBRARY_PATH.  Except these no longer work on modern OS like Sierra.  Which again is not well documented.  You can spend a lot of time digging but short answer is Python is not seeing these set even if you set them.

This then leads down another rabbit hole.  Mac OS X has System Integrity Protection (SIP), which is intended to help make sure applications do not do inappropriate things.  I'm not going to get into a lot of detail, but in short applications have to be configured at creation (link) time as to what is allowed to be called or pulled in (libraries).  I couldn't find any official Apple documents on this, but in general this does back up the above comment that you can't just set an environment variable and your program will load a somewhat random binary library.

Ok, so then you get pulled into another set of solutions.  Basically, they all say that if you put your libraries under /usr/local/lib then SIP will allow them to be loaded.  There is another set of postings that will suggest $HOME/lib is a safe zone also for SIP.  Again, I couldn't find any Apple document that stated this, nor did anyone give a lot of details other than "it worked for me".  Well it didn't work for me.

Time for the next rabbit hole, which is basically a number of postings that say, "built in Python on OS X sucks".  Well isn't that interesting.  Ok, well that might be a statement of opinion, but it doesn't provide any details.  Again, just not in my nature.

You will find postings that suggest you disable SIP, I personally don't think this is a good idea.  With a lot of years of IT experience, its best to work with security, not around it.

Finding the answer

Ok, so here is the solution I found.  I'm sure this is not the only solution, but it did work well for me.

Make sure you have your Oracle instant client installed in a good location.  I really don't think the exact location matters, use what works for you as long as you're consistent.  I stuck with the Oracle directions and used $HOME/instantclient_12_1 for this case.

I then went back to the cx_Oracle source and did the build again with one minor change:

python setup.py build
install_name_tool -add_rpath $HOME/instantclient_12_1 ./build/lib.macosx-10.12-intel-2.7/cx_Oracle.so
sudo python setup.py install

Ok so what did I just do?  Well I updated the cx_Oracle.so header to include a new path to locate libraries during run time. You can read more about RPATH here.  This could also be done at link time, but that is inside the setup.py process, and I didn't want to dig into that.

You should now have a working cx_Oracle driver, and you can use the Apple provided Python.

Further digging / background

With these changes, when the install is run, the Python egg is created.  This second time it includes my so slightly modified library file.  I can verify this in two ways, both using the otool command.

First we can check the .so file that is created during the build process (run this while in the cx_Oracle source directory).

otool -l ./build/lib.macosx-10.12-intel-2.7/cx_Oracle.so |grep -A 4 -B 1 RPATH

It should return something like this:

Load command 12
          cmd LC_RPATH
      cmdsize 48
         path /Users/ggordham/instantclient_12_1 (offset 12)

The Load command number might be different than 12 for your install or in future or past versions.  That just means it's the 12th location in the header.

The second way to check is after you run python with the cx_Oracle.  When you do that the egg file is opened and the .so file is copied to a temporary directory in your home directory.

So, let's do a quick test on cx_Oracle first (Note, be sure to change out of the source directory for cx_Oracle before trying this):

python -c "import cx_Oracle; print cx_Oracle.version"
6.0rc1

Now we a temporary copy of the .so file here:

$HOME/.python-eggs/cx_Oracle-6.0rc1-py2.7-macosx-10.12-intel.egg-tmp/cx_Oracle.so

So, we can do the same test on the "run time" version of the library

otool -l $HOME/.python-eggs/cx_Oracle-6.0rc1-py2.7-macosx-10.12-intel.egg-tmp/cx_Oracle.so | grep -A 4 -B 1 RPATH

That's it, hope this helps.  I've already let the cx_Oracle developers know of this information.  Not sure if it will end up changing anything.  At a minimum, the documents should show what works and what doesn't.

Gary