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...
Interact with a database table (delete, modify, append) records.
DTML, Python based script, ZSQL method
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> </td>
<dtml-in "xnames">
<dtml-let xindex=sequence-index>
<dtml-unless "xindex in xcolhide">
<td> </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
Go to the "spreadsheet" page and display it. You will see all your columns displayed into a table.
| Task | Code | Name | Surname | Office | Level | |
|---|---|---|---|---|---|---|
| Append | ||||||
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.
This is just a sample to show how to manage database tables with DTML and a touch of Python. Every suggestion would be welcomed.