how to search SQLite3 DB using a variable in Python

user_869325 0
Viewed 135

I'm currently writing a program in Python which I need to search a SQLite3 database, I want to be able to search the DB using a variable, which I will get data for the variable from a Tkinter Combobox selection (GUI)

Below is my table code...

db = sqlite3.connect('db.sqlite3')
cursor = db.cursor()
cursor.execute('''
            CREATE TABLE IF NOT EXISTS depot_racks(
                id INTEGER PRIMARY KEY,
                rack TEXT,
                rackloc TEXT,
                floorzone TEXT,
                product TEXT,
                serial TEXT,
                status TEXT,
                entdate TEXT,
                servdate TEXT,
                parts TEXT,
                pat TEXT)''')

Below is my code for trying to search the DB using the variable which gets the users selection...

def search_all_racks():
    selection = rackTextBox.get()
    cursor.execute("SELECT * FROM depot_racks WHERE product LIKE '%selection%'")
    for row in cursor.fetchall():
        dataArea.insert('end', row)

This returns nothing, is there a way to use the selection variable inside the SQLite query?

Thanks in advance

python-3.x sqlite python
1 Answers

You are using the string literal 'selection' and not the value of the variable selection.
This is the correct way to do it:

cursor.execute("SELECT * FROM depot_racks WHERE product LIKE '%' || ? || '%'", (selection,))

This way the wildcards % will be concatenated with the value of the string selection.

user_104988280
Related