Using Python With Oracle Database 11g

 

Purpose

This tutorial shows you how to use Python with Oracle Database 11g. 

For more tutorials, visit the following Oracle By Examples articles: 

Developing a Python Web Application with Oracle Database 11g
Using Python with Oracle Database 11g
 

Time to Complete

Approximately 1 hour

Overview

Python is a popular general purpose dynamic scripting language. With the rise of Frameworks, Python is also becoming common for Web application development. If you want to use Python and an Oracle database, this tutorial helps you get started by giving examples. If you are new to Python review the Appendix: Python Primer to gain an understanding of the language.

Prerequisites

For this Hands On Session, the following has already been installed for you:
  

  • Oracle Database 11gR2, with a user "pythonhol" and password (case sensitive) of "welcome". The example tables in this schema are from Oracle's Human Resources or "HR" schema.
  • Python 2.4 with the cx_Oracle 5.0.2 extension.
  • The Django 1.1 framework.
  • The files you use throughout the tutorial are located in the /home/pythonhol directory that you logged into.

Connecting to Oracle

To create a connection to Oracle, perform the following steps:

Review the code as follows, which is contained in the connect.py file in the $HOME directory.

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

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

connect-gif

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@127.0.0.1/orcl')
  
                
print con.version
con.close()

Run the script:

                 
python connect.py

connect2-gif

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 lab, 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@127.0.0.1/orcl')
                 
ver = con.version.split(".")
print ver
con.close()

Re-run the script in the command line terminal:

                 
python connect.py

connect-split-gif

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

connect-slice-gif

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

connect-meth-gif

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

connect-loop-gif

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

 

Using Database Resident Connection Pooling

Database Resident Connection Pooling is a new feature of Oracle Database 11g. It is useful for short lived scripts such as typically used by web applications. It allows the number of connections to be scaled as web site usage grows. It allows multiple Apache processes on multiple machines to share a small pool of database server processes. Without DRCP, a Python connection must start and terminate a server process.

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.

 

python-nopool-gif

 

python-pool-gif

Batch scripts doing long running jobs should generally use non-pooled connections.

This tutorial shows how DRCP can be used by new or existing applications without writing or changing any application logic. Perform the following steps:

Review the code as follows, which is contained in the connect_drcp.py file in the $HOME directory.

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

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

connect-drcp-gif

The output is again simply the version of the database.

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

 

Creating a Simple Query

A common task when developing Web applications is to query a database and display the results in a Web browser. There are a number of functions you can use to query an Oracle database, but the basics of querying are always the same:

1. Parse the statement for execution.
2. Bind data values (optional).
3. Execute the statement.
4. Fetch the results from the database.

To create a simple query, and display the results, perform the following steps.

Review the code as follows that is contained in the query.py file in the $HOME directory.

import cx_Oracle
con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')
cur = con.cursor()
cur.execute('select * from departments order by department_id')
for result in cur:
    print result
cur.close()
con.close()         

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

query-gif

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

 

Fetching Data

There are a number of ways to fetch data from an Oracle database. Perform the following steps.

Review the code as follows that is contained in the query_one.py file in the $HOME directory.

import cx_Oracle
con = cx_Oracle.connect('pythonhol/welcome@127.0.01/orcl')
cur = con.cursor()
cur.execute('select * from departments order by department_id')
               
row = cur.fetchone()
print row
row = cur.fetchone()
print row
cur.close()
con.close()
       

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

query-one-gif

The two fetchone() calls print two records.

 

Review the code as follows that is contained in the query_many.py file in the $HOME directory.

import cx_Oracle
con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')
cur = con.cursor()
cur.execute('select * from departments order by department_id')
               
res = cur.fetchmany(numRows=3)
print res
cur.close() con.close()

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

query-many-gif

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

Review the code as follows that is contained in the query_all.py file in the $HOME directory.

import cx_Oracle
con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')
cur = con.cursor()
cur.execute('select * from departments order by department_id')
               
res = cur.fetchall()
print res
cur.close()
con.close()

Run the script in a terminal window:

python query_all.py

query-all-gif

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 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@127.0.0.1/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()

