Using Python With Oracle Database 11g

Install Oracle Database 11.2

Install the Python 2.x and the cx_Oracle 5.0 extension

Extract these files to your $HOME location.

Open a terminal window and review the code contained in $HOME/connect.py

The cx_Oracle module is imported to provide the API for accessing the Oracle database. Many inbuilt and third party modules can be included in this way in Python scripts.

The connect() method is passed the username "pythonhol", the password "welcome" and the connection string. In this case, Oracle's Easy Connect connection string syntax is used. It consists of the IP of your machine and the DB service name "orcl".

The close() method closes the connection. Any connections not explicitly closed will be automatically released when the script ends.

In a command line terminal run:

python connect.py

If the connection succeeds, the version number is printed: An exception is thrown if the connection fails.

Indentation is used in Python to indicate the code structure. There are no statement terminators unlike many other languages and there are no begin/end keywords or braces to indicate blocks of code.

Open connect.py in an editor. Indent the print statement by two spaces ad save the file:

import cx_Oracle

con = cx_Oracle.connect('pythonhol/welcome@localhost/orcl')
  print con.version

con.close()

Run the script:

python connect.py

The number of spaces or tabs used for indentation is not important as long as it is used in a consistent way in each block. Here the Python interpreter is not expecting a new code block level after the connect() call so it warns about the different indentation.

In other cases such as in 'if' and loop blocks (shown later), take care that all statements in each block are equally indented.

If you are copying and pasting from this tutorial, check the pasted indentation is correct before running each example.

Python treats everything as an object. The "con" object has a "version" attribute, which is a string.

Change the script to use a "split" string method too:

import cx_Oracle

con = cx_Oracle.connect('pythonhol/welcome@localhost/orcl')
ver = con.version.split(".")
print ver

con.close()

Re-run the script in the command line terminal:

python connect.py

The output is a "list", which is a Python's name for an array.

Python Lists can be accessed via indexes.

Change connect.py to:

import cx_Oracle

con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')
ver = con.version.split(".")
print ver
print ver[0]
print ver[-1]
print ver[1:4]

con.close()

Re-run the script in the command line terminal:

python connect.py

Python Lists are zero based, so ver[0] prints the first element of the list. The last element of the list is ver[-1]. A list slice is created by ver[1:4]. This returns the elements starting at position 1 and up to, but not including, elements from position 4.

Python lists have methods and can also be manipulated with operators.

Change connect.py to:

import cx_Oracle

con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')
ver = con.version.split(".")
print ver
print ver.index("1")
ver.remove("2")
print ver

ver1 = ["11", "g"]
ver2 = ["R", "2"]
print ver1 + ver2	

con.close()

Re-run the script in the command line terminal:

python connect.py

The index("1") method returns the index of the "1" element, counting from zero. The remove("2") method deletes an element from the list. The "+" operator can be used to join two lists.

Other datatypes Python has are Dictionaries, which are associative arrays, and a type called a tuple, which is like a list but cannot be changed.

Loops can be used to iterate over lists.

Change connect.py to:

import cx_Oracle

con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')
ver = con.version.split(".")
for v in ver:
    print v
    if v == "11":
        print "It's 11"
    else:
        print "Not 11"

con.close()

Make sure the indentation is correct!

The colon ":" is used to indicate a code block. The first print and if are at the same level of indentation because they are both inside the loop.

Re-run the script in the command line terminal:

python connect.py

The loop prints and tests each value from the list in turn.

Review the code contained in $HOME/connect_drcp.py

This is similar to connect.py but ":pooled" is appended to the connection string. A Connection Class "HOL" is also passed into the connect() method and the "purity" of the connection is defined as the ATTR_PURITY_SELF constant.

The connection class tells the database server pool that connections are related. Session information (such as the default date format) might be retained between connection calls, giving performance benefits. Session information will be discarded if a pooled server is later reused by an application with a different connection class name.

Applications that should never share session information should use a different connection class and/or use ATTR_PURITY_NEW to force creation of a new session. This reduces overall scalability but prevents applications mis-using session information.

Run connect_drcp.py

python connect_drcp.py

The output is again simply the version of the database.

Script logic does not need to be changed to benefit from DRCP connection pooling.

Review the code contained in $HOME/query.py

The cursor() method opens a cursor for statements to use.

The execute() method parses and executes the statement.

The loop fetches each row from the cursor and prints it.

Run the script in a terminal window:

python query.py

The results of the query are displayed as Python 'tuples', which are arrays that cannot be changed

Review the code contained in $HOME/query_one.py

This uses the fetchone() method to return just a single row as a tuple. When called multiple time, consecutive rows are returned:

Run the script in a terminal window:

python query_one.py

The two fetchone() calls print two records.

Review the code contained in $HOME/query_many.py

The fetchmany() method returns a list of tuples. Here the numRows parameter specifices that three rows should be returned.

