Searching SQLite Database with Python Variables

Question

I have a SQLite database that I'd like to search using Python variables as in:

cur.execute("SELECT * FROM list WHERE InstitutionName=Variable")

Ideally this would allow me to return each row as a list to a larger list that contains all the rows a user is searching for. I realize what I have above is pseudocode. How could I actually write it?

Answer 1

I think that you want to use the parameter substitution feature:

cur.execute("SELECT * FROM list WHERE InstitutionName=?", (Variable,))

There's more documentation in the actual execute command and in the 4th example code box on the sqlite3 docs page.

Note that you should explicitly not use the % or format function as this is susceptible to injection attacks:

# NEVER DO THIS
cur.execute("SELECT * FROM list WHERE InstitutionName='%s'" % (Variable,))

• Molly Walters: I'd give you an up vote if I had enough reputation! This is exactly what I was looking to do! Thank you so much<3

Answer 2

If you want to display multiple records from database then you can use the (LIKE) keyword in your sql query:

("SELECT * FROM TABLENAME WHERE name LIKE'%?%'",(Variable,))

Answer 3

If you want to use LIKE

cur.execute("SELECT * FROM list WHERE InstitutionName like '%'||?||'%'", (Variable,))