[Zope] howto sql-query directly from my products methods?

[Albert Ting]

> hans wrote:
> 
> > well, i could instantiate a bunch of zsql methods, each with
> > its own query and then call them from my products methods.
> > but how could i obviate the need to have these zsql methods
> > and query directly? Or is it just not worth it?

This is what I've done.  However, it's a hack in the sense I'm relying on
the current SQL() class implementation and am using Zope-2.3.3.  Still,
I've been quite happy with the results and does what you've been looking
for.  This may have to be cleaned up a little for later versions and would
be curious of any updates.

Hopefully one of the Zope developers will make something like this part of
the Zope API.

Enjoy,
Albert

----------------------------------------------------------------------
# -*- mode: python; -*-
import Globals
import OFS
import StringIO
from Products.ZSQLMethods.SQL import SQL

# given a connection_id string, the ZSQL template, variable list, and
# definitions, execute the ZSQL statement.  Return a ZSQL Result object.
# Example:
#
#    expresion = '''SELECT FirstName,LastName FROM Users
#                   WHERE Users.Email=<dtml-sqlvar email type="string">'''
#    result = query(self,expression,"email",email="foo@bar.com")
#
# This does not store a ZSQL object in the ZODB, so that ZODB remains
# unchange.  IMHO, this provides the most flexibility, allowing us to
# create an arbitrary number of ZSQL statements from python code,
# without being tied down to the traditional point&click editing.
def query(self,connection_id,template,vars="",**keywords):
    id = "__custom_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(MAX_ROWS):
        # DA parses max_rows and saves it in max_rows_
        sql.max_rows=sql.max_rows_=keywords[MAX_ROWS]
    else:
        sql.max_rows=sql.max_rows_=1000

    results = sql(keywords)

    return results