Developing a Python Web Application with Oracle Database 11g

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

This tutorial shows you how to use 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.

Prerequisites

1. Install Oracle Database 11.2

2. Create a user named PYTHONHOL with password of
'welcome'. Install Oracle's sample HR schema in PYTHONHOL and
make the following modifications:

-- Add a sequence and trigger to autopopulate the EMPLOYEE_ID field
-- when new records are inserted.
create sequence emp_id_seq start with 400;
create trigger my_emp_id_trigger
before insert on employees for each row
begin
select emp_id_seq.nextval into :new.employee_id from dual;
end;
/

-- To simplify the example we remove this trigger otherwise
-- records can only be updated once without violating the
-- PYTHONHOL.JHIST_EMP_ID_ST_DATE_PK constraint
drop trigger update_job_history;

-- Allow employees to be changed when testing the lab after hours.
drop trigger secure_employees;

3. Install Python 2.x and the cx_Oracle 5.1 extension

4. Install the Django 1.3 framework

5. Extract these files.

6. Move the files from the default directory to your $HOME directory.

Creating a Django Application

The Django framework is one of several popular Python Frameworks for creating Python web applications. Django is a Model-View-Controller framework.

This section of the tutorial creates an Employee administration application using Oracle's Human Resources sample schema. The schema has been slightly modified to simplify some aspects of the instructions.

Note: Leading whitespace is significant in Python. Be careful when changing code as you work through this tutorial. Indentation is used in Python to indicate the code structure. There are no statement terminators unlike many other languages and there are no begin or end keywords, or braces to indicate blocks of code. Many of Django's configuration files use Python conventions.

.

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.

 

.

In the Firefox web browser 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 Django's generated $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=4, 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',
    'django.contrib.staticfiles',
    # Uncomment the next line to enable the admin:
    # 'django.contrib.admin',
    # Uncomment the next line to enable admin documentation:
    # 'django.contrib.admindocs',
    '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 include:

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',
    'django.contrib.staticfiles',
    # Uncomment the next line to enable the admin:
    'django.contrib.admin',
    # Uncomment the next line to enable admin documentation:
    # 'django.contrib.admindocs',
    '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. Accept the default username, specify pythonhol@example.com for the e-mail address, 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 three 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 patterns, include, url

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

urlpatterns = patterns('',
    # Examples:
    # url(r'^$', 'mysite.views.home', name='home'),
    # url(r'^mysite/', include('mysite.foo.urls')),

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

    # Uncomment the next line to enable the admin:
    url(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.


 

Writing Your Own Django Public Interfaces

For this next section you will create your own Employees application, similar to the defaut admin application, but providing a base application
for further extension.

.

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.*)$', '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.

 

Summary

In this tutorial, you have learned how to:

Hardware and Software Engineered to Work Together Copyright © 2011, Oracle and/or its affiliates. All rights reserved