Escaping optional parameter in WHERE clause

[Steve Holden]

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/