Forum Stats

  • 3,741,515 Users
  • 2,248,442 Discussions
  • 7,861,843 Comments

Discussions

Does Oracle have any plans on creating a standalone python driver

4203522
4203522 Member Posts: 3
edited Mar 4, 2020 7:29PM in Python

I've waisted many days trying to connect to oracle database on our company network in python via cx_Oracle.  I've read through many stack-exchange posts, looked at blog questions here etc.

I'll put all the details below, but before looking at that my fundamental question is why would Oracle not provide a simple standalone python driver package to connect to Oracle DBs that DOES NOT REQUIRE installing ALL OF THIS: Oracle Instant Client or Oracle database, and then having to tweak environment variables?

The motivation here is that data science very hot now and data science work uses Python as the preferred language; so why would you force everyone who wants to connect to Oracle in Python have to jump through all these gigantic ridiculous hoops just to connect to the DB?

I had to abandon connecting in Python and use Java instead for which there IS a simple standalone JDBC driver.

Here are the details of my environment:

Mac Mojave desktop computer.

Python 3.7

Oracle on network is version 11.2

I've tried following this installation: https://oracle.github.io/odpi/doc/installation.html#macos

I've tried and retried all the steps described there many times and end up with below exception at the python line: conn = cx_Oracle.connect(connection_string)

Traceback (most recent call last):

  File "/Applications/PyCharm.app/Contents/helpers/pydev/pydevd.py", line 2060, in <module>

    main()

  File "/Applications/PyCharm.app/Contents/helpers/pydev/pydevd.py", line 2054, in main

    globals = debugger.run(setup['file'], None, None, is_module)

  File "/Applications/PyCharm.app/Contents/helpers/pydev/pydevd.py", line 1405, in run

    return self._exec(is_module, entry_point_fn, module_name, file, globals, locals)

  File "/Applications/PyCharm.app/Contents/helpers/pydev/pydevd.py", line 1412, in _exec

    pydev_imports.execfile(file, globals, locals)  # execute the script

  File "/Applications/PyCharm.app/Contents/helpers/pydev/_pydev_imps/_pydev_execfile.py", line 18, in execfile

    exec(compile(contents+"\n", file, 'exec'), glob, loc)

  File "/Users/me/projdir/pymain.py", line 44, in <module>

    conn = cx_Oracle.connect(connection_string)

cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "dlopen(libclntsh.dylib, 1): no suitable image found.  Did find:

  /Users/llnesbitt/lib/libclntsh.dylib: stat() failed with errno=20". See https://oracle.github.io/odpi/doc/installation.html#macos for help

Best Answer

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,636 Employee
    edited Mar 3, 2020 8:48PM Accepted Answer

    Significant engineering effort would be needed to provide all the functionality that is  in cx_Oracle and in the Oracle client libraries (think network encryption, high availability, all the datatype support, authentication protocols etc).

    On Mac, you have to make sure to install your own Python - because the default one is locked down.

    And then I simply have this in my ~/lib directory:

      /Users/cjones/lib:  total used in directory 0 available 9223372036852910769  lrwxr-xr-x   1 cjones  staff    48 12 Nov 15:04 libclntsh.dylib -> /Users/cjones/instantclient_19_3/libclntsh.dylib

    This version will connect to 11.2 or later databases.

