Using Python With Oracle Database 11g

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, with some modifications to simplify the web application example.

Python 2.4 with the cx_Oracle 5.0.3 extension.

The Django 1.2.1 framework.

To start, create a Django project.  From a terminal window, run:
           
cd $HOME
django-admin.py startproject mysite

This creates a mysite directory in your home directory. It also generates code for a new Django project, including project settings and database configuration.

Look at what startproject created:

ls -lt mysite

You should see:

These files are:

1. _init_.py: An empty file telling Python to consider this directory a Python package.
2. manage.py: A command-line script that allows the Django project to be administered.
3. settings.py: Settings for this Django project.
4. urls.py: The mappings for the project that maps URL requests to Django files.

To run the skeleton application, in your terminal window, start the Django development web server:

cd $HOME/mysite
python manage.py runserver

The Django development web server has been started. Because this is a development web server, it should not be used in production.

Start the Firefox web browser and go to http://localhost:8000/

Press control-C in the terminal window to terminate the web server.

Setup the database connection information. Edit $HOME/mysite/settings.py.

Replace only the current DATABASES entry with:

DATABASES = {
    'default': {
    'ENGINE':   'django.db.backends.oracle',
    'NAME':     'localhost/orcl',
    'USER':     'pythonhol',
    'PASSWORD': 'welcome',
  }
}

Make sure there is no whitespace before the keyword DATABASES. Save the file.

This entry tells Django to use the Oracle interface, connecting to the local database with the service name of orcl. The username and password are also specified.

A Django project can be thought of as a website. An application is a sub-component of that website that does something, such as manage employee information.

Inside the mysite project directory, create an application:

cd $HOME/mysite
python manage.py startapp python_hol

When the command succeeds, it finishes silently and creates a directory python_hol for the new application.

Note: If you see an error such as:

