DOWNLOAD Oracle Database XE Python

TAGS python, scripting, All

Using Python with TurboGears

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.

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

• 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

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.

$ 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.

Figure 1 TurboGears project welcome screen

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"  

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.

#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)

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.

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"
                            

1. The "import model" statement at the top, which grants you access to the model.py file, which contains the model objects you want to manipulate
2. The hr business method, which takes three input parameters: the selfobject representing the request, as well as the job_id and sort parameters, the latter of the two having predefined default values in case the incoming request does not provide them. Once inside the method body, you will find model.Job.select(), which takes care of selecting all the Job objects from our model, followed by a conditional that Tchecks the value of the job_id parameter.

   Inside this conditional, you will also find a similar declaration in the form model.Employee.select(order_by=[sort]), which takes care of selecting all the Employee objects from our model. Note, however, that this declaration has anadditional order_by=[sort] declaration, indicating that the objects should be ordered with the sort value, which, by default, has a LAST_NAME value and refers to an actual field in the Employee object, hence our Employee database table. In a similar fashion and also inside the conditional, you can find model.Employee.select('JOB_ID = \'' + job_id + '\'',order_by=[sort]), indicating in this case that only those Employee objects matching a JOB_ID by the given job_id parameter should be selected.

3. Capping off our hr method is the return statement, which declares a Python dictionary containing both our processed Employee and Job object lists as well as the selected job_id value. Which brings us to the question of where these values are returned to. Well if you gaze over to the top of the method, you will find the declaration @expose(template="acmehr.templates.employees"), which indicates that the values returned by this method should be exposed inside the template named acmehr.templates.employees, the template being, of course, of the HTML kind that will be presented to an end user. But before we get to the actual template, let's address the final controller method supervisor.

Note that this last method returns a string, because the method will be invoked exclusively in an asynchronous fashion (a.k.a. Ajax), returning its results in plain text for a browser to consume. Similarly, note that this method is not exposed to any template but, rather, uses @expose("xml-rpc") to indicate its XML-RPC or RESTful nature, which is common in Ajax designs.

Finally, the naming and parameter conventions our controller methods use also have relevance with respect to their access URLs. Both methods are placed inthe Root class, which indicates that the methods will be accessible inthe root directory of our Web server, with the methods' names further serving as a virtual directory. Attempting to access http://localhost:8010/hr/ will invoke the hr method, and requesting http://localhost:8010/supervisor/ will return the results obtained by the supervisor method.

Furthermore, TurboGears also uses a URL subdirectory convention to detect parameters passed on to the method. So if, for instance, you wanted to pass a job_id and sort value to the hr method, you could invoke the following URL: http://localhost:8010/hr/PU_CLERK/FIRST_NAME, which would call hr with a job_id value of PU_CLERK and a sort value of FIRST_NAME.

This last process works by associating each subdirectoryin the same order as the method's input parameters, providing a simple approach to the classical URL parameter strings by using the ? and & characters in the form http://localhost:8010/hr?job_id=PU_CLERK&sort=FIRST_NAME, which also works in TurboGears, of course. This convention shortcut is simply something you should be aware of because we will use it in our next section for creating templates.

Having reviewed TurboGears controllers, you're ready to put it all together to explore TurboGears views.

TurboGears Views

For its views, TurboGears relies on the Python Kid template engine as well as other projects such as Mochikit, which provide easy out-of-the-box Ajax features. Because we can explore only so many features, such as the TurboGears modules you've been exposed to—SQLAlchemy in the database tier and CherryPy in the controller tier—in this limited space, the following is a very basic template that makes use of the aforementioned Python modules. Figure 2 illustrates the rendered template, and Listing 4 contains the template code itself.

Figure 2 TurboGears-renderedtemplate for Acme HR

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:py="http://purl.org/kid/ns#"
    py:extends="'master.kid'">
