[ACCEPTED]-SQLite parameter substitution and quotes-sqlite

Accepted answer
Score: 21

To anyone who like me found this thread 6 and got really frustrated by people ignoring 5 the fact that sometimes you can't just ignore 4 the quotes (because you're using say a LIKE 3 command) you can fix this by doing something 2 to the effect of:

var = name + "%"
c.execute('SELECT foo FROM bar WHERE name LIKE ?',(var,))

This will allow you to 1 substitute in wildcards in this situation.

Score: 14

I find the named-parameter binding style 6 much more readable -- and sqlite3 supports it:

c.execute('SELECT cleanseq FROM cleanseqs WHERE newID=:t', locals())

Note: passing 5 {'t': t} or dict(t=t) instead of locals() would be more punctiliously 4 correct, but in my opinion it would interfere 3 with readability when there are several 2 parameters and/or longer names. In any case, I 1 do find the :t better than the ?;-).

Score: 10

about """If I delete the quotes sourronding 11 the ?, it works. But I want the quotes to 10 remain there since I remember that there 9 are cases where I need them."""

What you 8 remember from when you were building the 7 whole SQL statement yourself is irrelevant.

The 6 new story is: mark with a ? each place in 5 the SQL statement where you want a value 4 substituted then pass in a tuple containing 3 one value per ? -- it's that simple; the 2 wrapper will quote any strings to make sure 1 that they are acceptable SQL constants.

Score: 4

Lose the quotes around ?

c.execute('select cleanseq from cleanseqs WHERE newID=?',(t,))

It's treating it 3 as the string "?".

Do you need to use double 2 quotes around the whole expression, instead 1 of singles?

Score: 3

The library will handle quoting and escaping 1 for you. Simply write your query like this:

c.execute('SELECT cleanseq FROM cleanseqs WHERE newID=?', (name,))

More Related questions