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?

score 17 · Accepted Answer · answered Feb 19 '13 at 04:29

17

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

answered Feb 19 '13 at 04:29

Xymostech

9,710
3
34
44

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 – Molly Walters Feb 19 '13 at 04:37

score 2 · Answer 2 · answered May 20 '19 at 12:01

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

answered May 20 '19 at 12:01

Jawad Saqib

95
1
3
10

score 0 · Answer 3 · answered Dec 15 '18 at 14:20

0

If you want to use LIKE

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

answered Dec 15 '18 at 14:20

Punnerud

7,195
2
54
44

Searching SQLite Database with Python Variables

3 Answers3

Linked