Home arrow Zope arrow Page 9 - Zope And MySQL

Of Methods And Madness - Zope

It might seem intimidating, but hooking Zope up to a MySQL database is easier than you think. And once you've got the two talking nice to each other, it becomes even simpler to create dynamic, Zope-based Web applications. Take a look.

TABLE OF CONTENTS:
  1. Zope And MySQL
  2. Making The Connection
  3. Adapting To The Environment
  4. The Magic Of The Movies
  5. Visiting The Box Office
  6. Adding Things Up
  7. Submitting To The King
  8. Erasing The Past
  9. Of Methods And Madness
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 27
April 10, 2002

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
The final item on the agenda involves building an interface and methods to edit existing records in the database table. Again, I'll hark back to the "list" object created right at the beginning. You'll remember that this "list" object also contained a link to an "edit" object, which was passed a record ID (in much the same way as the "delete" object discussed on the previous page).

<font size="-1"><a href="edit?id=<dtml-var id>">edit</a> | <a href="delete?id=<dtml-var id>">delete</a></font>
Let's look at this "edit" object in detail:

<dtml-var standard_html_header> <dtml-if submit> <dtml-call updateMethod> <h2>Item edited!</h2> <p> <a href="list">View the entire collection</a> or <a href="add">add another title</a> <dtml-else> <h2>Edit DVD</h2> <dtml-in selectOneMethod> <form action="edit" method="POST"> <table border=0> <tr> <td>Title</td> <td><input name="title" width=30 value="<dtml-var name="title">"></td> </tr> <tr> <td>Director</td> <td><input name="director" width=30 value="<dtml-var name="director">"></td> </tr> <tr> <td>Cast</td> <td><input name="cast" width=30 value="<dtml-var name="cast">"></td> </tr> <tr> <td>Genre</td> <td><input name="genre" width=30 value="<dtml-var name="genre">"></td> </tr> <tr> <td colspan=2 align=center> <input type="hidden" name="id" value=<dtml-var name="id">> <input type="submit" name="submit" value="Edit DVD"> </td> </tr> </table> </form> </dtml-in> </dtml-if> <dtml-var standard_html_footer>
Again, this consists of both a form and a form processor, separated from each other by a DTML "if" test and the "submit" variable. Let's look at the form first:

<dtml-if submit> // snip <dtml-else> <h2>Edit DVD</h2> <dtml-in selectOneMethod> <form action="edit" method="POST"> <table border=0> <tr> <td>Title</td> <td><input name="title" width=30 value="<dtml-var name="title">"></td> </tr> <tr> <td>Director</td> <td><input name="director" width=30 value="<dtml-var name="director">"></td> </tr> <tr> <td>Cast</td> <td><input name="cast" width=30 value="<dtml-var name="cast">"></td> </tr> <tr> <td>Genre</td> <td><input name="genre" width=30 value="<dtml-var name="genre">"></td> </tr> <tr> <td colspan=2 align=center> <input type="hidden" name="id" value=<dtml-var name="id">> <input type="submit" name="submit" value="Edit DVD"> </td> </tr> </table> </form> </dtml-in> </dtml-if>
The first thing this form does is call the Z SQL Method named "selectOneMethod". This Z SQL Method is similar to the "selectAllMethod" discussed a few pages back, except that it includes an additional modifier - a record ID - in order to return a single record from the table rather than a list of all available records. Here's what it looks like:

SELECT * FROM dvd WHERE id = <dtml-sqlvar id type="int">
The record ID, obviously, gets passed to this method as an argument from the "list" object.

After the Z SQL method has been invoked, the fields in the resulting record set are converted into DTML variables and used in the form, via the <dtml-var> construct, to pre-fill the various form fields. Here's what it looks like:



Notice, from the DTML code above, that the record ID is again passed forward to the form processor via a hidden field in the form.

<input type="hidden" name="id" value=<dtml-var name="id">>
Once this form is submitted, the form processor takes over.

<dtml-if submit> <dtml-call updateMethod> <h2>Item edited!</h2> <p> <a href="list">View the entire collection</a> or <a href="add">add another title</a> <dtml-else> // snip </dtml-if>
As you can see, the form processor invokes a Z SQL Method, "updateMethod", to update the database with the new information for the selected record. Here's what "updateMethod" looks like:

UPDATE dvd SET title=<dtml-sqlvar title type="string">, director=<dtml-sqlvar director type="string">, cast=<dtml-sqlvar cast type="string">, genre=<dtml-sqlvar genre type="string"> WHERE id = <dtml-sqlvar id type="int">
As with the original "insertMethod", the UPDATE query above is dynamically constructed on the basis of form input variables.

Once the Z SQL Method is successfully executed, the resulting output looks like this:



And you're done! You now have a Zope interface to add, edit and delete records from a MySQL database. Wasn't all that hard, was it?{mospagebreak title=An Object Lesson} If you've been paying attention, the process of communicating with MySQL through Zope should be fairly clear. Assuming a working database connection, there are two basic things you need: Z SQL Methods to execute queries (these Z SQL Methods can be passed arguments so that queries can be constructed dynamically, as demonstrated in this article) and DTML Methods to invoke the Z SQL Methods (and pass them arguments, where required).

If you're used to PHP or Perl, this hard separation between methods and their invocation may be somewhat difficult to grasp at first. However, if you persist, you'll soon find that Zope's object-based approach to SQL is actually preferable to the looser approach in those languages. By encapsulating specific SQL functionality into objects, Zope immediately allows reuse of those objects - and, by implication, their specific functionality - across a Web site; this, in turn, makes it possible to package complex queries into a single object and invoke it wherever required, as many times as required.

One of the most obvious advantages of this approach becomes visible when you need to perform a series of related queries - for example, insert a record into table A, retrieve the ID of the inserted record, use that ID as a foreign key when inserting a record into Table B, and so on - multiple times. Zope makes it possible to create a generic Z SQL Method that accepts a series of arguments and performs - internally - as many queries as are needed to achieve the desired result. A user of this Z SQL Method can invoke it transparently, as many times as required, blissfully unaware of the activities that take place within it. Take it one step further: if changes are required, they can take place within the Z SQL Method, requiring no changes at all to the DTML Methods that invoke the object; this obviously makes maintenance easier.

Anyway, that's about all I have time for. I hope you found this article interesting, and that it gave you some insight into how Zope can be connected to external database systems. In case you need more information, these links should offer you a starting point:

A discussion of the ZODB versus a regular RDBMS, at http://www.zope.org/Members/anthony/sql_vs_ZODB

Zope-MySQL software, at http://sourceforge.net/projects/mysql-python

Zope-MySQL installation instructions, at http://www.zope.org/Members/alanpog/zmysqlda_steps

The official Zope Web site, at http://www.zope.org/

Zope documentation, at http://www.zope.org/Documentation

Till next time...stay healthy! Note: All examples in this article have been tested on Linux/i586 with Zope 2.5. Examples are illustrative only, and are not meant for a production environment. Melonfire provides no warranties or support for the source code described in this article. YMMV!

 
 
>>> More Zope Articles          >>> More By icarus, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ZOPE ARTICLES

- Creating Zope Products
- Plone Content Types With Archetypes
- Flat User Management in Zope
- Creating Basic Zope Applications
- Getting started with Zope for Linux and Sola...
- ZPT Basics (part 4)
- ZPT Basics (part 3)
- ZPT Basics (part 2)
- ZPT Basics (part 1)
- Exception Handling In DTML
- DTML Basics (part 4)
- DTML Basics (part 3)
- DTML Basics (part 2)
- DTML Basics (part 1)
- Using Zope With Apache

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: