[DB-SIG] Passing 'pre-quoted' SQL to 'execute'.

[Chris Cogdon]

I'm using a particular way of organising my python and SQL that I 
think's pretty neat, but I'm wondering if I'm not making things too 
difficult for myself, and there's a 'better way' out there I'm unaware 
of.

I'm using pyPgSQL. The application is a community artwork site, where 
several thousand artists have uploaded 200,000 images.

Firstly, I have a class that handles all my database queries. I've 
actually separated parts into different modules, and used a module and 
the 'mix-in' methodology to bring them all together so it's one big 
interface to the rest of the program. For simplification, I've hidden 
those details.

So, here's part of that class:

class VCL_DATABASE:
     def get_artists ( self, criteria ):
         cur = self.cursor ()
         try:
             condition = criteria.make_sql_condition ()
             cur.execute ( "select * from artist where "+condition )
             # The cursor is now turned into a list of artists, code 
omitted for brevity
             return artists
         finally:
             cur.close ()

That's pretty simple. The interesting part is where the 'criteria' is 
passed to the 'get_artists' function. There are a few such classes and 
each can return their SQL conditions however they want. Here's an 
example:

class ARTIST_DISPLAYNAME_CRITERIA ( ARTIST_CRITERIA ):
     def __init__ ( self, displayname ):
         self.displayname = displayname
     def make_sql_condition ( self ):
         return "displayname="+PgSQL._quote(self.displayname)

There are many more like it, and I also have ways to 'join' the 
criteria together. The resultant conditions can be quote complex, and 
there's also a way for it to ask the 'select' to join extra tables to 
get the criteria it needs. (For example, I might want all artist that 
have new artwork in the last 14 days, which would require a join on 
several other tables). I've omitted all that code for brevity.

So... my questions are these:

- I'm piecemealing the SQL together from different sources, meaning I 
need to quote values using _quote rather then inside the 'execute' 
function. I don't like the idea of me using a 'underscored' function, 
but there seems to be no way around it. Is there a better way? (The 'as 
shipped' pg module has a 'bare' quote function)

- Am I causing more work for myself with the method for the condition 
separate from the execute? Do other people find some way of passing all 
the necessary values into the 'execute', with all the SQL being 
generated at the last moment ?

- I /believe/ that if 'execute' does not see any list or dictionary 
following, it does not quote the SQL at all, which is necessary for 
this method to work. Is my assumption correct?

Thanks for your attention!

-- 
    ("`-/")_.-'"``-._        Chris Cogdon <chris@cogdon.org>
     . . `; -._    )-;-,_`)
    (v_,)'  _  )`-.\  ``-'
   _.- _..-_/ / ((.'
((,.-'   ((,/   fL