[Zope] dynamically creating ZSQLMethods in python

[Albert Ting]

I found it a pain to manage ZSQL Methods inside Zope, too many mouse
clicking for my taste.  Besides, I felt it was "safer" if I could hide the
ZSQL calls within python.

Someone recently posted a solution using a ZSQLMethod's
manage_edit function.  However, every time the method is called, the
Data.fs file gets updated and continues to grow until you do a purge.  

Below is snapshot of what I've done.  It does not modify Data.fs, nor do
you have any ZSQLMethod objects stored in Data.fs.  The only thing you need
is a sql connection object.

This works in 2.3, I have not tried it in 2.4, nor with ZEO.  

# example:
#  return up to 100 rows from the MyDB.Users table
#    res=queryTable(self,"my_connection","select * from Users","",MAXROWS=100)
from Products.ZSQLMethods.SQL import SQL
def queryTable(self,connection_id,template,vars,keywords):
    id = "__my_query"

    sql = SQL(id, id, connection_id, vars, template)
    # normally if we create a ZSQL method in zope (or add sql instance
    # to self object), the sql instance would hold the ZSQL DA object.
    # So we need to manually set it.
    #
    # note that we don't store the sql object into the zope object, as
    # the transaction gets stored in the Data.fs.  Even deleting later
    # makes it worse, as the "undo" records gets updated
    setattr(sql,sql.connection_id, getattr(self,sql.connection_id))
    if keywords.has_key(MAXROWS):
        # DA parses max_rows and saves it in max_rows_
        sql.max_rows=sql.max_rows_=keywords[MAXROWS]

    results = sql(keywords)

    return results