query-all2-gif

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.

 

Improve Query Performance

This section demonstrates a way to improve query performance by increasing the number of rows returned in each batch from Oracle to the Python program. Perform the following steps:

First, create a table with a large number of rows. Review the following query_arraysize.sql script.

set echo on
              


drop table bigtab;
              


create table bigtab (mycol varchar2(20));
              


begin
              


  for i in 1..20000
              


  loop
              


   insert into bigtab (mycol) values (dbms_random.string('A',20));
              


  end loop;
              


end;
              


/
              


show errors
              


commit;
              


   
            

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

               
sqlplus pythonhol/welcome@127.0.0.1/orcl



@query_arraysize
exit

query-arraysize-gif

 

Review the code that is contained in the query_arraysize.py file in the $HOME directory.

import time
import cx_Oracle
con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')
start = time.time()
cur = con.cursor()
cur.arraysize = 100
cur.execute('select * from bigtab')
res = cur.fetchall()
# print res  # uncomment to display the query results
elapsed = (time.time() - start)
print elapsed, " seconds"
cur.close()
con.close()

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 networks 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

query-arraysize2-gif

Reload a few times to see the average times.

query-arraysize2-gif

 

Edit 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

query-arraysize4-gif

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.

 

Using Bind Variables

Bind variables enable you to re-execute statements with new values, without the overhead of reparsing the statement. Bind variables improve code reusability, and can reduce the risk of SQL Injection attacks.

To use bind variables in this example, perform the following steps.

.

.

.

Review the code as follows that is contained in the bind_query.py file in the $HOME directory.

import cx_Oracle
con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')
cur = con.cursor()
cur.prepare('select * from departments where department_id = :id')
cur.execute(None, {'id': 210})
res = cur.fetchall()
print res
cur.execute(None, {'id': 110})
res = cur.fetchall()
print res
cur.close()
con.close()       

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

bind-query-gif

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@127.0.0.1/orcl
drop table mytab;
              


create table mytab (id number, data varchar2(20));
exit
            

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

 

bind-insert-gif

 

Review the code as follows that is contained in the bind_insert.py file in the $HOME directory.

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

bind-insert2-gif

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.

 

Creating Transactions

When you manipulate data in an Oracle Database (insert, update, or delete data), the changed or new data is only available within your database session until it is committed to the database. When the changed data is committed to the database, it is then available to other users and sessions. This is a database transaction. Perform the following steps:

.

Edit the script used in the previous section 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

bind-insert3-gif

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.

 

 

Using PL/SQL Stored Functions and Procedures

PL/SQL is Oracle's procedural language extension to SQL. PL/SQL procedures and functions are stored and run in the database. Using PL/SQL lets all database applications reuse logic, no matter how the application accesses the database. Many data-related operations can be performed in PL/SQL faster than extracting the data into a program (for example, Python) and then processing it. Oracle also supports Java stored procedures.

In this tutorial, you will create a PL/SQL stored function and procedure and call them in Python scripts. Perform the following steps:

.

.

.

.

.

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

sqlplus pythonhol/welcome@127.0.0.1/orcl
              


create table ptab (mydata varchar(20), myid number);
exit

sp01-gif

 

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

set echo on
              


create or replace function
              


myfunc(d_p in varchar2, i_p in number) return number as
              


begin
              


  insert into ptab (mydata, myid) values (d_p, i_p);
              


  return (i_p * 2);
              


end;
              


/
              


show errors
            

Start SQL*Plus and run the script:

sqlplus pythonhol/welcome@127.0.0.1/orcl
@create_func
exit

 

sp02-gif

 

Review the code as follows that is contained in the plsql_func.py file in the $HOME directory.

import cx_Oracle
con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')
cur = con.cursor()
res = cur.callfunc('myfunc', cx_Oracle.NUMBER, ('abc', 2))
print res
cur.close()
con.close()               

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

plsql-func-gif

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

 

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

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

set echo on
              


create or replace procedure
              


myproc(v1_p in number, v2_p out number) as
              


begin
              


   v2_p := v1_p * 2;
              


end;
              


