TurboGears offers Python-istas an excellent framework for creating Web-enabled applications backed by an Oracle database.

Published November 2007

Python has enjoyed great success over the years as a scripting language, with its succinct syntax and minimalist focus. Legions of "Python-istas" have carved out numerous uses for the language, ranging from graphical interfaces all the way to robotics, but until recently, database-backed Web applications were one glaring void that remained to be filled by the Python community. With TurboGears, you can now make use of Python for putting together best-of-breed object-relational (O/R) mappers, template toolkits, and other ancillary parts needed to bring database-backed applications to the Web with Oracle database technology.

Python and TurboGears: Background and Architecture

Serving as a testament to Python's popularity are the numerous third-party libraries and modules that have been developed throughout the years. In the case of Web-bound applications using Python, there has been no shortage of pieces for filling voids; similarly, numerous Python pieces for accessing relational databases have also been developed, but this hasn't alleviated the Python void mentioned at the outset in the area of database-backed applications, as a whole, enabled on the Web.

Reminiscent of the state of affairs in platforms such as Java Platform, Enterprise Edition (Java EE), which continuously sprout specifications and frameworks for dealing with the problems du jour in database-backed Web applications, TurboGears is the Python community's answer to bringing together numerous parts of the Python ecosystem to deal with the requirements typical of today's database-backed Web applications.

In this sense, TurboGears simply enables Python practitioners to choose from many staple Python libraries, some of which are presented in Table 1, that tackle numerous issues of database-backed Web applications.

Python library	Target
SQLObject	An object-relational mapper
SQLAlchemy	An object-relational mapper
CherryPy	An object-oriented HTTP framework
Kid	An XML-based template library
Mochikit	An Ajax/JavaScript library with Pythonesque syntax
Cheetah	A template engine and code generation tool

Table 1 TurboGears support for Python libraries

Some of these libraries may be familiar to Pythonistas for creating static-based Web sites and others perhaps for accessing data directly from a relational database in some non-Web environment, but it's the integration code needed to make these Python parts click together that makes TurboGears an exciting addition to the Python community.

Armed with this brief overview of TurboGears' position relative to other Python projects, let's get started on preparing our Python and Oracle environment for TurboGears.

Setting Up Your Environment: Python, Oracle Database, cx_Oracle, and TurboGears

Python

Python interpreters are available for numerous platforms and processor architectures. If you are on a *nix box, chances are good that you already have some version installed on it by default. For TurboGears, it is recommended that you have a Python 2.5.x version installed on your machine, although support for earlier Python versions is available. For best results and to get the most out of your TurboGears applications, it is advisable to upgrade to a 2.5.x branch

Depending on your OS and processor architecture, you can download a series of 2.5.x Python versions here.

Oracle Database

You can choose from numerous Oracle Database releases for your TurboGears applications. Whether it's one of the earlier incarnations of Oracle8i or a more recent Oracle Database 11g release, the lightweight Oracle Database Express Edition or Oracle Database Enterprise Edition, you can rely on whatever release of Oracle Database your organization has in house and still be able to tap your information from Python middleware. With the exception of a few Python/Oracle installation quirks, we won't go into Oracle Database installation specifics here, because there are already excellent resources available on the subject. In case you are unfamiliar with Oracle Database technology, however, here are a couple links to get you started:

• Oracle Database 10g Express Edition: An entry-level, small-footprint database, which I use in this article to illustrate TurboGears' capabilities



• Oracle Database Editions and Characteristics: For those with more-sophisticated requirements, a side-by-side comparison of numerous Oracle Database versions

cx_Oracle

cx_Oracle serves as the core and lowest-level API for bridging a Python environment to an Oracle database. cx_Oracle is to Python what an Oracle JDBC driver is to Java, enabling an application to execute raw SQL queries and manipulate database cursors with a fine level of detail.

But much like Java applications—which, in this day and age, rarely make direct use of raw SQL commands and instead rely on higher-level O/R mappers that simply use JDBC drivers as a building block—cx_Oracle installation is required for making use of TurboGears' higher-level O/R mapping modules.

You can download cx_Oracle prebuilt binaries or cx_Oracle source code, the latter for building cx_Oracle from scratch to suit your Oracle/Python version combination, if desired.

cx_Oracle Utilities

Although they are not required for TurboGears operation, you may also be interested in two more open source Python/Oracle projects related to cx_Oracle:

• cx_OracleTool: A series of cross-platform tools written in Python that perform common Oracle Database developer tasks



• cx_OracleDBATools: A series of cross-platform tools written in Python to perform Oracle DBA tasks such as starting, stopping, and creating Oracle databases

TurboGears

Once you have all the preceding installations up and running, TurboGears is extremely straightforward to set up, requiring the execution of a single Python script. The installation script, tgsetup.py, which you can download here and which should be invoked by use of $ python tgsetup.py, does the following:

• Downloads and installs numerous Python modules used by default in TurboGears, such as Cheetah, CherryPy, Kid, SQLObject, and Easy Install
• Installs a shortcut script, tg-admin, to ease TurboGears project creation and configuration and installs the respective shortcut scripts to the installed Python modules: cheetah, kid, sqlobject-admin, and easy_install

In case you want to further verify your TurboGears installation, look under [PYTHON_HOME]/lib/python2.5/site-packages/ to see the series of Python module/eggs used by default in TurboGears. As for the shortcut scripts, these will be placed by default in your system's path directory—typically /usr/local/bin/ on a *nix station—so they can be invoked from any directory in your system.

SQLAlchemy

As a final step, you need to install SQLAlchemy, the Python O/R mapper of choice for our TurboGears project; more on this selection in the next section.

