Perform Basic CRUD Operations with cx_Oracle, Part 3

Here’s how to use Python for CRUD operations in Oracle Database.

By Blaine Carter

July/August 2018

Python is a powerful open source language, and the cx_Oracle driver gives your Python application access to the full power of Oracle Database.

In this article series, I’m going to take a look at how to perform CRUD (create, retrieve, update, and delete) operations in Python with the cx_Oracle driver. Part 1 of this series included setup information and examples for create: the C in CRUD. Part 2 continued with information and examples of how to perform operations for the R in CRUD: retrieve.

This article, Part 3, continues the series with examples of how to perform operations for the U in CRUD: update.

For setup information for the complete article series—including links and instructions for downloading the cx_Oracle driver and setting up the sample data—refer to “Perform Basic CRUD Operations Using CX_ORACLE, Part 1.”

Additional Setup

Before you run the update examples with cx_Oracle, create a reset script and a template that will handle all the example updates.

Reset template. To keep the examples clean and precise, you’ll want to reset the data at times. Create a new file called reset_data.py with the following code, and then run it whenever you would like to reset the data. (Note that this version adds pet data not included in other articles in this series.)

import cx_Oracle
import os
connectString = os.getenv('db_connect')
con = cx_Oracle.connect(connectString)
cur = con.cursor()

# Delete rows
statement = 'delete from cx_pets'
cur.execute(statement)
 
# Reset Identity Column
statement = 'alter table cx_pets modify id generated 
BY DEFAULT as identity (START WITH 8)'
cur.execute(statement)

# Delete rows
statement = 'delete from cx_people'
cur.execute(statement)

# Reset Identity Column
statement = 'alter table cx_people modify id generated 
BY DEFAULT as identity (START WITH 3)'
cur.execute(statement)

# Insert default rows
rows = [(1, 'Bob', 35, 'I like dogs'), 
(2, 'Kim', 27, 'I like birds')]
cur.bindarraysize = 2
cur.setinputsizes(int, 20, int, 100)
cur.executemany("insert into cx_people(id, name, age, notes) 
values (:1, :2, :3, :4)", rows)
con.commit()

# Insert default rows
rows = [(1, 'Duke', 1, 'dog'),
        (2, 'Pepe', 2, 'bird'),
        (3, 'Princess', 1, 'snake'),
        (4, 'Polly', 1, 'bird'),
        (5, 'Rollo', 1, 'horse'),
        (6, 'Buster', 1, 'dog'),
        (7, 'Fido', 1, 'cat')]
cur.bindarraysize = 2
cur.setinputsizes(int, 20, int, 100)
cur.executemany("insert into cx_pets (id, name, owner, type) 
values (:1, :2, :3, :4)", rows)
con.commit()

cur.close()

Update template. To run the update examples in this article, create a new file called update.py with the following code

import cx_Oracle
import os
connectString = os.getenv('db_connect')
con = cx_Oracle.connect(connectString)

def get_all_rows(label, data_type='people'):
 # Query all rows
 cur = con.cursor()
 if (data_type == 'pets'):
    statement = 'select id, name, owner, type 
                 from cx_pets order by owner, id'
 else:
    statement = 'select id, name, age, notes 
                 from cx_people order by id'
 cur.execute(statement)
 res = cur.fetchall()
 print(label + ': ')
 print (res)
 print(' ')
 cur.close()

get_all_rows('Original Data')

# Your code here

get_all_rows('New Data')

And for each example exercise, replace the # Your code here line with the specific example code. The update.py template includes the helper function get_all_rows(), which encapsulates a SELECT statement to verify that the updates worked. The SELECT functionality is covered in Part 2 of this series.

Note: Please review all example code in this article, and run it only if you are sure it will not cause any problems with your system.

Simple Update

Now perform a simple update that modifies a single record in the cx_people table. Replace the # Your code here line in update.py with the following code snippet, and run update.py in your Python session:

cur = con.cursor()
statement = 'update cx_people set age = :1 where id = :2'
cur.execute(statement, (31, 1))
con.commit()

When I run this code in my Python session, I see

Original Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like birds')]

New Data:
[(1, 'Bob', 31, 'I like dogs'), (2, 'Kim', 27, 'I like birds')]

Here’s what the update.py code does:

• Gets a cursor object from the connection. You will use this cursor to perform your database operations.
• Prepares a SQL UPDATE statement, changing the age to 31 for the record whose id is 1.
• Executes the statement, using bind variables. (See Part 2 of this series for an explanation of bind variables.)
• Commits the transaction.

Deeper Dive

Now update Bob’s notes to 'I like cats'.

When you’re done, the results should be

Original Data:
[(1, 'Bob', 31, 'I like dogs'), (2, 'Kim', 27, 'I like birds')]

New Data:
[(1, 'Bob', 31, 'I like cats'), (2, 'Kim', 27, 'I like birds')]

What does the successful code look like? Like this:

cur = con.cursor()
statement = 'update cx_people set notes = :1 where id = :2'
cur.execute(statement, ("I like cats", 1))
con.commit()

Reset the data. Now is a good time to run reset_data.py.

Boilerplate change. Now change the boilerplate get_all_rows statements in update.py to get pet data. Replace the last three code lines of update.py with

get_all_rows('Original Data', 'pets')

# Your code here

get_all_rows('New Data', 'pets')

Make Sure Your WHERE Clause Is Specific

Note that the previous example used the id column in the WHERE clause. For this data set, id is the primary key. You do not always need to use a primary key, but make sure you update only the rows you intend to.

Now let’s look at updating multiple rows. Let’s have Bob give his dog, Duke, to Kim. Replace the # Your code here line in update.py with the following code snippet, and run update.py in your Python session:

cur = con.cursor()
statement = 'update cx_pets set owner = :1 
             where owner = :2 and type = :3'
cur.execute(statement, (2, 1, 'dog'))
con.commit()

When I run this code in my Python session, I see

Original Data:
[(1, 'Duke', 1, 'dog'), (3, 'Princess', 1, 'snake'), 
(4, 'Polly', 1, 'bird'), (5, 'Rollo', 1, 'horse'), 
(6, 'Buster', 1, 'dog'), (7, 'Fido', 1, 'cat'), 
(2, 'Pepe', 2, 'bird')]

New Data:
[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), 
(5, 'Rollo', 1, 'horse'), (7, 'Fido', 1, 'cat'), 
<strong>(1, 'Duke', 2, 'dog')</strong>, (2, 'Pepe', 2, 'bird'), 
<strong>(6, 'Buster', 2, 'dog')</strong>]

Here’s what the update.py code does:

• Gets a cursor object from the connection
• Prepares a SQL UPDATE statement, changing the owner value to 2 (Kim) for the records with an owner value of 1 (Bob) and a type value of 'dog'
• Executes the statement, using bind variables
• Commits the transaction

This example uses only owner and type values, and it assumes that Bob has only one dog, Duke, as in the original data. But the new reset data function added a second dog, Buster. This example demonstrates what can happen when multiple users are working with the same data set.

In the data, the only unique identifier for cx_pets is id. Bob may have two dogs—or even two dogs named Duke. If you intend to change a specific row, make sure to use a unique identifier.

It also helps to . . .

Verify the Number of Affected Rows

Now let’s give Buster back to Bob. This time, use the unique id column and print out the number of rows affected, using Cursor.rowcount. Replace the # Your code here line in update.py with the following code snippet, and run update.py in your Python session:

cur = con.cursor()
statement = 'update cx_pets set owner = :1 where id = :2'
cur.execute(statement, (1, 6))
con.commit()
print('Number of rows updated: ' + str(cur.rowcount))
print(' ')

When I run this code in my Python session, I see

Original Data:
[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), 
(5, 'Rollo', 1, 'horse'), (7, 'Fido', 1, 'cat'), 
(1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird'), 
(6, 'Buster', 2, 'dog')]

Number of rows updated: 1

New Data:
[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), 
(5, 'Rollo', 1, 'horse'), <strong>(6, 'Buster', 1, 'dog')</strong>, 
(7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), 
(2, 'Pepe', 2, 'bird')]