/
              


show errors
            

Start SQL*Plus and run the script::

sqlplus pythonhol/welcome@127.0.0.1/orcl
@create_proc
exit

sp03-gif

 

Review the code as follows that is contained in the plsql_proc.py file in the $HOME directory.

import cx_Oracle
con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl')
cur = con.cursor()
myvar = cur.var(cx_Oracle.NUMBER)
cur.callproc('myproc', (123, myvar))
print myvar.getvalue()
cur.close()
con.close()

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

sp04-gif

The getvalue() method displays the returned value.

 

Continuous Query Notification

Continuous Query Notification (also called Database Change Notification) allows applications to receive notifications when a table changes, for example when rows have been inserted. This can be useful in many circumstances, including for mid-tier cache invalidation. A cache might hold some values that depend on data in a table. If the table changes, the cached values must be updated with the new information.

This example show DCN events being handled in Python. Perform the following steps:

.

.

.

.

.

.

.

Review the code as follows that is contained in the dcn.py file in the $HOME directory.

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
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") 

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

dcn-gif

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@127.0.0.1/orcl
              


insert into mytab (id) values (11);
              


commit;

dcn2-gif

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

dcn3-gif

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;

dcn4-gif

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

dcn5-gif

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.

dcn6-gif

 

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@127.0.0.1/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:

dcn-gif

 

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;

dcn7-gif

The new message should be displayed.

dcn8-gif

 

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

dcn9-gif

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.

 

Using the Django Framework

The Django framework is one of several popular Python Frameworks for creating Python web applications.

This exercise creates a simple Django web application. First a simple report is shown.

 

.

.

To start, create a Django project.  From a terminal window, run:
           
  
              
django-admin.py startproject myproj

django01-gif

 

To create an application within this new project run:

               
cd myproj



python manage.py startapp myapp
ls -l
ls -l myapp

django02-gif

 

django03-gif

The myproj directory now contains a skeleton application:

__init__.py - makes the directory a Python package
__init__.pyc - compiled version of the above
manage.py - script for managing this application
myapp - directory for the new application's files
settings.py - configuration settings for the project
settings.pyc - compiled version of the above
urls.py - allows URLs to invoke Python methods

The myapp directory contains:

__init__.py - makes the directory a Python package
models.py - Python classes mapping to each table
tests.py - Used to construct a testsuite
views.py - Python code to generate web output

To allow the application to connect to the database, edit myproj/settings.py. Near the top of the file update the database connection parameters to read:

 DATABASE_ENGINE = '
              
oracle
' DATABASE_NAME = '
127.0.0.1/orcl
' DATABASE_USER = '
pythonhol
' DATABASE_PASSWORD = '
welcome
'

The DATABASE_HOST and DATABASE_PORT values can be left empty.

 

django04-gif

 

At the bottom of the file, add a line to INSTALLED_APPS to associate the application with the project:

 INSTALLED_APPS = (
           'django.contrib.auth',
           'django.contrib.contenttypes',
           'django.contrib.sessions',
           'django.contrib.sites',
            
              
'myproj.myapp'
)

 

django05-gif

Save the file and close the editor.

 

Run the skeleton application. In a terminal window execute the following command in the myproj directory:

python manage.py runserver

 

django06-gif

This starts a builtin development webserver on port 8000 and waits to handle web requests. The shell prompt is not returned. Leave it running for the moment.

 

Open a browser and enter the following URL:

http://127.0.0.1:8000/

 

django07-gif

The default Django application page is displayed. No database connection has been made at this stage.

 

To construct the application you first need to edit myproj/myapp/models.py and
add a new class. Edit the file and add the following to the end:

class locations(models.Model):
      location_id = models.IntegerField(primary_key = True)
      street_address = models.CharField(max_length = 40)
      postal_code = models.CharField(max_length = 12)
      city = models.CharField(max_length = 30)
      state_province = models.CharField(max_length = 25)
      country_id = models.CharField(max_length = 2)
      class Meta:
          db_table = "locations"

 

django08-gif