[pythonhol@localhost mysite]$ python manage.py startapp python_hol
Traceback (most recent call last):
File "manage.py", line 4, in ?
import settings # Assumed to be in the same directory.
File "/home/pythonhol/mysite/settings.py", line 13
DATABASES = {
^
SyntaxError: invalid syntax

you probably have whitespace before the DATABASES keyword in mysite/settings.py. Edit the file, remove the leading whitespace, save the file and rerun the startapp command.

Next, you need to define models for the application. Initially, $HOME/mysite/python_hol/models.py is essentially empty.

cat $HOME/mysite/python_hol/models.py

Typically Django applications are written to auto-generate tables from a new application's model. However, in this tutorial we create an application based on existing tables. Run:

cd $HOME/mysite
python manage.py inspectdb > python_hol/models.py

This may take a few seconds to complete. This command interrogates the pythonhol schema and creates a model for each table.

cat $HOME/mysite/python_hol/models.py

You can see each table has a class with class variables for each column. The classes are subclasses of django.db.models.Model. Each field has a type. Many fields have a length that is used for validation. In new applications the length could be used to create the correct database schema. Django supports all of the common database relationships: many-to-one, many-to-many, and one-to-one.

The tutorial code focuses on employees and jobs, so the models not related to employees and jobs are not needed.

Overwrite the $HOME/mysite/python_hol/models.py file with the $HOME/sample/models.py file so that only the required models remain.

cp $HOME/sample/models.py $HOME/mysite/python_hol/models.py

The $HOME/mysite/python_hol/models.py file now contains the following:

# $HOME/mysite/python_hol/models.py

from django.db import models

class Job(models.Model):
job_id = models.CharField(max_length=10, primary_key=True)
job_title = models.CharField(max_length=35)
min_salary = models.IntegerField(null=True, blank=True)
max_salary = models.IntegerField(null=True, blank=True)
class Meta:
db_table = u'jobs'
def __str__(self):
return self.job_title

class Employee(models.Model):
employee_id = models.AutoField(primary_key=True)
first_name = models.CharField(max_length=20, blank=True)
last_name = models.CharField(max_length=25)
email = models.CharField(unique=True, max_length=25)
phone_number = models.CharField(max_length=20, blank=True)
hire_date = models.DateField()
job = models.ForeignKey(Job)
salary = models.DecimalField(null=True, max_digits=8, decimal_places=2, blank=True)
commission_pct = models.DecimalField(null=True, max_digits=2, decimal_places=2, blank=True)
manager = models.ForeignKey('self', null=True, blank=True)
department_id = models.IntegerField(null=True, blank=True)
class Meta:
db_table = u'employees'
def __str__(self):
return '%s %s' % (self.first_name, self.last_name)

class JobHistory(models.Model):
employee = models.ForeignKey(Employee, primary_key=True)
start_date = models.DateField(unique=True)
end_date = models.DateField()
job = models.ForeignKey(Job)
department_id = models.IntegerField(null=True, blank=True)
class Meta:
db_table = u'job_history'

The changes to models.py are:

1. The order of the models is changed because of model dependencies. For example, the EMPLOYEES table is dependent on the JOBS table so the Job model must occur before the Employee model.
2. Django models use names in the singluar form. The generated ones are based on Oracle's table names which are plural, so the model names were changed to singular. For example EMPLOYEES was changed to Employee.
3. A def _str_(self) function was added to each model. This will come in handy later.
4. The Employee's employee_id's models.IntegerField was changed to models.AutoField. This, with some help from a trigger and sequences, will auto-increment the primary identifier when a new record is inserted.
5. There were also changes to the foreign key fields, adding models.ForeignKey. This is because inspectdb cannot recognize foreign key constraints so these have to be defined manually.
6. The foreign key fields were renamed, removing the _ID suffix. This is because Django will assume this suffix by default so including it in the model leads to errors such as "JOB_ID_ID does not exist".

Edit the $HOME/mysite/settings.py file again. Scroll to the bottom of the file.

Change the INSTALLED_APPS setting to include the string 'mysite.python_hol' so it looks like this:

...

INSTALLED_APPS = (
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.sites',
'django.contrib.messages',
# Uncomment the next line to enable the admin:
# 'django.contrib.admin',
'mysite.python_hol'
)

...

Save the file.

The INSTALLED_APPS variable holds the names of all applications that are available in this website. The default applications that come with Django are:

django.contrib.auth – An authentication system.
django.contrib.contenttypes – A framework for content types.
django.contrib.sessions – A session framework.
django.contrib.sites – A framework for managing multiple sites with one Django installation.

These applications will not be discussed in this tutorial, but you will activate the admin application that is currently commented out.

To activate the optional Django admin application, you must enable it in settings.py, synchronize the database, and then update the URL mapping file.

First, edit the $HOME/mysite/settings.py file again. Change the INSTALLED_APPS setting and uncomment the 'django.contrib.admin' line so the file looks like this:

...

INSTALLED_APPS = (
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.sites',
'django.contrib.messages',
# Uncomment the next line to enable the admin:
'django.contrib.admin',
'mysite.python_hol'
)

...

Save the file

Next, synchronize the database:

cd $HOME/mysite
python manage.py syncdb

Answer yes to the prompt to create a superuser. Specify pythonhol@example.com for the e-mail address, accept the default username, and set the password to welcome. You will need the username and password later.

This updates the database tables needed by the admin application. Feel free to look at the newly created tables in the database by using SQL*Plus.

sqlplus pythonhol/welcome
select table_name from user_tables where table_name like '%DJANGO%';
exit

This shows that Django has created tables such as DJANGO_SITE and DJANGO_ADMIN_LOG.

Finally, update the URL mappings. Edit $HOME/mysite/urls.py. Uncomment the 3 lines that reference the admin application. This file maps URLs to application pages.

In the end, you should have a urls.py file that looks like this:

from django.conf.urls.defaults import *

# Uncomment the next two lines to enable the admin:
from django.contrib import admin
admin.autodiscover()

urlpatterns = patterns('',
# Example:
# (r'^mysite/', include('mysite.foo.urls')),

# Uncomment the admin/doc line below and add 'django.contrib.admindocs'
# to INSTALLED_APPS to enable admin documentation:
# (r'^admin/doc/', include('django.contrib.admindocs.urls')),

# Uncomment the next line to enable the admin:
(r'^admin/', include(admin.site.urls)),
)

Save the file.

The uncommented URL regular expression pattern handles when the URL "localhost:8000/admin/" is called and maps the request to an application for processing.

To create an administration interface for the Employee and Job models, create a file called $HOME/mysite/python_hol/admin.py.

cp $HOME/sample/admin.py.1 $HOME/mysite/python_hol/admin.py

The admin.py file contains the following:

from mysite.python_hol.models import Employee, Job
from django.contrib import admin
admin.site.register(Employee)
admin.site.register(Job)

This file tells the admin application that the Employee and Job models have an administration interface.

Start the development web server and explore the admin application.

cd $HOME/mysite
python manage.py runserver

Normally, the server auto-reloads code every time you modify a file, but the action of creating a new file doesn't always trigger the auto-reloading logic.

Open the Firefox web browser and go to http://localhost:8000/admin/

Login using the username (pythonhol) and password (welcome) you created earlier.

You should see several other types of content that can be administered, including groups, users and sites. These are core features included in Django.

If you see the following error:

SyntaxError at /admin/

invalid syntax (urls.py, line 5)

Request Method: GET
Request URL: http://localhost:8000/admin/
Django Version: 1.2.1
Exception Type: SyntaxError
Exception Value:

invalid syntax (urls.py, line 5)

you may have leading whitespace at the specified line of urls.py. Edit urls.py and correct this. The web server does not have to be restarted - it will automatically reload the changed file. However, you need to reload the URL in the browser.

Click the Employees link.

Now you are at the change list page for employees. This page displays all the employees in the database and could be used to change records.

Click the William Gietz link.

This page could be used to modify the employee's details or delete an employee. But, don't edit any details yet. With only a few configuration options Django has constructed a complete record editing system.

Here are some details about this page:

1. It is automatically generated from the Employees model.
2. The different model field types correspond to the appropriate HTML
   input fields.
3. Each DateTimeField gets a JavaScript calendar widget.
   

You can also customize the admin form.

cp $HOME/sample/admin.py.2 $HOME/mysite/python_hol/admin.py

The modified admin.py adds a new EmployeeAdmin class and replaces the previous admin.site.register(Employee) line to invoke the new class.

The file should now look like this:

from mysite.python_hol.models import Employee, Job
from django.contrib import admin

class EmployeeAdmin(admin.ModelAdmin):
list_display = ('first_name', 'last_name', 'email', 'phone_number', 'hire_date', 'salary')
list_filter = ['hire_date']
search_fields = ['last_name']
date_hierarchy = 'hire_date'

admin.site.register(Employee, EmployeeAdmin)

admin.site.register(Job)

Compare it with $HOME/sample/admin.py.1 used as the admin.py file earlier.

The EmployeeAdmin class restricts the columns displayed, changes their order, adds a Filter sidebar that lets users filter the change list by the HIRE_DATE field, and adds a search box at the top of the change list that allows users to search by name and allows drill down by date.

Take a look at the revised admin form. Go to http://localhost:8000/admin/python_hol/employee/ in the web browser.

Close any open editor changing urls.py and replace the file:

cp $HOME/sample/urls.py $HOME/mysite/urls.py

The urls.py file should now look like this:

from django.conf.urls.defaults import *
from mysite.python_hol.models import Employee

# Uncomment the next two lines to enable the admin:
from django.contrib import admin
admin.autodiscover()

info_dict = {
'queryset': Employee.objects.all(),
}
employee_info = {'model' : Employee}

urlpatterns = patterns('',
(r'^employees/$', 'django.views.generic.list_detail.object_list',
dict(info_dict, template_name='employees/employee_list.html')),
(r'^employees/create/$', 'django.views.generic.create_update.create_object', dict(employee_info,
template_name='employees/employee_form.html', post_save_redirect='/employees/')),
(r'^employees/update/(?P<object_id>\d+)/$', 'django.views.generic.create_update.update_object',
dict(employee_info, template_name='employees/employee_form.html', post_save_redirect='/employees/')),
(r'^employees/delete/(?P<object_id>\d+)/$', 'django.views.generic.create_update.delete_object',
dict(employee_info, template_name='employees/employee_confirm_delete.html',
post_delete_redirect='/employees/')),

(r'^admin/', include(admin.site.urls)),

(r'^site_media/(?P<path>.*)$', 'django.views.static.serve',
{'document_root': '/home/pythonhol/mysite/python_hol/static'}),
)

This contains the URL mappings that map URLs to views. In this example, generic views are used. When a web request is made, Django traverses the regular expressions in order. When it finds a regular expression that matches, the specified view is invoked and passed a Python dictionary containing the data to be processed.

For example, if the requested URL is http://localhost:8000/employees/, with nothing after the slash, then
the r'^employees/' regular expression is matched. The action taken is to process and return the employee_list.html file, which you will create later, using an object_list. The employee_list.html file is a template file that contains a mix of HTML markup, and code executed by Django.

If the requested URL is http://localhost:8000/employees/update/23 then the URL pattern r'^employees/update/(?P<object_id>\d+)/$' is matched. It loads the function update_object from the presupplied django.views.generic.create_update.py. Finally, it calls that update_object() function:

update_object(request=<HttpRequest object>, object_id='23')

The object_id='23' part comes from the regular expression (?P<object_id>\d+).

If you load http://localhost:8000/employees/ right now, you would see an error:

To get the application running you need to create the templates that were specified in urls.py. First, create the necessary directories:

mkdir $HOME/mysite/python_hol/templates
mkdir $HOME/mysite/python_hol/templates/employees

Edit $HOME/mysite/settings.py and change the TEMPLATE_DIRS entry to use the new templates directory:

...

TEMPLATE_DIRS = (
"/home/pythonhol/mysite/python_hol/templates"
)

...

Note: The file contains other code - do not delete that!

Create a Django template file that lists all employees:

cp $HOME/sample/employee_list.html $HOME/mysite/python_hol/templates/employees/

This template file contains:

<html>
<head>
<link rel="stylesheet" type="text/css" href="/site_media/css/python_hol.css">
</head>
<body>
<div id="content">
<a href="/employees/create" class="addlink">Add New Employee</a>
{% if object_list %}
<table id="employee_list_table" frame="hsides" rules="cols" style="border-width: 5px">
<tr>
<th>Email</th>
<th>First Name</th>
<th>Last Name</th>
<th>Phone #</th>
<th>Hire Date</th>
<th>Salary</th>
<th>Commission %</th>
<th>Action</th>
</tr>
{% for employee in object_list %}
<tr>
<td><a href="/employees/update/{{ employee.employee_id }}/">{{ employee.email }}</a></td>
<td>{{ employee.first_name }}</td>
<td>{{ employee.last_name }}</td>
<td>{{ employee.phone_number }}</td>
<td>{{ employee.hire_date }}</td>
<td>{{ employee.salary }}</td>
<td>{{ employee.commission_pct }}</td>
<td><a href="/employees/delete/{{ employee.employee_id }}/">Delete</a></td>
</tr>
{% endfor %}
</table>
{% else %}
<p>No employees found.</p>
{% endif %}
</div>
</body>
</html>

This contains HTML and Django template tags. This example checks if the object_list is correctly passed from
urls.py. If it does exist, an HTML table is created by looping over all the rows in object_list.

Set up the cascading style sheet that is loaded at the top of the employee_list.html template file:

mkdir $HOME/mysite/python_hol/static
mkdir $HOME/mysite/python_hol/static/css
cp $HOME/sample/python_hol.css $HOME/mysite/python_hol/static/css

If you had stopped the web server, restart it.

cd $HOME/mysite
python manage.py runserver

In the browser, load the URL for the application:

http://localhost:8000/employees/

The new application is displayed showing employee details.

Note that all records from the table are displayed in one long page.

If you are having trouble viewing this page, check that you have not confused "employee" and "employees" (plural) anywhere in the instructions.

Instead of displaying all records in one long page, add pagination to display only 25 records per page.

Note: If you have urls.py open in your editor, quit and reload the file from disk.

Edit $HOME/mysite/urls.py and go to the first URL pattern (i.e. r'^employees/$'). Add a paginate_by=25 clause, changing the line to:

(r'^employees/$', 'django.views.generic.list_detail.object_list',
dict(info_dict, paginate_by=25, template_name='employees/employee_list.html')),

Add a page navigation bar at the bottom of each page.

Edit $HOME/mysite/python_hol/templates/employees/employee_list.html. Add the following code at the bottom, after the '% endif %' and before the existing </div>:

<div style="margin-top:10px;"/>
{% if page_range %}
<ul class="page_list">
{% for page in page_range %}
<li><a href="/employees/?page={{page}}">{{page}}</a></li>
{% endfor %}
</ul>
{% else %}
<p>No more pages.</p>
{% endif %}
</div>

Retain the existing content in the file!

Reload the page http://localhost:8000/employees/ in the browser and scroll to the bottom. Notice that there are now 5 pages of results available.

Page through the results.

To insert new employee records, you need to create a form. To do this, create a new template file:

cp $HOME/sample/employee_form.html $HOME/mysite/python_hol/templates/employees

The file contains:

<html>
<head>
<link rel="stylesheet" type="text/css" href="/site_media/css/python_hol.css">
</head>
<body>
{% if error_message %}
<p><strong>{{ error_message }}</strong></p>
{% endif %}
<div id="formdiv">
<form action="" method="post">{% csrf_token %}
{% if object %}
<h1>Edit Employee {{ object.first_name }} {{ object.last_name }}</h1>
{% else %}
<h1>Create Employee</h1>
{% endif %}
<p>Use this form to manage your employees.</p>
<form action="" method="post">{% csrf_token %}
{{ form.as_p }}
<button type="submit">Save</button>
</form>
</div>
</body>
</html>

This is the form invoked by the urls.py mapping when the URLs http://localhost:8000/employees/create/, or http://localhost:8000/employees/update/ are called.

In the template, when an employee object exists, the form displays the Edit Employee heading. Otherwise, it displays the Create Employee heading.

In your browser load http://localhost:8000/employees/create/ which is the URL for the 'Add New Employee' link.

Leave the fields blank and click Save.

Notice that the automatically created validation highlights the required fields.

Create a new employee record. In the form, enter the following values:

Chris
Jones
cj@example.com
650-506-8630
2010-07-31
Programmer

Click Save.

The new record is shown on one of the pages.

Update an existing employee record. In your browser load http://localhost:8000/employees/update/100/ which is the URL for the 'SKING' link on the employee list page.

Change Steven to Stevie and click Save.

The record has been changed.

The application should show a deletion confirmation page before performing a record deletion. To achieve this functionality, create a deletion confirmation template.

cp $HOME/sample/employee_confirm_delete.html $HOME/mysite/python_hol/templates/employees/

Note: Do not press [Enter] to add a line break between the source file and destination directory in the cp command.

The employee_confirm_delete.html file contains the following code:

<html>
<head>
<link rel="stylesheet" type="text/css" href="/site_media/css/python_hol.css">
</head>
<body>
<h1>Delete {{ object.first_name }} {{ object.last_name }}?</h1>
{% if error_message %}
<p><strong>{{ error_message }}</strong></p>
{% endif %}
<form action="" method="post">{% csrf_token %}
{{ form.as_p }}
<input type="submit" value="Yes" />
</form>
</body>
</html>

Test the deletion confirmation functionality. In your browser, load http://localhost:8000/employees/.

Locate the record for Bruce Ernst and click the Delete button.

Notice the URL is now http://localhost:8000/employees/delete/104/.

Click Yes on the confirmation page. This confirms the deletion of Bruce Ernst's employee record.

Notice the record for this employee has been deleted.

If you try to delete a manager such as SKING you will get an Oracle integrity constraint error.  In future, the application business logic could be enhanced to only enable the 'delete' action on non-managerial         employees. This sample application has much scope for extension and being made robust.

Django allows web applications to be be created easily. The models and views can be designed and the schema automatically generated or, as shown here, the models can be derived from an existing schema.

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.