Introduction
Zope is mostly known for its well developed content management capabilities, but it was
mainly developed as a web application development platform. Web programming can be
achieved using many means like DTML, TALES, Python scripts, and even Perl. Since Zope
was developed using Python and it includes a limited (for security reasons) Python
processor, which comes installed with Zope, this language is the most natural election for
Zope programmers. However, programmers who prefer programming with Perl would be
able to do the same stuff.
Developers of Zope applications distribute their code as "Zope Products" that the user
installs on the "Products" folder of the Zope distribution. Those products could be created,
deleted, and managed from a Python script method. Even python script method is by itself a
product. This fact is one of the secrets of Zope's flexibility. A Python script method can also
be created, deleted, and managed from another Python script method.
Sometimes, I have to generate the same code many times but with different names and
variables. If it happens, I use to create a script that automatically generate the code for me
by passing to it the needed data. In this article I will describe the process I use for the
automatic generation of a database form. The example will demonstrate how to create all
the needed methods for generating a database user interface on the fly.
Lets say I have a big database with many tables. For each table I
have to make a form for browsing, editing and adding new data. I
have to create a DTML method that present the data, a ZMySQL
method and three ZSQL methods for interfacing the database. I also
prefer to save the Zope objects in a folder hierarchy that shows me
the database and table names (figure 1).
Before beginning to this as Python scripts, we will create a fully
functional example with all the necessary components to be sure
that everything works as expected. When we will be done, we will
use those objects as our working templates.
Database Interface
The first step I always do is to create a functional template code of the application. Let say
we have a database named 'mycompany' and a table named 'customers'. You can
reproduce this database using the SQL code shown on listing 1.
Listing 1
-- MySQL dump 8.21
--
-- Host: localhost
Database: mycompany
---------------------------------------------------------
-- Server version
3.23.48-log
--
-- Table structure for table 'CUSTOMERS'
--
CREATE TABLE CUSTOMERS (
company_id int(11) NOT NULL auto_increment,
company varchar(25) default NULL,
contact_name varchar(50) default NULL,
address varchar(250) default NULL,
phone varchar(25) default NULL,
PRIMARY KEY (company_id)
) TYPE=MyISAM;
--
-- Dumping data for table 'CUSTOMERS'
--
INSERT INTO CUSTOMERS VALUES (1,'IDN Ltd.','David Gold',
'21 SW St. Sunrise, FL','1-888-255555');
INSERT INTO CUSTOMERS VALUES (2,'SERCOLA Inc.','Michael Soto',
'433 Plaza Boulevard, Ca.','1-666-7755777');
INSERT INTO CUSTOMERS VALUES (3,'Cressitess Ltd.','Carol Dress',
'67 Columbus St., Ontario, Canada','2-454-54455455');
To create a ZMySQL method I select "Z MySQL Database Connection" from the push-down
control on the Zope administrator interface. A form on which we must enter the database
connection parameters will appear. Mostly, we must enter a connection string like this:
'mycompany@localhost:3336 admin password /tmp/mysql.sock'
If your mysql server runs in the same machine as Zope (localhost) and have a standard
installation the following may suffice:
'mycompany admin mypassword'
The next step is to generate the needed ZSQL method to interface with the database. We
will begin with a method that lists all the fields and all the rows in the database. We will call
the method 'list_customers' and the SQL query template will look like this:
'SELECT * FROM customers'
For this object, we do not need to supply any argument.
The second ZSQL method will be used for editing the data showed. The id will be
'update_customers', the arguments include: 'company_id, company, contact_name,
address, phone' and the SQL query template will be:
'UPDATE customers SET (
company= <dtml-sqlvar company type=string>,
contact_name = <dtml-sqlvar contact_name type=string>,
address = <dtml-sqlvar address type=string>,
phone = <dtml-sqlvar phone type=string>
) WHERE company_id=<dtml-sqlvar company_id type=int>,
The third ZSQL method will be intended for the addition of new data. We will call it
'add_customer', and will have the same arguments as the second ZSQL object, excluding
the 'company_id'. The SQL query template will be:
'INSERT INTO customers (company, contact_name, address, phone)
VALUES (
<dtml-sqlsvar company type=string>,
<dtml-sqlvar contact_name type=string>,
<dtml-sqlvar address type=string>,
<dtml-sqlvar phone type=string>
)
We exclude the company_id from the query because it was defined as an 'auto_increment'
field, and this data will be generated automatically.
The fourth and last ZSQL method will implement a delete query:
'DELETE FROM customers WHERE
company_id=<dtml-sqlvar company_id type=int>
Database access form
Now we will generate the DTML method for the data form. This form will let the user to see,
modify, add and delete data. For achieving this we will combine Zope script at the server
side for retrieving items from the database and JavaScript at the client side for the
presentation of data. The code of such dtml method is presented in listing 2. The resulting
form is shown in figure 2.
Figure 2. The database interface.
Listing 2
<dtml-var standard_html_header>
<script language='JavaScript'>
<!--
data = [];
n=-1;
<dtml-in get_CUSTOMERS>
<dtml-let rows=sequence-key>
data[<dtml-var sequence-index>] = ['<dtml-var company_id>','<dtml-var
company>', \
'<dtml-var contact_name>','<dtml-var address>','<dtml-var
phone>',''];
for (l=0;l<data[<dtml-var sequence-index>].length;l++) {
if (data[<dtml-var sequence-index>][l] == 'None') {
data[<dtml-var sequence-index>][l] = 0;
}
}
</dtml-let>
</dtml-in>
max = data.length-1;
function setValues() {
x = document.formObj.ptr.value;
document.formObj.company_id.value = data[x][0];
document.formObj.company.value = data[x][1];
document.formObj.contact_name.value = data[x][2];
document.formObj.address.value = data[x][3];
document.formObj.phone.value = data[x][4];
}
function goup() {
x = document.formObj.ptr.value;
x++;
if (x > max) x = max;
document.formObj.ptr.value = x;
setValues();
document.formObj.submit1.value = "Update data";
}
function godown() {
x = document.formObj.ptr.value;
x--;
if (x < 0) x = 0;
document.formObj.ptr.value = x;
setValues();
document.formObj.submit1.value = "Update data";
}
function newData() {
document.formObj.ptr.value = max + 1;
document.formObj.company_id.value = "";
document.formObj.company.value = "";
document.formObj.contact_name.value = "";
document.formObj.address.value = "";
document.formObj.phone.value = "";
document.formObj.submit1.value = "Insert data";
}
function updateData() {
document.formObj.action="update_document";
}
function previewReport() {
document.formObj.action="report";
}
-->
Listing 2 (continued)
</script>
<h1>Database: mycompany</h1><h2>Table: CUSTOMERS</h2>
<table> <form name='formObj' action='report' method='post'>
<tr><th bgcolor='#eaeaea'> company_id </th>
<td><input type='text' name='company_id'></td>
<th bgcolor='#eaeaea'> company </th>
<td><input type='text' name='company'></td> </tr>
<tr><th bgcolor='#eaeaea'> contact_name </th>
<td><input type='text' name='contact_name'></td>
<th bgcolor='#eaeaea'> address </th>
<td><input type='text' name='address'></td></tr>
<tr><th bgcolor='#eaeaea'> phone </th>
<td><input type='text' name='phone'></td>
<tr><td colspan='3'><input type='button' value=' << ' onclick='godown();'>
<input type='button' value=' >> ' onclick='goup();'>
<input type='hidden' name='max'>
Record:<input type='text:int' name='ptr' size='4'>
<input type='button' value='New' onclick='newData();'>
</td></tr>
<tr bgcolor='#ddffdd'><td colspan='3'><P><P>
<input type='hidden' name='table' value='CUSTOMERS'>
<input type='submit' name='submit1' value='Update data'
onclick="formObj.action='update';">
<input type='submit' name='submit2' value='Preview report'
onclick="formObj.action='report';">
</td></tr>
</table> </form>
<center><a href="/databases/mycompany"> mycompany Home </a> |
<a href="/databases/index_html"> Home </a>
</center>
<dtml-var standard_html_footer>
Now that we have all the templates and are sure that they work as we expect, we will
proceed to create the python scripts for the automatic generation of database interfaces.
Generating the same stuff using Python script
Creating folders
Create a new Python script object on the Zope interface and write in the following code:
from Products.PythonScripts.standard import html_quote
request = container.REQUEST
RESPONSE = request.RESPONSE
session = context.REQUEST
folder = "database"
base = request.PARENTS[1]
base.manage_addFolder(folder)
By running this python script we create a new folder called 'database'. In the code, the
'PARENTS' parameter represent the parent folder of the actual object. It is equivalent to
PARENTS[0]. In our case, we wanted to generate the folder in the parent folder of the actual
parent folder (PARENT[1]). A schematic view of what we made is shown in figure 3:
[test]
|_______[mycompany]
|
|_______ my_script
|
|_______[database] *
( PARENT[1] )
( PARENT[0] )
( <-- we are here)
( generated folder)
Figure 3. A schematic view of the PARENT directories.
The function manage_addFolder(id[,title]) requires at least one parameter: the name (id) of
the object. This function does the same as adding a new folder using the management
interface. Similarly, you can delete an existing folder using the following function:
base.manage_delFolder(folder)
Now, what if a previous folder with the same name exists yet ? In this case we will receive
an error message. To avoid this situation we need to check previously if a folder with the
name we want to create exists. We accomplish that with the following code:
for names in container.objectIds('Folder'):
if names == "database":
print "Sorry, a folder with that name exists !"
else:
base.manage_addFolder("database")
print "A folder named %s was successfully created" % "database"
The first line will pass to the names variable the ids of all the folders found in the parent
directory. If we write objectIds() without any parameter it will return the names of all the
objects found on the parent directory.
Generating ZMySQL methods
One of the most used databases for web development is MySQL. Zope has a product to
interface the MySQL database called ZMySQL-DA that, as most of the Zope products, can
be interfaced through python scripts. To generate a ZMySQL interface like that created
earlier we use the manage_ prefix as follows:
conn_str = "mycompany admin password"
base[folder].manage_addZMySQLConnection('myDB_connect','mydDB_connect', conn_str,
'YES');
As shown here, we define first the connection string by adding the database name, the user
and the password. Then we create the database connection object by adding it with:
manage_addZMySQLConnection(<id>,<title>,<conn_str>,'YES');
The base[folder] points to the location where the object must be saved. In this case, base
is the parent of the current parent directory (or PARENT[1]) and folder say to save it in the
'database' folder.
Generating ZSQL methods
Now we will generate the ZSQL objects for accessing the database tables. This object will
be saved in a separate sub-folder called SQL. We generate it as follows:
base[folder].manage_addFolder("SQL")
ZSQLMethod is a product that is pre-installed in Zope and is the common interface for
accessing all the database engines. This method uses previously created database
connections for query submit. The code that generate the ZSQL object is:
query = "SELECT * FROM mycompany"
myid = "list_customers"
res = base[folder].sql.manage_addProduct['ZSQLMethods'].manage_addZSQLMethod
(myid,"",'myDB_connect',"",query,request,"post")
First we defined the query we will use. Next we defined the id of our object. Then we create
the ZSQLMethod using those parameters. We have write in two manage_ methods:
manage_addProduct['ZSQLMethods'], and manage_addZSQLMethod(...). The
addZSQLMethod takes six parameters: id, title, the database connection object, arguments,
query, request, and method ('post'). The arguments parameter are the variables that we
need to pass to the SQL method. In this case it is null string because our query does not
require any arguments. The request and method fields could be leaved always as shown in
the example.
Now we will generate the method that updates the data on the database:
args = "company, contact_name, address, phone, company_id"
query = "UPDATE customers SET (
company= <dtml-sqlvar company type=string>,
contact_name = <dtml-sqlvar contact_name type=string>,
address = <dtml-sqlvar address type=string>,
phone = <dtml-sqlvar phone type=string>
) WHERE company_id=<dtml-sqlvar company_id type=int>"
myid = "update_customers"
res = base[folder].sql.manage_addProduct['ZSQLMethods'].manage_addZSQLMethod
(myid,"",'myDB_connect',args,query,request,"post")
As we can see, it is very similar to our previous example with a difference in the inclusion of
the args argument. I leave the creation of the other methods as an exercise for the reader.
Creating DTML methods
At this point we have created the whole infrastructure needed for our project. Now we will
create the pages that will present the table data to the user (the user interface). The code of
the python script is shown in listing 3. I had colored the most relevant code for discussion
purposes. You can compare this code with the template of the form listed in listing 2.
The first block (blue) correspond to the functions that check the type of fields contained in
the table so we can put the correct HTML <input ...> widgets with their appropriate sizes.
Next we define our variables (red). The green marked block defines the form fields. Here we
call the function printFields() to put the correct HTML widget. We had saved all the content
of the form page on a string variable that we have called page. The next step is to create a
DTML method that will contain the content of the page object as follows:
base = request.PARENTS[1][db_list][sel_tables]
base.addDTMLMethod(id= "index_html",file= page)
At this point we have concluded to code. By calling the code (selecting the test tab on the
manager interface, or calling it from another method like a DTML method) you can run the
code and see the results. The code generated is only an example of the possibilities offered
by Zope's python scripts. We can automate even more the generation of applications by
accessing other Zope products. Because security issues, python scripts have many
limitations with respect to a full python implementation. This can be circumvented by using
Zope's external methods. A functional and more complete example I use widely in my daily
work could be found on http://zope.org/Members/karpati/projects/sql2form.
Listing 3
from Products.PythonScripts.standard import html_quote, DTML
request = container.REQUEST
RESPONSE = request.RESPONSE
import string
# #############################################################################
# ########### functions
# #############################################################################
def int_size(str):
a = string.replace(str,'int(','')
a = string.replace(a,'tiny','')
a = string.replace(a,'small','')
a = string.replace(a,'medium','')
a = string.replace(a,'big','')
b = string.replace(a,')','')
c = string.replace(b,'unsigned','')
d = string.atoi(c)
return d
def printFields(list, max_cols, side, request):
# define the variable type
vartypes = {}
varsize = {}
for data in container.getCols(tab=sel_tables):
if (string.find(data.type,"int") != -1):
vartypes[data.field] = "int" # "int"
varsize[data.field] = int_size(data.type)
elif (string.find(data.type,"char") != -1):
vartypes[data.field] = "string"
# "string"
varsize[data.field] = 8
elif (string.find(data.type,"year") != -1):
vartypes[data.field] = "int"
varsize[data.field] = 4
elif (string.find(data.type,"time") != -1):
vartypes[data.field] = "string"
varsize[data.field] = 8
elif (string.find(data.type,"text") != -1):
vartypes[data.field] = "string"
varsize[data.field] = 15
elif (string.find(data.type,"set") != -1):
vartypes[data.field] = "select" # select
varsize[data.field] = 15
elif (string.find(data.type,"enum") != -1):
vartypes[data.field] = "select" # select
varsize[data.field] = 15
elif (string.find(data.type,"blob") != -1):
vartypes[data.field] = "string" # upload
varsize[data.field] = 15
elif (string.find(data.type,"float") != -1):
vartypes[data.field] = "float"
varsize[data.field] = 8
elif (string.find(data.type,"double") != -1):
vartypes[data.field] = "float"
varsize[data.field] = 8
else:
vartypes[data.field] = ""
varsize[data.field] = 15
doc = ""
c = 1
for d in list:
init = ""
end = ""
if c > max_cols:
c = 1
if c == 1:
init = "<tr> \n"
end = ""
if c == max_cols:
init = ""
end = "</tr> \n"
doc = doc + "%s<th bgcolor='#eaeaea'> %s </th><td>" % (init, request[d])
doc = doc + "<input type='text:%s' size='%s' name='%s'>" % (vartypes[d],
varsize[d], d)
doc = doc + "</td> %s \n" % end
c = c + 1
return doc
# ################ end of functions ##################################
Listing 3 (continued)
#### define variables
session = context.REQUEST
db_list = mycompany
sel_tables = customers
cols = 2
selectedList = [ 'company_id', 'company', 'contact_name', 'address', 'phone']
myid = "get_%s" % sel_tables
### define the form content
page = """
<dtml-var standard_html_header>
<script language='JavaScript'>
<!--
data = [];
n=-1;
"""
page = page + "<dtml-in " + myid + "> \n "
page = page + " <dtml-let rows=sequence-key> \n "
page = page + "data[<dtml-var sequence-index>] = ['"
for d in selectedList:
page = page + "<dtml-var %s>','" % d
page = page + "']; \n"
page = page + """
for (l=0;l<data[<dtml-var sequence-index>].length;l++) {
if (data[<dtml-var sequence-index>][l] == 'None') {
data[<dtml-var sequence-index>][l] = 0;
}
}
"""
page = page + "\n </dtml-let>\n </dtml-in> \n max = data.length-1; \n"
page = page + "function setValues() { \n
x = document.formObj.ptr.value; \n"
n = 0
for d in selectedList:
page = page + " document.formObj." + d + ".value = data[x][%s]; \n" % n
n = n + 1
page = page + """
}
function goup() {
x = document.formObj.ptr.value;
x++;
if (x > max) x = max;
document.formObj.ptr.value = x;
setValues();
document.formObj.submit1.value = "Update data";
}
function godown() {
x = document.formObj.ptr.value;
x--;
if (x < 0) x = 0;
document.formObj.ptr.value = x;
setValues();
document.formObj.submit1.value = "Update data";
}
function newData() {
document.formObj.ptr.value = max + 1;
"""
for d in selectedList:
page = page + " document.formObj." + d + ".value = \"\"; \n"
page = page + """
document.formObj.submit1.value = "Insert data";
}
function updateData() {
document.formObj.action="update_document";
}
Listing 3 (continued)
function previewReport() {
document.formObj.action="report";
}
-->
</script>
"""
page = page + "<h1>Database: " + db_list + "</h1><h2>Table: "+ sel_tables
page = page + "</h2><table> <form name='formObj' action='report' method='post'> \n"
max_cols = session.SESSION.get('cols')
c = 1
page = page + printFields(selectedList, max_cols, label, request)
page = page + """
<tr><td colspan='3'><input type='button' value=' << ' onclick='godown();'>
<input type='button' value=' >> ' onclick='goup();'> <input type='hidden'
name='max'>
Record:<input type='text:int' name='ptr' size='4'><input type='button' value='New'
onclick='newData();'>
</td></tr>
<tr bgcolor='#ddffdd'><td colspan='3'><P><P>
<input type='hidden' name='table' value='%s'>
<input type='submit' name='submit1' value='Update data'
onclick="formObj.action='update';">
<input type='submit' name='submit2' value='Preview this page'
onclick="formObj.action='report';">
<input type='submit' name='submit2' value='Preview report'
onclick="formObj.action='browse';">
</td></tr>
</table> </form>
<center><a href="/databases/%s"> %s Home </a> | <a href="/databases/index_html">
Home </a></center>
<dtml-var standard_html_footer>
""" % (sel_tables, db_list,db_list)
base = request.PARENTS[1][db_list][sel_tables]
base.addDTMLMethod(id="form",file=page)
Conclusion
Zope is a very powerful infrastructure for web development. Programmers are able to
automate the creation of product's derived objects using Python scripts. The knowledge
given on this tutorial is very partial and is only intended to demonstrate the broad
capabilities a programmer can found on Zope for web applications development.
Tomas Karpati is an IT consultant specialized in the area of service of health,
biotechnology, pharma and medical devices. He likes to enjoy most of his free time
with his lovely wife and and his two children. He could be found at
karpati@it4biotech.com
Zope is mostly known for its well developed content management capabilities, but it was
mainly developed as a web application development platform. Web programming can be
achieved using many means like DTML, TALES, Python scripts, and even Perl. Since Zope
was developed using Python and it includes a limited (for security reasons) Python
processor, which comes installed with Zope, this language is the most natural election for
Zope programmers. However, programmers who prefer programming with Perl would be
able to do the same stuff.
Developers of Zope applications distribute their code as "Zope Products" that the user
installs on the "Products" folder of the Zope distribution. Those products could be created,
deleted, and managed from a Python script method. Even python script method is by itself a
product. This fact is one of the secrets of Zope's flexibility. A Python script method can also
be created, deleted, and managed from another Python script method.
Sometimes, I have to generate the same code many times but with different names and
variables. If it happens, I use to create a script that automatically generate the code for me
by passing to it the needed data. In this article I will describe the process I use for the
automatic generation of a database form. The example will demonstrate how to create all
the needed methods for generating a database user interface on the fly.
Lets say I have a big database with many tables. For each table I
have to make a form for browsing, editing and adding new data. I
have to create a DTML method that present the data, a ZMySQL
method and three ZSQL methods for interfacing the database. I also
prefer to save the Zope objects in a folder hierarchy that shows me
the database and table names (figure 1).
Before beginning to this as Python scripts, we will create a fully
functional example with all the necessary components to be sure
that everything works as expected. When we will be done, we will
use those objects as our working templates.
Database Interface
The first step I always do is to create a functional template code of the application. Let say
we have a database named 'mycompany' and a table named 'customers'. You can
reproduce this database using the SQL code shown on listing 1.
Listing 1
-- MySQL dump 8.21
--
-- Host: localhost
Database: mycompany
---------------------------------------------------------
-- Server version
3.23.48-log
--
-- Table structure for table 'CUSTOMERS'
--
CREATE TABLE CUSTOMERS (
company_id int(11) NOT NULL auto_increment,
company varchar(25) default NULL,
contact_name varchar(50) default NULL,
address varchar(250) default NULL,
phone varchar(25) default NULL,
PRIMARY KEY (company_id)
) TYPE=MyISAM;
--
-- Dumping data for table 'CUSTOMERS'
--
INSERT INTO CUSTOMERS VALUES (1,'IDN Ltd.','David Gold',
'21 SW St. Sunrise, FL','1-888-255555');
INSERT INTO CUSTOMERS VALUES (2,'SERCOLA Inc.','Michael Soto',
'433 Plaza Boulevard, Ca.','1-666-7755777');
INSERT INTO CUSTOMERS VALUES (3,'Cressitess Ltd.','Carol Dress',
'67 Columbus St., Ontario, Canada','2-454-54455455');
To create a ZMySQL method I select "Z MySQL Database Connection" from the push-down
control on the Zope administrator interface. A form on which we must enter the database
connection parameters will appear. Mostly, we must enter a connection string like this:
'mycompany@localhost:3336 admin password /tmp/mysql.sock'
If your mysql server runs in the same machine as Zope (localhost) and have a standard
installation the following may suffice:
'mycompany admin mypassword'
The next step is to generate the needed ZSQL method to interface with the database. We
will begin with a method that lists all the fields and all the rows in the database. We will call
the method 'list_customers' and the SQL query template will look like this:
'SELECT * FROM customers'
For this object, we do not need to supply any argument.
The second ZSQL method will be used for editing the data showed. The id will be
'update_customers', the arguments include: 'company_id, company, contact_name,
address, phone' and the SQL query template will be:
'UPDATE customers SET (
company= <dtml-sqlvar company type=string>,
contact_name = <dtml-sqlvar contact_name type=string>,
address = <dtml-sqlvar address type=string>,
phone = <dtml-sqlvar phone type=string>
) WHERE company_id=<dtml-sqlvar company_id type=int>,
The third ZSQL method will be intended for the addition of new data. We will call it
'add_customer', and will have the same arguments as the second ZSQL object, excluding
the 'company_id'. The SQL query template will be:
'INSERT INTO customers (company, contact_name, address, phone)
VALUES (
<dtml-sqlsvar company type=string>,
<dtml-sqlvar contact_name type=string>,
<dtml-sqlvar address type=string>,
<dtml-sqlvar phone type=string>
)
We exclude the company_id from the query because it was defined as an 'auto_increment'
field, and this data will be generated automatically.
The fourth and last ZSQL method will implement a delete query:
'DELETE FROM customers WHERE
company_id=<dtml-sqlvar company_id type=int>
Database access form
Now we will generate the DTML method for the data form. This form will let the user to see,
modify, add and delete data. For achieving this we will combine Zope script at the server
side for retrieving items from the database and JavaScript at the client side for the
presentation of data. The code of such dtml method is presented in listing 2. The resulting
form is shown in figure 2.
Figure 2. The database interface.
Listing 2
<dtml-var standard_html_header>
<script language='JavaScript'>
<!--
data = [];
n=-1;
<dtml-in get_CUSTOMERS>
<dtml-let rows=sequence-key>
data[<dtml-var sequence-index>] = ['<dtml-var company_id>','<dtml-var
company>', \
'<dtml-var contact_name>','<dtml-var address>','<dtml-var
phone>',''];
for (l=0;l<data[<dtml-var sequence-index>].length;l++) {
if (data[<dtml-var sequence-index>][l] == 'None') {
data[<dtml-var sequence-index>][l] = 0;
}
}
</dtml-let>
</dtml-in>
max = data.length-1;
function setValues() {
x = document.formObj.ptr.value;
document.formObj.company_id.value = data[x][0];
document.formObj.company.value = data[x][1];
document.formObj.contact_name.value = data[x][2];
document.formObj.address.value = data[x][3];
document.formObj.phone.value = data[x][4];
}
function goup() {
x = document.formObj.ptr.value;
x++;
if (x > max) x = max;
document.formObj.ptr.value = x;
setValues();
document.formObj.submit1.value = "Update data";
}
function godown() {
x = document.formObj.ptr.value;
x--;
if (x < 0) x = 0;
document.formObj.ptr.value = x;
setValues();
document.formObj.submit1.value = "Update data";
}
function newData() {
document.formObj.ptr.value = max + 1;
document.formObj.company_id.value = "";
document.formObj.company.value = "";
document.formObj.contact_name.value = "";
document.formObj.address.value = "";
document.formObj.phone.value = "";
document.formObj.submit1.value = "Insert data";
}
function updateData() {
document.formObj.action="update_document";
}
function previewReport() {
document.formObj.action="report";
}
-->
Listing 2 (continued)
</script>
<h1>Database: mycompany</h1><h2>Table: CUSTOMERS</h2>
<table> <form name='formObj' action='report' method='post'>
<tr><th bgcolor='#eaeaea'> company_id </th>
<td><input type='text' name='company_id'></td>
<th bgcolor='#eaeaea'> company </th>
<td><input type='text' name='company'></td> </tr>
<tr><th bgcolor='#eaeaea'> contact_name </th>
<td><input type='text' name='contact_name'></td>
<th bgcolor='#eaeaea'> address </th>
<td><input type='text' name='address'></td></tr>
<tr><th bgcolor='#eaeaea'> phone </th>
<td><input type='text' name='phone'></td>
<tr><td colspan='3'><input type='button' value=' << ' onclick='godown();'>
<input type='button' value=' >> ' onclick='goup();'>
<input type='hidden' name='max'>
Record:<input type='text:int' name='ptr' size='4'>
<input type='button' value='New' onclick='newData();'>
</td></tr>
<tr bgcolor='#ddffdd'><td colspan='3'><P><P>
<input type='hidden' name='table' value='CUSTOMERS'>
<input type='submit' name='submit1' value='Update data'
onclick="formObj.action='update';">
<input type='submit' name='submit2' value='Preview report'
onclick="formObj.action='report';">
</td></tr>
</table> </form>
<center><a href="/databases/mycompany"> mycompany Home </a> |
<a href="/databases/index_html"> Home </a>
</center>
<dtml-var standard_html_footer>
Now that we have all the templates and are sure that they work as we expect, we will
proceed to create the python scripts for the automatic generation of database interfaces.
Generating the same stuff using Python script
Creating folders
Create a new Python script object on the Zope interface and write in the following code:
from Products.PythonScripts.standard import html_quote
request = container.REQUEST
RESPONSE = request.RESPONSE
session = context.REQUEST
folder = "database"
base = request.PARENTS[1]
base.manage_addFolder(folder)
By running this python script we create a new folder called 'database'. In the code, the
'PARENTS' parameter represent the parent folder of the actual object. It is equivalent to
PARENTS[0]. In our case, we wanted to generate the folder in the parent folder of the actual
parent folder (PARENT[1]). A schematic view of what we made is shown in figure 3:
[test]
|_______[mycompany]
|
|_______ my_script
|
|_______[database] *
( PARENT[1] )
( PARENT[0] )
( <-- we are here)
( generated folder)
Figure 3. A schematic view of the PARENT directories.
The function manage_addFolder(id[,title]) requires at least one parameter: the name (id) of
the object. This function does the same as adding a new folder using the management
interface. Similarly, you can delete an existing folder using the following function:
base.manage_delFolder(folder)
Now, what if a previous folder with the same name exists yet ? In this case we will receive
an error message. To avoid this situation we need to check previously if a folder with the
name we want to create exists. We accomplish that with the following code:
for names in container.objectIds('Folder'):
if names == "database":
print "Sorry, a folder with that name exists !"
else:
base.manage_addFolder("database")
print "A folder named %s was successfully created" % "database"
The first line will pass to the names variable the ids of all the folders found in the parent
directory. If we write objectIds() without any parameter it will return the names of all the
objects found on the parent directory.
Generating ZMySQL methods
One of the most used databases for web development is MySQL. Zope has a product to
interface the MySQL database called ZMySQL-DA that, as most of the Zope products, can
be interfaced through python scripts. To generate a ZMySQL interface like that created
earlier we use the manage_ prefix as follows:
conn_str = "mycompany admin password"
base[folder].manage_addZMySQLConnection('myDB_connect','mydDB_connect', conn_str,
'YES');
As shown here, we define first the connection string by adding the database name, the user
and the password. Then we create the database connection object by adding it with:
manage_addZMySQLConnection(<id>,<title>,<conn_str>,'YES');
The base[folder] points to the location where the object must be saved. In this case, base
is the parent of the current parent directory (or PARENT[1]) and folder say to save it in the
'database' folder.
Generating ZSQL methods
Now we will generate the ZSQL objects for accessing the database tables. This object will
be saved in a separate sub-folder called SQL. We generate it as follows:
base[folder].manage_addFolder("SQL")
ZSQLMethod is a product that is pre-installed in Zope and is the common interface for
accessing all the database engines. This method uses previously created database
connections for query submit. The code that generate the ZSQL object is:
query = "SELECT * FROM mycompany"
myid = "list_customers"
res = base[folder].sql.manage_addProduct['ZSQLMethods'].manage_addZSQLMethod
(myid,"",'myDB_connect',"",query,request,"post")
First we defined the query we will use. Next we defined the id of our object. Then we create
the ZSQLMethod using those parameters. We have write in two manage_ methods:
manage_addProduct['ZSQLMethods'], and manage_addZSQLMethod(...). The
addZSQLMethod takes six parameters: id, title, the database connection object, arguments,
query, request, and method ('post'). The arguments parameter are the variables that we
need to pass to the SQL method. In this case it is null string because our query does not
require any arguments. The request and method fields could be leaved always as shown in
the example.
Now we will generate the method that updates the data on the database:
args = "company, contact_name, address, phone, company_id"
query = "UPDATE customers SET (
company= <dtml-sqlvar company type=string>,
contact_name = <dtml-sqlvar contact_name type=string>,
address = <dtml-sqlvar address type=string>,
phone = <dtml-sqlvar phone type=string>
) WHERE company_id=<dtml-sqlvar company_id type=int>"
myid = "update_customers"
res = base[folder].sql.manage_addProduct['ZSQLMethods'].manage_addZSQLMethod
(myid,"",'myDB_connect',args,query,request,"post")
As we can see, it is very similar to our previous example with a difference in the inclusion of
the args argument. I leave the creation of the other methods as an exercise for the reader.
Creating DTML methods
At this point we have created the whole infrastructure needed for our project. Now we will
create the pages that will present the table data to the user (the user interface). The code of
the python script is shown in listing 3. I had colored the most relevant code for discussion
purposes. You can compare this code with the template of the form listed in listing 2.
The first block (blue) correspond to the functions that check the type of fields contained in
the table so we can put the correct HTML <input ...> widgets with their appropriate sizes.
Next we define our variables (red). The green marked block defines the form fields. Here we
call the function printFields() to put the correct HTML widget. We had saved all the content
of the form page on a string variable that we have called page. The next step is to create a
DTML method that will contain the content of the page object as follows:
base = request.PARENTS[1][db_list][sel_tables]
base.addDTMLMethod(id= "index_html",file= page)
At this point we have concluded to code. By calling the code (selecting the test tab on the
manager interface, or calling it from another method like a DTML method) you can run the
code and see the results. The code generated is only an example of the possibilities offered
by Zope's python scripts. We can automate even more the generation of applications by
accessing other Zope products. Because security issues, python scripts have many
limitations with respect to a full python implementation. This can be circumvented by using
Zope's external methods. A functional and more complete example I use widely in my daily
work could be found on http://zope.org/Members/karpati/projects/sql2form.
Listing 3
from Products.PythonScripts.standard import html_quote, DTML
request = container.REQUEST
RESPONSE = request.RESPONSE
import string
# #############################################################################
# ########### functions
# #############################################################################
def int_size(str):
a = string.replace(str,'int(','')
a = string.replace(a,'tiny','')
a = string.replace(a,'small','')
a = string.replace(a,'medium','')
a = string.replace(a,'big','')
b = string.replace(a,')','')
c = string.replace(b,'unsigned','')
d = string.atoi(c)
return d
def printFields(list, max_cols, side, request):
# define the variable type
vartypes = {}
varsize = {}
for data in container.getCols(tab=sel_tables):
if (string.find(data.type,"int") != -1):
vartypes[data.field] = "int" # "int"
varsize[data.field] = int_size(data.type)
elif (string.find(data.type,"char") != -1):
vartypes[data.field] = "string"
# "string"
varsize[data.field] = 8
elif (string.find(data.type,"year") != -1):
vartypes[data.field] = "int"
varsize[data.field] = 4
elif (string.find(data.type,"time") != -1):
vartypes[data.field] = "string"
varsize[data.field] = 8
elif (string.find(data.type,"text") != -1):
vartypes[data.field] = "string"
varsize[data.field] = 15
elif (string.find(data.type,"set") != -1):
vartypes[data.field] = "select" # select
varsize[data.field] = 15
elif (string.find(data.type,"enum") != -1):
vartypes[data.field] = "select" # select
varsize[data.field] = 15
elif (string.find(data.type,"blob") != -1):
vartypes[data.field] = "string" # upload
varsize[data.field] = 15
elif (string.find(data.type,"float") != -1):
vartypes[data.field] = "float"
varsize[data.field] = 8
elif (string.find(data.type,"double") != -1):
vartypes[data.field] = "float"
varsize[data.field] = 8
else:
vartypes[data.field] = ""
varsize[data.field] = 15
doc = ""
c = 1
for d in list:
init = ""
end = ""
if c > max_cols:
c = 1
if c == 1:
init = "<tr> \n"
end = ""
if c == max_cols:
init = ""
end = "</tr> \n"
doc = doc + "%s<th bgcolor='#eaeaea'> %s </th><td>" % (init, request[d])
doc = doc + "<input type='text:%s' size='%s' name='%s'>" % (vartypes[d],
varsize[d], d)
doc = doc + "</td> %s \n" % end
c = c + 1
return doc
# ################ end of functions ##################################
Listing 3 (continued)
#### define variables
session = context.REQUEST
db_list = mycompany
sel_tables = customers
cols = 2
selectedList = [ 'company_id', 'company', 'contact_name', 'address', 'phone']
myid = "get_%s" % sel_tables
### define the form content
page = """
<dtml-var standard_html_header>
<script language='JavaScript'>
<!--
data = [];
n=-1;
"""
page = page + "<dtml-in " + myid + "> \n "
page = page + " <dtml-let rows=sequence-key> \n "
page = page + "data[<dtml-var sequence-index>] = ['"
for d in selectedList:
page = page + "<dtml-var %s>','" % d
page = page + "']; \n"
page = page + """
for (l=0;l<data[<dtml-var sequence-index>].length;l++) {
if (data[<dtml-var sequence-index>][l] == 'None') {
data[<dtml-var sequence-index>][l] = 0;
}
}
"""
page = page + "\n </dtml-let>\n </dtml-in> \n max = data.length-1; \n"
page = page + "function setValues() { \n
x = document.formObj.ptr.value; \n"
n = 0
for d in selectedList:
page = page + " document.formObj." + d + ".value = data[x][%s]; \n" % n
n = n + 1
page = page + """
}
function goup() {
x = document.formObj.ptr.value;
x++;
if (x > max) x = max;
document.formObj.ptr.value = x;
setValues();
document.formObj.submit1.value = "Update data";
}
function godown() {
x = document.formObj.ptr.value;
x--;
if (x < 0) x = 0;
document.formObj.ptr.value = x;
setValues();
document.formObj.submit1.value = "Update data";
}
function newData() {
document.formObj.ptr.value = max + 1;
"""
for d in selectedList:
page = page + " document.formObj." + d + ".value = \"\"; \n"
page = page + """
document.formObj.submit1.value = "Insert data";
}
function updateData() {
document.formObj.action="update_document";
}
Listing 3 (continued)
function previewReport() {
document.formObj.action="report";
}
-->
</script>
"""
page = page + "<h1>Database: " + db_list + "</h1><h2>Table: "+ sel_tables
page = page + "</h2><table> <form name='formObj' action='report' method='post'> \n"
max_cols = session.SESSION.get('cols')
c = 1
page = page + printFields(selectedList, max_cols, label, request)
page = page + """
<tr><td colspan='3'><input type='button' value=' << ' onclick='godown();'>
<input type='button' value=' >> ' onclick='goup();'> <input type='hidden'
name='max'>
Record:<input type='text:int' name='ptr' size='4'><input type='button' value='New'
onclick='newData();'>
</td></tr>
<tr bgcolor='#ddffdd'><td colspan='3'><P><P>
<input type='hidden' name='table' value='%s'>
<input type='submit' name='submit1' value='Update data'
onclick="formObj.action='update';">
<input type='submit' name='submit2' value='Preview this page'
onclick="formObj.action='report';">
<input type='submit' name='submit2' value='Preview report'
onclick="formObj.action='browse';">
</td></tr>
</table> </form>
<center><a href="/databases/%s"> %s Home </a> | <a href="/databases/index_html">
Home </a></center>
<dtml-var standard_html_footer>
""" % (sel_tables, db_list,db_list)
base = request.PARENTS[1][db_list][sel_tables]
base.addDTMLMethod(id="form",file=page)
Conclusion
Zope is a very powerful infrastructure for web development. Programmers are able to
automate the creation of product's derived objects using Python scripts. The knowledge
given on this tutorial is very partial and is only intended to demonstrate the broad
capabilities a programmer can found on Zope for web applications development.
Tomas Karpati is an IT consultant specialized in the area of service of health,
biotechnology, pharma and medical devices. He likes to enjoy most of his free time
with his lovely wife and and his two children. He could be found at
karpati@it4biotech.com
No comments:
Post a Comment