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:
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):
SYSTEM_OCON =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Host Name )(PORT = Port Number ))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Service Name )
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(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. #conn.close() 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.