Here’s what the update.py code does:

• Gets a cursor object from the connection
• Prepares a SQL UPDATE statement, changing the owner value to 1 (Bob) for the records with an id value of 6 (Buster)
• Executes the statement, using bind variables
• Commits the transaction

Cursor.rowcount shows the number of rows affected for INSERT, UPDATE, and DELETE statements and the number of rows returned in a SELECT statement.

Another Deeper Dive

Now let’s give all birds to Kim—all the birds she doesn’t already have—and print the number of affected rows.

When you’re done, the results should be

Original Data:
[(3, 'Princess', 1, 'snake'), (4, 'Polly', 1, 'bird'), 
(5, 'Rollo', 1, 'horse'), (6, 'Buster', 1, 'dog'), 
(7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), 
(2, 'Pepe', 2, 'bird')]

Number of rows updated: 1

New Data:
[(3, 'Princess', 1, 'snake'), (5, 'Rollo', 1, 'horse'), 
(6, 'Buster', 1, 'dog'), (7, 'Fido', 1, 'cat'), 
(1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird'), 
(4, 'Polly', 2, 'bird')]

What does the successful code look like? Like this:

cur = con.cursor()
statement = 'update cx_pets set owner = :1 
             where type = :2 and owner != :3'
cur.execute(statement, (2, 'bird', 2))
con.commit()
print('Number of rows updated: ' + str(cur.rowcount))
print(' ')

Some Other Things You Can Try

Here are some other updates to try with the sample data:

• Change multiple column values.
• Perform an update that changes all rows. And if the row count is greater than 2, roll back the change.

The next article will conclude this series with coverage of the D in CRUD: delete.

Next Steps

DOWNLOAD cx_Oracle.

GET this article’s code examples from GitHub.

READ Part 1 and Part 2 of this article series.

Photography by Shutterstock