www.oracle.com/technetwork/cn/server-storage/linux/python-091105.html

Connecting to Oracle

import cx_Oraclecon = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')print con.versioncon.close()

Using Database Resident Connection Pooling

Below left is diagram of nonpooling. Every script has its own database server proces. Scripts not doing any database work still hold onto a connection until the connection is closed and the server is terminated. Below right is a diagram with DRCP. Scripts can use database servers from a pool of servers and return them when no longer needed.

con = cx_Oracle.connect('pythonhol', 'welcome', '127.0.0.1:/orcl:pooled',             cclass = "HOL", purity = cx_Oracle.ATTR_PURITY_SELF)

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.

Creating a Simple Query

cur = con.cursor()cur.execute('select * from departments order by department_id')for result in cur:    print resultcur.close()

The cursor() method opens a cursor for statements to use. The execute() method parses and executes the statement.

Fetching Data

row = cur.fetchone()print rowrow = cur.fetchone()print row

This uses the fetchone() method to return just a single row as a tuple.

res = cur.fetchmany(numRows=3)

Here the numRows parameter specifices that three rows should be returned.

res = cur.fetchall()

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

Improve Query Performance

import time

import cx_Oraclecon = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')start = time.time()cur = con.cursor()cur.arraysize = 100cur.execute('select * from bigtab')  #table bigtab is a table with a large number of rowsres = cur.fetchall()# print res  # uncomment to display the query resultselapsed = (time.time() - start)print elapsed, " seconds"cur.close()con.close()

The default arraysize used by cx_Oracle is 50. In general, larger array sizes improve performance.

Using Bind Variables

cur.prepare('select * from departments where department_id = :id')cur.execute(None, {'id': 210})cur.execute(None, {'id': 110})

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 statement contains a bind variable ":id".

rows = [ (1, "First" ), (2, "Second" ), (3, "Third" ),(4, "Fourth" ), (5, "Fifth" ), (6, "Sixth" ),(7, "Seventh" ) ]cur.bindarraysize = 7cur.setinputsizes(int, 20)cur.executemany("insert into mytab(id, data) values (:1, :2)", rows)#con.commit()

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.

Creating Transactions

con.commit()con.close()

The commit() is on the connection. When the changed data is committed to the database, it is then available to other users and sessions.

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

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

Using PL/SQL Stored Functions and Procedures

set echo oncreate or replace functionmyfunc(d_p in varchar2, i_p in number) return number asbegin  insert into ptab (mydata, myid) values (d_p, i_p);  return (i_p * 2);end;

creates a PL/SQL stored function myfunc() to insert a row into the ptab table, and return double the inserted value.

res = cur.callfunc('myfunc', cx_Oracle.NUMBER, ('abc', 2))

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.

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

set echo oncreate or replace proceduremyproc(v1_p in number, v2_p out number) asbegin   v2_p := v1_p * 2;end;

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.

myvar = cur.var(cx_Oracle.NUMBER)cur.callproc('myproc', (123, myvar))print myvar.getvalue()

Continuous Query Notification

import cx_Oracledef 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

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.

con = cx_Oracle.Connection("pythonhol/welcome@127.0.0.1/orcl",events = True)subscriptionInsDel = con.subscribe(callback = DCNCallback,             operations = cx_Oracle.OPCODE_INSERT | cx_Oracle.OPCODE_DELETE,rowids = True)subscriptionInsDel.registerquery('select * from mytab')raw_input("Hit Enter to conclude this demo\n")

The subscribe() call registers the DCNCallback() to be called in a new thread when an INSERT or 

UPDATE occurs. 

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 script concludes with a raw_input() call that waits for user input before terminating.