This creates an object-relational mapping for the LOCATIONS table. Each attribute of the class corresponds to a column existing in the table. The setting primary_key = True specifies that the location_id column is the primary key of the model. Character fields have their length specified.

 

In myproj/myapp/views.py you need to add a new function to query the model and generate the report output. Add the following to the end of the file:

from django.template import Context, loader
from django.http import HttpResponse
from myproj.myapp.models import locations
def index(request):
      location_list = locations.objects.all().order_by('location_id')
      tmpl = loader.get_template("index.html")
      cont = Context({'locations': location_list})
      return HttpResponse(tmpl.render(cont))

 

django09-gif

This imports the 'locations' model from models.py.

A query set ordered by LOCATION_ID is created. It is used to render the index.html template file. The resulting HTML page is passed back to Django for final display to the user.

 

The index.html template file needs to be created to print the query set when 'index' renders the model. Enter the following commands to create the templates directory.

cd myproj/myapp
mkdir templates
              


cd templates
            

 

Create the myproj/myapp/templates/index.html file using an editor. The contents are as follows:

<html>
 <head>
  <title>Office Locations</title>
  </head>
<body bgcolor="#ffffff">
              


<h1>Office Locations</h1>
<table border="1">
 <tr>
   <th>Location ID</th>
   <th>Street Address</th>
   <th>City</th>
 </tr>
 {% for loc in locations %}
 <tr>
   <td>{{loc.location_id}}</td>
   <td>{{loc.street_address}}</td>
   <td>{{loc.city}}</td>
 </tr>
 {% endfor %}
</table>
              


</body>
</html>
            

 

django10-gif

This uses Django templating syntax denoted by the '{%' and '%}' and the '{{' and '}}' pairs. The locations variable is the value set by the Context call in the view. The loop constructs one HTML table row per query-set row. Django pre-processes the index.html file and creates the final HTML sent to the browser.

 

Finally, you need to tell Django how to call our 'index' view function.

Edit myproj/urls.py and add the following 'url()' line at the end.

               
url(r'^myapp/', include('myproj.myapp.urls')),

 

django11-gif

The r'^myapp/' string is a regular expression. This causes URLs containing 'myapp/' be passed to the myproj/mypass/urls.py file for further processing. The regular expression says that 'myapp/' must be after the hostname of the URL like:

 http://.../myapp/

 

Create a new file myproj/myapp/urls.py to dispatch application URLs. Add the following code:

from django.conf.urls.defaults import *
from myapp.views import index
urlpatterns = patterns('',
           url(r'^$', index),
           )

 

django12-gif

This calls the 'index' function in views.py. Note the regular expression no longer contains 'myapp' since this was already matched at the project level and is not passed into this mapping file.

 

In your terminal window, use ^C to stop the webserver and restart it again with:

python manage.py runserver

++++image django14-gif==  where's django13-gif?==

django14-gif

 

In your browser load the URL of the application:

http://127.0.0.1:8000/myapp/

The application page shows the three desired fields of LOCATIONS table data:

 

django15-gif

The application overview is:

1. A browser page request for http://.../myapp invokes the Django webserver

2. Django’s URL dispatcher runs through the URL patterns in the myproj/urls.py file and chooses the first one that matches the URL. This in turn invokes the myproj/myapp/urls.py dispatcher. This then invokes the 'index' view, which is a Python callback function.

3. The view uses the data model to obtain database data. The 'index.html' template is then rendered with the LOCATIONS table data.

4. The 'index' view returns an HttpResponse object populated with the rendered template.

5. The HttpResponse object is rendered by Django to the browser.

The view used in this example was for querying data. Views can also create objects which are saved to the database. Using the locations model, code to insert a new object or row in a view could be:

    new_loc = locations(       
          location_id = 7000,
          street_address = "123 ABC",
          postal_code = "9999",
          city = "My City",
          state_province = "My State",
          country_id = "US")
    new_loc.save()

Deletion is done like:

    loc = locations.objects.get(location_id__exact=1000)
    loc.delete()

This also shows the syntax that generates a query matching a single record.

 

Using AJAX and Python

