Zoping around: More complete Spreadsheet-like App

A simple way to deal with your database tables in zope


Intro

I had to write a database app with Zope and it was difficult for me to find informations about database tables management in Zope. After a while I found a couple of "How to" about table display. I just decided to put them together...


Task

Interact with a database table (delete, modify, append) records.


Tools

DTML, Python based script, ZSQL method


Procedure


1. Create a simple ZSQL method "spreadsheetQuery" to retrieve all rows from a table with this contents (Arguments = tbl):


SELECT * FROM <dtml-var tbl>

2. Create a DTML method named "spreadsheet", with this contents:


<dtml-comment> put here your database query </dtml-comment>
<dtml-unless xquery>
<dtml-call "REQUEST.set('xquery', spreadsheetQuery(tbl = 'my_favorite_table'))">
</dtml-unless>

<dtml-comment> put here fields to hide (by index) </dtml-comment>
<dtml-unless xcolhide>
<dtml-call "REQUEST.set('xcolhide',[])">
</dtml-unless>

<dtml-comment> sort column index </dtml-comment>
<dtml-unless xpos>
<dtml-call "REQUEST.set('xpos','0')">
</dtml-unless>

<dtml-comment> sort method </dtml-comment>
<dtml-unless xmode>
<dtml-call "REQUEST.set('xmode','0')">
</dtml-unless>

<dtml-comment> edit box size </dtml-comment>
<dtml-unless xeditsize>
<dtml-call "REQUEST.set('xeditsize','16')">
</dtml-unless>

<dtml-comment> number of rows to display </dtml-comment>
<dtml-unless xrows>
<dtml-call "REQUEST.set('xrows','10')">
</dtml-unless>

<dtml-if "xmode=='1'">
<dtml-call "REQUEST.set('xmode','')">
<dtml-else>
<dtml-call "REQUEST.set('xmode','1')">
</dtml-if>

 

<form method="post" action="spreadsheetAction">
<dtml-comment> some other parameters </dtml-comment>
<dtml-let xbgcolor_head="'#C0C0C0'"
xbgcolor_row="'#EFEFEF'"
xbgcolor_add="'#FFCCFF'"
xnames="xquery.names()"
xtasks="['', 'Modify', 'Delete']"
xsort_field="xnames[_.int(xpos)]">

<dtml-in xquery sort_expr="xsort_field" reverse_expr="xmode" size="xrows" start=query_start >

<dtml-if sequence-start>
<dtml-if previous-sequence>
<a href="&dtml-URL;&dtml-sequence-query;query_start=&dtml-previous-sequence-start-number;">Previous
<dtml-var previous-sequence-size></a>
</dtml-if>


<table border="1" cellpadding="4">
<tr bgcolor="&dtml-xbgcolor_head;">
<td>Task</td>
<dtml-in "xnames">
<dtml-let xindex=sequence-index>
<dtml-unless "xindex in xcolhide">
<th> <a href="?xpos=&dtml-sequence-index;&xmode=&dtml-xmode;">
<dtml-let xname=sequence-item>
<dtml-in "_.string.split(xname,'_')">
<dtml-var sequence-item capitalize>
</dtml-in>
</dtml-let>
</a> </th>
</dtml-unless>
</dtml-let>
</dtml-in>
</dtml-if>

<tr <dtml-if sequence-even> bgcolor=&dtml-xbgcolor_row;</dtml-if>>

<td>
<select name="srows.xtask:records">
<dtml-in xtasks>
<!-- for each item add a selectable row -->
<option value="&dtml-sequence-item;"> <dtml-var sequence-item> </option>
</dtml-in>
</select>
</td>

<dtml-in sequence-item no_push_item>
<dtml-let xindex=sequence-index>
<dtml-unless "xindex in xcolhide">
<td>
<dtml-let xkey="'srows.%s:records' % (xnames[xindex])">
<input size="&dtml-xeditsize;" name="&dtml-xkey;" value="&dtml-sequence-item;">
</dtml-let>
</td>
</dtml-unless>
</dtml-let>
</dtml-in>
</tr>

 

<dtml-if sequence-end>

<tr bgcolor="&dtml-xbgcolor_add;">
<td> Append </td>
<dtml-in "xnames">
<dtml-let xindex=sequence-index>
<dtml-unless "xindex in xcolhide">
<dtml-let xkey="'srows.%s:records' % (xnames[xindex])">
<td><input size="&dtml-xeditsize;" name="&dtml-xkey;"></td>
</dtml-let>
</dtml-unless>
</dtml-let>
</dtml-in>
<input type="hidden" name="srows.xtask:records" value="Append">

</tr>

<tr bgcolor="&dtml-xbgcolor_head;">
<td>&nbsp;</td>
<dtml-in "xnames">
<dtml-let xindex=sequence-index>
<dtml-unless "xindex in xcolhide">
<td>&nbsp;</td>
</dtml-unless>
</dtml-let>
</dtml-in>
</tr>

</table>

<dtml-if sequence-end>
<dtml-if next-sequence>
<a href="&dtml-URL;&dtml-sequence-query;query_start=&dtml-next-sequence-start-number;">Next
<dtml-var next-sequence-size></a>
</dtml-if>
</dtml-if>
</dtml-if>

</dtml-in>

</dtml-let>

<table width="100%">
<tr><td>
<div align="center">
<input type="submit" value="Update">
</div>
</td></tr>
</table>
</form>


3. Create a Python script named "spreadsheetAction", with the following contents (this is just a dummy sample without any useful application):


##parameters=srows
for row in srows:
  if row.xtask == 'Delete':
    deleted = deleted +1
    print "record <%s> deleted" % (str(row.values()[0]).strip())
    continue
  elif row.xtask == 'Append':
    print "record <%s> appended" % (str(row.values()[0]).strip())
  elif row.xtask == 'Modify':
    modified = modified +1
    print "record <%s> modified" % (str(row.values()[0]).strip())
    continue
  else:
    print "record <%s> skipped" % (str(row.values()[0]).strip())

return printed

Usage

Go to the "spreadsheet" page and display it. You will see all your columns displayed into a table.

 

Screenshot

Task Code Name Surname Office Email Level
Append
             

 

Explanation

When page is rendered by Zope you see all your columns. By clicking on the column header you can sort the table.

Every cell contains an edit box you can use in order to modify the contents. The first column provides an "Action" you can perform on the associated row.
Avalaible actions are "Nothing (blank), Delete, Modify". The last row provide just the "Append" action. Obviously:

Nothing (blank) Row will be skipped
Delete Row will be deleted
Modify Row will be modified
Append Row will be added to the table

 

When you submit the form to the Python Script, Zope organize your data in records, every record has an action key "xtask" you can use for your own task.


Parameters


Conclusion

This is just a sample to show how to manage database tables with DTML and a touch of Python. Every suggestion would be welcomed.