SQLite parameter substitution and quotes

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)

Solution 1:

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 2:

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 ?;-).