cx_Oracle Tutorial

Download and install the latest version of python from Python Website if not already done.

Setting up virtual environment is not a must. But this is a best practice when working on python projects. Click virtual environment for more details.

Install cx_Oracle using the command: pip install cx_Oracle

Type command pip freeze
Should give an output like:

cx-Oracle==8.3.0

cx_Oracle has built-in functions that we can use to easily establish an Oracle database connection. First step is to use the cx_Oracle.makedsn method to get the dsn using host name(ip), port number and sid or service. Then use cx_Oracle.connect() method to create a connection by providing the user name, password and the dsn.

import cx_Oracle 
h = '' #Your host name/ip
p = '' #Your port number
sid = '' #Your sid
u = '' #Your login user name
pw = '' #Your login password
d = cx_Oracle.makedsn(h, p, sid=sid)
conn = cx_Oracle.connect(user=u, password=pw, dsn=d)
print(conn)

To run the code, use command python filename.py

On running the code should see the below output if connection successful.

cx_Oracle.Connection to username@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=portno))(CONNECT_DATA=(SID=sid)))

A cursor is essentially an identifier for a group of rows. Cursor helps in the traversal of records from database and to manage data from an SQL query. A cursor object is required in cx_Oracle for all the basic operations like select, insert, update and delete.

We can create multiple cursors from same connection. Any changes done to data using one cursor is visible to the other cursor immediately. In the below samples, you can see that a cursor object is created before doing any data manipulations. Same cursor can be used to perform multiple tasks as well. Use this carefully in your code as per your need and make sure to close the connections after the tasks are completed.

To fetch data from the database, we will usually be writing a Select query. Select query can be with one or more parameters or without parameters as well. If we need to perform multiple tasks before fetching data stored procedure is probably the best option. We will go through each of these in detail in the below sections.

As mentioned earlier, to perform database operations using cx_Oracle, we need to create a cursor from the connection object. Then call the execute method, that helps to select the required data from mentioned table of an Oracle schema. Then use the fetchall(), fetchone() or fetchmany() functions (explained in later sections below) to work on the data as per your need.

Below sample has a select query without parameters.

import cx_Oracle 
h = '' #Your host name/ip
p = '' #Your port number
sid = '' #Your sid
u = '' #Your login user name
pw = '' #Your login password
d = cx_Oracle.makedsn(h, p, sid=sid)
conn = cx_Oracle.connect(user=u, password=pw, dsn=d)
cursor = conn.cursor()
cursor.execute("SELECT * FROM TableName")
for row in cursor.fetchall():
    print(row)

If we need to write a select query with one or more parameters, we can do so by making a minor change in the execute section as below. Include the parameters within the select query like :id, ie, use a : before the parameter variables. Also include the actual variables in the execute method as a list within square brackets [].

import cx_Oracle 
h = '' #Your host name/ip
p = '' #Your port number
sid = '' #Your sid
u = '' #Your login user name
pw = '' #Your login password
d = cx_Oracle.makedsn(h, p, sid=sid)
conn = cx_Oracle.connect(user=u, password=pw, dsn=d)
cursor = conn.cursor()
id = 1
nm = 'Test'
cursor.execute("SELECT * FROM TableName WHERE id = :id and name = :nm", [id, nm])
row = cursor.fetchone()
print(row)

Once we understand how to use a select query in cx_Oracle, rest of the DML operations are pretty much similar and easy to follow. We just need to replace the select with insert query and declare the parameters as mentioned above. One important step after performing a DML task is to call commit() function to persist the data changes.

To insert multiple records at a time we can use the 'insert into...select...' format.

import cx_Oracle
h = '' #Your host name/ip
p = '' #Your port number
sid = '' #Your sid
u = '' #Your login user name
pw = '' #Your login password
d = cx_Oracle.makedsn(h, p, sid=sid)
conn = cx_Oracle.connect(user=u, password=pw, dsn=d)
cursor = conn.cursor()
cursor.execute("INSERT INTO TableName (id, name) VALUES (:id, :firstname)", [someid, 'somename'])
conn.commit()