This section shows how AJAX techniques can be used to change part of an HTML page without reloading the whole page. The example changes the 'index' view so that when one record is clicked on, the full address of that location is shown. Perform the following steps:

 

In this first part, a new view that returns a complete address (including the country) for a given location will be created. You will begin by setting up the underlying model. Edit myproj/myapp/models.py:

Above the existing 'locations' model add a model for the COUNTRIES table:

class countries(models.Model):
    country_id = models.CharField(max_length = 2, primary_key = True)
    country_name = models.CharField(max_length = 40)
    region_id = models.IntegerField()
    class Meta:
        db_table = "countries"  

To make the relationship with the LOCATIONS table explicit, change the locations model and replace:

 country_id = models.CharField(max_length = 2)

with:

 country = models.ForeignKey(countries)
        

Note that it is not "country_id". Django automatically adds the "_id" suffix for a foreign key and uses COUNTRY_ID in the COUNTRIES and LOCATIONS tables as the join column.
 

ajax01-gif

The full models.py file (with changes in bold) is:

from django.db import models

# Create your models here.

               
class countries(models.Model):
country_id = models.CharField(max_length = 2, primary_key = True)
country_name = models.CharField(max_length = 40)
region_id = models.IntegerField()
class Meta:
db_table = "countries"
class locations(models.Model):
    location_id = models.IntegerField(primary_key = True)
    street_address = models.CharField(max_length = 40)
    postal_code = models.CharField(max_length = 12)
    city = models.CharField(max_length = 30)
    state_province = models.CharField(max_length = 25)
  
              
country = models.ForeignKey(countries)
class Meta: db_table = "locations"

 

Edit myproj/myapp/views.py and add a new view method 'address' to construct an address. The full file is:

# Create your views here.
from django.template import Context, loader
from django.http import HttpResponse
from myproj.myapp.models import locations
def index(request):
    location_list = locations.objects.all().order_by('location_id')
    tmpl = loader.get_template("index.html")
    cont = Context({'locations': location_list})
    return HttpResponse(tmpl.render(cont))
def address(request, lid):
    address_list = locations.objects.select_related().filter(location_id=lid)
    s = ""
    for loc in address_list:
        s = '[{"STREET_ADDRESS":"' + loc.street_address + \
                '","CITY":"' + loc.city + \
                '","POSTAL_CODE":"' + loc.postal_code + \
                '","COUNTRY":"' + loc.country.country_name + '"}]'
    return HttpResponse(s)

Note: Make sure that the indentation is exactly as shown above and in the screenshot below.

 

ajax02-gif

For the address view, the 'lid' parameter value will be passed from the URL that calls the view, as shown later.

With the 'select_related()' method, Django does an inner join between the LOCATIONS and COUNTRIES tables and retrieves all results for the given location identifier into the address_list QuerySet object. Django also binds the value of 'lid' for efficiency and security.

The 'for' loop is an easy way to access the first element of the retrieved QuerySet. Because LOCATION_ID is a primary key, the loop will only execute once.

The variable 's' is built up using string concatenation to contain JSON format version of the row. JSON is a text format often used as a lightweight protocol for transferring data between Javascript in the browser and a server-side script. Newer versions of Python have methods for JSON encoding and decoding that could be used instead of the explicit string concatenation shown here.

Unlike the existing 'index' view, an HTML template is not used. The HttpResponse() is simply to echo the JSON string to the browser.

 

To make the new 'address' view callable, edit myproj/myapp/urls.py (not myproj/urls.py) and add:

               
url(r'^addr/(?P<lid>\w+)$', address),

Also change the following line:

from myapp.views import index

to

from myapp.views import index
              
, address

The complete file looks like (with changes in bold):

from django.conf.urls.defaults import *
from myapp.views import index
              
, address
urlpatterns = patterns('',
                      
              
url(r'^addr/(?P<lid>\w+)$', address),
url(r'^$', index), )

 

ajax03-gif

This new rule matches URLs of the form:

 http://.../myapp/addr/1234

In this example, the parameter 'lid' will be set to 1234 and passed into the 'address' view.

 

To test the new view, call it explictly in a browser:

