elegent sql building

[Eddie Corns]

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!!

Eddie