How to Connect Python to Oracle Database using cx_Oracle

Need to connect Python to an Oracle database using cx_Oracle connect?

If so, in this short guide, you’ll see the steps to establish this type of connection from scratch.

Steps to Connect Python to Oracle using cx_Oracle connect

Step 1: Install the cx_Oracle package

If you haven’t already done so, install the cx_Oracle package. You may use the following syntax to install the cx_Oracle package under Windows:

pip install cx_Oracle

Step 2: Retrieve the connection information

Next, retrieve the connection information. You can do that by locating your tnsnames.ora file on your computer (e.g., type tnsnames.ora in the Windows search bar).

Now, open your tnsnames.ora file and look for your desired connection.

It should look like the info below (highlighted in colors 3 elements that you usually need to look for before you can establish a connection between Python and your Oracle database):

(ADDRESS = (PROTOCOL = TCP)(HOST = Host Name)(PORT = Port Number))
(SERVICE_NAME = Service Name)
(RETRIES = 180)
(DELAY = 5)

Step 3: Connect Python to Oracle using cx_Oracle connect

Finally, copy/type the following syntax in Python while adding the needed info based on your Oracle connection:

import cx_Oracle

dsn_tns = cx_Oracle.makedsn('Host Name', 'Port Number', service_name='Service Name') # if needed, place an 'r' before any parameter in order to address special characters such as '\'.
conn = cx_Oracle.connect(user=r'User Name', password='Personal Password', dsn=dsn_tns) # if needed, place an 'r' before any parameter in order to address special characters such as '\'. For example, if your user name contains '\', you'll need to place 'r' before the user name: user=r'User Name'

c = conn.cursor()
c.execute('select * from database.table') # use triple quotes if you want to spread your query across multiple lines
for row in c:
    print (row[0], '-', row[1]) # this only shows the first two columns. To add an additional column you'll need to add , '-', row[2], etc.

Please note that there are additional ways to retrieve the information needed to facilitate your connection to the 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

Conclusion and Additional Resources

You just saw how to connect Python to Oracle using cx_Oracle connect. Once you established such a connection, you can start using SQL in Python to manage your data.

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

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