[Zope-DB] Dynamically generate sql-query in ZSQL Method
garry at joydiv.fsnet.co.uk
Wed Apr 7 11:59:59 EDT 2004
On Wednesday 07 April 2004 12:35 am, Peter Sabaini wrote:
> Ian Bicking wrote:
> > On Apr 6, 2004, at 3:23 PM, Jim Penny wrote:
> >> Ian has told you how to do it. Now, I will tell you, don't do it.
> > I would disagree with you -- not for some principled reason, but for a
> > very practical reason: Z SQL Methods suck. A lot. A huge, huge
> > amount. They are horrible. Generating your own SQL inline sucks. A
> > lot. It is horrible. *Sometimes* the inline method is less horrible.
> > These are the unfortunate choices foisted upon us by the environment.
> > (Actually, it's Z SQL plus the ZMI plus DTML that is the real poison,
> > IMHO -- they all work together to make things extra unpleasant)
> Out of curiosity -- what is so horrible about ZSQL Methods? I use them
> quite extensively (though mostly to talk to stored procedures) and may
> have some nits but dont find them _that_ horrible...
> > Note also that well designed SQL generation in Python isn't horrible. A
> > Python script like:
> > ## select_all
> > ## parameters: table_name
> > # re isn't available in the default Zope installation; I'd actually
> > usually # do this with an extension method...
> > import re
> > assert re.search(r'^[a-zA-Z_][a-zA-Z0-9_]*$', table_name), "Invalid
> > table name: %s" % table_name
> > return container.raw_sql(sql='SELECT * FROM %s' % table_name)
> > That isn't so bad. In fact, I would assert it is much better than the Z
> > SQL analog.
> Personally, I'd rather encapsulate this in a stored procedure, or, if
> this isn't possible, in a Python class if I really _had_ to dynamically
> determine a table name.
> - peter.
Now you've got me confused. Being relatively new to Zope this has left me
thinking whether I am doing things correctly. i am writing my SQL in a ZSQL
method, using DTML for logic and for gathering variables to input to my ZSQL
I have a database application running from Zope with Postgresql for the last 6
months, programmed as above and have had not one problem with it. Does this
discussion mean that I should not do it this way or will things get messy
when I add modules to the application, which I plan to.
More information about the Zope-DB