MRAB wrote:
> 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)
db.execute(query, tuple(values))
> result = db.fetchall()
> return result
The .execute() method should take two arguments, the second being a
tuple of data values. Some interfaces don't like an empty tuple when the
query has no parameters.
regards
Steve
--
Steve Holden +1 571 484 6266 +1 800 494 3119
Holden Web LLC http://www.holdenweb.com/
Want to know? Come to PyCon - soon! http://us.pycon.org/
--
http://mail.python.org/mailman/listinfo/python-list
