SQLite parameter substitution and quotes
Solution 1
about """If I delete the quotes sourronding the ?, it works. But I want the quotes to remain there since I remember that there are cases where I need them."""
What you remember from when you were building the whole SQL statement yourself is irrelevant.
The new story is: mark with a ? each place in the SQL statement where you want a value substituted then pass in a tuple containing one value per ? -- it's that simple; the wrapper will quote any strings to make sure that they are acceptable SQL constants.
Solution 2
To anyone who like me found this thread and got really frustrated by people ignoring the fact that sometimes you can't just ignore the quotes (because you're using say a LIKE command) you can fix this by doing something to the effect of:
var = name + "%"
c.execute('SELECT foo FROM bar WHERE name LIKE ?',(var,))
This will allow you to substitute in wildcards in this situation.
Solution 3
I find the named-parameter binding style much more readable -- and sqlite3
supports it:
c.execute('SELECT cleanseq FROM cleanseqs WHERE newID=:t', locals())
Note: passing {'t': t}
or dict(t=t)
instead of locals()
would be more punctiliously correct, but in my opinion it would interfere with readability when there are several parameters and/or longer names. In any case, I do find the :t
better than the ?
;-).
Solution 4
Lose the quotes around ?
c.execute('select cleanseq from cleanseqs WHERE newID=?',(t,))
It's treating it as the string "?".
Do you need to use double quotes around the whole expression, instead of singles?
Solution 5
The library will handle quoting and escaping for you. Simply write your query like this:
c.execute('SELECT cleanseq FROM cleanseqs WHERE newID=?', (name,))
Comments
-
BlogueroConnor almost 2 years
I have this line that works OK:
c.execute('select cleanseq from cleanseqs WHERE newID="%s"'%name)
But I want to use SQLite parameter substitution instead instead of string substitution (because I see here that this is safer).
This is my (failed) try:
t = (name,) c.execute('select cleanseq from cleanseqs WHERE newID="?"',t)
But this line returns:
'Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.'
So the left part of my statement doesn't work. I am supplying one binding (name, in t) but seems that the question mark (?) is not being parsed. If I delete the quotes sourronding the ?, it works. But I want the quotes to remain there since I remember that there are cases where I need them.
So the question is: How do I convert this line:
c.execute('select cleanseq from cleanseqs WHERE newID="%s"'%name)
-
Martijn about 11 years+1 It also makes it easier to re-use a variable multiple times in the same query, without having to re-add it several times.