someone wrote:
Hi,
as you can see below I have some optional parameter for my query (mf,
age). They are in WHERE clause only if not empty.
In this function they are not escaped as, for example, 'search'
parameter, cause I can't pass them to execute function, which does
escaping automatically.
I could write another if's block like that
if mf and not age:
db.execute(query, search, mf, limit)
if age and not mf:
db.execute(query, search, age, limit)
if age and mf:
db.execute(query, search, mf, age, limit)
Is there a better way to deal with optional WHERE clause?
Pet
def getData(self, db, params):
search = params.get('search','')
age = params.get('age','')
mf = params.get('mf','')
limit = params.get('limit',1)
if mf:
mf = " AND mf = %s " % mf
if age:
age = " AND age = %s " % age
query = """
SELECT * FROM mytable
WHERE class = 'P'
AND name = %s
""" + mf + """
""" + age + """
ORDER BY id DESC
LIMIT %s;
"""
db.execute(query, search, limit)
result = db.fetchall()
return result
How about:
def getData(self, db, params):
search = params.get('search', '')
age = params.get('age', '')
mf = params.get('mf', '')
limit = params.get('limit', 1)
query = """
SELECT * FROM mytable
WHERE class = 'P'
AND name = %s
"""
values = [search]
if mf:
query += " AND mf = %s"
values.append(mf)
if age:
query += " AND age = %s"
values.append(age)
query += """
ORDER BY id DESC
LIMIT %s;
"""
values.append(limit)
db.execute(query, *values)
result = db.fetchall()
return result
--
http://mail.python.org/mailman/listinfo/python-list
