Using query parameters subtitution outside of execute()

[Daniele Forghieri]

Il 28/03/2014 10:16, Peter Otten ha scritto:
> Daniele Forghieri wrote:
>
>> Hi to all. I'm using sqlite3 with python 2.7 on windows.
>>
>> I use the query substitution parameters in my query but I need to pass
>> part of the query to a function, something like (it's not the real
>> examples, just to clarify the question):
>>
>> def loadAll(cursor, id, queryAdd = None):
>>       if queryAdd is None:
>>           qry =  'select * from files where catalog = ?'
>>       else:
>>           qry = 'select * from files where catalog = ? and %s' %
>>           (queryAdd))
>>
>>       cursor.execute(qry, (id, ))
>>       ...
>>
>> I would like to use the query substitution even when I create, in
>> another piece of code, the queryAdd part, something like:
>>
>> queryAdd = cursor.querySubst('enabled = ? and hide = ? and data > ?',
>> (enabled, hidden, min_date, ))
>>
>> when the function take care of the date format, quoting the parameter
>> and so on
>>
>> It's possible or not ?
> You can use named parameters
>
> http://docs.python.org/dev/library/sqlite3.html#cursor-objects
>
> Your function might become (untested)
>
> def load_all(cursor, parameters, condition="catalog = :id"):
>      query =  'select * from files where ' + condition
>      cursor.execute(query, parameters)
>      ...
>
> load_all(
>      cursor, dict(id=42, fromdate=datetime.date.today()),
>      condition="catalog = :id and date >= :fromdate")
>

     Thank. With this I can solve the problem but I have to specify the 
query twice and if I have to change something I need to made it 
everywhere I use the function and is something I would like to avoid.

     I also don't like very mush to pass or create a dict for a function 
call but that's probably me coming from old plain C ;)

     Daniele Forghieri