<head>
<meta CONTENT="text/html;charset=utf-8" http-equiv="Content-Type" py:replace="''"/>
<title>Welcome to TurboGears</title>
<script type="text/javascript">
function filterHR(form) {
         // Construct URL to get filtered results
         tourl = "${std.url('/hr')}/" + form.options[form.selectedIndex].value
         // Redirect to constructed URL for filtered results
         location.href = tourl
}

function supervisorHR(employee_id,supervisor_id) {
     // Check if we have a supervisor
     if(supervisor_id) {
        var req = MochiKit.Async.getXMLHttpRequest();
        // Construct URL to get supervisor details
        req.open("POST", "/supervisor/" + supervisor_id);
        // Make AJAX call to get supervisor details
        var d = MochiKit.Async.sendXMLHttpRequest(req);
        d.addBoth(function(transport) {
             var supervisor_detail = MochiKit.DOM.getElement("managerCellFor"+employee_id);
             supervisor_detail.innerHTML = "<b>"+ transport.responseText + "</b>"
        });
    } else {
        // It's the big boss no supervisor, just modify the cell no callback needed
        var supervisor_detail = MochiKit.DOM.getElement("managerCellFor"+employee_id);
        supervisor_detail.innerHTML = "<b>No direct supervisor</b>";
    }
}
</script>
</head>
<body>
<div>
<h4 style="text-align:center">
   Displaying <form>
    <select onchange="filterHR(this)">
    <option value="ALL"> All Positions </option>
    <option py:for="job in jobs"
            py:content="job.job_title"
            py:attrs="dict(value=job.job_id,selected=(job.job_id==selectedjob_id and 'selected' or None ))"/>
    </select>
   </form>
</h4>
   <table align="center">
    <tr>
    <th><a href="${std.url('/hr')}/${selectedjob_id}/FIRST_NAME"> First Name </a></th>
                              


    <th><a href="${std.url('/hr')}/${selectedjob_id}/LAST_NAME">Last Name</a></th>
                              


    <th>Phone</th><th>Direct Supervisor (Manager)</th>
    </tr>
    <tr py:for="employee in employees">
    <td>${employee.first_name} </td>
    <td>${employee.last_name}</td>
    <td>${employee.phone_number}</td>
    <td style="text-align:center" id="managerCellFor${employee.employee_id}">
      <a href="javascript:void(0)" onclick="supervisorHR
                              


         ('${employee.employee_id}','${employee.manager_id}')">Click to see</a></td>
     </tr>
   </table>
</div>
</body>
</html>
                            

Next you need to create your template in a file named employees.kid in the acmehr/templates subdirectory—recall our controller @expose value of template="acmehr.templates.employees"—and place Listing 4's contents inside it, as detailed below.

The first thing you should be aware of in our project template is that it inherits certain layout behaviors from yet another template. Take a look at the root <html> tag and the attribute py:extends="'master.kid'", which indicates that prior to rendering the template, TurboGears will inherit the layout behaviors of the master.kid template—which, in this case, include header content, footer content, body layout, and other behavioral characteristics.

Now let's move on to the main table in the template, right around the area where you will find the <tr py:for="employee in employees"> idiom. You'll recall from our hr controller method that the return method included an employees list that would be made available to the template, so what this last line will do is create a for loop on the employees list, making each iteration available in a variable named "employee."

Inside this last loop, you will find lines such as

<td>${employee.first_name} </td> 

<td>${employee.phone_number}</td>

In addition to the main table, you will also find two sections in the template that allow an end user to either sort or filter the employee values. Looking over the header cells in the main table, you will notice that both the first and last name values are wrapped around an HTML link in the form:

<a href="${std.url('/hr')}/${selectedjob_id}/FIRST_NAME">

The filtering mechanism for the employee table works in a similar fashion to the sorting mechanism. Above the main table, you will find an HTML select list containing all the job titles available at Acme. Note how make we make use of the same idiom used in our main table, py:for="job in jobs", except that we are using it to iterate over the jobs list that was added inside the controller.

