Using Oracle database for Python data persistence (1)

Despite the rapid popularity of Python among developers, Oracle database has long been the best enterprise database. Combining the two in an effective way is an interesting topic, but it is actually a real challenge because both of them have to pay a lot.

Despite being warned, this article does not outline the most outstanding Python and Oracle database features, but provides a series of independent examples. This article uses an example to show you how to use these two technologies in a complementary way. In particular, this article will guide you through the PL/SQL stored procedure (orchestrating its calls in a Python script) to create Python applications supported by Oracle, this application implements business logic in Python and databases.

As you will learn in this article, even the lightweight Oracle Database 10g quick edition (XE) can be effectively used as the database backend of data-driven Web applications, its front-end layer is built using Python. In particular, Oracle Database XE supports Oracle xml db, which is a set of Oracle Database XML technologies that are often needed to build Web applications.

Sample Application

When users use your applications, collecting information about user operations becomes a popular mechanism to receive user feedback. In general, compared to any survey that allows users to clearly express their preferences, click tracking tools incorporated into online applications can provide you with a large amount of information about user preferences.

Let's take a simple example. Suppose you want to select the title of three latest Oracle Technology Network (OTN) articles from the "OTN-new article RSS" page, and put these links on your site. Then, you want to collect information about the number of times the user follows each link on your site. This is what our example will do. Now, let's try to figure out how to implement all these features. First, you must determine how to distribute business logic between application layers. In fact, deciding how to distribute business logic between application layers may be the most challenging part of the database-driven application planning. Although there are usually multiple ways to execute business logic, your work is to find the most effective method. As a general experience, when planning database-driven applications, you should carefully consider the implementation of key data processing logic in the database. This method can help you reduce the network overhead related to sending data between the Web server and the database, and reduce the burden on the Web server.

Apply all these theories to our example. For example, place the burden of getting detailed information about Articles inserted into the database to the stored procedure created in the database, in this way, the Web server no longer needs to process tasks related to maintaining data integrity. In practice, this means that you do not have to write specific Python code, which is used to track whether there are records related to the articles whose links are clicked in the database. If not, insert the record, then retrieve all the required details from the "OTN-new article RSS" page. By allowing the database to track such things on its own, you can get a solution with higher scalability and less error-prone. In this example, the Python code obtains the article link only from the RSS page and sends a message to the database when the user clicks an article link.

Figure 1 provides a graphical description of how sample components interact with each other and how they interact with external sources.
 

Figure 1: an advanced view of how the sample application works.

The rest of this article describes how to implement this sample application. For a brief description of how to set and start this example, see the readme.txt file in the root directory of the sample code.

Prepare the Work Environment

To build the example discussed here, you need to install the following software components (see Downloads portlet) and make it work properly in your system:

Apache HTTP Server 2.x

Oracle Database 10g quick Edition

Python 2.5 or later

Mod_python Module

Cx_Oracle Module

For details about how to install the above components, refer to another OTN article "Building a rapid Web development environment for Python Server Pages and Oracle" (Author: Przemyslaw Piotrowski ).