Databases and Zope

Reuven shows you how easy it is to turn a simple Zope site into one that reads and writes data in a relational database.
Configuring psycopg

You can test psycopg by restarting Zope and adding a new product in the root directory. (Unfortunately, restarting Zope is the only way to tell the system that a new product has been installed.) The product you want to install is called Z Psycopg Database Connection in the “add product” menu in the upper right-hand corner of the Zope management screen.

Each database connection object allows you to connect to a single database on a single host, with a single user name and password. This means that if you have divided your information across two different databases (or two different database servers), you will need two connection objects.

When you choose Z Psycopg Database Connection from the “add product” menu, you are then asked to provide some basic details about this database connection. You must enter an ID (which must be unique within a folder) and a title (which will appear in the management screen), as well as a database connection string. This connection string tells Zope how to find and connect to a PostgreSQL server. In my office, the atf database sits on the PostgreSQL server on “databases”, and I can connect to it with the user “reuven” and no password. Thus, I enter the following connection string:

host=databases dbname=atf user=reuven

You can leave the rest of the items in their default state if you wish. Click on the Add button, and you will be returned to the folder in which you added the new connection object.

Clicking on the connection object displays several Zope tabs that you can use to administer the database connection. The four most interesting ones are:

  • Status: this tab tells you whether the database connection is open (i.e., whether it is connected to your PostgreSQL server). It also allows you to close the connection.

  • Properties: you can modify the properties that you set when you initially created the database connection object. This is particularly useful if the database is moved to a different server or if you change the password necessary to access it.

  • Test: you can test the database connection by sending any SQL query to it. Of course, the query must be valid; if you send illegal SQL or try to address a table that does not exist, then you will get an appropriate error from the PostgreSQL server. For example, you can enter SELECT * FROM pg_database;. You can enter any SQL via this box, which can be convenient for testing your database when you have no direct Telnet or SSH connection. If you enter an INSERT or UPDATE query, Zope will indicate that the query didn't return any results. As always, it's a good idea to avoid using SELECT * except in trivial examples to avoid being surprised by the order or name of columns in the result set.

  • Browse: you can look through the tables in a PostgreSQL database with the browse tab, which displays a Zope tree-style list of tables and fields.

ZSQL Methods

Now that we have a database connection, we can create one or more ZSQL methods. Each ZSQL method is a single SQL query (with variable arguments, if you want) that works with a connection.

Let's create a ZSQL method that lets us add a new name into an address book. Of course, this means that we must first have an appropriate table defined in our database. We can create the table by sending the content of Listing 1 to PostgreSQL, either in the test tab for our database connection or by using the traditional psql command-line interface.

Listing 1. Creating a Table

If you try to add a ZSQL method when no database connection is available, Zope will display an error message, complaining that it could not find any suitable database connection.

Zope's built-in system of “acquisition” means that a ZSQL method can use any database connection above it in the Zope hierarchy. A user thus can choose a different database connection for each method, making it possible to integrate information from different databases in a single application—or to migrate your web site from one brand of database to another.

To create a ZSQL method, go to the folder in which you created your database connection and choose “ZSQL method” from the “add product” menu. You will be asked to enter several items: an ID (which must uniquely identify your object in this folder), a title (which will be visible in the management interface), arguments (which we will discuss in the next section) and finally the SQL itself. Your SQL query can be as simple or as complex as you like and can perform an INSERT, UPDATE or DELETE.

Once you have added your ZSQL method to the system, clicking on it brings up a number of Zope tabs. One of these tabs is labeled test, and (as you might expect) it allows you to test the query. If your query has arguments, then you are asked to enter them in an HTML form. If not, you are simply asked to click on the Submit Query button. This returns, as with the test tab from our database connection object, an HTML table describing the results of our query—or a message indicating that our query returned no results.

We can create a ZSQL method for each query we wish to perform. While this might seem a bit odd, it's actually a very flexible and elegant solution that I've grown to appreciate more and more. If I expect to perform 20 queries in a web application, I can put each of them in a separate ZSQL method and then invoke those methods from within DTML pages.

Within a DTML page, we can retrieve results from a ZSQL method by naming it in a <dtml-in> tag. For example, I create a ZSQL method that implements the following query:

    SELECT first_name, last_name, phone_number,
           fax_number, cell_number
      FROM AddressBook
  ORDER BY last_name, first_name

If I give this ZSQL the name “names-and-phone-numbers”, then I can invoke it from within a DTML document with the code in Listing 2. In just a few lines of DTML, we have successfully managed to produce a simple (but useful and flexible) ZSQL method. But how does it work?

Listing 2. Invoking ZSQL from within a DTML Document

When Zope receives a request for this DTML document, it parses the DTML and executes each of the tags contained within. The <dtml-in> loop construct expects a sequence as an argument; in this particular case, the sequence is the result from invoking the names-and-phone-numbers method. The <dtml-in> tag also assigns one variable for each column in the returned result set. This is how we can use the <dtml-var first_name> tag to print the user's first name; Zope automatically assigns the value of the first_name column to a variable called first_name.

In order to avoid printing unnecessary and blank lines, we use <dtml-if> to check that we did not receive a NULL or empty value back from PostgreSQL.



Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

query from a python script

Anonymous's picture

Is it possible to use this connection product in order to
submit an arbitrary query from a python script from the zmi