For one of our current projects we are using Python as an interface to a database as well as using Python to write CGI scripts containing data from our database. The purpose of this article is to shed some light on how we use Python to accomplish these tasks. We are connecting to a Helix database to gather our information. The connection to the database is made via AppleEvents, although we have a Python wrapper over this that allows us to gain access to the database through Python.

One interesting aspect of this project is that we are controlling the flow of our scripts from an external database. We have one relation (table) in this database that controls everything. Its records tell the scripts where to get their information, and we retrieve these records as a list from the database. Once we have the information in our list, we simply loop through each item in the list and check to make sure that the key is the key we are looking for. For instance, if we were doing Login step #1, we would look for the key “Login.1”. Once we find a record with this key, we use the other fields in the list (database name, user name, password, table name, and view name) as a path to where to process the step. This stored information (except for they key and the function name) is used as the connection information for each step. An example record would be: Login.1 (Key) Database name.hlx (Database name) user1 (User name) testing (Password) loginInfo (Table name) step1 (View name) function1 (Function to call, if needed) After processing this record (i.e. sending/retrieving data and a possible function call), we can continue looking for the next task to accomplish. By searching through the records by name, we are able to process only the needed steps, without hitting any unnecessary ones. Of course this same sort of concept could be used with other web applications using languages such as PHP with an SQL database. Here you would simply need to define a table that contained all your control information in the appropriate format. To get the information we listed above you would have an SQL statement similar to: SELECT 'Key', 'Database Name', 'User Name', 'Password', 'Table Name', 'View Name', 'Function To Call' FROM 'Control Database' From here you would proceed to process each record returned, and then pass each individual piece of information to your next SQL statement. Using this approach had really never occurred to me before when I coded in SQL and PHP, but it can be helpful, because it keeps your code a little cleaner. It also helps the code to be more robust, because, even if the database information changes, the calls wonâ??t have to change since they are generic in form. Also, keeping all the step-by-step information in one location gives you a nice spot to view it, and it helps to keep everything on track when you are considering adding or deleting steps in the process. This entire concept can be translated into PHP - pulling a table name from a control database, and it’s fairly easy to integrate this type of dynamic SQL information into a query - in PHP one could:

"SELECT * FROM '$tablename' WHERE id = '$userid'

A change that we added fairly late in the development process was for our control relation to include a function name field. This are only used for “pre” and “post” tasks - tasks that should happen before or after the main body of the script runs. Each function name stored in the database corresponds to a function in a common module that we import into each of our scripts. When the information is retrieved from the database, we use the Python’s .__getattribute__() function to return the object in our Common module with the same name as the value in our function name field - this will return a callable function. By doing this, we are able to easily call the function without knowing it’s name before calling it. This approach also allows us to add pre and post tasks to the entire solution very easily, by modifying the common module.

This feature is quite useful to us, since we have some tasks that need to happen at every script’s beginning and end. We don’t want to have to worry about the function names for each task, and we also don’t want to worry about how many of these functions need to be called. Instead of adding these functions to multiple scripts, we can store all the functions in one module, and then we only need to add a minimal amount of code to our scripts to get and call each specific function. Again, using an outside source as our main form of control for our scripts allows for greater flexibility. We can also easily add pre and post tasks as they are needed, without having to greatly modify every script. For our HTML output we use HtmlTmpl (HTML Template), which is a template-based HTML parser. We simply have to import it into our scripts. HTMLTMPL allows us to generate templates that can be highly customized. We are able to keep most of the HTML in templates, then use HTMLTMPL to insert changing values into the template (for tables, lists, etc.) HTMLTMPL uses template variables, which can be used to pass the data from our scripts to the templates. For instance, would be a variable called name, which could contain any sort of data to be included and output in the template. This works quite well when you need to print HTML tables that contain customer-specific information, such as name, address, phone number, etc. Each piece of information can be contained in a template variable, and then it can be output however and wherever you want it to be in the rendered template.

A sample HTMLTMPL page containing personal welcome and an introductory message would look something like this:

`

Hello, <TMPL_VAR firstname> <TMPL_VAR lastname>.

<TMPL_VAR intromessage>

`As you can see we pass the user’s first last name in template variables when we process the template. The introductory message is also sent to the template at processing time.

The templates can also perform if statements, which are quite useful when we have to produce HTML different browsers. When we output the template, we simply pass a template variable with a value of 0 or 1 that will let the template renderer know which HTML should be output.

These templates provide a nice, concise way for us to print out our HTML without having to use all sorts of awkward looking print statements in our code. We use a wrapper function in one of our local modules that handles the processing of the template, so we simply need to pass to that function what template file we want to render, what variables to include in that template, and what data should be stored in each variable. The advantage of this wrapper function is that we can set certain template variables that will always be the same, such as the path to the appropriate CSS file, or the path to our CGI directory, which we use in our links. After passing in all our information to our wrapper function, the code in HTMLTMPL will take care of the rest for us.

We use another tool that has been mentioned here before, CGIWrap. Although CGIWrap is not a Python-specific tool, it aids us in the running of our Python scripts on our server. CGIWrap will basically allow scripts to be run as a user that is different than the user running on our web server. In our case, we need to run as the logged-in user, so we can send Apple Events to our database.

After having spent a fair amount of time on this project there is one big thing that really has become noticeable to me. When I’ve heard presentations about using python to build web interfaces there has always been mention of some extra (sometimes heavy-weight) tool that needs to be used to accomplish the process. Sometimes you even need more than one tool, or the tool you need requires lots of other tools to make it work. For instance you will need one tool to do all of the HTML formatting and processing, and then sometimes you will need special software installed on your server to make sure that everything works right. Plus, most of the time, it seems that you have to learn a whole new syntax structure instead of using one that you are already familiar and comfortable with. The advantage we have here is that our extra tools are minimal. In the case of HTMLTMPL, we don’t even really have to use it, but it does make our job much easier by isolating the “view” part of the page from the “logic” behind the scenes.

When we put all these tools together we are able to create a web site where the user can check on their user-specific information. CGIWrap will run the initial script for each page, which will call a function to print out the initial template for that page, and then we will use Python to communicate back and forth with our database. After we have the necessary data, we will again call our template function to print the rest of the HTML for the page. The nice thing with our templates is that we can add HTML to the page whenever we need to. We can print out the beginning of the page without knowing what the rest of the page will look like. Once a user clicks a different link, the entire process begins again. I hope that by reading this article you have been able to see the power of Python. It is being used as the controlling force of our entire web interface process. From gathering the data from our database to outputting the HTML for our various pages, it truly is the glue that brings each piece of the process together and helps them to function correctly.