elegent sql building

[Charles Medcoff]

On second thought there are a couple of minor drawback to this.

1.  Function parameter names must match the table column names - maybe
good, maybe bad.

2.  One is stuck with columns of all the same type - in this case
strings - of course I did not indicate that I needed that flexiblity
in my original problem statement.

I still like the SQL trick though.  With that in mind I had another
crack at it that addresses my comments above.  (Not quite as elegent
syntactically as your solution :( )

This version uses your SQL trick and the "and or trick" to address my
original problem.

Thanks again.

# original solution
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]))

# solution 2
def buildSql2(vehicleIds, engineTypes, transTypes):
    return "SELECT * FROM vehicle WHERE 1=1 %s %s %s" % \
        ( vehicleIds and "AND svehicleid IN (%s)" % ",".join(["'%s'" %
(id) for id in vehicleIds]) or "", \
          engineTypes and "AND ienginetype_id IN (%s)" %
",".join(["%s" % (e ) for e  in engineTypes]) or "", \
          transTypes and "AND itranstype_id IN (%s)" % ",".join(["%s"
% (t ) for t in transTypes]) or "")


def test():
    print buildSql2(['vin1', 'vin2', 'vin3'], ['4cy', '6cy'], ['4sp',
'5sp'] )
    print buildSql2(['vin1', 'vin2', 'vin3'], [], ['4sp', '5sp'] )
    print buildSql2(['vin1', 'vin2', 'vin3'], ['4cy', '6cy'], [] )
    print buildSql2(['vin1', 'vin2', 'vin3'], [], [] )


if __name__ == "__main__":
    test()