Python – Oracle connection options

In this blog I’ll present several ways for connecting to Oracle database.

As a first step To connect to Oracle database you need to import cx_oracle package.

You can think of cx_oracle as Oracle jdbc driver for Java programmers.

Since the first public appearance in July, 20017, Oracle is constantly improve Python cx_driver by adding formidable set of functionality, even more than it has in Java jdbc driver.

Latest version of Oracle Python driver at the time of writing this article is version 7.

That should be enough to convince you to expect production grade stability, as development status is set to mature, which you can check at the following link:

https://pypi.org/project/cx_Oracle/

Here are the most common ways to access Oracle database by using cx_driver:

1.

If you have defined tns entry in tnsnames.ora, use the following method:

db = cx_Oracle.connect(‘scott/xx@orcl’)

2.

Same as 1, written in a different way:

db = cx_Oracle.connect(user=’scott’, password = ‘xx’, dsn = ‘ORCL’)

3.

Without tns entry, connection defined within dsn (data source name):

db = cx_Oracle.connect(user=’scott’, password = ‘xx’, dsn = ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(host = localhost) (Port = 1521))) (CONNECT_DATA = (SID = orcl)))’)

4.

Same as 3, except this time service name instead of SID have been used:

db = cx_Oracle.connect(user=’scott’, password = ‘xx’, dsn = ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(host = localhost) (Port = 2263))) (CONNECT_DATA = (SERVICE_NAME = python_test)))’)

5.

Connection string is separately defined (SID and service name versions are provided)

#create connection string by using SID

connectionstring = cx_Oracle.makedsn(host = ‘localhost’, port = 1521, sid = ‘orcl’)

#or by using SERVICE_NAME

connectionstring = cx_Oracle.makedsn(host = ‘localhost’, port = 2263, service_name = ‘python_test’)

db = cx_Oracle.connect(user=’scott’, password = ‘xx’, dsn = connectionstring)

Here is the complete demo:

import cx_Oracle
import os

os.environ["ORACLE_HOME"] = "/home/xx/app/xx/product/18.0.0/client_1"

db = cx_Oracle.connect('scott/xx@orcl')


cursor = db.cursor()

try:
    cursor.execute('SELECT * FROM emp')
    for row in cursor:
        print(row)

except cx_Oracle.DatabaseError as ora_exception:
    error, = ora_exception.args
    print("Oracle Error Code: ", error.code)
    print("Oracle Error Message: ", error.message)

cursor.close()
db.close()

If you forget to set ORACLE_HOME in your program like this:

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

you’ll get 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".

I’ve tested the code from above on both: CPython and Anaconda distribution, by using Python pipenv and cond virtualization, with full Oracle client installed.

---