Kookaburra Kookaburra - 10 days ago 6
Python Question

Search SQLite3 database using a Python variable

I am trying to search my SQLite3 database using a pythonic variable as a search term. The term I'm searching for is a part of the contents of the cell in the database (e.g. Smith in a cell: [Harrison GB, Smith JH]) and is often in the middle of the string in a cell.

I have tried to code it as shown below:

def read_from_db():
c.execute("SELECT authors, year, title, abstract FROM usertable WHERE authors LIKE (?)",(var1,))
data = c.fetchall()
print(data)
for row in data:
searchlist.append(row)

var1="Smith"
read_from_db()


This should show the results row after row. However, I get 0 results when var1 = "Smith". When I change its value to "Harrison GB, Smith JH", I get all the results.

When I try to solve it by changing the SQLite3 execute query I yield an error.

ERROR
c.execute("SELECT authors, year, title, abstract FROM usertable WHERE authors LIKE '%?%'",(var1,))
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.


I get syntax errors if I change the endings with: $?$, (%?%) etc. I tried this with:

...authors="%?%"


But this doesn't work either. There is a few similar questions on SO, but they don't exactly solve my issue...

I am running Python 3.4 and SQLite3 on Windows 10.

Answer

Consider concatenating the % wildcards to the binded value, var1:

c.execute("SELECT authors, year, title, abstract" +
          " FROM usertable WHERE authors LIKE (?)", ('%'+var1+'%',))

The reason you need to do so is the ? placeholder substitutes a string literal in parameterized queries and for LIKE expressions, wildcards with values together are string literals as denoted by their enclosed single quotes:

SELECT authors, year, title, abstract FROM usertable WHERE authors LIKE '%Smith%'

Your initial attempt failed because you wrap ? with single quotes and the cursor cannot bind the param to prepared statement properly and so the symbol is taken literally as question mark.