You can use the easy_install script that was installed along with TurboGears. Just execute easy_install SQLAlchemy, and the download/installation sequence for SQLAlchemy should begin.

Creating Your First TurboGears Project

Our sample TurboGears project will be designed to furnish employee information to the Acme human resources department, providing Ajax-enabled interfaces to enhance the navigation and data-loading capabilities behind this application. Furthermore, to simplify our development, we will also make use of the HR data schema provided in Oracle Database Express Edition, which will likely serve as a familiar starting data model for many.

With that said, let's kick things off by creating our project, using the tg-admin utility. Listing 1 shows this process.

Listing 1 Creating a TurboGears project

$ tg-admin quickstart --sqlalchemy
Enter project name: ACME HR
Enter package name [acmehr]:
Do you need Identity (usernames/passwords) in this project? [no]



<100~150 lines processing results> 

The tg-admin quickstart --sqlalchemy command starts a project wizard, which then asks us for a project name. Enter ACME HR. The next two questions will have a bracketed default answer. Given our project needs, these values will do, so just click Enter to accept. Once you've completed this process, a directory called ACME-HR, containing all of the project's files, will be created.

Next, descend into the ACME-HR directory and open the dev.cfg file in a text editor, commenting out every line, starting with sqlalchemy.uri, and uncommenting/modifying the line server.socket_port=8010. The latter is a port modification that is necessary because the default 8080 may conflict with the Oracle Database Express Edition Web server. Once you've made this small modification, execute—in the same top-level directory—the start-acmehr.py script, which will initiate a test Web server and start serving the project at http://localhost:8010/. If you point your browser to this address, you will see a screen similar to the one presented below.

It's that simple. Your TurboGears project is now up and running, after these few steps. Now let's explore how to access an Oracle database from TurboGears.

TurboGears Models

As I mentioned earlier, TurboGears relies on the use of object-relational mappers for accessing data from a relational database and bringing it over to a Python middleware layer for business processing. By default, TurboGears makes use of a Python O/R mapper named SQLObject, but as you might have noticed in the previous step, you used the --sqlalchemy flag, which creates a project with the configuration files that make it possible to use SQLAlchemy as the default O/R mapper instead—see the "SQLAlchemy Versus SQLObject" sidebar for more information on this choice.

The first step in configuring TurboGears to use an Oracle database is to assign the database's access parameters to the main development configuration file, dev.cfg, and add a line like the following:

	  
sqlalchemy.dburi="oracle://hr:hr@xe"  

where hr:hr refers to the database username/password and @xe is an Oracle instance containing the sample HR schema provided in Oracle Database Express Edition.

Next, move down to the acmehr subdirectory and open the file named models.py, which will contain all the necessary declarations for tapping into our Oracle database and will be the focus of the remainder of this section.

Because we already have an existing HR schema to work with, the first thing we will do is hook into some of the relational tables present in this schema. Listing 2 illustrates models.py with the necessary code to latch onto the EMPLOYEES and JOBS tables.

Listing 2 TurboGears models.py for pre-existing tables

#Default imports for sqlalchemy and turbogears
from sqlalchemy import *
from turbogears.database import metadata, session
from sqlalchemy.ext.assignmapper import assign_mapper
# Import binder method, needed to access pre-existing tables
from turbogears.database import bind_meta_data
bind_meta_data()
#Declare tables to access
employees = Table('employees', metadata, autoload=True)
jobs = Table('jobs', metadata, autoload=True)
#Declare class objects 
class Employee(object): pass
class Job(object): pass
#Performing table/object binding, placing in session 
assign_mapper(session.context, Employee, employees)
assign_mapper(session.context, Job, jobs)

As you can see, accessing an existing database table from TurboGears consists of the same three-step sequence: creating a Table object, creating its corresponding Python object representation, and mapping them together. With this, we're done bringing our data model into Python. It's that straightforward.

Now it's time to define the actual business methods that will manipulate this data inside a TurboGears controller.

TurboGears Controllers

The TurboGears controller will serve as the broker between the object models we've just created and the templates charged with generating the final Web page. In the same acmehr subdirectory, open the controllers.py file and modify it to reflect Listing 3.

Listing 3 TurboGears controllers.py

from turbogears import controllers, expose, flash
import model



class Root(controllers.RootController):
     #Default home page
    @expose(template="acmehr.templates.welcome")
    def index(self):
        import time
        # log.debug("Happy TurboGears Controller Responding For Duty")
        flash("Your application is now running")
        return dict(now=time.ctime())



    # Return HR list, with filtering capabilities by Job type
    @expose(template="acmehr.templates.employees")
    def hr(self,job_id="ALL",sort="LAST_NAME"):
        job_list = model.Job.select()
        if (job_id == "ALL"):
            employee_list = model.Employee.select(order_by=[sort])
            return dict(employees=employee_list,jobs=job_list,selectedjob_id=job_id)
        else:
            employee_list = model.Employee.select('JOB_ID = \'' + job_id + '\'',order_by=[sort])
            return dict(employees=employee_list,jobs=job_list,selectedjob_id=job_id)



     # Returns a simple string, for a given employee_id/manager in text
    @expose("xml-rpc")
    def supervisor(self,employee_id=None):
        if (employee_id):
            manager = model.Employee.get(employee_id)
            # Check if the ID check out
            if(manager):
                manager_string = " " + manager.first_name + " " + manager.last_name + 
                  " - " + manager.phone_number + " "
                return str(manager_string)
            else:
                return "No employee with that ID"
        else:
            return "Please pass an employee ID"

Full article...