In my daily programming there are many times that I had to generate the same code many times. If the code is needed almost always I use to create a script that automaticly generate the code by giving it some base data. Let 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. Then I create a DTML method that present the data, a ZMySQL object and three ZSQL objects for interfacing the database. I also prefere to save the zope objects in a folder herarchie that shows me the database and table names, as follows:

    [database]
         |_____[table]
                   |_____[sql]
                   |_____ ZMySQL
                   |_____ form

The first step I always made is to create a functional example code of the application. Let say we have a database named 'mycompany' and a table named 'customers'. To create a ZMySQL method I select "Z MySQL Database Connection" from the push-down control on the Zope interface. It will present a form in which we must enter the database connection parameters. Mostly, we 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 instalation the following may suffice:

       'mycompany admin password'

The next step is to generate the needed ZSQL object to interface with the database. Lets begining with a object that list all the fields of all the cases in the database. We will call the method 'list_customers' and the sql query template will be:

       'SELECT * FROM CUSTOMERS'

For this object, we do not need to add any argument.

The second ZSQL object will be used for editing the data showed. The id will be 'update_customers', the arguments 'company_id, company, contact_name, address, phone' and the sql query template will be:

        'UPDATE customers SET (
           company= <dtml-var company>, 
           contact_name = <dtml-var contact_name>, 
           address = <dtml-var address>, 
           phone = <dtml-var phone>
         ) WHERE company_id=<dtml-var company_id>, 

The third and last ZSQL object 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-var company>, 
           <dtml-var contact_name>, 
           <dtml-var address>, 
           <dtml-var phone>
          )

The exclussion of the company_id from this clause is because it was defined as an 'auto_increment' field, and this data will be generated automaticly.

Now we will generate a DTML method for presentation of the data.

o
o
o
o

Generating the same stuff using Python script

Creation of folders

from Products.PythonScripts.standard import html_quote
request = container.REQUEST
RESPONSE =  request.RESPONSE
session = context.REQUEST

file = "database"
base = request.PARENTS[1]
base.manage_addFolder(file)

The 'PARENTS' parameter represent the parent folder of the actual object. It is equivalent to PARENTS[0]. In our case, we want to generate the folder in the parent folder of the actual parent folder:

     [test]                                     ( PARENT[1] )
        |_______[mycompany]                     ( PARENT[0] )
        |            |_______ my_script         ( <-- we are here)
        |
        |_______[database] *                    ( generated folder)

Creation of DTML methods

index = """
<dtml-var standard_html_footer>
<h1> Wellcome to <dtml-var title_or_id> </h1>
<dtml-var standard_html_footer>
"""
base[file].addDTMLMethod(id="index_html",file=index)

Creation of the ZMySQL object

conn_str = "mycompany admin password"
base[file].manage_addZMySQLConnection('myDB_connect','mydDB_connect', conn_str, 'YES');

Creation of the ZSQL object

Now we will generate the ZSQL objects for accessing the database tables. These objects will be saved in a separate sub-folder called sql. We generate it as follows:

base[file].manage_addFolder("sql")

The code for the generation of the ZSQL object is:

query = "SELECT * FROM mycompany"
myid = "list_customers" 
res = base[file].sql.manage_addProduct['ZSQLMethods'].manage_addZSQLMethod(myid,"",'myDB_connect',"",query,request,"post")