Python and cx_Oracle for Oracle database connections

In my latest adventures, I have been improving my Python skills in regards to running Oracle SQL and PL/SQL code.  Once I got the process down for basic connection and execution, a database migration including new LDAP connectivity threw me for a loop until I got it figured out.

First, a little background on cx_oracle.  cx_Oracle is a Python extension module that enables access to Oracle databases. It conforms to the Python database API 2.0 specification with a considerable number of additions and a couple of exclusions.  cx_Oracle 7 has been tested with Python version 2.7, and with versions 3.5 through 3.8. You can use cx_Oracle with Oracle 11.2, 12, 18 and 19 client libraries. Oracle’s standard client-server version functionality allows connection to both older and newer databases. For example Oracle 19c client libraries can connect to Oracle Database 11.2.

To check if you have the cx_oracle module installed in your current Python environment, you can simply create a Python script with the line import cx_Oracle and execute it with python <filename> or execute this command from the Python command line.  If there are no errors, then the module is likely installed.

If not, you can install it in Linux with the command with the pip (python installs packages) module:

python -m pip install cx_Oracle --upgrade

If you receive an error about the pip module being unavailable, you can install it with the command:

yum install python-pip

Now that we know what cx_oracle is for and how to install it, here is an example of using it to connect to an Oracle RAC system:

import sys
import os
import cx_Oracle

dsnStr = cx_Oracle.makedsn('<SCAN_NAME>','<LISTEN_PORT>',service_name='<SERVICE_NAME>')
con = cx_Oracle.connect(user='<USER>',password='PWD',dsn=dsnStr)
cur.con.cursor()
sql="""
<SQL or PL/SQL to execute>
"""
cur.execute(sql)
cur.close()
con.close()


Seems pretty simple.  This changed when we tried to configure a connection similar to the one above for a database using LDAP authentication.  The new sqlnet.ora and ldap.ora files where in place, but every connection attempt resulted in a ORA-12154 error.  In my particular case, Oracle instant client 12.2 and (regular) client 18c were installed on the server where these scripts were executed.  Even placing the necessary .ora network files in both locations resulted in the same errors.

The python command print os.environ will give you a list of all the environment variables that are set in the current environment.  Note – be sure to use the import os command to import the proper libraries first.  This was an important finding for me since I was using the Jenkins development environment and the actual execution user was not something I could see.  Using this command, I found out the identity of the OS user whom the python scripts were executing under.  After getting access to the development server, this account did not have any of the Oracle environment settings associated with it such as ORACLE_HOME, TNS_ADMIN, or LD_LIBRARY_PATH.  My next step was to set these under the user account on the server.  Even so, the attempts to connect to the Oracle database resulted in the same ORA-12154 error.  As a curious side, the print os.environ command did not list these variables after setting them at the OS level for the user account.

Now what?

Well, if you could pull up the environment variables using os.environ, couldn’t you set them as well?  Setting the values of ORACLE_HOME, TNS_ADMIN, and LD_LIBRARY_PATH in the Python script turned out to be the answer and ensured I was pointing to the correct Oracle client binaries.  Here is the resulting code:

import sys
import os
import cx_Oracle

os.environ['ORACLE_HOME'] = '/opt/oracle/version'
os.environ['TNS_ADMIN'] = '/opt/oracle/version/network/admin'
os.environ['LD_LIBRARY_PATH'] = '/opt/oracle/version/lib'

con = cx_Oracle.connect('{}/{}@{}'.format('<USER>','<PWD>','<LDAP_DESIGNATOR>'))
cur.con.cursor()
sql="""
<SQL or PL/SQL to execute>
"""
cur.execute(sql)
cur.close()
con.close()

Also, while working to get the python script to recognize the Oracle environment variable settings at the OS level, I discovered the use of the Python subprocess module in regards to calling scripts at the OS level.  This was a work around that could take advantage of the OS account environment settings.  However, I was told that even if username and password credentials were masked as they were passed in, they could be displayed by executing ps -ef on the server.  I have not been able to verify this, but it could be a security issue if executing Python scripts against production databases.

import os 
import subprocess 

output = subprocess.check_output(['<PATH_AND_SCRIPT_NAME',str('<ARG1>'),str('<ARG12')]) 
print(output)

If the script called executes SQL or PL/SQL, it is a good idea to use the WHENEVER SQLERROR EXIT 1 clause to ensure that any errors cause a failure and are captured.

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s