[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,))

Source: stackoverflow.com

[ACCEPTED]-SQLite parameter substitution and quotes-sqlite

Python, Sqlite3 - How to convert a list to a BLOB cell

django error 'too many values to unpack'

How to discover table properties from SQLAlchemy mapped object

Problem with regexp python and sqlite

Unique combination of fields in SQLite?

Can SQLAlchemy automatically create relationships from a database schema?

Mapping result rows to namedtuple in python sqlite

Comparing two potentially NULL values in SQLite query

sharing a :memory: database between different threads in python using sqlite3 package

Operator NOT IN with Peewee