Using query parameters subtitution outside of execute()
Daniele Forghieri
guru at digitalfantasy.it
Fri Mar 28 10:16:05 EDT 2014
Il 28/03/2014 14:53, Peter Otten ha scritto:
> 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)
This one is, IMHO, cleaner and more manageable!
Thank you very very much, I really appreciate your help.
>> 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 ;)
>
>
I'm trying but the old habits comes out, the first time I need
to parse a string I do it like in C, looking at every single char using
a couple of help function: the performance were really horrible, the
memory used was huge than I change the way I do things (and start to use
sqlite3 to store my data instead of using text files, parsed by a
proprietary lib).
The worse is the contrary, when I must use C and I think 'Here
I use a dictionary, at the end convert it in a list that I sort with
that key ...' only to realize that I don't have dictionary and the list
I can use are very less manageable that the ones I used in Python ...
It seems to me that going from C to Python you start writing
inefficient code or write more lines than an average Python programmer
but 'something moves' and the result happens. Moving from Python to C I
always feel like I missed something and the first approach, good for
Python, is simply not working in C ;(
Thanks again
Daniele Forghieri
More information about the Python-list
mailing list