Python SQLite parameter substitution with wildcards in LIKE

Question

I am attempting to use a parametrized LIKE query with Python's Sqlite library as below:

self.cursor.execute("select string from stringtable where string like '%?%' and type = ?", (searchstr,type))

but the ? inside of the wildcard is not being evaluated leaving me with this error:

"sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied."

I also tried to use the tagged version of querying with:

like '%:searchstr%' and in the list having {"searchstr":searchstr...

but when I do that the query runs but never returns any results even though manually putting in "like '%a%'"... return hundreds of results as it should

any suggestions please?

score 108 · Accepted Answer · answered Jun 23 '10 at 20:40

108

The quotes protect either ? or :name from being taken as a place-holder -- they're taken literally. You need to place the percent signs around the string you're passing, and use the plain placeholder without quotes. I.e.:

self.cursor.execute(
  "select string from stringtable where string like ? and type = ?",
  ('%'+searchstr+'%', type))

Note that neither ? is in quotes -- and that's exactly as it should be for them to be taken as placeholders.

answered Jun 23 '10 at 20:40

Alex Martelli

854,459
170
1,222
1,395

Does Python have PHP-like parsing of variables in double quotes? e.g. `"$var"` in PHP would parse to the value of `$var` enclosed in quotes. – Hamman Samuel Jun 18 '15 at 21:39
1

@HammanSamuel, no, in Python single and double quotes are equivalent -- to interpolate variables you must be explicit, e.g you can use https://docs.python.org/2/library/string.html#template-strings, passing e.g `vars()` to the `.substitute` method. – Alex Martelli Jun 19 '15 at 20:53
wow, this is surprising! Any way around it? any way to put the % inside de string? – josinalvo May 07 '20 at 00:03
btw, thank you for the answer, it worked like a charm – josinalvo May 07 '20 at 00:03

score -1 · Answer 2 · answered Nov 02 '20 at 13:56

Though is not an exact answer to question, and not to compete to be one, this solution however still tries to answer "parameter substitution in LIKE" as the title draws attention with this in mind too (like it did to me)

I was working in a similar manner and I combined two styles together. this way, user can enter the field name into function along with "%" being in the search parameter itself.

though field name needs sanitation, it is enough to use in small test projects. also moving "%" wildcard from query to a parameter allows user to use other wildcards.

database.py

def find_item(field,term):
    cursor.execute("""
        SELECT rowid,* FROM customers
        WHERE (%s) LIKE ?
    """%field,(term,))

app.py

import database
database.find_item("first_name","%li%")
database.find_item("email","_li%")

Python SQLite parameter substitution with wildcards in LIKE

2 Answers2

Linked

Related