Developing a Python Web Application with Oracle Database 11g
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.
1 . | 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.
|
---|---|
2 . | Look at what startproject created: ls -lt mysite You should see: These files are:
|
3 . | 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.
|
4 . | In the Firefox web browser go to http://localhost:8000/
|
5 . | Press control-C in the terminal window to terminate the web server.
|
6 . | 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.
|
7. | 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 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 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.
|
8. | 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
|
9. | 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 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 ofdjango.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.
|
10. | 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:
|
11. | 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. These applications will not be discussed in this tutorial, but you will activate the admin application that is currently commented out.
|
12. | 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
|
13. | Next, synchronize the database: cd $HOME/mysite 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 This shows that Django has created tables such as DJANGO_SITE and DJANGO_ADMIN_LOG.
|
14. | Finally, update the URL mappings. Edit $HOME/mysite/urls.py. Uncomment the three lines that reference the adminapplication. 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. |
15. | 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.
|
16. | Start the development web server and explore the admin application. cd $HOME/mysite 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.
|
17. | 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 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.
|
18. | 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.
|
19. | 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:
|
20. | 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.
|
21. | 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.
1. | 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 If the requested URL is http://localhost:8000/employees/update/23 then the URL patternr'^employees/update/(?P<object_id>\d+)/$' is matched. It loads the function update_object from the presupplieddjango.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+).
|
---|---|
2. | If you load http://localhost:8000/employees/ right now, you would see an error:
|
3 . | 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
|
4 . | Edit $HOME/mysite/settings.py and change the TEMPLATE_DIRS entry to use the new templates directory: ... TEMPLATE_DIRS = ( ... Note: The file contains other code - do not delete that!
|
5. | 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
|
6. | 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
|
7. | If you had stopped the web server, restart it. cd $HOME/mysite
|
8. | 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.
|
9. | 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',
|
10. | 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!
|
11. | 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.
|
12. | 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/, orhttp://localhost:8000/employees/update/ are called. In the template, when an employee object exists, the form displays the Edit Employee heading. Otherwise, it displays theCreate Employee heading.
|
13. | 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.
|
14. | Create a new employee record. In the form, enter the following values: Chris Click Save. The new record is shown on one of the pages.
|
15. | 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.
|
16. | 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>
|
17. | 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:
- Use the Django framework
- Create a Django Application
- Write a Django public interface