Building Oracle Database-backed Web Applications in Django

By Yuli Vasiliev

Learn how to configure Django to interact with an Oracle database and then use ORM for database connectivity.

Published August 2009

Productivity in the context of software development means the ability to get things done in a timely manner. Developers often find themselves working on the same tasks over and over again, which undoubtedly reduces their productivity. This is where frameworks come to the rescue:Utilizing an appropriate framework or frameworks, you can concentrate on the project requirements rather than dealing with low-level, nasty details of implementation.

Django, the Python-based Web application framework, was originally designed to simplify development of database-driven, news-oriented Web applications. Since then, it has evolved into a full-featured Web framework often chosen to simplify the development of complex, database-backed Web applications.

Django's object-relational mapper (ORM) lies at the heart of the framework, mediating between data models, which are Python classes you build on top of the django.db.models.Model class and underlying relational database objects. After you have defined data models, you will be able to create, retrieve, update, and delete database data via the Python objects mapped to the objects residing in the underlying database. It's important to emphasize that aside from PostgreSQL, MySQL, and SQLite, Django officially supports Oracle Database, letting you access and manipulate Oracle database data using the ORM feature.

Setting up Django

If you haven't yet installed Django, you can do it now. First, make sure you have Python 2.3 or later installed. Also, be sure to install cx_Oracle driver, a Python DB-API implementation for Oracle, so that Python can interact with your Oracle database. For details, you can refer to another OTN article “ Build a Rapid Web Development Environment for Python Server Pages and Oracle” by Przemyslaw Piotrowski. Then, you can get Django from the Download page on the Django Software Foundation website at http://www.djangoproject.com/download/. At the time of this writing, the latest official version was 1.1.

The installation is straightforward. Just unpack the downloaded package, change directory into the one where you unpacked Django, and then run the following command:

python setup.py install


The above command starts the installation process that should take just a few seconds. The simplest way to make sure that the installation has been successful is through the Python interactive interpreter. In an interactive interpreter session, just enter the following command:

import django


If everything is all right, you should see no error messages.

Getting Started

Assuming you have installed Django, you can now give it a try and create your first Django project and an application within it.

In Django, a project comprises configuration and apps for a particular Web site. Thus, a single project may actually include multiple applications. For simplicity, though, the project being created here will include a single application.

To begin with, you might create a folder to be used for housing your Django projects, enter into this folder from an operating system prompt, and then issue the following command:

django-admin.py startproject myproj 


As a result, the myproj folder should appear, along with the following four files within it: __init__.py, manage.py, settings.py, and urls.py. Each of these four files plays a particular role in the project.

  • __init__.py is included to make this directory a Python package, thus enabling you to refer to the project's components with the dot notation; for example, myproj.settings.
  • manage.py represents a command-line utility that offers the same commands as django-admin.py shown above but is designed to work with this particular project.
  • settings.py is a configuration file containing the project's settings. Here, you can specify database connection information, time zone and language code, information about the applications installed in the project, and some other settings.
  • urls.py, also known as URLconf, is a configuration file containing Python code that maps URL patterns to Python callback functions.

As you can see, even configuration files of a Django project are presented in the form of Python source code files. This approach proves advantageous because it enables you to assign settings dynamically and/or import them from other configuration files.

Now that you have created a project, you're ready to move on and create an application within that project. For that, enter into the myproj folder generated by django-admin.py previously, and issue the following command:

manage.py startapp myapp


On Linux, you may need to do:

chmod +x manage.py
./manage.py startapp myapp


The above should generate the folder myapp and the following four files within it: __init__.py, models.py, views.py and tests.py. Again, __init__.py is used here to make this directory a Python package. Tests.py can be used to construct a testsuite for your application. And you will use the remaining two files to define the app's models and views respectively:

  • models.py is designed to contain models – Python classes that you build on top of the django.db.models.Model class, and each of which maps to a single database table.
  • views.py is designed to contain views – Python functions each of which either returns an HttpResponse object with the content to be displayed on the requested page or raises an HTTP exception.

The next step is to tie the newly created application to the project. To do this, you will need to edit the settings.py file in the myproj folder, appending string ‘myproj.myapp' to the INSTALLED_APPS tuple as follows:

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


You're done with the building of the application skeleton. Before you can see the application in action, though, some extra steps are needed. These steps may vary depending on what you want your application to do, of course. For example, you won't need to put database information into the settings.py configuration file and design data models if you're working on a simple application that is not going to interact with a database. In most cases, however, you will need to take at least the following five steps:

  • Specify database information in settings.py
  • Create models
  • Create templates
  • Create views
  • Configure URL patterns in urls.py


