In the previous post

https://www.josip-pojatina.com/en/python-oracle-connection-options/

you can find how to connect to Oracle database by using cx_oracle Python driver, full Oracle Client installation and Red Hat rpm based distribution (Red Hat, CentOS, Oracle Linux, Fedora).

 In reality, more than 90% of all Linux servers in a Cloud belongs to Ubuntu (unlike on premise situation where Red Hat is a clear leader).

Since Ubuntu is based on Debian (not Red Hat), and there are a lot of queries on GitHub how to set up properly Oracle Python environment on Ubuntu, I spent some time to clarify some details and available options.

Explanations of how to install cx_oracle properly on cx_oracle project homepage is far from enough, especially Linux installation description (Ubuntu is even not mentioned there).

Details you can find on the following link:

https://cx-oracle.readthedocs.io/en/latest/installation.html#quick-start-cx-oracle-installation

I assume you already have Ubuntu 18.04 LTS up and running.

As I intended to use Ubuntu for developing Python apps, we need to install Oracle Client first.

There are two types of Oracle client:

  • Oracle client
  • Oracle Instant client

I’m going to use the later one, as Oracle client is not compatible with Ubuntu.

Main difference between those two are in their capabilities.

If you need to develop Oracle apps based on Pro*C development, XML or similar, Oracle client is the only option you have.

That requires to have one of Red Hat derivatives like OEL / Cent OS / Fedora.

In 99% of cases those functionalities are not required, so we can use Oracle instant client.

First you need to download Instant Client from the following link:

https://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

I advise, after accepting license agreement, to choose:

Instant Client Downloads for Linux x86-64 (64-bit)

Version 18.3.0.0.0

Basic Package

instantclient-basic-linux.x64-18.3.0.0.0dbru.zip (72,794,506 bytes) (cksum – 3435694482)

There are some suggestions to additionally download:

Tools – optional packages

SQL*Plus Package – The SQL*Plus command line tool for SQL and PL/SQL queries

You can do that, but there is a better solution that I’ll explain in one of the next blogs.

As instant client is just a zip file, you only need to unzip it.

I’ve used the following location to unzip instant client file (replace user with your Linux user account):

/home/user/instantclient_18_3

Next step is to install the following libraries (as a root): 

apt-get install libaio1 libaio-dev

After that you can install cx_oracle.

There are several options here, but in essence it depends on your Python distribution.

If you are going to use CPython (default Python interpreter), you can execute the following command (with user account):

python -m pip install cx_Oracle --upgrade

In case you are using Anaconda distribution for Scientific tasks, you need to execute the following: 

conda install -c anaconda cx_oracle

If you are using Python virtualization, commands that you need to execute are the same.

The only difference is that you need to activate your virtual environment first, before executing one of those two commands.

Finally comes the most frustrating part when you try to connect to Oracle db from Python code, that results in the following error.

cx_Oracle.DatabaseError: DPI-1047: 64-bit Oracle Client library cannot be loaded: "libclntsh.so: cannot open shared object file: No such file or directory".

In my previous post “Python – Oracle connection options” on the following link:

https://www.josip-pojatina.com/en/python-oracle-connection-options/

you can find solution for that error in case you have full Oracle client (not Oracle instant client) installed.

In short you need to add:

os.environ["ORACLE_HOME"] = "path_to_ORACLE_HOME"

in you Python code.

Working example of Python application accessing scott sample schema is provided in the same post.

If you have only instant client installed, the most important step is to set LD_LIBRARY_PATH that points to location where you have unzip downloaded instant client zip file.

Here you need to know when LD_LIBRARY_PATH takes the effect?

Correct answer is: It’s before your even start your Python program, which means you cannot set it or change it in your Python program by adding the following line of code, as you ‘ll need to reload env:

os.environ["LD_LIBRARY_PATH"] = "/home/user/instantclient_18_3"

There are three options on Linux to solve the issue with instant client libraries.

1.

Explicitly export environment LD_LIBRARY_PATH and execute Python program after

export LD_LIBRARY_PATH=/home/user/instantclient_18_3:$LD_LIBRARY_PATH

python3 test.py

This solution works only if you manually execute the Python program from the command line.
In case you need to connect to Oracle db from Python app with GUI, or from virtual environment (like pipenv, conda, virtualenv) or from Enterprise Scheduling SW, read on.

2.
Add and load required instant client libraries globally

# cd to the following directory
root@xx-Ubuntu1804LTS:> cd /etc/ld.so.conf.d

# create a new configuration file 
root@xx-Ubuntu1804LTS:/etc/ld.so.conf.d>touch oracle-instantclient.conf

# edit conf file with your favorite editor 
root@xx-Ubuntu1804LTS:/etc/ld.so.conf.d>vim oracle-instantclient.conf 

# add the following line in oracle-instantclient.conf 
/home/user/instantclient_18_3

# re-read library configuration 
root@xx-Ubuntu1804LTS:/etc/ld.so.conf.d>ldconfig

From now on, almost all situations are covered. Python with GUI, Python program executed from Enterprise Scheduling SW, program executed from virtual environment or from the command line.
The only problem with this solution is if you need to have several versions of instant client.
One of the reasons might be if you need to access Oracle 11.2 or some older versions, as current version of Oracle instant client (18.3) supports Db version >= 12.2
In such cases you’ll either need to install older version of instant client that covers all your target databases if possible, or to create script that will switch loaded libraries when needed, or to use some kind of virtualization (like Docker or Virtualbox).
Another drawback is root privileges that are required to be able to execute commands from above.
If you can’t obtain required privileges, check if next solution is more suitable for your case.

 

3.

Last option is to copy shared libraries from the instant client directory into your Python library directory.

That option is quick and dirty way to solve the problem if you don’t have a root privilege on machine and it works.

You can make a backup of Python lib directory before, in case you’ll need to revert all back.

Downside of that approach is that you are mixing Python libraries with Oracle instant client shared objects.

This is how it should look like

#from location where you have extracted sqlcl-18.4.0.007.1818.zip
#file (/home/user/instantclient_18_3), copy all shared libraries 
#to lib directory of your Python interpreter (in this case it's 
#/home/user/anaconda3/envs/python3/lib/) 

cp /home/user/instantclient_18_3/*.so /home/user/anaconda3/envs/python3/lib/

cp /home/user/instantclient_18_3/*.so.18.1 /home/user/anaconda3/envs/python3/lib/

#List of files that should appear in Python lib directory: 
#libclntsh.so
#libclntsh.so.18.1
#libclntshcore.so.18.1
#libipc1.so
#libmql1.so
#libnnz18.so
#libocci.so
#libocci.so.18.1
#libociei.so
#libocijdbc18.so
#libons.so
#liboramysql18.so

For Windows installation, you can follow instructions on the cx_Oracle project page:

https://cx-oracle.readthedocs.io/en/latest/installation.html#quick-start-cx-oracle-installation

I can’t confirm if instructions are correct as I haven’t tested Windows set up yet.

 

Summary:

Having Python 2 & 3 branches, Oracle full & instant client, different versions of instant client that support different versions of Oracle databases, different version of cx_Oracle driver, many versions of Linux distributions, Windows, options to run system Python or virtualized (pipenv, virtualenv…) , different distributions of Python (CPython, Anaconda), different ways of executing python program (from the command line, scheduled job that doesn’t apply env, virtualized Python program), command line or GUI Python program…, there are many variables you need to think about to setup your environment correctly.

Hopefully in this article you’ll find all bits in one place to setup everything correctly.



Get notified when a new post is published!

Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.