cx_Oracle python module for Oracle database interfacing

oracle_cx_oracle

Skill - cx_Oracle python module for Oracle database interfacing

In this post we will use cx_oracle python module for interfacing with an Oracle database.

Installing cx_oracle python module

Run the following command in your python environment

python -m pip install cx_Oracle

Connect to database with Connection and Cursor objects

The required parameters for connecting to an Oracle database are

database username (example: system)
database password (example: p#ssw0rd)
database server host (example: localhost / 192.168.19.5)
database server listening port (example: 1521)
database service name (example: xepdb1 or ORCL)

All the above parameters will be used to create a connection string in the format <username>/<password>@<dbHostAddress>:<dbPort>/<dbServiceName> (example: system/p#ssw0rd@localhost:1521/xepdb1)

import cx_Oracle

# connection string in the format
# <username>/<password>@<dbHostAddress>:<dbPort>/<dbServiceName>
connStr = 'system/pass@localhost:1521/xepdb1'

# initialize the connection object
conn = None
try:
    # create a connection object
    conn = cx_Oracle.connect(connStr)

    # get a cursor object from the connection
    cur = conn.cursor()

    # do something with the database
except Exception as err:
    print('Error while connecting to the db')
    print(err)
finally:
    if(conn):
        # close the cursor object to avoid memory leaks
        cur.close()

        # close the connection object also
        conn.close()
print("execution complete!")

The above python program uses a connection object and a cursor object.
Connection object establishes connection with the database.
Cursor object uses the connection object to execute SQL commands in the database to update data or fetch data from the database.
Cursors can also perform transactions for atomic execution of multiple commands. That means either all SQL commands will be executed, or else all the SQL commands will be cancelled.

SQL to create the table used in this blog post

-- drop the existing table
-- DROP TABLE students;

CREATE TABLE students (
	id NUMBER GENERATED by default on null as IDENTITY,
	st_name varchar(250) NOT NULL,
	dob timestamp NOT NULL,
	studentid NUMBER NOT NULL,
	PRIMARY KEY (id),
	UNIQUE (st_name, dob),
	UNIQUE (studentid)
);

Insert rows example

import cx_Oracle
import datetime as dt
import pandas as pd

# connection string in the format
# <username>/<password>@<dbHostAddress>:<dbPort>/<dbServiceName>
connStr = 'system/pass@localhost:1521/xepdb1'

# initialize the connection object
conn = None
try:
    # create a connection object
    conn = cx_Oracle.connect(connStr)

    # get a cursor object from the connection
    cur = conn.cursor()

    # prepare data insertion rows
    dataInsertionTuples = [
        ('xyz', dt.datetime(2021, 1, 1), 7654),
        ('abc', dt.datetime(2020, 10, 12), 9724)
    ]

    # create sql for deletion of existing rows to avoid insert conflicts
    sqlTxt = 'DELETE from "test1".students where\
                (st_name=:1 and dob=:2)\
                or (studentid=:3)'
    # execute the sql to perform deletion
    cur.executemany(sqlTxt, [x for x in dataInsertionTuples])

    rowCount = cur.rowcount
    print("number of existing rows deleted =", rowCount)

    # create sql for data insertion
    sqlTxt = 'INSERT INTO "test1".students\
                (st_name, dob, studentid)\
                VALUES (:1, :2, :3)'
    # execute the sql to perform data extraction
    cur.executemany(sqlTxt, dataInsertionTuples)

    rowCount = cur.rowcount
    print("number of inserted rows =", rowCount)

    # commit the changes
    conn.commit()
except Exception as err:
    print('Error while inserting rows into db')
    print(err)
finally:
    if(conn):
        # close the cursor object to avoid memory leaks
        cur.close()

        # close the connection object also
        conn.close()
print("data insert example execution complete!")

In the above program we use executemany function to execute a single SQL statement multiple times for each of the parameters tuple of the input list.
We injected input variables into SQL text using placeholders (like :1, :2 etc) to avoid the risk of SQL injection.
Also we have used python objects as input parameters based on the table columns like strings, numbers, datetime objects instead of converting them into SQL command strings to address the SQL injection issue. Do not convert input variables to strings by yourself. Let cx_Oracle handle it.
Before executing the insert command, we executed a delete command and deleted existing rows with same keys as newly inserted rows to avoid conflicts while insertion (You can implement your own conflict resolution strategy)
The conn.commit() function is committing all the uncommitted database changes made by the cursor object by executing SQL commands. Without calling this function, the database changes made by the cursor object will not be permanent. Hence do not forget to call conn.commit after executing a database modification command like INSERT, DELETE, UPDATE.

Insert dataframe example

The excel file used in this example can be downloaded from here

import cx_Oracle
import datetime as dt
import pandas as pd

# connection string in the format
# <username>/<password>@<dbHostAddress>:<dbPort>/<dbServiceName>
connStr = 'system/pass@localhost:1521/xepdb1'

# initialize the connection object
conn = None
try:
    # create a connection object
    conn = cx_Oracle.connect(connStr)

    # get a cursor object from the connection
    cur = conn.cursor()

    # create a sample dataframe
    # dataDf = pd.DataFrame(columns=["ST_NAME","DOB","STUDENTID"],
    #                       data=[['xyz', dt.datetime(2021, 1, 1), 7654],
    #                             ['abc', dt.datetime(2020, 10, 12), 9724]])
    
    # read dataframe from excel
    dataDf = pd.read_excel("cx_oracle_insert_data.xlsx")
    # print(dataDf.columns)
    
    # reorder the columns as per the requirement
    dataDf = dataDf[["ST_NAME","DOB","STUDENTID"]]

    # prepare data insertion rows from dataframe
    dataInsertionTuples = [tuple(x) for x in dataDf.values]
    print(dataInsertionTuples)

    # create sql for deletion of existing rows to avoid insert conflicts
    sqlTxt = 'DELETE from "test1".students where\
                (st_name=:1 and dob=:2)\
                or (studentid=:3)'
    # execute the sql to perform deletion
    cur.executemany(sqlTxt, [x for x in dataInsertionTuples])

    rowCount = cur.rowcount
    print("number of existing rows deleted =", rowCount)

    # create sql for data insertion
    sqlTxt = 'INSERT INTO "test1".students\
                (st_name, dob, studentid)\
                VALUES (:1, :2, :3)'
    # execute the sql to perform data extraction
    cur.executemany(sqlTxt, dataInsertionTuples)

    rowCount = cur.rowcount
    print("number of inserted rows =", rowCount)

    # commit the changes
    conn.commit()
except Exception as err:
    print('Error while inserting rows into db')
    print(err)
finally:
    if(conn):
        # close the cursor object to avoid memory leaks
        cur.close()

        # close the connection object also
        conn.close()
print("data insert example execution complete!")

Fetch rows from database

import cx_Oracle
import datetime as dt
import pandas as pd

# connection string in the format
# <username>/<password>@<dbHostAddress>:<dbPort>/<dbServiceName>
connStr = 'system/pass@localhost:1521/xepdb1'

# initialize the connection object
conn = None
try:
    # create a connection object
    conn = cx_Oracle.connect(connStr)

    # get a cursor object from the connection
    cur = conn.cursor()

    # create sql for querying data
    sqlTxt = 'select st_name, dob, studentid from "test1".STUDENTS \
                where dob >= :1 and studentid > :2 \
                order by st_name, studentid'
    # execute the sql to perform data extraction
    cur.execute(sqlTxt, (dt.datetime(2018, 1, 1, 0, 0, 0), 3000))

    # get the column names returned from the query
    colNames = [row[0] for row in cur.description]

    # fetch all rows from query
    dbRows = cur.fetchall()

    print("number of fetched rows =", len(dbRows))

    # iterate through all the fetched rows
    for rowIter in range(len(dbRows)):
        print("reading data from {0} row".format(rowIter))
        rowTuple = dbRows[rowIter]
        print("name =", rowTuple[0])
        print("dob =", rowTuple[1])
        print("studentId =", rowTuple[2])

    # create a dataframe from the fetched records (optional)
    recordsDf = pd.DataFrame.from_records(dbRows, columns=colNames)
except Exception as err:
    print('Error while fetching rows from db')
    print(err)
finally:
    if(conn):
        # close the cursor object to avoid memory leaks
        cur.close()

        # close the connection object also
        conn.close()
print("data fetch example execution complete!")

In the above code we have created an SQL fetch command to be executed as a string.

 sqlTxt = 'select st_name, dob, studentid from "test1".STUDENTS \
                where dob >= :1 and studentid > :2 \
                order by st_name, studentid'

We have also given SQL input placeholders as :1, :2, … to inject python variables into SQL statement while execution. This is a strongly recommended way of inserting variables in SQL commands since this avoids SQL injection attacks in our python programs.
cur.fetchall() will return the results of SQL fetch query as a list of tuples from our cursor variable.
[row[0] for row in cur.description] will return the column names in order for the fetched list of data tuples.

Update rows example

import cx_Oracle
import datetime as dt
import pandas as pd

# connection string in the format
# <username>/<password>@<dbHostAddress>:<dbPort>/<dbServiceName>
connStr = 'system/pass@localhost:1521/xepdb1'

# initialize the connection object
conn = None
try:
    # create a connection object
    conn = cx_Oracle.connect(connStr)

    # get a cursor object from the connection
    cur = conn.cursor()

    # create sql for updating table rows
    sqlTxt = 'UPDATE "test1".students set st_name = :1 where st_name = :2'

    # execute the sql to perform update
    cur.execute(sqlTxt, ("hgf", "abc"))

    rowCount = cur.rowcount
    print("number of updated rows =", rowCount)

    # commit the changes
    conn.commit()
except Exception as err:
    print('Error while updating rows in db')
    print(err)
finally:
    if(conn):
        # close the cursor object to avoid memory leaks
        cur.close()

        # close the connection object also
        conn.close()
print("data update example execution complete!")

Delete rows example

import cx_Oracle
import datetime as dt
import pandas as pd

# connection string in the format
# <username>/<password>@<dbHostAddress>:<dbPort>/<dbServiceName>
connStr = 'system/pass@localhost:1521/xepdb1'

# initialize the connection object
conn = None
try:
    # create a connection object
    conn = cx_Oracle.connect(connStr)

    # get a cursor object from the connection
    cur = conn.cursor()

    # create sql for deleting table rows
    sqlTxt = 'DELETE from "test1".students where st_name = :1'

    # execute the sql to perform update
    cur.execute(sqlTxt, ("xyz",))

    rowCount = cur.rowcount
    print("number of deleted rows =", rowCount)

    # commit the changes
    conn.commit()
except Exception as err:
    print('Error while deleting rows in db')
    print(err)
finally:
    if(conn):
        # close the cursor object to avoid memory leaks
        cur.close()

        # close the connection object also
        conn.close()
print("data delete example execution complete!")

Video

You can see the video on this post here and here

References

Official cx_Oracle quickstart guide - https://www.oracle.com/database/technologies/appdev/python/quickstartpythononprem.html
cx_Oracle documentation - https://cx-oracle.readthedocs.io/en/latest/user_guide/introduction.html#getting-started

Table of Contents

Taming Python

Search This Blog