Using query parameters subtitution outside of execute()

[Peter Otten]

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")