This HTML select list containing job titles is also equipped with the onchange="filterHR (this)" attribute, which enables it to trigger a JavaScript function named filterHR when an end user changes the list's selection. If you scroll up to the top, you will notice that filterHR extracts the selected job title, builds a URL, and reinvokes the hr controller method using location.href, in effect rerendering the template with a newly acquired employee list filtered with the provided parameters.

Both the filtering and ordering functions in the template require a complete screen refresh to be rendered, so let's illustrate the last piece of functionality present in this template, which enables data to be loaded asynchronously, a process better known as an Ajax design.

Using this Ajax design means that end users are presented with less data in the main interface, but if they so desire, they can single-click to obtain the remaining employee information without experiencing a screen refresh. Figure 3 shows the main interface with some cells rendered in this form.

Figure 3 TurboGears-rendered template for Acme HR, with applied filter and Ajax calls

In the last cell of our main table, we provide an HTML link pointing to a JavaScript function named supervisorHR, which takes two input parameters: the employee_id displayed in the row, and its corresponding manager_id.

If you inspect the supervisorHR JavaScript function, near the top of the template, you will notice that we start by declaring a conditional that checks whether a supervisor_id was provided, which can then fall into the following scenarios:

• If you have a supervisor_id — An XmlHttp/Aja request is constructed by use of the MochiKit library, with the invocation URL pointing to our supervisor controller function, using supervisor_id as the input parameter, which (as pointed out earlier) will return the supervisor's name and telephone number in plain text. Next, a callback method is declared in which the Ajax response is rendered over the employee ID cell making the Ajax call, a process that is achieved with another MochiKit function, which overwrites the cell's contents with the Ajax response (available in transport.responseTex) using the cell's id value.
  
  
• If we don't have a supervisor_id — There is no need to perform any XmlHttp/Ajax request, because the employee has no supervisor. In this case, we would also overwrite the employee ID cell, using the same MochiKit function as above, but instead place a hard-coded text message indicating that the employee has no supervisor.

TurboGears Production Issues: Oracle/Python Unicode and mod_python with Oracle HTTP Server

TurboGears and Python are both built to represent strings in a Unicode format, often abbreviated as UTF-8, for Unicode Transformation Format. Under most circumstances, this often-desirable format for representing strings works without much trouble, but in some scenarios, it can become a very obscure error to correct.

A Unicode error in TurboGears can range from a mangled character represented with a ? in the final Web layout, to a complete stoppage in an application if the conflict occurs deep enough in the stack.

If you work exclusively with the English language, you can probably skip ahead. If, however, you work with certain European or Asian languages, you may need to tweak your TurboGears installation to manage Unicode correctly.

The problem with using certain languages in TurboGears and Python lies in the special characters they contain. Characters such as é , ñ , ö or ç are often represented in their own native encodings, creating a conflict with the default Unicode format that TurboGears and Python use.

To avoid such errors, refer to Table 2, which contains a series of steps, classified by tier, recommended for supporting UTF-8.

Another element that has important consequences once your TurboGears projects start to grow is the backing Web server. In our Acme HR sample project, we made use of a very simple test server integrated into TurboGears, but this is in no way a Web server designed to take on the workload of a production site.

For production deployments, it is recommended that you use the Apache Web server and mod_python module, the former being the Web server Oracle HTTP Server is based on and the latter a module necessary for invoking Python scripts from within this Web server.

This process entails a few configuration changes to your Oracle HTTP Server configuration file (httpd.conf) as well as the installation of mod_python, including the creation of a bootstrapping script necessary for your project to be served via Oracle HTTP server. You can find these steps thoroughly documented at docs.turbogears.org/1.0/mod_python.

Conclusion

As you've hopefully experienced in our brief project, TurboGears provides an excellent choice for Python-istas looking for a comprehensive framework on which to create Web-enabled applications backed by an Oracle database.

Similarly, for those organizations using Oracle technology, TurboGears offers yet another means by which a data store can be exposed to the Web, adding to the growing body of middleware languages such as Java, PHP, and Ruby and, in the process, allowing proficient in-house Python-istas to stay within their most productive environment and bring Oracle data to the Web.