This article continues my Using Python to Access Oracle, Part 1 article. It assumes that you have basic skills writing Python programs. It also assumes you read the earlier article, installed and configured the cx_Oracle library, and worked through the basic connection and static and dynamic cursors.

This article covers

  • How you write a query with keyword pairs or sequence parameters
  • How you write a query with dynamic parameters using a dictionary
  • How you write insert, update, and delete statements with parameters

This article takes a few moments to show you some best practices for embedding and dispatching your embedded SQL statements. You should take note of the tricks and techniques because they may save you a lot of time later.

As with the prior article, this article uses Python 2.7 because it appears to be the primary commercial version of Python in most organizations. At least, it’s still what most vendors ship with Linux distros.

All the sample programs are available online. They’re designed to be called as an argument of the python command, or from the Python IDLE environment. You can add the following to the first line of the programs to call them from the operating system.

#!/usr/bin/python

If you want to test these from the command line, you need to do two things. You need to create a $PYTHONPATH environment variable. You also need to ensure any Python libraries or programs are one of the paths defined by the $PYTHONPATH environment variable.

Part 1 shows you how to query data with a static string and a dynamic string. However, it shows you only the basics, like how to query a string literal or how to query with a single parameter value. The single parameter approach uses a name and value pair, which is properly known as a sequence.

This section shows you how to query data with multiple bind variables. As introduced in Part 1, a bind variable may occur anywhere in the string and it is prefaced with a colon. The position and name of the bind variable are important.

There are two approaches available with multiple bind variables. One uses a sequence and the other uses a dictionary. The sequence approach works best when you know in advance the number of parameters. The dictionary approach is the required solution when you don’t know the number of parameters in advance.

How you write a query with keyword pairs or sequence parameters

This section shows you how to use a sequence with a known set of two parameters. It introduces several new elements. Two of the new elements should be familiar to experienced Python programmers, they’re line continuation and regular expressions. There are also two new elements that aren’t really Python related.

The two new elements deal with appending a line return to each line of the SQL statement. The line return approach makes it much easier to site read a SQL statement, which is the obvious part. The less obvious purpose behind using a line return is that it guarantees statement parsing. For example, you need to insert a whitespace between:

  • The last column in a SELECT-list and the FROM keyword
  • The last element of the FROM clause and the WHERE keyword
  • The last element of every comparison in the WHERE clause and an AND, OR, GROUP BY, HAVING, or ORDER BY keyword.

If you fail to insert a whitespace or line return, SQL statements fail to parse when they’re dispatched to the Oracle database. Such statements raise errors that you may find hard to qualify. Likewise, you may also find it harder to site read code for a trailing whitespace than you would for a line return. At least, if you follow my formatting example below where the line returns are aligned to make site reading easier.

The basicLookup.py program also introduces the re, regular expression, library. You use the re library to ensure best practices when you precondition your query before submitting it to the Oracle database.

# Import the Oracle library.
import cx_Oracle
import re
import sys

# Define a list.
dvd = ('DVD_FULL_SCREEN','DVD_WIDE_SCREEN')

try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")

  # Create a cursor.
  cursor = db.cursor()

  # Define a dyanmic query.
  stmt = "SELECT common_lookup_id"                   + "\n" + \
         "FROM   common_lookup"                      + "\n" + \
         "WHERE  common_lookup_table = 'ITEM'"       + "\n" + \
         "AND    common_lookup_column = 'ITEM_TYPE'" + "\n" + \
         "AND    common_lookup_type IN (:x,:y)"

  # Parse the statement by replacing line returns with a single
  # whitespace, replacing multiple whitespaces with single spaces.
  stmt = re.sub('\s+',' ',stmt.replace('\n',' ').replace('\r',''))

  # Declare a dynamic statement with a sequence.
  cursor.execute(stmt, x = dvd[0], y = dvd[1])

  # Read the contents of the cursor.
  for row in cursor:
    print (row[0])
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection.
  cursor.close()
  db.close()

The dvd list holds the parameters for the query. The two parameter values are the DVD_FULL_SCREEN and DVD_WIDE_SCREEN string values. You pass them into the :x and :y bind variables inside the query.

You spent some effort to render the SQL query into a readable format. That effort was to simplify your life as a programmer. The next step simplifies how Oracle processes the query.

You should adopt a format so it becomes more likely that other queries match the same character stream. That’s because when multiple queries match the same character stream they decreases the parsing load on the Oracle database server. It also means the character streams don’t age out as quickly as unique character streams.

