[ZPT] Plone conference talk - ideas please!

[Evan Simpson]

Mitch Pirtle wrote:
> I have not figured out yet how to use ZSQL and ZPT, and cannot find any
> documentation that puts it all together.  If someone, anyone can walk me
> through this process then I'll happily provide copious documentation for
> this seemingly FAQ that goes unanswered...

I presume that you're talking about calling ZSQL Methods from ZPTs and 
using the resulting records?  Here's some pointers:

There are three basic ways to call a ZSQL Method:

1. Call it with no arguments.  If it has any parameters declared, it 
will search for them in the request object, use the default value if it 
doesn't find them there, and raise an exception if there's no default 
value.  Such a call can be a simple path expression in a ZPT, such as 
"/here/mymethod.sql".

2. Pass a single positional argument.  This can be a dictionary or a 
record object of the kind produced by a form input with a name such as 
"foo.bar:record".  The ZSQL method will look for parameters (if any) in 
this object, and *not* in the request object.  From ZPT, such a call 
must be a Python expression, such as "python:here['mymethod.sql'](foo)".

3. Pass named arguments.  The ZSQL Method will accept the named 
arguments, and will not look anywhere else for its parameters.  From 
ZPT, such a call must be a Python expression, such as 
"python:here['mymethod.sql'](size=6, color='red')".

The result of calling a ZSQL Method, assuming that it contains a SELECT 
statement, is a special object that can be used to access information 
about the query result, primarily the returned rows.  ZPTs can use 
tal:repeat to iterate over the rows contained in the result, and can 
access fields in each row as attributes of the repeat variable.

Example:

<table>
   <tr><th>Full Name</th><th>Salary</th></tr>
   <tr tal:repeat="row here/mymethod.sql">
     <td tal:content="row/fullname">Fred Farkas</td>
     <td tal:content="row/salary">30000</td>
   </tr>
</table>

It can sometimes be very useful to preprocess the results of a ZSQL 
Method before passing them to a ZPT for display -- filtering, 
calculating totals, and formatting dates and monetary amounts.  This is 
a good job for a Script, and for the ".dictionaries()" method of the 
ZSQL result set.

Example:

from Products.PythonScripts.standard import thousands_commas
rows = []
for row in here['mymethod.sql']().dictionaries():
     if row['salary'] < 10000:
         continue
     row['salary'] = thousands_commas('$%.2f' % row['salary'])
     rows.append(row)
return here['page.zpt'](rows=rows)

----------

<table>
   <tr><th>Full Name</th><th>Salaries over $10,000</th></tr>
   <tr tal:repeat="row options/rows">
     <td tal:content="row/fullname">Fred Farkas</td>
     <td tal:content="row/salary">$30,000.00</td>
   </tr>
</table>

Cheers,

Evan @ 4-am