Need to connect Python to an Oracle database using cx_Oracle connect?
If so, in this short post, I’ll show you the steps to establish this type of connection from scratch.
Steps to Connect Python to Oracle using cx_Oracle connect
(1) First, install the cx_Oracle package. You may use the PIP approach to perform such an installation.
(2) Then, 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)
(3) Open your tnsnames.ora file and look for your desired connection.
It should look like the info below (I highlighted in colors the 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))
(LOAD_BALANCE = YES)
(SERVER = DEDICATED)
(SERVICE_NAME = Service Name)
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
(4) Now 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 any special character 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 any special character 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, '-', row) # this only shows the first two columns, to add an additional column you'll need to add , '-', row, 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 in Oracle to get the Service Name:
select sys_context(‘userenv’,’service_name’) from dual
- You can also run the following query in Oracle to get the list of users:
select username from dba_users
We just saw how you can connect Python to Oracle using cx_Oracle connect. Once you established such a connection, you can definitely use 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 posts:
- Connect Python to SQL Server using pyodbc
- Connect Python to MS Access Database using pyodbc
- Connect Python to MySQL using MySQLdb
For further information about the cx_Oracle package, please refer to the cx_Oracle’s documentation.