The :x and :y bind variables are placeholders. As placeholders, the runtime values don’t make the query’s character stream different.  The query with the placeholders stays in the memory cache more frequently than queries without bind variables.

A guideline removes line returns and multiple whitespaces, and it replaces them with single whitespaces. You use replace string functions and the re library to optimize the query with placeholders.

You replace the line returns with a single whitespace, and any line feeds with a whitespace. (Line feeds typically occur only on the Windows platform.) After replacing the characters, you use the re library to substitute a single whitespace for any instances of multiple whitespaces.

The call to the execute function passes a sequence of:

x = dvd[0]
y = dvd[1]

The execute function assigns the dvd[0] element of the list to the :x bind variable and the dvd[1] element of the list to the :y bind variable.

You call the query like this:

python basicLookup.py

It returns the possible primary key for two values from the common_lookup table, like

1013
1014

You should now see how to work with a set of known inputs to query. The same logic applies for sequences to INSERT, UPDATE, or DELETE statements. 

You need to standardize the query before you submit the to the Oracle engine. Rather than use parameterize functions, the example programs assume inputs to the query come from embedded lists. The basicLookup.py program uses the dvd list defined at the top of the program.

How you write a query with dynamic parameters using a dictionary

The prior example works when you know how many input values exist. It doesn’t work when you don’t know the number of inputs in advance. You use a dictionary assignment when the number of inputs can change.

Like the earlier example, the next program uses a list of values instead of a function with parameters. The basicDynamicLookup.py program shows you how to manage a variable list of input values to a query.

The program reuses the techniques of managing the layout of the query. It also reuses the best practice for standardizing the query structure before submission to the SQL statement engine.

The following basicDynamicLookup.py program shows you how to structure the program.

# Import the Oracle library.
import cx_Oracle
import re
import sys

# Define an alphabetic indexing tuple.
ind = tuple('abcdefghijklmnopqrstuvwxyz')

# Define a parameter list and empty target list.
typ = ('DVD_FULL_SCREEN','DVD_WIDE_SCREEN','BLU-RAY')
mat = {}

try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")

  # Create a cursor.
  cursor = db.cursor()

  # Define a dynamic query.
  stmt = "SELECT common_lookup_id"                   + "\n" + \
         ",      common_lookup_type"                 + "\n" + \
         ",      common_lookup_meaning"              + "\n" + \
         "FROM   common_lookup"                      + "\n" + \
         "WHERE  common_lookup_table = 'ITEM'"       + "\n" + \
         "AND    common_lookup_column = 'ITEM_TYPE'" + "\n" + \
         "AND    common_lookup_type IN ("

  # Build dictionary and append dynamic bind list to statement.
  for j, e in enumerate(typ):
    mat[str(ind[j])] = typ[j]
    if j == len(typ) - 1:
      stmt = stmt + ":" + str(ind[j])
    else:
      stmt = stmt + ":" + str(ind[j]) + ", "

  # Close lookup value set.
  stmt =  stmt + ")" + "\n" \
         "ORDER BY 1"

  # Parse the statement by replacing line returns with a single
  # whitespace, replacing multiple whitespaces with single spaces.
  stmt = re.sub('\s+',' ',stmt.replace('\n',' ').replace('\r',''))

  # Declare a dynamic statement.
  cursor.execute(stmt, mat)

  # Read the contents of the cursor.
  for row in cursor:
    print (row)
 
except cx_Oracle.DatabaseError, e:
  error, = e.args|  print >> sys.stderr, "Oracle-Error-Code:", error.code
print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection.
  cursor.close()
  db.close()

Writing a list of dynamic bind variables inside a query requires you to know a trick. Like magic tricks, this trick has a couple steps. You need the trick because bind variables can’t be numeric. They must start with an alphabetic value.

You find the number of dynamic bind variables by reading the size of the typ list. You need to map the variable index to an alphanumeric value, which is why the program creates the ind tuple and empty mat tuple. If you’re new to Python, please take note that ordinary parentheses enclose a populated list and curly braces enclose an empty list.

The dynamic list of parameters is put inside a SQL lookup list. At the same time, the values from the typ list are stored in the mat dictionary. The program uses a for-loop with enumeration to read through and translate numeric indexes to alphabetic bind variables and add the values to the mat dictionary. After reading the list of three possible values, the program appends a closing parenthesis and an ORDER BY clause.