Answers

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,636 Employee
    edited Mar 3, 2020 8:48PM Accepted Answer

    Significant engineering effort would be needed to provide all the functionality that is  in cx_Oracle and in the Oracle client libraries (think network encryption, high availability, all the datatype support, authentication protocols etc).

    On Mac, you have to make sure to install your own Python - because the default one is locked down.

    And then I simply have this in my ~/lib directory:

      /Users/cjones/lib:  total used in directory 0 available 9223372036852910769  lrwxr-xr-x   1 cjones  staff    48 12 Nov 15:04 libclntsh.dylib -> /Users/cjones/instantclient_19_3/libclntsh.dylib

    This version will connect to 11.2 or later databases.

  • 4203522
    4203522 Member Posts: 3
    edited Mar 4, 2020 2:59PM

    Thanks cj for the quick response and an answer that helped solve my problem.  Now that I know exactly what it took to fix it I have

    a better perspective on how the cx_Oracle and instantclient have to be setup.  So for the sake of any other readers who might have

    a similar problem I'm going to expand on cj's answer and give the exact details that I had to do.

    1. the description in step 3 on this page: https://oracle.github.io/odpi/doc/installation.html#macos

      did not work in my case.  For one thing on my MAC there is no such directory ~/lib.  There is however a

      /Library directory off of the root folder.  But putting a symlink there did not fix the problem either.

    2. the crux of the issue is that the symlink must be setup correctly and in a location that the running python code can find it:

       a. in the python code at the point when it calls conn = cx_Oracle.connect('usr/[email protected]//host:port:schema'), there will be a call

       made inside the cx_Oracle or instantclient code like this:  dlopen(libclntsh.dylib, 1) and thus the python interpreter must be able to

       find libclntsh.dylib some how at runtime;

       b. so far the only way I could get this to work is to create a symlink in the python project root directory where the python

         app is running.  And the symlink must be named libclntsh.dylib and it must point to the full-path-location of the symlink

         that comes bundled with instantclient;

    As an example: suppose you have

    -a python project in subdirectory /Users/bob/mypyproj

    -and you have a python "script" file called orclconn.py like this: /Users/bob/mypyproj/orclconn.py

    and within orclconn.py it's going to connect to Oracle database.

    -and you unzipped the instantclient to /Users/bob/instantclient_19_3

        then in the project root create a symlink file like this:

    /Users/bob/mypyproj/libclntsh.dylib

        and this symlink must point to the symlink file where you unzipped the instantclient here

    /Users/bob/instantclient_19_3/libclntsh.dylib

        (that symlink file will in turn point to the actual file, something like /Users/bob/instantclient_19_3/libclntsh.dylib.19.1)

    now when you run the python script IN THE directory where orclconn.py is (your python program) then the call to dlopen(libclntsh.dylib, 1)

    will work.

    So a couple followup thoughts:

    1. I realize that my solution may not be ideal for some people because it requires creating a new symlink

    in every directory where you're going to run a python program from.  But like I said, I still haven't figured out a "global" location

    to create a symlink that works, though I've tried to put it in /Library without success.  Having said that I actually prefer not to

    muddy up global environment settings any more that I have to and prefer this anyway.

    2. I'm going to amend my original post suggestion above: Oracle does not necessarily need to create a "standalone" python driver because this thin

    client is fine.  Perhaps a little better explanation of how to set it up and how it works on the installation web page above would be nice.

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,636 Employee
    edited Mar 4, 2020 4:20PM
    1. the description in step 3 on this page: https://oracle.github.io/odpi/doc/installation.html#macos  did not work in my case.  For one thing on my MAC there is no such directory ~/lib. 

    Just create ~/lib.

    To help me improve the documentation, is the issue that you don't know what '~' means?  Or that you are concerned about creating directories?

    Update: The doc clearly shows the commands to run.  Is there some structural issue (layout, headings, numbering etc) that could be improved in the doc?

    mkdir ~/lib

    ln -s /opt/oracle/instantclient_19_3/libclntsh.dylib ~/lib/

    Chris

  • 4203522
    4203522 Member Posts: 3
    edited Mar 4, 2020 7:16PM

    I do understand what '~' means.  It is the user's "home" directory on (Linux and Mac).

    I went ahead and created the ~/lib subdirectory as you suggested.

    And then I created the symlink file in that directory and removed it from my python project root directory.

    And low and behold it worked.  So apparently the cx_Oracle code is specifically looking in ~/lib for the symlink named libclntsh.dylib.

    I.e. it'll look for this symlink at runtime: ~/lib/libclntsh.dylib.  Is that correct?

    I'll explain where I went wrong:

    1. my knowledge of symlinks was minimal and I took it for granted and ended up incorrectly creating the symlinks.  I tried creating

      the symlink in just about every possible permutation except for the ONE correct way to do it!  For example I did both of these:

      -INCORRECT: created a symlink ~/lib (where lib was a symlink file not a directory);

      -INCORRECT: I also created symlinks in various locations but I pointed it to the instantclient subdirectory NOT the lbclntsh.dylib file WITHIN the

      instantclient subdirectory.

    2. on a MAC I think (correct me if I'm wrong) the special directories are slightly different than most Linux in regards to

       lib vs Library.  I.e. I don't think that ~/lib on a MAC is considered a special subdirectory whereas it IS on most other Linux.

    Now here's what I'd suggest for documentation improvements: (tell the user to review their knowledge of symlinks like this):

    1. I'd recommend reviewing your knowledge of symlinks on Linux/MAC-OS (they work the same on both).  In particular understand that:

       1. a symlink IS a file and thus it can have any name just like a file can have any valid filename and it can reside in any subdirectory.

       2. a symlink can point to either a file or a subdirectory.

       3. when setting up instantclient the symlink will point to a FILE not a SUBDIRECTORY.

       4. the name of the symlink needs to be 'lbclntsh.dylib' because that is what cx_Oracle will be looking for at runtime

    2. Point out that if there is no directory called ~/lib then simply create one (just like you advised me above) and then put the symlink in there.

    by emphasizing these points I think it'll help users avoid the mistakes I made.

    Thanks again.

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,636 Employee
    edited Mar 4, 2020 7:29PM
    I went ahead and created the ~/lib subdirectory as you suggested.And then I created the symlink file in that directory and removed it from my python project root directory.And low and behold it worked. 

    That's great. I appreciate the persistence to sort this out.

    So apparently the cx_Oracle code is specifically looking in ~/lib for the symlink named libclntsh.dylib.I.e. it'll look for this symlink at runtime: ~/lib/libclntsh.dylib.  Is that correct?

    cx_Oracle simply calls a system function that uses the system library search path which, on macOS, happens to include ~/lib. There's no code in cx_Oracle to resolve the directory or file. Also it wouldn't matter whether the file itself or a symlink to the file exists.

    on a MAC I think (correct me if I'm wrong) the special directories are slightly different than most Linux in regards to   lib vs Library.  I.e. I don't think that ~/lib on a MAC is considered a special subdirectory whereas it IS on most other Linux.

    Isn't it the opposite? It's definitely special on macOS.

    For the doc you suggest adding:

    1. I'd recommend reviewing your knowledge of symlinks on Linux/MAC-OS (they work the same on both).  In particular understand that:   1. a symlink IS a file and thus it can have any name just like a file can have any valid filename and it can reside in any subdirectory.   2. a symlink can point to either a file or a subdirectory.   3. when setting up instantclient the symlink will point to a FILE not a SUBDIRECTORY.   4. the name of the symlink needs to be 'lbclntsh.dylib' because that is what cx_Oracle will be looking for at runtime

    That's a lot of wording for people to read.  Would you have read it?  The commands already give the steps.  And it doesn't have to be a symlink - it could be the file itself.

    2. Point out that if there is no directory called ~/lib then simply create one (just like you advised me above) and then put the symlink in there.

    I can add that near the existing command that shows creating the directory.  Thanks!

Sign In or Register to comment.