Application Development

Perform Basic CRUD Operations with cx_Oracle, Part 4

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.

This article series takes a look at how to perform create, retrieve, update, and delete (CRUD) 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. Part 3 provided examples of how to perform operations for the U in CRUD: update.

This article, Part 4, concludes the series with examples of how to perform operations for the D in CRUD: delete.

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 delete examples with cx_Oracle, create a reset script and a template that will handle all the delete examples.

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 people and 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 8)'
cur.execute(statement)

# Insert default rows
rows = [(1, 'Bob', 35, 'I like dogs'),
        (2, 'Kim', 27, 'I like birds'),
        (3, 'Cheryl', 23, 'I like horses'),
        (4, 'Bob', 27, 'I like rabbits'),
        (5, 'Stacey', 45, 'I like snakes'),
        (6, 'Pete', 23, 'I like cats'),
        (7, 'Pat', 36, 'I like dogs')]
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, 'Dragon', 2, 'bird'),
        (3, 'Sneaky', 5, 'snake'),
        (4, 'Red', 2, 'bird'),
        (5, 'Red', 3, 'horse'),
        (6, 'Buster', 1, 'dog'),
        (7, 'Fido', 7, '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()

The delete template. To run the delete examples in this article, create a new file called delete.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', 'pets')

# Your code here

get_all_rows('New Data', 'pets')

Then, for each example exercise, replace the # Your code here line with the specific example code.

The delete.py template includes the helper function get_all_rows(), which encapsulates a SELECT statement to verify that the deletes worked. The SELECT functionality is covered in Part 2 of this series.

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

Reset the data. Before you perform your first delete operation, run reset_data.py to set up the data.

Simple Delete

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

cur = con.cursor()
statement = 'delete from cx_pets where id = :id'
cur.execute(statement, {'id':1})
con.commit()

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

Original Data:
[(1, 'Duke', 1, 'dog'), (6, 'Buster', 1, 'dog'), (2, 'Dragon', 2, 'bird'), (4, 'Red',
2, 'bird'), (5, 'Red', 3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')]

New Data:
[(6, 'Buster', 1, 'dog'), (2, 'Dragon', 2, 'bird'), (4, 'Red', 2, 'bird'), (5, 'Red',
3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')]

Here’s what delete.py does:

  • Gets a cursor object from the connection. You will use this cursor to perform your database operations.
  • Prepares a SQL DELETE statement, deleting the cx_pets record with an id of 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 delete all the birds. When you’re done, the results should be

Original Data:
[(6, 'Buster', 1, 'dog'), (2, 'Dragon', 2, 'bird'), (4, 'Red', 2, 'bird'), (5, 'Red',
3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')]

New Data:
[(6, 'Buster', 1, 'dog'), (5, 'Red', 3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')]

What does the successful code look like? Like this:

cur = con.cursor()
statement = 'delete from cx_pets where type = :type'
cur.execute(statement, {'type':'bird'})
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 delete.py to get people and pet data. Replace the last three code lines of delete.py with

get_all_rows('Original People Data', 'people')
get_all_rows('Original Pet Data', 'pets')

# Your code here

get_all_rows('New People Data', 'people')
get_all_rows('New Pet Data', 'pets')

Deleting Records Referenced by Foreign Keys

If you are using integrity constraints in your database (of course you are, because you let the database do some heavy lifting for you), you will sometimes need to change the way you process your changes. In this article’s data design, a foreign key constraint in cx_pets ensures that if a pet has an owner, that owner exists. Here is the statement that creates this constraint in the “Create the Database Objects” section of Part 1 of this article series.

ALTER TABLE CX_PETS ADD CONSTRAINT FK_CX_PETS_OWNER FOREIGN KEY ("OWNER")
REFERENCES "CX_PEOPLE" ("ID")
/

If you attempt to delete a record in cx_people that is referenced in cx_pets (where a pet has that owner), you will get an error.

To see this, replace the # Your code here line in delete.py with the following code snippet and run delete.py in your Python session:

cur = con.cursor()
statement = 'delete from cx_people where id = :id'
cur.execute(statement, {'id':1})
con.commit()

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

Traceback (most recent call last):
 File "delete.py", line 25, in 
 cur.execute(statement, {'id':1})
cx_Oracle.IntegrityError: ORA-02292: integrity constraint (DD.FK_CX_PETS_OWNER)
violated - child record found

Before deleting the person, you must handle the pets (watch out for claws and teeth).

There are handling options, depending on the database design:

If pets are not required to have an owner. And if you want to delete only the person, not the pets, you can update the pets’ data and set the pet owner value to null.

If pets are required to have an owner. You can delete the pets of the owner.

In either of the above scenarios, you can update the data and set the pet owner to another person.

The delete is successful. Bob is moving out of the area, and his new apartment doesn’t allow pets, so he’s giving his dogs to Kim. Let’s use that last option here.

Replace the # Your code here line in delete.py with the following code snippet, and run delete.py in your Python session:

cur = con.cursor()

statement = 'update cx_pets set owner = :1 where owner = :2'
cur.execute(statement, (2, 1))

statement = 'delete from cx_people where id = :id'
cur.execute(statement, {'id':1})
con.commit()

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

Original People Data:
[(1, 'Bob', 35, 'I like dogs'), (2, 'Kim', 27, 'I like birds'), (3, 'Cheryl', 23,
'I like horses'), (4, 'Bob', 27, 'I like rabbits'), (5, 'Stacey', 45, 'I like snakes'), (6, 'Pete', 23, 'I like cats'), (7, 'Pat', 36, 'I like dogs')]

Original Pet Data:
[(1, 'Duke', 1, 'dog'), (6, 'Buster', 1, 'dog'), (2, 'Dragon', 2, 'bird'), (4, 'Red',
2, 'bird'), (5, 'Red', 3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')]

New People Data:
[(2, 'Kim', 27, 'I like birds'), (3, 'Cheryl', 23, 'I like horses'), (4, 'Bob', 27,
'I like rabbits'), (5, 'Stacey', 45, 'I like snakes'), (6, 'Pete', 23, 'I like cats'), (7, 'Pat', 36, 'I like dogs')]

New Pet Data:
[(1, 'Duke', 2, 'dog'), (2, 'Dragon', 2, 'bird'), (4, 'Red', 2, 'bird'), (6, 'Buster', 2, 'dog'), (5, 'Red', 3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')]

Here’s what delete.py does:

  • Gets a cursor object from the connection.
  • Prepares a SQL UPDATE statement, changing the owner to 2 (Kim) for the records with an owner of 1 (Bob). Updating is covered in Part 3 of this series.
  • Executes the statement, using bind variables.
  • Prepares a SQL DELETE statement, deleting records with an id of 1 (Bob).
  • Executes the statement, using bind variables.
  • Commits the transaction.

When you change data, it’s a good idea to verify the number of affected rows. This is covered in Part 2 of this series.

Another Deeper Dive

Due to a zoning change, snakes are no longer allowed in the area. Stacey has decided to move and take Sneaky (her snake) with her. Let’s fix the data to reflect that.

When you’re done, the results should be

Original People Data:
[(2, 'Kim', 27, 'I like birds'), (3, 'Cheryl', 23, 'I like horses'), (4, 'Bob', 27, 'I like rabbits'), (5, 'Stacey', 45, 'I like snakes'), (6, 'Pete', 23, 'I like cats'),
(7, 'Pat', 36, 'I like dogs')]

Original Pet Data:
[(1, 'Duke', 2, 'dog'), (2, 'Dragon', 2, 'bird'), (4, 'Red', 2, 'bird'), (6, 'Buster', 2, 'dog'), (5, 'Red', 3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')]

New People Data:
[(2, 'Kim', 27, 'I like birds'), (3, 'Cheryl', 23, 'I like horses'), (4, 'Bob',
27, 'I like rabbits'), (6, 'Pete', 23, 'I like cats'), (7, 'Pat', 36, 'I like dogs')]

New Pet Data:
[(1, 'Duke', 2, 'dog'), (2, 'Dragon', 2, 'bird'), (4, 'Red', 2, 'bird'), (6, 'Buster', 2, 'dog'), (5, 'Red', 3, 'horse'), (7, 'Fido', 7, 'cat')]

What does the successful code look like? Like this:

cur = con.cursor()

statement = 'delete from cx_pets where owner = :owner'
cur.execute(statement, {'owner':5})

statement = 'delete from cx_people where id = :id'
cur.execute(statement, {'id':5})
con.commit()

Some Other Things You Could Try

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

  • Change the database constraints to delete the child record or make it null on delete (a cascading delete). Delete a person, and let the database handle the child (pet) records.
  • Remove the people who don’t have any pets.

Next Steps

DOWNLOAD cx_Oracle.

GET this article’s code examples from GitHub.

READ the previous articles of this series.
Part 1.
Part 2.
Part 3.

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.