cx_Oracle is a third-party Python library that facilitates communication between Oracle database tables and Python. In this post, we’ll explore the cx_Oracle API and demonstrate how to handle data as it passes from Oracle to Python via the cx_Oracle interface.

A cursor is a control structure that enables traversal over the records in a database. This is Python’s primary means of accessing database table data. The setup is:

  • Create a connection object
  • Define a cursor
  • Construct a query string for the data of interest
  • Pass the query string to the cursor’s execute method
  • Iterate over the cursor as you would any other Python iterable
# creating connection object =>    
db = cx_Oracle.connect(<SCHEMA_NAME>, <SCHEMA_PASSWORD>, <DATABASE>)

# defining cursor =>
cursor = db.cursor()  

# defining query =>
SQL = "SELECT * FROM SAMPLE_TABLE"  

# calling cursor's execute method of SQL =>
cursor.execute(SQL) 

# iterating over cursor =>
for record in cursor: print(record)

The data returned by the cursor will be a list of tuples, so data elements can be accessed by row or selectively by referencing record components using index offset.

For example, to connect to database schema SCHEMA_1 in database DB using password password, and query and return TABLE_1, the full specification would be:

import cx_Oracle

db     = cx_Oracle.connect('SCHEMA_1', 'password', 'DB')
cursor = db.cursor()  
SQL    = "SELECT * FROM TABLE_1"  
cursor.execute(SQL) 
for record in cursor: print(record)

Note that the returned cursor object is a Python iterator, which means the data elements can only be traversed once.

Per the Python DB API 2.0, details specific to the returned dataset should be written to the cursor’s description attribute. cursor.description is a list of 7-tuples, each containing information that describe characteristics of a single column:

  • fieldname
  • datatype
  • display_size
  • internal_size
  • precision
  • scale
  • null_ok

To obtain the list of headers associated with the queried dataset, extract the first element of each 7-tuple as present in cursor.description:

headers = [i[0] for i in cursor.description]



With the basic mechanics of cx_Oracle’s API covered (and more generally, the Python DB API 2.0), we’ll walk through a few examples of common tasks that can be accomplished using the library.

Common Data Handling and Manipulation Techniques

As mentioned previously, records returned by a cursor can only be traversed once. If multiple passes need to be run on the data, pass each yielded record into a list. Once the records have been written to a list, they can be traversed without limit:

# query `SCHEMA.TABLENAME@DATABASE`, and redirect
# returned data to a list for additional processing =>
import cx_Oracle

db     = cx_Oracle.connect('SCHEMA_1', 'password', 'DB')
cursor = db.cursor()  
SQL    = "SELECT * FROM TABLE_1"  
cursor.execute(SQL) 

# read cursor records into `records` list =>
records = [i for i in cursor]

# iterate over cursor, printing each record =>
for record in records: print(record)

# demonstrate persistence of data =>
len(records)


It may be desirable to write the queried data to file. For this task, we can leverage the csv module:

import cx_Oracle
import csv

db     = cx_Oracle.connect('SCHEMA_1', 'password', 'DB')
cursor = db.cursor()  
SQL    = "SELECT * FROM TABLE_1"  
cursor.execute(SQL) 

# extract headers from cursor.description =>
headers = [i[0] for i in cursor.description]


# open a file for writing and create a csv.writer instance =>
with open("DATA.csv", "w") as f:

    fcsv = csv.writer(f)

    # write header row, then write the rest of the data =>
    fcsv.writerow(headers)

    # iterate over cursor, writing records to fcsv =>
    for record in cursor: fcsv.writerow(record)


assert f.closed


# read written file data back; result will be a list of lists =>
with open("DATA.csv", "r") as f:
    fcsv = csv.reader(f)
    for i in fcsv: print(i)  



We’ll conclude with an example of how to substitute bind variables iteratively. Assume the dataset of interest is a table of insurance claims, where each record is identified as one of BLD_FIRE, BLD_WATR and BLD_WTHR for ‘Building Fire’, ‘Building Water’ and ‘Building Weather’ claims respectively (assume the field identifying the type of claim is PERIL_ID in TABLE_1). We want to return a separate list of tuples for BLD_FIRE, BLD_WATR and BLD_WTHR claims. We’ll create a dictionary with perils as keys each containing a list of the associated peril’s records. The result will be a dictionary of lists of tuples:

import cx_Oracle

db     = cx_Oracle.connect('SCHEMA_1', 'password', 'DB')
cursor = db.cursor()  
SQL    = "SELECT * FROM TABLE_1"  
cursor.execute(SQL) 

# extract headers:
headers = [i[0] for i in cursor.description]

# perils to iterate over:
perils  = ['BLD_FIRE', 'BLD_WATR', 'BLD_WTHR']
master  = {}

for p in perils:

    params = {'peril':p}
    SQL    = "SELECT * FROM TABLE_1 WHERE PERIL_ID=:peril"
    cursor.execute(SQL, params)
    recs   = [i for i in cursor]

    # insert headers as first position:
    recs.insert(0, tuple(headers))
    master[p] = recs


# display results:
print(master['BLD_FIRE'])    #prints 'BLD_FIRE' list
print(master['BLD_WATR'])    #prints 'BLD_WATR' list
print(master['BLD_WTHR'])    #prints 'BLD_WATR' list

# to list the fields that you've binding on, call the cursor's `bindnames` method:
print(cursor.bindnames())        


Conclusion

cx_Oracle facilitates straightforward Oracle database interaction, and thanks in large part to Python’s DB API 2.0, any RDBMS-distributed 3rd-party library will expose similiar functionality, and will operate for the most part in a manner similiar to what we’ve covered here. Note that you can find a demonstration of DB API 2.0 as it pertains to sqlite3 here. Until next time, happy coding!