Basic CRUD Operations Using cx_Oracle, Part 4: Update

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.

WARNING: PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

We will be using a helper function get_all_rows().  This function encapsulates a select statement used to verify that the updates worked. The select functionality is covered in the R part of this series, so we won’t go into the details here.

Sample :

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, we 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.)

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:

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.

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'), (6, 'Buster', 1, 'dog'),
 (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird')]

Cursor.rowcount will show you the number of rows affected for insert, update and delete statements and the number of rows returned in a select statement. 

Extra Fun 1 

Update Bob’s notes to ‘I like cats’ . Your 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')]

Answer

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 

Change the boilerplate get_all_rows statements to get pet data.

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.

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'),
 (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird'), (6, 'Buster', 2, 'dog')]

In our example we only used owner and type; assuming that Bob only had one dog, Duke, as it is in our original data. With the new reset data function, we added a second dog, Buster. This example is intended to demonstrate what may happen when multiple users are working with the same data set. 

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 let's 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.

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'), (6, 'Buster', 1, 'dog'),
 (7, 'Fido', 1, 'cat'), (1, 'Duke', 2, 'dog'), (2, 'Pepe', 2, 'bird')]

Cursor.rowcount will show you the number of rows affected for insert, update and delete statements and the number of rows returned in a select statement. 

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:

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')]

Answer

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 could try 

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

Read more...

Bind Variable SQL Server with Dot Net

If you've been developing applications on SQL Server for while, you've no doubt come across the concept of  "Bind Variable". Bind variables are one of those Dot Net concepts that experts frequently cite as being key to application performance, but it's often not all that easy to pin down exactly what they are and you need to alter your programming style to use them.

When you use SQL to communicate data between your web application and a database, you have the option to include the literal data in an SQL statement or use bind variables. Bind variables are placeholders for actual values in SQL statements. Writing SQL statements with bind variables rather than substitution variables or literals minimizes processing time and can improve application performance by 20 to 30 percent. Using bind variables can also help prevent an SQL injection attack.
This article compares the performance and security benefits of using bind variables (also known as bind parameters or dynamic parameters) rather than substitution variables or literals in SQL statements. I briefly introduce bind variables, then demonstrate how they're used in SQL statements and show the resulting performance improvement. I also show you how to use bind variables to effectively deter an SQL injection attack in a sample Java application.

Overview of bind variables
A bind variable consists of a variable indicated by a placeholder character such as a question mark (?), :name, or @name. The placeholder character depends on the SQL database server that you use. You provide the actual value of the placeholder at runtime, just before the SQL statement is executed.

How bind variables improve application performance
In most relational databases, an SQL statement is processed in three steps:

1. Parsing the SQL statement: Verifies the SQL statement syntax and access rights and builds the best (optimized) execution plan for the SQL statement. Placeholder variables are not known at this point.
2. Binding variables: Where the API provides the actual values for placeholders.
3. Execution: Done with the selected execution plan and actual value of the placeholder variables.

Each time an SQL statement is sent to a database, an exact text match is performed to see if the statement is already present in the shared pool. If no matching statement is found, the database performs a hard parse of that statement. If a matching statement is found, then the database initiates a soft parse.

• In a hard parse, the SQL statement needs to be parsed, checked for syntax errors, checked for correctness of table names and column names, and optimized to find the best execution plan.
• In a soft parse, the SQL statement already exists in a shared pool, so very little processing is required for access rights and session verification.

Using bind variables enables soft parsing, which means that less processing time is spent on choosing an optimized execution plan. Information about how to process the SQL statement has been saved, along with the SQL statement itself, in a shared pool.

Sample :

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2008R2.HumanResources.Employee 
       WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;

Read more...