Path: csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!newsfeed.xs4all.nl!newsfeed2a.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Peter Otten <__peter__@web.de>
Subject: Re: Using query parameters subtitution outside of execute()
Date: Fri, 28 Mar 2014 14:53:06 +0100
Organization: None
References: <5335367B.2090604@digitalfantasy.it> <lh3ek9$236$1@ger.gmane.org> <533562E3.1000400@digitalfantasy.it>
Mime-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 7Bit
User-Agent: KNode/4.11.5
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.8669.1396014805.18130.python-list@python.org>
Lines: 81
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:69276

Daniele Forghieri wrote:

> 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.

How about that one:

def query_subst(sql, parameters):
    return sql, parameters

def load_all(cursor, id, query_add=None):
    query =  'select * from files where catalog = ?'
    parameters = (id,)
    if query_add is not None:
        query += " and " + query_add[0]
        parameters += query_add[1]
    cursor.execute(query, parameters)
    ...

enabled = True
hidden = False
min_date = datetime.date.today()

query_add = query_subst(
    'enabled = ? and hide = ? and date > ?', 
    (enabled, hidden, min_date))

load_all(cs, 42, query_add)

>      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 ;)

Get over it ;)