If you were to print the contents of the completed statement, it would display:

SELECT common_lookup_id
,      common_lookup_type
,      common_lookup_meaning
FROM   common_lookup
WHERE  common_lookup_table = 'ITEM'
AND    common_lookup_column = 'ITEM_TYPE'
AND    common_lookup_type IN (:a, :b, :c)
ORDER BY 1

The call to the execute method of the cursor passes the dynamic statement and dictionary rather than a sequence of name and value pairs. It simplifies the call mechanic and enables programmers to build more flexible code.

If you want to output a comma-separated values (CSV) file, you would replace the:

  for row in cursor:
    print (row)

from the preceding example with the following:

  # Fetch one row at a time.
  for row in cursor:
    # A variable to hold a row.
    data = ''

    # Set fresh counter for each row.
    count = 0

    # Read the number of columns.
    for j in range(len(row)):
      # Declare a variable to hold a column value.
      datum = ''

      # Read valid column values.
      if str(row[count]) != 'None':
        datum = str(row[count])

      # Only add a column when it is not the last column.
      if count == len(row) - 1:
        data += datum
      else:
        data += datum + ','

      # Increment the column counter.
      count += 1

    # Print the CSV formatted row.
    print data

It then prints the output as a CSV file format, which you can then write to a file.

This section showed you how to work with dynamic queries and dictionary bind variables. It should establish how you can work with querying data and formatting it for CSV exports.

How you write insert, update, and delete statements with parameters

Beyond querying the data, you need to transact with data. Transactions involve data manipulation language commands, like the INSERT, UPDATE, and DELETE statements. This section will demonstrate how to insert, update, and delete data.

The examples use sequences for the bind variables but you may also use dictionaries like you did for the dynamic query. The example programs adopt the following conventions:

  • An “s” preceding the title case of the column name for local Python variables
  • A “b” preceding the title case of the column name for bind variables

Adopting these two conventions should avoid you having to troubleshoot ORA-1036 errors when there’s a conflict on an illegal name or number. It also helps you avoid ORA-1745 errors, which are thrown when you use an invalid host/bind variable name. (Typically, thrown when you use something like date for a bind variable.)

The example inserts a row, modifies the same row, and deletes the row. This organization lets you work with an insert, update, and delete statement in a natural approach.

The following insertItem.py program writes a row to the item table. It uses a sequence to populate the item_id column and an empty_clob() call to initialize a CLOB column in the table. The latter technique is required when the CLOB column is mandatory, or not null constrained.

# Import the Oracle library.
import cx_Oracle
import re
import sys

# Declare variables.
sBarcode = 'B01IS31U6S'
sType = 1014
sTitle = 'Star Trek Beyond'
sRating = 'PG-13'
sRatingAgency = 'MPAA'
sReleaseDate = '01-NOV-16'
sCreatedBy = 1
sCreationDate = '26-NOV-2016'
sLastUpdatedBy = 1
sLastUpdateDate = '26-NOV-2016'

try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")

  # Create a cursor.
  cursor = db.cursor()

  # Execute a query.
  stmt = "INSERT"               + "\n" + \
         "INTO   item"          + "\n" + \
         "( item_id"            + "\n" + \
         ", item_barcode"       + "\n" + \
         ", item_type"          + "\n" + \
         ", item_title"         + "\n" + \
         ", item_desc"          + "\n" + \
         ", item_rating"        + "\n" + \
         ", item_rating_agency" + "\n" + \
         ", item_release_date"  + "\n" + \
         ", created_by"         + "\n" + \
         ", creation_date"      + "\n" + \
         ", last_updated_by"    + "\n" + \
         ", last_update_date )" + "\n" + \
         "VALUES"               + "\n" + \
         "( item_s1.nextval"    + "\n" + \
         ", :bBarcode"          + "\n" + \
         ", :bType"             + "\n" + \
         ", :bTitle"            + "\n" + \
         ",  empty_clob()"      + "\n" + \
         ", :bRating"           + "\n" + \
         ", :bRatingAgency"     + "\n" + \
         ", :bReleaseDate"      + "\n" + \
         ", :bCreatedBy"        + "\n" + \
         ", :bCreationDate"     + "\n" + \
         ", :bLastUpdatedBy"    + "\n" + \
         ", :bLastUpdateDate )"

  # Parse the statement by replacing line returns with a single
  # whitespace, replacing multiple whitespaces with single spaces.
  stmt = re.sub('\s+',' ',stmt.replace('\n',' ').replace('\r',''))

  # Declare a dynamic statement.
  cursor.execute(stmt, bBarcode = sBarcode               \
                     , bType = sType                     \
                     , bTitle = sTitle                   \
                     , bRating = sRating                 \
                     , bRatingAgency = sRatingAgency     \
                     , bReleaseDate = sReleaseDate       \
                     , bCreatedBy = sCreatedBy           \
                     , bCreationDate = sCreationDate     \
                     , bLastUpdatedBy = sLastUpdatedBy   \
                     , bLastUpdateDate = sLastUpdateDate )

  # Commit the inserted value.
  db.commit()
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection.
  cursor.close()
  db.close()

