In this post we’re going to take a look at the U in CRUD: Update.
We use the cx_Oracle driver to update 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 updates 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 then run it whenever you would like to reset the data. (Notice this version adds 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 |
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 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() |
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') # Your code here get_all_rows('New Data') |
For each exercise, replace the “# Your code here” line with your code.
Simple update
We will perform a simple update that modifies 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 UPDATE statement, changing age to 31 for the 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 = 'update cx_people set age = :1 where id = :2' cur.execute(statement, (31, 1)) con.commit() |
|
1 2 3 4 5 |
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')] |
Extra Fun 1
Update Bob’s notes to ‘I like cats’ .
Your results should be:
|
1 2 3 4 5 |
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')] |
Reset the data
Now is a good time to run reset_data.py.
Boilerplate change
Change the boilerplate get_all_rows statements to get pet data.
|
1 2 3 4 5 |
get_all_rows('Original Data', 'pets') # Your code here get_all_rows('New Data', 'pets') |
Make sure your where clause is specific
In the above example, notice that we used the id column in our where clause. For our data set, id is the primary key. You do not always have to use a primary key, but you should make sure you only update the rows you intend to.
Next let’s look at updating multiple rows. We’ll have Bob give his dog Duke to Kim.
- 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) and a type of ‘dog’.
- 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 = 'update cx_pets set owner = :1 where owner = :2 and type = :3' cur.execute(statement, (2, 1, 'dog')) con.commit() |
|
1 2 3 4 5 |
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>] |
In our data, the only unique identifier for cx_pets is id. Bob may have two dogs, or even two dogs named Duke. Make sure if you intend to change a specific row you use a unique identifier.
It also helps to…
Verify the number of affected rows
Now lets give Buster back to Bob. This time we will use the unique id column and we will print out the number of rows affected using Cursor.rowcount.
- 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 1 (Bob) for the records with an id of 6 (Buster).
- 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 |
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(' ') |
|
1 2 3 4 5 6 7 |
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')] |
Extra Fun 2
Give all birds to Kim that she doesn’t already have and print the number of affected rows .
Your results should be:
|
1 2 3 4 5 6 7 |
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'), <strong>(4, 'Polly', 2, 'bird')</strong>] |
Some other things you could try
- Change multiple column values
- Preform an update that changes all rows, if the rowcount is greater than 2, rollback the change
Series sections
Initial Setup
Create records
Retrieve records
Update records
Delete records
Very nice!
Thanks, feel free to point out any corrections I need to make.
cur.rowcount is not working as expected. it always gives -1
I have not been able to reproduce cur.rowcount returning a -1.
Could you please post your code and the version of cx_oracle you’re using?
Also, you can look at https://github.com/oracle/oracle-db-examples/tree/master/python/CRUD-examples I have uploaded all of the examples from this CRUD series and verified that they all work “on my machine.”
Thank U
Very helpful.