Python Oracle Connection

This article will demonstrate how to make a python 3 oracle db connection. An Oracle database connection in python does not work out of the box and requires some initial setup before getting started. The article will demonstrate two different methods of using an Oracle Instant Client with Python to make a python Oracle Connection. The first method being to copy the instant client oracle libraries into your Python installation. The second method being to use the cx_Oracle documentation which uses the init_oracle_client method.

Copy Oracle Instant Client dll files

The following steps outline the general procedure for setting up your python and oracle connection.

1. Download and install python 3 using instructions for your operating system of choice. This article is using Windows 10.
2. Install the cx_Oracle library. If using PyCharm this can be installed as a python plugin. Note that if you cannot find the cx_Oracle library in PyCharm, IntelliJ, or any other JetBrains product it may be found as cx-Oracle (the hyphen instead of the underscore).
3. Download an Oracle Instant Client from: https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html (again, note that this article provides instructions for Windows. If you are using Linux or MacOS, download the Oracle Instant Client that matches your OS)
4. Copy the dll files in this screenshot below from the Oracle Instant Client to your python installation directory. In this example, the python installation directory is C:\Users\Bob\AppData\Local\Programs\Python\Python39
   

To spell it out, the dll’s are the following:

• oci.dll
• ocijdbc21.dll
• ociw32.dll
• oramysql.dll
• orannzsbb.dll
• oraocci21.dll
• oraocci21d.dll
• oraociei.dll
• orasql.dll

There you go, your python app should now be able to make an Oracle Database connection.

Set the Oracle Client directory

Setting the Oracle client directory within the cx_Oracle library is the recommended method for setting up your python Oracle connection. We chose to document it second, however, because the first example better demonstrates the necessary libraries needed to get the final connection to Oracle.

Here is a code sample for loading the Oracle client libraries. Again, note that this example is for making the connection on a Windows OS.

import cx_Oracle
lib_dir=r"C:\oracle\instantclient_19_9"
cx_Oracle.init_oracle_client(lib_dir=lib_dir)

Make sure that the ORACLE_HOME environment variable has been set to your instant client location also.

Test Oracle DB Connection in Python

The following code is an example of how to make the Oracle DB connection after setting up your instant client.

import cx_Oracle
def get_connection(user, password, dsn):
    try:
        return cx_Oracle.connect(user=user, password=password, dsn=dsn, encoding="UTF-8")
    except cx_Oracle.DatabaseError as e:
        print("Error connecting to the database", e)
        raise e

Parameter definitions:
user: The username of the user connecting to Oracle
password: The password of the user connecting to Oracle
dsn: The host and SID of the Oracle db being connected to. For example, example.com/PROD

To close the connection, use the following method:

def close(connection):
    try:
        if connection:
            connection.close()
    except cx_Oracle.DatabaseError as e:
        print("Error closing the database connection", e)
        raise e

Conclusion

The article has demonstrated how to configure a python installation with the ability to make an Oracle database connection, in Windows. Let us know in the comments if you have any questions or need help troubleshooting your database connection in Oracle. Check out our other posts.