Run the script in a terminal window:

python query_many.py

The first three rows of the table are returned as a list of tuples.

Review the code contained in $HOME/query_all.py

Run the script in a terminal window:

python query_all.py

This uses the fetchall() method to return all rows. The output is a list (Python's name for an array) of tuples. Each tuple contains the data for one row.

The list can be manipulated in the any Python manner. Edit $HOME/query_all.py and change the code to the following (in bold) and rerun the script again

import cx_Oracle

con = cx_Oracle.connect('pythonhol/welcome@localhost/orcl')

cur = con.cursor()
cur.execute('select * from departments order by department_id')
res = cur.fetchall()
for r in res:
    print r

cur.close()
con.close()

Now each tuple is printed separately. The choice of which fetch method to use will depend on how you want to process the returned data.

First, create a table with a large number of rows. Review $HOME/query_arraysize.sql

In a terminal window use SQL*Plus to run the script:

sqlplus pythonhol/welcome@localhost/orcl
@query_arraysize
exit

Review the codecontained in $HOME/query_arraysize.py

This uses the 'time' module to measure elapsed time of the query. The arraysize is set to 100. This causes batches of 100 records at a time to be returned from the database to a cache in Python. This reduces the number of "roundtrips" made to the database, often reducing network load and reducing the number of context switches on the database server. The fetchone(), fetchmany() and even fetchall() methods will read from the cache before requesting more data from the database.

From a terminal window, run:

python query_arraysize.py

Reload a few times to see the average times.

Note: The time values you observe may differ from those displayed in the screenshots because of hardware or system load differences.

Edit $HOME/query_arraysize.py and change the arraysize from

cur.arraysize = 100

to

cur.arraysize = 2000

Rerun the script a few times and compare the performance of the two arraysize settings. In general, larger array sizes improve performance. Depending how fast your system is, you may need to use different arraysizes than those given here to see a meaningful time difference.

python query_arraysize.py 
    

The default arraysize used by cx_Oracle is 50. There is a time/space tradeoff for increasing the arraysize. Larger arraysizes will require more memory in Python for buffering the records.

Note: The time values you observe may differ from those displayed in the screenshot because of hardware or system load differences.

Review the code contained in $HOME/bind_query.py

The statement contains a bind variable ":id". The statement is only prepared once but executed twice with different values for the WHERE clause.

The special symbol 'None' is used in place of the statement text argument to execute() because the prepare() method has already set the statement. The second argument to the execute() call is a Python Dictionary. In the first execute call, this associative array has the value 210 for the key of "id".

The first execute uses the value 210 for the query. The second execute uses the value 110.

From a terminal window, run:

python bind_query.py

The output shows the details for the two departments.

The cx_Oracle driver supports array binds for INSERT statements, which can greatly improve performance over single row inserts.

Review the following commands to create a table for inserting data:

sqlplus pythonhol/welcome@localhost/orcl
drop table mytab;
create table mytab (id number, data varchar2(20));
exit

Run SQL*Plus and cut-and-paste the commands.

Review the code contained in $HOME/bind_insert.py

The 'rows' array contains the data to be inserted.

The bindarraysize is here set to 7, meaning to insert all seven rows in one step. The setinputsizes() call describes the columns. The first column is integral. The second column has a maximum of 20 bytes.

The executemany() call inserts all seven rows.

The commit call is commented out, and does not execute.

The final part of the script queries the results back and displays them as a list of tuples.

From a terminal window, run:

python bind_insert.py

The new results are automatically rolled back at the end of the script so re-running the script will always show the same number of rows in the table.

Edit $HOME/bind_insert.py and uncomment the commit call (in bold below):

import cx_Oracle

con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')

rows = [ (1, "First" ),
         (2, "Second" ),
         (3, "Third" ),
         (4, "Fourth" ),
         (5, "Fifth" ),
         (6, "Sixth" ),
         (7, "Seventh" ) ]

cur = con.cursor()
cur.bindarraysize = 7
cur.setinputsizes(int, 20)
cur.executemany("insert into mytab(id, data) values (:1, :2)", rows)

con.commit()

# Now query the results back

cur2 = con.cursor()
cur2.execute('select * from mytab')
res = cur2.fetchall()
print res

cur.close()
cur2.close()
con.close()

The commit() is on the connection, not on the cursor.

Rerun the script several times and see the number of rows in the table increasing each time:

python bind_insert.py

If you need to initiate a rollback in a script, the con.rollback() method can be used.

In general you want all or none of your data committed. Doing your own transaction control has performance and data-integrity benefits.

Start SQL*Plus and create a new table, ptab with the following command:

sqlplus pythonhol/welcome@localhost/orcl
create table ptab (mydata varchar(20), myid number);
exit

Review $HOME/create_func.sql which creates a PL/SQL stored function myfunc() to insert a row into the ptab table, and return double the inserted value:

Start SQL*Plus and run the script:

sqlplus pythonhol/welcome@localhost/orcl
set echo on
@create_func
show errors
exit

Review the code in $HOME/plsql_func.py

This uses callfunc() to execute the function. The constant cx_oracle.NUMBER indicates that the return value is numeric. The two PL/SQL function parameters are passed as a tuple and bound to the function parameter arguments.

From a terminal window, run:

python plsql_func.py

The output is result of the PL/SQL function calculation.

To call a PL/SQL procedure, use the cur.callproc() method.

Review $HOME/create_proc.sql which creates a PL/SQL procedure myproc() to accept two parameters. The second parameter contains an OUT return value.

Start SQL*Plus and run the script::

sqlplus pythonhol/welcome@localhost/orcl
set echo on
@create_proc
show errors
exit

Review the code contained in $HOME/plsql_proc.py

This creates a numeric variable myvar to hold the OUT parameter. The number 123 and the return variable name are bound to the procedure call parameters using a tuple.

From a terminal window, run:

python plsql_proc.py

The getvalue() method displays the returned value.

Review the code contained in $HOME/dcn.py

This script creates a function called DCNCallback(). This function will be called when a table changes. The 'message' parameter is a cx_Oracle object that will contain information about the changes. The function simply prints out the kinds of changes that have occurred and the affected rowids.

The main body of the script is below the function - note the indentation level of 'con = ...' is the same as for 'def ...'. The body creates the database connection with the 'events = True' parameter to allow the database to send an event notification to Python when a table change takes place.

The subscribe() call registers the DCNCallback() to be called in a new thread when an INSERT or UPDATE occurs. Only one Python thread can run at any given time. Python switches between threads as needed. The rowids = True parameter lets rowids be accessed in the callback.

The registerquery() call registers a query that selects everything from the MYTAB table. Any change to the table that is an UPDATE or DELETE will cause DCNCallback() to be called. The MYTAB table was created in a previous section of the lab.

The script concludes with a raw_input() call that waits for user input before terminating.

To run the DCN example, open two terminal windows. In the first window run:

python dcn.py

This will print a message and wait without returning a prompt:

Leave it running for the moment and continue with the next step.

In a second terminal window, enter the following commands

sqlplus pythonhol/welcome@localhost/orcl
insert into mytab (id) values (11);
commit;

When the commit occurs, the Python script (switch to your original terminal window) will receive notification and print the change notification message:

Note: The rowid will be different on your system.

Switch to your SQL*Plus terminal window and delete the new row by executing the following SQL commands:

delete from mytab where id = 11;
commit;

The new notification is printed and the Python terminal now looks like:

Note: The rowids will be different on your system.

Experiment with several operations. Try doing an INSERT followed by a DELETE before committing. This shows notification is received of each individual operation.

When you are finished, hit Enter to conclude the demo in the Python window to exit the script.

Extend dcn.py to also notify when an UPDATE occurs on MYTAB.

In the subscribe() call, change the operations parameter to

operations = cx_Oracle.OPCODE_INSERT | cx_Oracle.OPCODE_DELETE 
             | cx_Oracle.OPCODE_UPDATE,

Add a new 'if' test to the DCNCallback function:

if row.operation & cx_Oracle.OPCODE_UPDATE: 
       print "UPDATE of rowid:", row.rowid

The dcn.py script should now look as follows (the changes are in bold):

import cx_Oracle 


def DCNCallback(message): 
    print "Notification:" 
    for tab in message.tables: 
        print "Table:", tab.name
        for row in tab.rows: 
            if row.operation & cx_Oracle.OPCODE_INSERT: 
                print "INSERT of rowid:", row.rowid
            if row.operation & cx_Oracle.OPCODE_DELETE: 
                print "DELETE of rowid:", row.rowid
            if row.operation & cx_Oracle.OPCODE_UPDATE: 
                print "UPDATE of rowid:", row.rowid

con = cx_Oracle.Connection("pythonhol/welcome@localhost/orcl",
                           events = True) 
subscriptionInsDel = con.subscribe(callback = DCNCallback, 
       operations = cx_Oracle.OPCODE_INSERT | cx_Oracle.OPCODE_DELETE 
                    | cx_Oracle.OPCODE_UPDATE, 
       rowids = True) 
subscriptionInsDel.registerquery('select * from mytab') 

raw_input("Hit Enter to conclude this demo\n") 

In the Python terminal, restart the script:

In the SQL*Plus terminal, create a row and update it:

insert into mytab (id) values (11);
update mytab set id = 12 where id = 11;
commit;

The new message should be displayed.

Note: The rowids will be different on your system.

When you are finished, hit Enter to conclude this demo in the Python window to exit the script.

Database Change Notification is an efficient way to monitor table changes. It can also be used to notify when any of a subset of rows selected by a given query are changed.