Python+sqlite: the LIKE query with wildcards

Question

Hi I'm trying to search an SQlite table based on a user input with wildcards. I've tried different methods by I can't get it to work. This is the last thing I tried, it's a solution I found here actually, but it might have changed in python 3.

    search = input("type name or partial name: ")
    cur.execute("select * from contacts where name like ?",
                ('%'+search+'%'))

This produces this error on that last line there.

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

I tried a lot of different thing and this is driving me crazy. I'm starting to think this isn't possible without reading the entire table to a string first.

Answer 1

Your problem just seems to be that you have supplied a string as the second argument to cur.execute, when you probably meant to supply a single-element tuple containing the string.

Since a string is a sequence, every character in the string will be interpreted as a separate parameter and that's why you see the "Incorrect number of bindings" error.

Try:

cur.execute("select * from contacts where name like ?", ('%'+search+'%',))

Note the comma in the tuple, ('%'+search+'%',). Without it, it isn't a tuple, just a string in parentheses.