In this post we’re going to take a look at the D in CRUD: Delete.
We use the cx_Oracle driver to delete some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.
PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.
Helper Function
My helper function get_all_rows() encapsulates a select statement used to verify that the deletes worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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() |
Add this function to the top of your file.
Resetting the data
To keep the examples clean and precise, I will reset the data at times.
Create a new file called reset_data.py with the following code and run it whenever you would like to reset the data. (Notice this version adds people and pet data not included in other sections.)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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 Coulmn 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 Coulmn 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() |
Boilerplate template
The template we will be using is:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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') |
For each exercise, replace the “# Your code here” line with your code.
Reset the data
First let’s run reset_data.py to setup our data.
Simple delete
We will perform a simple delete that removes a single record in the cx_people table. These are the steps performed in the code snippet below.
- Get a cursor object from our connection. We will use this cursor to perform our database operations.
- Prepare a SQL DELETE statement, deleting the cx_pets record with an id of 1.
- Execute the statement using bind variables. (see the R part of this series for an explanation of bind variables)
- Commit the transaction.
|
1 2 3 4 |
cur = con.cursor() statement = 'delete from cx_pets where id = :id' cur.execute(statement, {'id':1}) con.commit() |
|
1 2 3 4 5 |
Original Data: [<del>(1, 'Duke', 1, 'dog')</del>, (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')] |
Extra Fun 1
Delete all the birds .
Your results should be:
|
1 2 3 4 5 |
Original Data: [(6, 'Buster', 1, 'dog'), <del>(2, 'Dragon', 2, 'bird')</del>, <del>(4, 'Red', 2, 'bird')</del>, (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')] |
Reset the data
Now is a good time to run reset_data.py.
Boilerplate change
Change the boilerplate get_all_rows statements to get people and pet data.
|
1 2 3 4 5 6 7 |
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 then you let the database do some heavy lifting for you), you will sometimes need to change the way you process your changes.
In our design, we have a Foreign Key constraint in cx_pets that ensures if a pet has an owner, that owner exists.
This is the statement that creates the constraint in the Creating the Database Objects section of the Initial Setup post.
|
1 2 |
ALTER TABLE CX_PETS ADD CONSTRAINT FK_CX_PETS_OWNER FOREIGN KEY ("OWNER") REFERENCES "CX_PEOPLE" ("ID") / |
If we attempt to delete a record in cx_people that is referenced in cx_pets (Person has a pet,) we get an error.
|
1 2 3 4 |
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:
|
1 2 3 4 5 |
Traceback (most recent call last): File "delete.py", line 25, in <module> 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 have to handle the pet (watch out for claws and teeth).
There are a few options here, depending on your database design:
- If: pets are not required to have an owner and you only want to delete the person, not the pets. Then: you can update the pets and set their owner to null.
- If: pets are required to have an owner. Then: you can delete the pets for the owner.
In either of the above scenarios you can update the pets and set their owner to another person.
Bob is moving out of our area and his new apartment doesn’t allow pets, so he’s giving them to Kim. Let’s use that last option here.
- Get a cursor object from our connection. We will use this cursor to perform our database operations.
- Prepare a SQL UPDATE statement, changing owner to 2 (Kim) for the records with an owner of 1 (Bob). Updating is covered in the U part of this series.
- Execute the statement using bind variables.
- Prepare a SQL DELETE statement, deleting records with an id of 1 (Bob).
- Execute the statement using bind variables. (see the R part of this series for an explanation of bind variables)
- Commit the transaction.
|
1 2 3 4 5 6 7 8 |
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() |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Original People Data: [<del>(1, 'Bob', 35, 'I like dogs')</del>, (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: [<strong>(1, 'Duke', 2, 'dog')</strong>, (2, 'Dragon', 2, 'bird'), (4, 'Red', 2, 'bird'), <strong>(6, 'Buster', 2, 'dog')</strong>, (5, 'Red', 3, 'horse'), (3, 'Sneaky', 5, 'snake'), (7, 'Fido', 7, 'cat')] |
Extra Fun 2
Due to a zoning change, snakes are no longer allowed in our area. Stacey has decided to move and take Sneaky with her.
Lets fix our data.
Your results should be:
|
1 2 3 4 5 6 7 8 9 10 11 |
Original People Data: [(2, 'Kim', 27, 'I like birds'), (3, 'Cheryl', 23, 'I like horses'), (4, 'Bob', 27, 'I like rabbits'), <del>(5, 'Stacey', 45, 'I like snakes'),</del> (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'), <del>(3, 'Sneaky', 5, 'snake'),</del> (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')] |
Some other things you could try
- Change the database constraints to delete or Null the child record on delete (a cascading delete). Delete a person and let the database handle the children.
- Remove the people who don’t have any pets.
Series sections
Initial Setup
Create records
Retrieve records
Update records
Delete records
You are being completely misleading by using the word prepare when you dont mean use the SQL “PREPARE” statement
I apologize if my wording is confusing, that’s just how I say it.
I prefer to store my SQL statement in a variable then use that variable instead of a string when I execute the query.
statement = 'delete from cx_pets where id = :id'cur.execute(statement, {'id':1})
If you prefer, you can put the string in the execute statement.
cur.execute('delete from cx_pets where id = :id', {'id':1})