To update, call update query inside the execute function as below.

import cx_Oracle 
h = '' #Your host name/ip
p = '' #Your port number
sid = '' #Your sid
u = '' #Your login user name
pw = '' #Your login password
d = cx_Oracle.makedsn(h, p, sid=sid)
conn = cx_Oracle.connect(user=u, password=pw, dsn=d)
cursor = conn.cursor()
cursor.execute("UPDATE TableName SET name = :fname WHERE id = :id", ['somename', someid])
conn.commit()

Use delete query to remove one or more records from a table.

import cx_Oracle 
h = '' #Your host name/ip
p = '' #Your port number
sid = '' #Your sid
u = '' #Your login user name
pw = '' #Your login password
d = cx_Oracle.makedsn(h, p, sid=sid)
conn = cx_Oracle.connect(user=u, password=pw, dsn=d)
cursor = conn.cursor()
cursor.execute("DELETE FROM TableName WHERE id = :delid", [someid])
conn.commit()

If we are using an Oracle stored procedure, use callproc to execute the stored procedure instead of execute function. Provide the sp name inside the callproc function. Below sample doesn't pass any parameters to the stored procedure.

import cx_Oracle
h = '' #Your host name/ip
p = '' #Your port number
sid = '' #Your sid
u = '' #Your login user name
pw = '' #Your login password
d = cx_Oracle.makedsn(h, p, sid=sid)
conn = cx_Oracle.connect(user=u, password=pw, dsn=d)
cursor = conn.cursor()
cursor.callproc("proc_name")

For stored procedures with parameters, pass this as a list within square brackets[], as shown below.

import cx_Oracle
h = '' #Your host name/ip
p = '' #Your port number
sid = '' #Your sid
u = '' #Your login user name
pw = '' #Your login password
d = cx_Oracle.makedsn(h, p, sid=sid)
conn = cx_Oracle.connect(user=u, password=pw, dsn=d)
cursor = conn.cursor()
id = 1
cursor.callproc("proc_name", [id])

We have multiple options to work on the selected data from the schema table. fetchone(), fetchmany() and fetchall(). We can use these in combination as well.

fetchone() returns next row. Use this for scenarios like getting a count.
fetchmany(noOfRows) returns rows based on the number specified as parameter. Can be used to work on a subset of data.
fetchall() returns all the rows from the select query. If this is used after fetchone() or fetchmany(), this will return the remaining rows from the select query.

import cx_Oracle
h = '' #Your host name/ip
p = '' #Your port number
sid = '' #Your sid
u = '' #Your login user name
pw = '' #Your login password
d = cx_Oracle.makedsn(h, p, sid=sid)
conn = cx_Oracle.connect(user=u, password=pw, dsn=d)
cursor = conn.cursor()
cursor.execute("SELECT * FROM TableName")
for row in cursor.fetchone():
    print(row)
for row in cursor.fetchmany(3):
    print(row)
for row in cursor.fetchall():
    print(row)

Built-in functions are available in cx_Oracle to commit or rollback any transactions.

commit() Commits all transactions since last commit or rollback.
rollback() Rolls back all uncommitted transactions.

Closing the database connection is an important step and make sure to do this for every opened connection, ideally in the finally block of the try...except statement. Use conn.close() to perform this action. Or else use with statement (explained in next section).

Using with statements as in below sample, we don't have to worry about explicitly closing the connections.

import cx_Oracle
h = '' #Your host name/ip
p = '' #Your port number
sid = '' #Your sid
u = '' #Your login user name
pw = '' #Your login password
d = cx_Oracle.makedsn(h, p, sid=sid)
with cx_Oracle.connect(user=u, password=pw, dsn=d) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM TableName")
        for row in cursor.fetchall():
            print(row)