Connect Python to Oracle Database using cx_Oracle

To connect Python to an Oracle database using cx_Oracle:

import cx_Oracle

dsn_tns = cx_Oracle.makedsn("Host Name", "Port Number", service_name="Service Name")
connection = cx_Oracle.connect(
    user=r"User Name", password="Personal Password", dsn=dsn_tns
)

cursor = connection.cursor()
cursor.execute("SELECT * FROM database.table")

for row in cursor:
    print(row)

cursor.close()
connection.close()

Steps to Connect Python to Oracle using cx_Oracle

Step 1: Install the cx_Oracle package

To start, install the cx_Oracle package using this command:

pip install cx_Oracle

Step 2: Retrieve the connection information

To retrieve the connection information, locate your tnsnames.ora file on your computer.

It should look like the info below (highlighted in colors are the elements to look for when connecting Python to your Oracle database):

Step 3: Connect Python to Oracle using cx_Oracle connect

Finally, apply the following syntax in Python while adding the needed info based on your Oracle connection information:

import cx_Oracle

dsn_tns = cx_Oracle.makedsn("Host Name", "Port Number", service_name="Service Name")
connection = cx_Oracle.connect(
    user=r"User Name", password="Personal Password", dsn=dsn_tns
)

cursor = connection.cursor()
cursor.execute("SELECT * FROM database.table")

for row in cursor:
    print(row)

cursor.close()
connection.close()

Please note that there are additional ways to retrieve the information needed to facilitate your connection to an Oracle database.

For example, you can run the following query to get the Service Name:

SELECT sys_context('userenv', 'service_name') FROM dual

You may also run the following query to get the list of users:

SELECT username FROM dba_users

Additional Resources

You can learn more about the different types of connections between Python and other database applications by visiting these guides:

• Connect Python to SQL Server using pyodbc
• Connect Python to MS Access Database using pyodbc

For further information about the cx_Oracle package, please refer to the cx_Oracle’s documentation.