elegent sql building

[Steve Holden]

"Eddie Corns" <eddie at holyrood.ed.ac.uk> wrote in message
news:9vtcim$dnp$1 at scotsman.ed.ac.uk...
> cmedcoff at home.com (Charles Medcoff) writes:
>
> >I recently read (parts of) "Dive Into Python" which inspired me to
> >write something like the following:
>
>
> >def buildSql(ids, engineTypes, transTypes):
> >    return "SELECT * FROM vehicle WHERE id in (%s) AND engineType IN
> >(%s) AND transType IN (%s)" % \
> >    (",".join(["'%s'" % (id) for id in ids]),
> >     ",".join(["'%s'" % (e ) for e  in engineTypes]),
> >     ",".join(["'%s'" % (t ) for t in transTypes]))
>
> >print buildSql(['vin1', 'vin2', 'vin3'], ['4cy', '6cy'], ['4sp',
> >'5sp'] )
>
> >Now you can imagine where the inputs to buildSql might trickle down
> >from a user interface.  This is nice; perhaps even elegent.  There is
> >a problem with it though.  What if any one of, or all of, the inputs
> >are empty?  The code needs to figure out whether to include the
> >"WHERE" and the "AND"'s and where to put them.  A chain of if's would
> >be inelegent.  Is there a better solution?  Suggestions appreciated.
>
> First attempt:
>
> def buildSql(ids, engineTypes, transTypes):
>     return " WHERE ".join(filter(None,["SELECT * FROM vehicle"," AND
".join(filter(None,["%s IN (%s)" % (b,a) * max(0,min(1,len(a))) for a,b in
zip([",".join(["'%s'" % i for i in x]) for x in
[ids,engineTypes,transTypes]],["id","engineType","transType"])]))]))
>
> There's a fairly nasty trick in there!
>
> Second (much better) attempt:
>
> def buildSql(ids, engineTypes, transTypes):
>     return " WHERE ".join(filter(None,["SELECT * FROM vehicle"," AND
".join([" IN ".join ([b,"(%s)"%a]) for a,b in zip([",".join(["'%s'" % i for
i in x]) for x in
[ids,engineTypes,transTypes]],["id","engineType","transType"]) if a])]))
>
>
> >>> print buildSql(['vin1', 'vin2', 'vin3'], ['4cy', '6cy'],
['4sp','5sp'] )
> SELECT * FROM vehicle WHERE id IN ('vin1','vin2','vin3') AND engineType IN
('4cy','6cy') AND transType IN ('4sp','5sp')
>
> >>> print buildSql(['vin1', 'vin2', 'vin3'], ['4cy', '6cy'], [] )
> SELECT * FROM vehicle WHERE id IN ('vin1','vin2','vin3') AND engineType IN
('4cy','6cy')
>
> >>> print buildSql(['vin1', 'vin2', 'vin3'], [], [] )
> SELECT * FROM vehicle WHERE id IN ('vin1','vin2','vin3')
>
> >>> print buildSql([], [], [] )
> SELECT * FROM vehicle
>
> >>> print buildSql([], ['4cy', '6cy'], [] )
> SELECT * FROM vehicle WHERE engineType IN ('4cy','6cy')
>
>
>
> Homework exercise: fill in the blank to generalise it better :)
>
> def buildSql(table, values, names):
>   return ...
>
>
> buildSql ("engines", [[1,2],[],[],[11,22]], ["id","type","size","weight"])
> --> SELECT * FROM engines WHERE id IN ('1','2') AND weight IN ('11','22')
>
>
> At the end of the day I think using 'if' would be more sane!!
>
The whole question of generating SQL from query input has a long and
fascinating history. It's obvious you want to ensure that you don't include
a cluse for those items the user has provided no specifications for.

If you chose to generalise a little more you might get to the idea of a
"specifier", consisting of a field name, a type (to say whether quotes are
required around the values) and a value list. You could then do something
like [UNTESTED CODE WARNING]:

    class Spec():
        def __init__(self, name, str, vlist):
            self.name = name
            self.str = str
            self.vlist = vlist

        def vals(self)
            if self.str:
                tstr = "'%s'"
            else
                tstr = "%s"
            return ", ".join([tstr % x for x in vlist])

        def cclause(self):
            return "%s IN (%s)" % (self.name, self.vals())

    def buildSQL(tbl, *specifiers):
        conds = " AND ".join([s.cclause() for s in specifiers])

It would be simpler not to provide the specifiers for those fields the user
had omitted, but it would also be fairly easy to add a predicate to
determine whether the cclause() method should process it or not, as in:

    def buildSQL(tbl, *specifiers):
        conds = " AND ".join([s.cclause() for s in specifiers if s.vlist])

Such class-based manipulations might not be worthwhile if this were simply
to handle user inputs and then be forgotten, but similar code could be used
procedurally rather than in object-oriented fashion.

regards
 Steve
--
http://www.holdenweb.com/