Tkinter/SQLite3: GUI that filters table data based on specific entry fields-CodePudding

I am trying to create a Tkinter GUI that based on user input, will go through a table and select out rows that match the criteria of only the entry fields that have had something inputted into them.

For example, lets say we have a table called pets with the columns name, age and species

  ------------------ ----------- ----------- 
 | name             | age       | species   |
  ------------------ ----------- ----------- 
 | Muffin           | 3         | Cat       |
 | Dash             | 8         | Dog       |
 | Winnie           | 10        | Cat       |
 | Chewie           | 1         | Hamster   |
 | Slinky           | 3         | Snake     |
  ------------------ ----------- -----------

And I have this Tkinter app which a user enters the criteria they want to filter:

from tkinter import *
import tkinter as tk
import sqlite3

def main():

    def do_something():
        connection = sqlite3.connect("my_database.db")
        cursor = connection.cursor()

        name = name_str.get()
        age = age_str.get()
        species = species_str.get()

        cursor.execute("SELECT * FROM pets WHERE name=? AND age=? AND species=? ", (name, age, species))
        pet = cursor.fetchall()

        for row in pet:
            print(row)
    
    root = tk.Tk()

    name_str = StringVar()
    age_str = StringVar()
    species_str = StringVar()

    frame = Frame(root, padx=20, pady=20)
    frame.pack()

    name_label = Label(frame, text="Name").pack()
    name_entry = Entry(frame, textvariable=name_str).pack()

    age_label = Label(frame, text="Age").pack()
    age_entry = Entry(frame, textvariable=age_str).pack()

    species_label = Label(frame, text="Species").pack()
    species_entry = Entry(frame, textvariable=species_str).pack()

    filter_button = Button(frame, text="Filter", command=do_something).pack()

    mainloop()

main()

If I want to find all the Cats in the table that are age 10, I would type 10 in the Age field and Cat in the Species field, using the SELECT statement, I would hope to print out pets that match that criteria (The only one in the table being Winnie)

However, because I have not provided an entry into the Name field, nothing is retrieved from the table (I assume this is because the StringVar() becomes NoneType when nothing is entered so the function is looking for name=None)

What is the best way to implement this filtering system that allows me to search the table based only the the entry fields I use? Is there a better way to format my SELECT statement that will do this for me or will I have to create a logical function using Python code?

I have tried using if statements to check if the fields have an entry in them before proceeding, with two methods of trying to filter data.

The first being a list that contains the columns I want to search, and if an entry is detected in any of the fields, it would add the SQL column name to an list called args = [].

First attempt:

args = []

if len(name) == 0:
    pass
else:
    args.append("name=?")

if len(age) == 0:
    pass
else:
    args.append("age=?")

if len(species) == 0:
    pass
else:
    args.append("species=?")

cursor.execute("SELECT * FROM pets WHERE {}").format(args)

The second method I tired was if the if statement detects an entry in the field, it will select all the rows that match the user input and add it onto a list, then move onto the next if statement with the hope that I could split the data into separate strings by index, but it didn't work how I expected it to.

Second attempt:

if len(name) == 0:
    pass
else:
    cursor.execute("SELECT name FROM pets WHERE name=?", [name])
    name_data = cursor.fetchall()
            
    name_list = []
            
    for row in name_data:
        name_list.append(row)


if len(age) == 0:
    pass
else:
    cursor.execute("SELECT age FROM pets WHERE age=?", [age])
    age_data = cursor.fetchall()
            
    age_list = []
            
    for row in age_data:
        age.append(row)


if len(species) == 0:
    pass
else:
    cursor.execute("SELECT species FROM pets WHERE species=?", [species])
    species_data = cursor.fetchall()
            
    species_list = []
            
    for row in species_data:
        species.append(row)

CodePudding user response：

You need to build the SQL dynamically:

def do_something():
    connection = sqlite3.connect("my_database.db")
    cursor = connection.cursor()

    name = name_str.get().strip()
    age = age_str.get().strip()
    species = species_str.get().strip()

    conditions = []
    args = []
    if name:
        conditions.append('name = ?')
        args.append(name)
    if age:
        conditions.append('age = ?')
        args.append(age)
    if species:
        conditions.append('species = ?')
        args.append(species)

    # default select all if no condition is given
    sql = "SELECT * FROM pets"
    if conditions:
        # add the WHERE clause if any condition is given
        sql  = " WHERE " " AND ".join(conditions)

    cursor.execute(sql, args)
    pets = cursor.fetchall()

    if pets:
        for pet in pets:
            print(pet)
    else:
        print('No pet found')