Introduction to accessing Oracle data from Python using the cx_Oracle library.

Working_with_cx_Oracle.md

Introduction to cx_Oracle

cx_Oracle is a third-party Python library that facilitates Oracle-to-Python database communication. 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 and call the cursor() method on the connection object
• Construct a query string for the data of interest
• Pass the query string to the cursor's execute method
• Step through the cursor as if it were any Python iterable

Here's an example:

db     = cx_Oracle.connect(<SCHEMA_NAME>, <SCHEMA_PASSWORD>, <DATABASE>)
cursor = db.cursor()  
SQL    = "SELECT * FROM SAMPLE_TABLE"
cursor.execute(SQL)

The result will be a list of tuples, so data elements can be accessed by row or selectively by referencing record components using index offset:

for record in cursor: 
    print(record)

(list of tuples is returned...)

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 describing the characteristics of a single column:

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

To obtain the header list for the returned dataset, extract the first element of each 7-tuple in cursor.description. Here are two ways to go about it (in both cases, headers will contain the dataset's fieldnames):

Method 1: Extracts the 0-th element from each 7-tuple and appends it to headers:

>>> headers = []
>>> for i in cursor.description: headers.append(i[0])

Method 2: list comprehension implementation:

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

Oracle-Python Data Handling and Manipulation Examples

The following are examples of how to carry out common tasks across the Oracle-Python interface.

Query SCHEMA.TABLENAME@DATABASE, and redirect returned data to a list for additional processing:

import cx_Oracle

db     = cx_Oracle.connect('SCHEMA','PASSWORD','DATABASE')
cursor = db.cursor()
SQL    = "SELECT * FROM SCHEMA.TABLENAME"
cursor.execute(SQL)

# Read `cursor` data elements into `records` list:
records = [i for i in cursor]

# Iterate over cursor, printing each record:
for record in cursor: print(record)
    
# Demonstrate persistence of data:
len(records)

Query SCHEMA.TABLENAME@DATABASE, and redirect output to file using Python's csv module:

import cx_Oracle
import csv

db     = cx_Oracle.connect('SCHEMA','PASSWORD','DATABASE')
cursor = db.cursor()
SQL    = "SELECT * SCHEMA.TABLENAME"
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)
    for record in cursor: fcsv.writerow(record)

assert f.closed

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

Changing bind variables iteratively. Assume we want to return a separate list of tuples for BLD_FIRE, BLD_WATR and BLD_WTHR perils (each identified in the PERIL_ID field). We'll create a master list, which will contain 3 lists of each associated peril's records. Result will be a list of lists of records:

import cx_Oracle
import os
import csv


# Setup connection:
db     = cx_Oracle.connect('SCHEMA','PASSWORD','DATABASE')
cursor = db.cursor()
SQL    = "SELECT * SCHEMA.TABLENAME"
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 SCHEMA.TABLENAME WHERE PERIL_IND=:peril"
    cursor.execute(SQL, params)
    recs      = [i for i in cursor]
    
    #insert headers as first element of each sub-list:
    recs.insert(0, tuple(headers))
    master.append(recs)
    

# Display results:
print(master[0])    #prints 'BLD_FIRE' list
print(master[1])    #prints 'BLD_WATR' list
print(master[2])    #prints 'BLD_WATR' list

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