Oracle Database Connection in Python

Python can connect to Oracle using a python package called cx_Oracle. Oracle is one of the famous and widely used databases and Python's data processing features are leveraged well using this connectivity. In this article we will see how we can connect to Oracle database and query the DB.

Installing cx_Oracle

We can use the below command to install the python package which can be used for establishing the connectivity ?

pip install cx_Oracle

Connecting to Oracle

Using this module we can connect to an Oracle database which is accessible through the Oracle service name. We create a cursor and execute the SQL query through the cursor for creating a table. All this is handled through a try-except structure to catch any exception or failure in database connectivity ?

Example

import cx_Oracle

# Connecting to DB
try:
    con = cx_Oracle.connect('username/password@servicename')
    cursor = con.cursor()
    
    # Creating a table
    cursor.execute("create table TableName(COL1 VARCHAR2(200), COL2 NUMBER(8))")
    print("Table Created")
    
except cx_Oracle.DatabaseError as e:
    print("Problem connecting to Oracle", e)
    
finally:
    # Close all database operations
    if cursor:
        cursor.close()
    if con:
        con.close()

Running the above code gives us the following result −

Table Created

Querying Data

After establishing the connection, you can also query existing tables and fetch data ?

import cx_Oracle

try:
    con = cx_Oracle.connect('username/password@servicename')
    cursor = con.cursor()
    
    # Querying data
    cursor.execute("SELECT * FROM employees WHERE department = 'IT'")
    rows = cursor.fetchall()
    
    for row in rows:
        print(row)
        
except cx_Oracle.DatabaseError as e:
    print("Error querying database:", e)
    
finally:
    if cursor:
        cursor.close()
    if con:
        con.close()

Conclusion

The cx_Oracle package provides a straightforward way to connect Python applications to Oracle databases. Always use proper exception handling and ensure database connections are properly closed to avoid resource leaks.