[Psycopg] How to execute an sql query from another zope product

[rm@fabula.de]

On Thu, Jun 27, 2002 at 06:04:36PM -0400, Tom Jenkins wrote:
> On Thu, 2002-06-27 at 17:32, rm@fabula.de wrote:
> > Hello everyone,
> > 
> > i have a  zope product that has an zpsycopg connection ID
> > as a property and needs to execute (dynamically build) SQL
> > queries. How do i access the connection from my product code.
> > 
> 
> we do this all the time... the trick is to use zope not fight it.

... which i sometimes find more clumsy than fighting it :-)

> create a ZSQLMethod dynamically like so
> 
> from Products.ZSQLMethods.SQL import SQL
> 
> [... code omited ... ]
> 
> once that's done, this object (be it Folder, or whatever object you
> create) has the new ZSQL method and can be called just like any other
> 
>  self.sqlDeleteCertification(id=self.certID)
> 

yes, that's the one solution i stumbled over searching the
web. Unfortunately, this approach seems to have some unwanted
side effects (correct me if i err): every query generated
will be part of the "Zope World", i.e. exist as a named entity
in the Zope server and hence add to the total count of object
(and in my experience that does have some effects on performance).
The queries my code produces are 'one-shot' optimized, non-
parameterized queries that can't be reused.
There shure i_must_ be a way to get hold of the db connection
without generating objects?

  Thanks for your quick response

   Ralf Mattes
> but since its python you can go for generic routines
> ours are more dynamic below for example:
> 
> from Products.ZSQLMethods.SQL import SQL
> 
> def buildSQL(self, params):
>    sqlCustomSQL = SQL(
>        'sqlDelete%s' % (params['type'],),   #zope id
>        'Delete %s' % (params['type'],),     #zope title
>        self.connection,                     #connection object
>        'id:%s' % (params['primaryKeyType'], #parameters
>        "select log_delete('%s', '%s',       #source
>            <dtml-sqlvar id type=%s>,
>            <dtml-sqlvar \"getUserID()\" type=int>" %
>          (params['tableName'], 
>           params['primaryKeyName'],
>           params['primaryKeyType'],))
> 
>    setattr(self, 'sqlDelete%s' % (params['type'],), sqlCustomSQL)
> 
>                   
> 
> -- 
> 
> Tom Jenkins
> Development InfoStructure
> http://www.devis.com
> 
>