ZSQL Results How-To

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

This how-to has been tested against Zope 2.5

Introduction

Sometimes is very useful to manipulate the result of a ZSQL method programmatically. While the internal mechanisms of Zope are the perfect tools for displaying and browsing the result, sometimes it is necessary to investigate deeply. This How-to provides a quick overview of the object returned by ZSQL methods, the Results object.

I will assume that the reader has a basic knowledge in Zope, Python and SQL. If not, there are many documents on the Net that can explain more than me about these subjects.

The Results class

Every time a ZSQL method is called, a Shared.DC.ZRDB.Results object is returned. This object contains various information which are quite interesting. Here is the list

• names: a list of all the field names retrieved form the executed query
• data_dictionary: a dictionary containing the structure of the table. To each key is associated a dictionary describing the field information
• dictionaries: a list containing the result of the query. Each item in the list is one record. Each record is described by a dictionary, where the key is the field name and the value is the field value.

Lets review those information in more detail.

Playing with ZSQL

How to use ZSQL from DTML is very well documented, and therefore it is unuseful to repeat this here. Please see the Zope Book about this subject. What is really interesting for us is accessing the ZSQL method from scripts, or accessing their properties from DTML.

Before we can try out some examples, we have to set up a little test environment (I do not like too theoretical things). What we need is:

• a ZSQL Connection object called MySQL_connection
• a table called Contacts with the following structure:

Field	Type	Attributes	Null	Default	Extra
firstname	varchar(25)		No
lastname	varchar(25)		No
address	varchar(50)		Yes
country	char(2)		Yes
zipcode	varchar(10)		Yes
tel	varchar(25)		Yes
fax	varchar(25)		Yes
email	varchar(25)		Yes

• a ZSQL Method called retrieve_all_contacts, which is nothing else then a simple select * from contacts

Running the query

Let's run the query from a simple Python script. The script text should be as follows:

rec = context.retrieve_all_contacts()
print "-" * 80
print "Table field names"
print "-" * 80
for name in rec.names():
    print name

When we run it, we will be returned:

--------------------------------------------------------------------------------
Table field names
--------------------------------------------------------------------------------
firstname
lastname
address
country
zip
tel
fax
email

Pretty simple isn't it? As you can see, you are now able to inspect the field names returned from you ZSQL method. This is not that much useful as the data_dictionary. If we modify the program, we will have a clearer view of the table structure:

print "-" * 80
print "Fields definitions"
print "-" * 80
dict = rec.data_dictionary()
for key in dict.keys():
    print dict[key]

The result will be:

--------------------------------------------------------------------------------
Fields definitions
--------------------------------------------------------------------------------
{'name': 'email', 'type': 't', 'null': 1, 'width': 18}
{'name': 'lastname', 'type': 't', 'null': 1, 'width': 9}
{'name': 'country', 'type': 't', 'null': 1, 'width': 2}
{'name': 'address', 'type': 't', 'null': 1, 'width': 18}
{'name': 'firstname', 'type': 't', 'null': 1, 'width': 7}
{'name': 'zip', 'type': 't', 'null': 1, 'width': 5}
{'name': 'fax', 'type': 't', 'null': 1, 'width': 0}
{'name': 'tel', 'type': 't', 'null': 1, 'width': 10}

As you can see from the sample above, is now quite simple to inspect what the query returned and eventually react correctly. The dictionary contains many key:value pairs, where each key is the field name and each value is another dictionary containing the information about the field structure. If you know databases, this should not be a problem for you to understand.

The next step is to retrieve the data from the Result object. As you may have already guessed it, they are contained in the abovementioned dictionaries list. Let's have a look at it:

print "-" * 80
print "All records"
print "-" * 80
recordset = context.retrieve_all_contacts().dictionaries()
rownum = 1
for record in recordset:
   print "Row %d" % (rownum)
   rownum = rownum + 1
   for key in record.keys():
      print key, "=", record[key]
   print "-" * 80

The result is how you should expect it:

--------------------------------------------------------------------------------
All records
--------------------------------------------------------------------------------
Row 1
email = spinwing@inwind.it
lastname = Vedovelli
country = IT
address = Via PP, 20 - Milan
firstname = Stefano
zip = 20100
fax =
tel = 0233333333
--------------------------------------------------------------------------------

Each item in the list is a dictionary representing a record, therefore you can ask the Result object for how many records you have just by calling len(rec.dictionaries()).

Using the Result object

Now we know how the Result object is made and how we can address it. There are many possible uses; depends what your application have to achieve.

For instance, one of the normal SQL habits is to ask the database if a record exist for a given value. For instance, you want to write a method that returns true if the record exists, false if not. You need:

• A ZSQL Method named retrieve_contact like defined as select * from Contacts where <dtml-sqltest lastname op=eq type=string>
• Python script named isContact.

This script accepts one parameter called contactName. The body of the script will be:

res = context.retrieve_contact(lastname=contactName)
return len(res.dictionaries())

Now it is easy to test if the record exist. You can of course make use of the <dtml-in><dtml-else> feature, but that feature you can't call it from Python, not as far as I know at least. Now the DTML method can look like:

<dtml-var standard_html_header>
<dtml-if expr="isContact('vedovelli')">
    if true......
<dtml-else>
    if false.....
</dtml-if>
<dtml-var standard_html_header>

 

Another Example (by David Hook)

With the ability of accessing the ZSQL method programmatically, we can test test conditions like zero records. Here is how the routines were used ....

python script: IsValidEmployee
Parameter: Employee_Number

res=context.sqlGet_Employee_Table()
valid_employee = 1
if len(res) == 0:
   valid_employee = 0
return valid_employee

Sql Method: sqlSearch_Employee_Table
Arguments: Employee_Number

SELECT tblMstr_Employee.Employee_Number
   FROM tblMstr_Employee
   WHERE (());

DTML: Employee_Search

<h3><p>Enter an Employee Number and then click check</p></h3>
<hr>
<form action="Employee_Results" method="get">
<table bgcolor=#6699cc width="100%">
<tr>
<td><b>Enter Global Employee Number and then click Check</b><p>
<b>Global Employee Number:</b><input name="Employee_Number" value=""><br>
<input type="SUBMIT" name="SUBMIT" value="Check"></td>
</tr>
</table>
</form>

DTML: Employee_Results

<dtml-if expr="IsValidEmployee('') < 1">
 Not Valid
<dtml-else>
 Valid
</dtml-if>

How it works

Employee_Search is displayed to allow the user to enter an Employee Number. Once check is clicked, Employee_Result is displayed and passed the variable Employee_Number.

Employee_Result uses IsValidEmployee(dtml-var Employee_Number) as the condtion for a dtml-if. The python script, IsValidEmployee(dtml-var Employee_Number) uses res= context.sqlSearch_Employee_Table to point to the results of the SQL method, sqlSearch_Employee_Table. len(res) gives the number of rows in the result table.

This will be 1 if the Employee_Number exists or 0 if not. IsValidEmployee defaults to valid_employee=1 (The employee exists). If the test If len(res)==0 is true then valid_employee is set to 0 (Employee does not exist). The value of valid_Employee becomes the value returned by IsValidEmployee and tested in Employee_Results. Employee_Results then evaluates the dtml-if and displays the appropriate messages.

A final note

In this how-to, I have only scratched the surface. Therefore I please the reader to mail me for new ideas, clarifications and suggestion. They are more than welcome.