http://127.0.0.1:8000/myapp/addr/1000

 

ajax04-gif

 

Now, the 'index' view can be changed to use the 'address' view to dynamically change the displayed page.

Edit myapp/templates/index.html and make the following changes. The full file is shown later to make copying easier.

Add a new HTML section below the table:

<p>
<div id="outputNode"></div>
</p>

This initially contains no text to be output. When the page is loaded nothing will be visible below the table. When this example is complete, Javascript code runs when clicking a link and updates the text content of this outputNode section. This causes the browser to show its new value without requiring a full page refresh.

Now add a link to be clicked. Change the template text from:

 <td>{{loc.location_id}}</td>
to
           
  
              
<td><a href="http://this_link_goes_nowhere/"
onClick="makeRequest({{loc.location_id}}); return false;">
{{loc.location_id}}<a></td>

This causes all the location identifiers to be links. The link is to an invalid URL, but that's OK - it will never get called. Instead the onClick event will call a new Javascript function that we'll create. The location_id value (substituted by Django's template expansion) is passed to the function. The "return false;" statements prevents the HREF default action of calling the (in this example) invalid URL.

 

ajax06-gif

Finally, in the <head> tag, add the Javascript function that makes an asynchronous HTTP request:

<script type="text/javascript">
function makeRequest(id)
              


{
              


   httpRequest = new XMLHttpRequest();
              


   httpRequest.open('POST', 'http://127.0.0.1:8000/myapp/addr/' + id);
              


   httpRequest.onreadystatechange = function()
              


   {
              


      if (httpRequest.readyState == 4) {            // The request is complete
              


        var JSONtext = httpRequest.responseText;
              


        var myArray = eval('(' + JSONtext + ')');   // beware security issues
              


        var txt = "Selected address is:<br>";
              


        for (var i = 0; i < myArray.length; ++i) {
              


           txt = txt + myArray[i]["STREET_ADDRESS"] + '<br>'
              


               + myArray[i]["CITY"] + " " + myArray[i]["POSTAL_CODE"] + '<br>'
              


               + myArray[i]["COUNTRY"];
              


        }
              


        document.getElementById("outputNode").innerHTML = txt;
              


      }
              


   }
              


   httpRequest.send(null);
              


}
            
</script>

This is the function called when a location identifier is clicked. It sets the URL to be called as http://127.0.0.1:8000/myapp/addr/ with the location identifier appended.

The line at the bottom of the Javascript:

httpRequest.send(null);

initiates the HTTP request, but before this, the onreadystatechange action is set to an anonymous function ready to handle any returned data.

The state change function is invoked asynchronously when the request state has changed. It checks if the request is complete. If it is, an eval converts the retrieved JSON string into a Javascript object. The resulting array myArray can be used directly in Javascript. In this example the loop will only be excuted once because LOCATION_ID is a primary key. The loop body concatenates the address data fields into a single string.

Beware of security issues with using eval() in this way. One of the many third party JSON parsers would be safer for a production system.

The line:

document.getElementById("outputNode").innerHTML = txt;

causes the page content of the outputNode div section to be changed to the address.

 

ajax05-gif

The complete myproj/myapp/templates/index.html file (with the changes in bold) is:

<html>
              


<head>
              


<title>Office Locations</title>
              


               
<script type="text/javascript">
function makeRequest(id)
              


{
              


   httpRequest = new XMLHttpRequest();
              


   httpRequest.open('POST', 'http://127.0.0.1:8000/myapp/addr/' + id);
              


   httpRequest.onreadystatechange = function()
              


   {
              


      if (httpRequest.readyState == 4) {            // The request is complete
              


        var JSONtext = httpRequest.responseText;
              


        var myArray = eval('(' + JSONtext + ')');   // beware security issues
              


        var txt = "Selected address is:<br>";
              


        for (var i = 0; i < myArray.length; ++i) {
              


           txt = txt + myArray[i]["STREET_ADDRESS"] + '<br>'
              


               + myArray[i]["CITY"] + " " + myArray[i]["POSTAL_CODE"] + '<br>'
              


               + myArray[i]["COUNTRY"];
              


        }
              


        document.getElementById("outputNode").innerHTML = txt;
              


      }
              


   }
              


   httpRequest.send(null);
              


}
            
               
</script>
</head> <body bgcolor="#ffffff">
<h1>Office Locations</h1>
              


<table border="1">
              


  <tr>
              


    <th>Location ID</th>
              


    <th>Street Address</th>
              


    <th>City</th>
              


  </tr>
              


  {% for loc in locations %}
              


  <tr>
              


     
              
<td><a href="http://this_link_goes_nowhere/"



onClick="makeRequest({{loc.location_id}}); return false;">



{{loc.location_id}}<a></td>
<td>{{loc.street_address}}</td> <td>{{loc.city}}</td> </tr> {% endfor %} </table>
               
<p>



<div id="outputNode"></div>



</p>
</body>
              


</html>
            

 

Now run the application by loading the index URL in a browser:

 http://127.0.0.1:8000/myapp/

All the location identifiers are now links.

 

ajax07-gif

 

Hover over any link. The status bar at the bottom of the browser will show the URL http://this_link_goes_nowhere/

 

ajax08-gif

Click on the last location identifier, '3200'. The invalid URL is not called. Instead, the Javascript function is called and the full address of the Mexico office is shown below the table (You may need to scroll down). With the slow development web server included in Django, this might take a second or two to refresh:

 

ajax09-gif

When used judiciously, AJAX is a technique that can improve the performance and usability of web applications. It can be used to display or send data, allowing complex applications to be created.

 

 

Summary

In this tutorial, you have learned how to:

  • Create a connection
  • Use Database Resident Connection Pooling
  • Create a simple query
  • Fetch data
  • Improve query performance
  • Use bind variables
  • Create transactions
  • Use PL/SQL stored functions and procedures
  • Use Continuous Query Notification
  • Use the Django framework
  • Use AJAX with Python

Appendix: Python Primer

Python is a dynamically typed scripting language. It is most often
used to run command-line scripts but is also used in Web applications.

Strings can be enclosed in single or double quotes:

'A string constant'
"another constant"

Multi line strings use a triple-quote syntax

"""
This is
your string
"""

Variables do not need types declared:

count = 1
ename = 'Arnie'

Associative arrays are called 'dictionaries':

a2 = {'PI':3.1415, 'E':2.7182}

Ordered arrays are called 'lists':

a3 = [101, 4, 67]

Tuples are like lists but cannot be changed once they are created. They are created with parentheses:

a4 = (3, 7, 10)

Strings and variables can be displayed with a print statement:

print 'Hello, World!'
print 'Hi', x

Formatted output is also possible:

print "There are %d %s" % (v1, v2)

Everything in Python is an object. As one example, given the value of the list a3 above, the append() method can be used to add a value to the list.

a3.append(23)

Now a3 contains [101, 4, 67, 23]

Code flow can be controlled with tests and loops. The if/elif/else statements look like:

if sal > 900000:
    print 'Salary is way too big'
elif sal > 500000:
    print 'Salary is huge'
else:
    print 'Salary might be OK'

This also shows how the clauses are delimited with colons, and each sub block of code is indented.

A traditional loop is:

for i in range(0, 10):
    print i

This prints the numbers 0 to 9. The value of i is incremented in each iteration.

The 'for' command can also be used to iterate over lists and tuples:

a5 = ['Aa', 'Bb', 'Cc']
for v in a5:
    print v

This sets v to each element of the list a5 in turn.

A function may be defined:

def myfunc(p1, p2):
    "Function documentation: add two numbers"
    print p1, p2
    return p1 + p2

Functions may or may not return values. This function could be called using:

v3 = myfunc(1, 3)

Function calls must appear after their function definition.

Functions are also objects and have attributes. The inbuilt __doc__ attribute can be used to find the function description:

print myfunc.__doc__

Sub-files can be included in Python scripts with an import statement.

import os
import sys

Many predefined modules exist, such as the os and the sys modules.

Comments are either single line:

# a short comment

or multi-line using the triple-quote token:

"""
a longer
comment
"""
 

 

 
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights