Simple Examples of ZSQL

Created by . Last modified on 2003/08/05.

Introduction [1]

One of the most useful aspects of Zope is its ability to interact with Database systems through Database Connectors and ZSQL Methods. The ZSQL manual provides a good starting point for understanding the interactions Zope, ZSQL, and Database Connectors and quickly get one writing simple ZSQL queries. Beyond that, unfortunately, the ZSQL programmer is mostly left to fend for him/herself. A cry particularly often heard is "how do I insert/add data to my database using ZSQL?"

This (currently very short) HOWTO is an attempt to document some of the methods I have used in getting ZSQL to perform various actions needed to fulfill requirements for project(s) I work with. In particular I am going to focus on a small, recent project to implement a web-based interface on top of a (very) simple database for holding customer work request information. The project is small, simple (did I say that already?) and also happens to be the only significant Zope application I have built to date, but at the very least it should provide a good starting place for discussion.

[2] To Start

Having a basic understanding of Zope will help tremendously. Check out the basic User Guides.

Hopefully, as you read this document you will be able to pick up the salient aspects of SQL necessary to do some database queries and modifications, but I would highly recommend you find a decent reference and keep it close. There do not seem to be too many on-line SQL references, but finding a reference in the bookstore should neither be too expensive or difficult. I really like my copy of The Essence of SQL by David Rozenshtein, ISBN 0-9649812-1-1 (I obtained mine through Amazon.com). It had more than enough information for the simple tasks I was trying to accomplish with this project.

The Small, Simple Project

The group this application was built for builds and supports automation systems for pilot plants (think of them as really, really small versions of large oil refineries). The stream of customer complaints and requests is nearly endless and always communicated informally- like over a phone call or through a quick grab in the hall. To get a better handle on this constant deluge a tiny Access database was prototyped to hold pretinent information from a request.

For a number of reasons not worth going into, it turned out that nearly no one used the Access DB in its raw form. Being recently indoctrinated to the Zope Way I suggested a quick web interface be constructed, so that at the very least the database would be easy to access (no pun intended, honest). I figured with Zope I could quickly prototype an interface, and I was almost right.

The Interface

There isn't really much to say about the interface. It certainly won't be winning any style awards, but I want to point out some of its functionality to provide some motivation for my later explanations (sorry, it's a writing thing, just bear with me, we'll get to something interesting real soon, I promise).

The web interface is designed to allow users to Browse, View, Edit, and Create request entries in the database. These are the four categories I built my ZSQL methods around. To View, Edit, or Create the user needs to have a Case ID number available, however to make things a bit more convenient I allow browsing by Plant ID as most users are more familiar with this form of designation.

Programming ZSQL

.. [Editing Rows in the Database]