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.
ZSQL Arguments

It's obvious how we can use ZSQL methods and DTML to perform the same query each time. But if we want to modify our basic query each time it is run, we will need to define one or more arguments.

For example, if we want to retrieve information about someone based on their last name (or a portion thereof, using SQL regular expressions), we will want to define the following sort of ZSQL method:

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

In DTML, we can replace the XXXXXX with the <dtml-sqlvar> tag, which automatically handles quoting for us. We must name the SQL variable that we are using, as well as indicate its type:

    SELECT first_name, last_name, phone_number,
           fax_number, cell_number
      FROM AddressBook
     WHERE last_name LIKE <dtml-sqlvar name_sqlregexp
  ORDER BY last_name, first_name
In order for the above ZSQL method to work, we must name an argument (name_sqlregexp) in the appropriate text box when creating our method. Zope will take the value of that variable, place it inside of our query and retrieve the results.

We can put even more of the burden on Zope if we use a <dtml-sqltest> tag, which operates similarly to <dtml-sqlvar>:

    SELECT first_name, last_name, phone_number,
           fax_number, cell_number
      FROM AddressBook
     WHERE <dtml-sqltest name_sqlregexp op="like"
  ORDER BY last_name, first_name

If we have stored the above query in a ZSQL method named select_by_last_name, then Zope can automatically produce skeleton DTML documents that allow users to enter search terms and see results. To do this, simply choose the “Z Search Interface” product from the “add product” list. You will be able to choose from all of the searchable objects on the system, including the ZSQL method that we just created (select_by_last_name). Choose this, and give the report an ID (I used search_by_last_name). Also give a name to the “input ID”, which is a fancy term for the HTML form that will be used to send input to search_by_last_name. (I named it search_by_last_name_form.) In modern versions of Zope, you also must indicate whether you want the system to create DTML methods or page templates; we want the former.

Clicking on Add creates two new DTML methods in the current folder, corresponding to the names that you entered in the form. Clicking on the “input ID” URL will present a simple HTML form into which you can enter an SQL regular expression. Clicking on the submit button will send your query to the search_by_last_name DTML method, which will in turn invoke our ZSQL method (select_by_last_name), which will then pass along our query to PostgreSQL. PostgreSQL returns results to select_by_last_name, which returns a result set to search_by_last_name, which then displays them in our web browser.

You can, of course, modify the DTML methods that are created to match the style of your own site. You also can copy the DTML that Zope created automatically into your own DTML pages, using them as examples of how to create your own database queries.


The only major task left is the implementation of an INPUT query, which adds items into the database. Luckily, this is rather easy: we create a ZSQL method that inserts a row into the database. Then we create a DTML document that submits its HTML form elements to another DTML document. This second document invokes <dtml-call> to our ZSQL method. Voilà--our record is inserted into the database.

Listing 3 shows the ZSQL method that we need, which I named insert_address_data. Now we'll create a simple DTML document, which will contain an HTML form (see Listing 4).

Listing 3. The ZSQL Method insert_address_data

Listing 4. DTML Document Containing HTML Form

Finally, we create the DTML document insert_address that receives input from insert_address_form and passes its arguments along to the ZSQL method insert_address_data:

<dtml-var standard_html_header>
<h2><dtml-var title_or_id></h2>
    <dtml-call insert_address_data>
    <p>Sorry, but the INSERT didn't work.</p>
    <p>Successfully inserted!
<dtml-var standard_html_footer>

Users can now insert information into our PostgreSQL table using an HTML form pointed at insert_address_form, and they can retrieve it using search_by_last_name_form. It's rather impressive that we can do so much in so few files—and even more so that we didn't have to touch a text editor once in order to get this to work, but that it could all be done using nothing more than our web browser.



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