BlogueroConnor BlogueroConnor - 16 days ago 6
Python Question

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)

Answer

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.