You can’t assign a sequence through a bind variable, or it would be rendered as text. The same is true of a call to the empty_clob() or empty_blob() functions, or any other SQL built-in function. That’s because bind variables are substituted during runtime and embedded strings are parsed, and in the case of functions called and executed.

The execute() function call is multiple lines with sequences, which means you need to use the line continuation character. The commit() function is required to make the write permanent and visible to other sessions.

You can use the following query to confirm the insert of the row:

COL item_id       FORMAT 9999 HEADING "Item|ID #"
COL item_title    FORMAT A20  HEADING "Item Title"
COL item_subtitle FORMAT A20  HEADING "Item Subtitle"
COL item_rating   FORMAT A6   HEADING "Item|Rating"
SELECT   item_id
,        item_title
,        item_subtitle
,        item_rating
FROM     item
WHERE    item_title = 'Star Trek Beyond'; 

It should return the following row:

Item                                            Item
 ID # Item Title           Item Subtitle        Rating
----- -------------------- -------------------- ------
 1601 Star Trek Beyond                          PG-13

 The next updateItem.py program updates the row you just inserted for the Star Trek Beyond film. The program uses sequences to pass a new subtitle to the row and an item title to find the row to update.

# Import the Oracle library.
import cx_Oracle
import re
import sys

# Declare variables.
sItemTitle = 'Star Trek Beyond'
sItemSubtitle = 'Extended Edition'

try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")

  # Create a cursor.
  cursor = db.cursor()

  # Execute a query.
  stmt = "UPDATE item"                           + "\n" + \
         "SET    item_subtitle = :bItemSubtitle" + "\n" + \
         "WHERE  item_title = :bItemTitle"

  # Parse the statement by replacing line returns with a single
  # whitespace, replacing multiple whitespaces with single spaces.
  stmt = re.sub('\s+',' ',stmt.replace('\n',' ').replace('\r',''))

  # Declare a dynamic statement.
  cursor.execute(stmt, bItemTitle = sItemTitle       \
                     , bItemSubtitle = sItemSubtitle )

  # Commit the inserted value.
  db.commit()
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection.
  cursor.close()
  db.close()

The item title finds the correct row or rows, and the item subtitle updates the item_subtitle column value. You can rerun the confirmation query. It should now return the following:

Item                                            Item
 ID # Item Title           Item Subtitle        Rating
----- -------------------- -------------------- ------
 1601 Star Trek Beyond     Extended Edition     PG-13

The last deleteItem.py program deletes the row you have inserted and updated. It uses sequences, like the prior two examples. 

# Import the Oracle library.
import cx_Oracle
import re
import sys

# Declare variables.
sItemTitle = 'Star Trek Beyond'

try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")

  # Create a cursor.
  cursor = db.cursor()

  # Execute a query.
  stmt = "DELETE FROM item"                + "\n" + \
         "WHERE  item_title = :bItemTitle"

  # Parse the statement by replacing line returns with a single
  # whitespace, replacing multiple whitespaces with single spaces.
  stmt = re.sub('\s+',' ',stmt.replace('\n',' ').replace('\r',''))

  # Declare a dynamic statement.
  cursor.execute(stmt, bItemTitle = sItemTitle )

  # Commit the inserted value.
  db.commit()
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection.
  cursor.close()
  db.close()

Rerun the diagnostic query after the deleteItem.py program and you should see no rows returned. The insert, update, and delete statements have shown you how to work with transactions.

This article should have shown you how to effectively work static and dynamic queries. You can find the scripts for this part on the github.com server. You can continue reading about using the cx_Oracle library and Python in Part 3.