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
Copy Oracle Instant Client dll files
The following steps outline the general procedure for setting up your python and oracle connection.
- Download and install python 3 using instructions for your operating system of choice. This article is using Windows 10.
- 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).
- 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)
- 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
To spell it out, the dll’s are the following:
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
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,
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
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.