Before proceeding to the above steps, let's take a high-level view at a Django application, so that you can better understand how things work in Django. The figure below provides a schematic representation of how a Django application works to fulfill a user's request.

According to the diagram in the figure, it works as follows:

  1. A user enters a URL of a Django-powered site or performs an action on an already loaded page of that site, thus sending a request to the Web server hosting the site.
  2. The Django's URL dispatcher runs through the URL patterns in the urls.py file and chooses the first one that matches the URL requested by the user, invoking then the view (Python callback function) associated with the found pattern.
  3. The view uses data models to obtain database data and then loads the specified template (an HTML page with special template tags embedded; it's similar to a JavaServer Page in Java) and passes it the context including the obtained data mapped to template variable names.
  4. Finally, the view returns an HttpResponse object populated with the rendered template, or an HTTP exception if something went wrong.

As you can see, Django is based on the idea of breaking down the logic of a Web application into models, views, and templates, thus effectively isolating business logic from presentation. Generally, this is similar to the Model-View-Controller (MVC) paradigm used in many other Web frameworks today. In the case of Django, though, views are much like controllers, mediating between models and templates. And Django's templates in turn are closer to MVC's views in that they are responsible for generating appropriate user interfaces, using the data obtained from the model.

Now that you have a grasp of the ideas behind Django, let's proceed with the project started at the beginning of this section and build a simple application interacting with an Oracle database.

Configuring Django to Interact with an Oracle Database

Before you can take advantage of the Django's database-related features, you have to tell Django how it can connect to your database. You can do this by configuring the database-related settings in your project's settings.py configuration file. As for the project created at the beginning of the preceding section, you need to edit settings.py located in the myproj directory generated by django-admin.py.

Open up the settings.py file in a text editor and edit the database-related settings accordingly to your database. For example, you might edit them like this:

DATABASE_ENGINE = 'oracle' 
DATABASE_NAME = 'XE' 
DATABASE_USER = 'hr' 
DATABASE_PASSWORD = 'hr' 
DATABASE_HOST = 'localhost' 
DATABASE_PORT = '1521'


The above assumes that you have the HR demonstration schema installed and unlocked in your database. In a real project, though, you would most likely use a custom schema designed for a particular application. It's interesting to note that Django can save you the trouble of creating the underlying tables yourself. After you're finished building data models, you can run the manage.py syncdb command to automatically create database tables – one for each data model found in the models.py file. For the sake of simplicity, however, the example in this article will employ already existing tables from the HR demonstration schema.

Now that you have instructed Django to interact with a certain Oracle database, you can proceed to building data models.

Object-relation Mapping with Models

As mentioned previously, Django supports Object-relation mapping with models, where each model maps to a single database table and represents a Python class that subclasses the django.db.models.Model standard class.

The following example illustrates how you might define a model on an existing table. In this example, you're using the employees table from HR, defining model fields only on selected fields of the table. Open up and edit the models.py file in the myproj/myapp directory, adding the employees class, as follows:

from django.db import models


# Create your models here.
                 
class employees(models.Model):
    employee_id = models.IntegerField(primary_key=True)
    first_name = models.CharField(max_length=20, null = True)
    last_name = models.CharField(max_length=25)
    email = models.CharField(max_length=25)
    class Meta:
         db_table = "employees"


Note the use of some model field options here. By setting primary_key = True, you explicitly specify that the field is the primary key for the model. The max_length argument is required for the CharField, which is a string field. The optional null argument, if set to True, tells Django to save empty values as NULL to the database. By default, it is set to False. To see the entire list of the field options and field types, you can refer to the Model field reference page in the Django  documentation.

Another interesting thing to note in the above example is the use of class Meta, through which you can give metadata options to your model. In this particular example, you use the db_table option to explicitly specify the name of the table to which the model is to map. The fact is that Django by default assumes that the name of a table is composed of the name of the model class and the application name, separated by the underscore (_) symbol. So, in this particular example, Django would look for a table named myapp_employees. Of course, the db_table is not the only option you can use with the model's internal class Meta. You can look at the list of available Meta options on the Model Meta options page in the Django documentation.

The example discussed here is simple in that it shows mapping to a single database table. In practice, however, you often have to deal with a collection of underlying database tables related to each other through foreign key constraints. To address this problem, Django offers the ForeignKey field type that lets you define model fields representing a many-to-one relationship.

Fortunately, the HR demonstration schema just contains a set of tables related to each other through foreign key constraints. You might choose, for example, the departments table whose manager_id field is a foreign key to employee_id in the employees table, and define the following model (add it into the models.py file):

class departments(models.Model):
    department_id = models.IntegerField(primary_key=True)
    department_name = models.CharField(max_length=30)
    manager = models.ForeignKey(employees, null = True)
    class Meta:
         db_table = "departments"


Looking at the above model definition, you might notice that it uses the name manager for the foreign key model field instead of manager_id actually used in the departments table. The fact is, the manager field used in the model refers to the corresponding employees object rather than the employee_id field of that object. Django implicitly appends _id to the foreign key field name when it comes to referring to the corresponding column in the underlying table. In some cases, though, a foreign key field in the table may have a name that doesn't hold _id at the end. If that is the case, you can explicitly specify the table column name, using the db_column argument of the foreign key model field. Although there is no need to do it in this particular example, you could use the following syntax to explicitly specify the name of the foreign key column in the departments table:

manager = models.ForeignKey(employees, db_column = 'manager_id', null = True)


Aside from many-to-one relationships defined with ForeignKey as discussed above, Django supports one-to-one and many-to-many relationships, which you can define in models with the OneToOneField and ManyToManyField fields respectively.

Using Database-abstraction API

Once you've finished building the models, you're ready to move on and build views in which those models will be utilized. This is where you can use Django database-abstraction API to create, retrieve, update and delete Python objects (models) mapped to the objects in the underlying database.

Let's create a simple view that will derive data from the employees and departments database tables, with the help of the models created in the preceding section. In the myproj/myapp directory, open the views.py file and edit it as follows:

# Create your views here.


from django.template import Context, loader
from django.http import HttpResponse
from myproj.myapp.models import employees, departments


def index(request):
    department_list = departments.objects.exclude(manager__employee_id__exact = None).order_by('manager__employee_id')
    tmpl = loader.get_template("index.html")
    cont = Context({'departments': department_list})
    return HttpResponse(tmpl.render(cont))


Here you added a simple view, a function called index actually, which populates a list of departments (department_list), including there only those departments that have a manager (what this means is that the manager_id field in the underlying table must be filled). Then, the view loads the index.html template, which will be discussed in the Creating the Presentation Tier section, and passes it the department_list for rendering. Finally, the rendered page is returned with an HttpResponse object to Django.

The most interesting part here is deriving data from the departments model. Note the use of the objects attribute of the departments model class. Through this attribute, you get access to a models.Manager object (don't confuse it with our manager field defined in the departments model). models.Manager objects are attached to a model object, providing methods that let you query data in the model's underlying table. These methods return a QuerySet object containing database data being retrieved in the form of model objects. A QuerySet object also provides a range of useful methods allowing you to further process the returned model objects. It's interesting to note that some QuerySet's methods return a new QuerySet object, while the others simply evaluate an existing QuerySet, returning some information based on that evaluation. The full list of the QuerySet methods can be found on the QuerySet API reference page in the Django documentation.

In the example discussed here, the models.Manager's method exclude is used to limit the departments objects being retrieved only to those that have a related employees object. Then, the QuerySet's order_by method returns a new QuerySet object ordered by the departments.manager.employee_id field. It's similar to the following Oracle SQL query:

SELECT * FROM departments WHERE manager_id IS NOT NULL ORDER BY manager_id;


Aside from the exclude method used here, there are three other methods you may call via the objects attribute to query the model's data. Here is the list:

  • all – returns a QuerySet that contains all the model objects derived from the model's underlying table.
  • filter – returns a QuerySet that contains only those model objects that match the specified criteria.
  • exclude – returns a QuerySet that contains those model objects that do not match the specified criteria.
  • get – returns a single model object that matches the specified criteria.

Since employee ids in the employees HR table start with 100, in the preceding example you might replace exclude with filter to populate the department_list with data, as follows:

department_list = departments.objects.filter(manager__employee_id__gte = 100).order_by('manager__employee_id')


Like in the original version, taking into consideration a many-to-one relationship defined between the departments and employees models as discussed earlier, an SQL equivalent in this case might be based on querying the departments table only and could look like this:

SELECT * FROM departments WHERE manager_id >= 100 ORDER BY manager_id;


All the model query methods discussed here, except the one called all, take lookup parameters to narrow the result set of the model objects retrieved. Thus, in the above example you used the following lookup parameter with the filter method querying departments objects:

manager__employee_id__gte = 100


As you might guess, the above lookup parameter fits the following pattern: field__subfield__lookuptype=value, where the argument is comprised of keywords separated by double-underscores. Here, manager is the departments model's field that refers to an employees object. employee_id in turn is the field of the employees model. And gte is a standard field lookup meaning greater than or equal. The above combination is possible in this example because the departments and employees models are related with a many-to-one relationship. When querying non-related data, though, you would use the following pattern: field__lookuptype=value. For example, to get an employees object whose employee_id is 100, you could query the employees model as follows:

emp = employees.objects.get(employee_id__exact = 100)


In practice, you may need to issue a query that has a complex select list and/or a complex WHERE clause. Say, you want to obtain employees objects based on the following query:

SELECT employees.*, (email||'@company.com') as email_address FROM employees;


How can you tell Django to add another field to the select list of the query lying behind the employees model? This is where the QuerySet method extra comes to the rescue.

employee_list = employees.objects.all().extra(select={'email_address': "(email||'@company.com')"})


As a result, Django will automatically add an extra attribute, email_address, to each employees object retrieved here.

Of course, you can use the extra method not only to enhance the select list of the query implicitly generated by a QuerySet. Also you can specify an explicit WHERE clause and add tables to the query's FROM clause, as well as provide dynamic parameters to be bound to the corresponding placeholders specified in the WHERE clause. Here is the list of arguments you can pass to the extra method:

  • select – adds extra fields to the select list of the query implicitly generated by a QuerySet
  • where – specifies an explicit WHERE clause in the QuerySet's query
  • tables – includes additional tables to the from list of the QuerySet's query
  • order_by – orders the QuerySet by a field or fields added with the select or tables argument
  • params – passes in dynamic parameters that will be safely bound to the placeholders specified in the WHERE clause

So far, you've looked at examples that illustrate how you can use the Django database-abstraction API to query underlying database data. Aside from querying, though, you can use the API to create, update, and delete database data.

The following snippet illustrates how you might create a new record in the departments database table, using the employees and departments models discussed here:

emp = employees.objects.get(employee_id__exact=100)
new_dept = departments(department_id = 1000, department_name = 'test', manager = emp)
new_dept.save()


As a result, a new row should appear in the departments table. It's interesting to note that the model.save method is also used to update an existing row:

dept = departments.objects.get(department_id__exact=1000)
dept.department_name = 'new name'
dept.save()


Finally, to delete a row, you use model.delete:

dept = departments.objects.get(department_id__exact=1000)
dept.delete()


Transaction Management

By default, Django uses the auto-commit transaction mode. This means it immediately commits the changes made by data-changing model methods such as model.save and model.delete, which you saw in the preceding section. However, you can change this default behavior for a particular view function, using transaction decorators available via the django.db.transaction module. You have the following three options:

  • @transaction.autocommit (default)
  • @transaction.commit_on_success
  • @transaction.commit_manually

For example, you might instruct Django to use a single transaction within a view function and commit at the end only if the function returns successfully, as follows:

from django.http import HttpResponse
from myproj.myapp.models import employees, departments
from django.http import Http404
from django.db import transaction


@transaction.commit_on_success
def newdept(request, emp_id, dept_id, dept_name):
    try:
      new_dept = departments(department_id = dept_id, department_name = dept_name, manager = None)
      new_dept.save()
      emp = employees.objects.get(employee_id__exact = emp_id)
      new_dept.manager = emp
      new_dept.save()
    except employees.DoesNotExist:
      raise Http404
    return HttpResponse("The %s department record has been inserted." %dept_name)

The newdept view function shown above will automatically commit all the work done within it only if it returns successfully. If an exception is raised, all the changes pending will be rolled back. However, should you remove the decoration placed before the newdept view function, or replace it with @transaction.autocommit, then the transaction behavior changes. An HTTP 404 exception will still be generated upon failure to find the employee specified. But this time the change made by the first new_dept.save will be committed to the database immediately, resulting in a department record with the manager_id field empty.

It's interesting to note that the code in the preceding listing could be significantly simplified with the get_object_or_404 function defined in the django.shortcuts module. Here is how the revision would look like:

...
from django.shortcuts import get_object_or_404 


@transaction.commit_on_success
def newdept(request, emp_id, dept_name, dept_id):
    new_dept = departments(department_id = dept_id, department_name = dept_name, manager = None)
    new_dept.save()
    emp = get_object_or_404(employees, employee_id__exact = emp_id)
    new_dept.manager = emp
    new_dept.save()
    return HttpResponse("The %s department record has been inserted." %dept_name )


The above syntax will not be an option, though, if you have chosen the commit_manually decorator. In that case, you will need to explicitly commit or rollback transaction with the transaction.commit or transaction.rollback respectively. So, the try-except syntax seems far more natural in this situation:

...
from django.db import transaction
...
@transaction.commit_manually
def newdept(request, emp_id, dept_id, dept_name):
    try:
      new_dept = departments(department_id = dept_id, department_name = dept_name, manager = None)
      new_dept.save()
      emp = employees.objects.get(employee_id__exact = emp_id)
      new_dept.manager = emp
      new_dept.save()
    except employees.DoesNotExist:
      transaction.rollback()
      raise Http404
    else:
      transaction.commit()
    return HttpResponse("The %s department record has been inserted." %dept_name)


Add the newdept view above to myproj/myapp/views.py.

Creating the Presentation Tier

As mentioned, Django templates are designed to display information passed to them from within views, using a django.template.Context object. Turning back to the index view function discussed in the Using Database-abstraction API section earlier, let's create template index.html used in that view.

First, create a directory called templates within the myapp directory. This is where Django will look for templates by default. Then, create index.html within the myapp/templates directory, and insert the following code into the file:

<h1>Managers of departments</h1>
<table border = 1px cellpadding="3" style="font-family:Arial">
<tr>
<th>empid</th>
<th>first name</th>
<th>first name</th>
<th>email</th>
<th>department name</th>
</tr>
{% for department in departments %}
<tr>
<td>{{department.manager.employee_id}}</td>
<td>{{department.manager.first_name}}</td>
<td>{{department.manager.last_name}}</td>
<td>{{department.manager.email}}</td>
<td>{{department.department_name}}</td>
</tr>
{% endfor %}
</table>

The code in the template is quite straightforward. You use standard HTML tags to define a header and table elements, embedding Django's template language elements whose variable tags are enclosed in double curly braces {{ .. }} and block tags are enclosed in {% .. %} pairs. To learn more about the Django template language, you can refer to the page “The Django template language”, in Django documentation.

URL Dispatching

The last puzzle to the Django application discussed here is the URLconf that should contain the URL patterns matching incoming requests with the view functions. Although you could define those patterns entirely at the project level, decoupling an application's URLs from the project's configuration is considered good practice. So, edit the urls.py file in the myproj directory as follows:

...
urlpatterns = patterns('',

...

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


Then, create an urls.py file in the myapp directory and insert the following code there:

from django.conf.urls.defaults import *
from myapp.views import index, newdept

urlpatterns = patterns('',
url(r'^(?P<emp_id>\d+)/(?P<dept_name>\w+)/(?P<dept_id>\d+)/$' , newdept),
url(r'^$', index),
)

As you might guess, the first pattern is designed to handle requests to the newdept view discussed at the Transaction Management section, and the second one is for requests to the index view.

Using Django Development Web Server

It's time to test drive the Django application you just built. For that, you could use the Django's built-in development Web server. First, enter into the myproj directory from an operating system prompt and issue the following command:

manage.py runserver


As a result, you should see some lines of output informing you that the development server is running, along with the address at which it is available (it should be http://127.0.0.1:8000/ by default). All that's left is to point your browser to http://127.0.0.1:8000/myapp/. The result should look like the figure below:


To test the newdept view, you might enter the following url in your Web browser: http://localhost:8000/myapp/100/test/1000/. This should insert a new record into the departments table, whose department_id is 1000, department_name is test, and manager_id is 100.


Using Django with Apache

The Django's built-in development Web server is good for testing only, meaning it's not a production server. When production is involved, you need to consider a more serious alternative, such as Apache.

You can deploy Django to Apache via the mod_python module, which is used to embed Python within Apache. So, first make sure you have the mod_python module installed on your Apache server (details can be found here). After that, you can add the following Location block to the Apache httpd.conf configuration file (use your actual paths in PythonPath):

< Location "/myapp/">
    SetHandler python-program
    PythonPath "['/home/user/myprojects', '/home/user/myprojects/myproj'] + sys.path"
    PythonHandler django.core.handlers.modpython
    SetEnv DJANGO_SETTINGS_MODULE myproj.settings
    PythonDebug On
< /Location>


After restarting Apache, you can test your application. If your Apache server is running on the localhost, you could point your browser to http://localhost/myapp/ to test the index view and to http://localhost/myapp/100/test/1000/ to see the newdept in action.

Conclusion

As you learned in this article, Django is a powerful Web framework that lets you create database-driven Web applications in no time. Connecting Django to an Oracle database is a breeze. And the Django's ORM feature is brilliant.


Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, Java technologies, databases, and service-oriented architecture (SOA). He is the author of Beginning Database-Driven Application Development in Java EE: Using GlassFish (Apress, 2008) and PHP Oracle Web Development, (Packt, 2007).