One of the big problems with the code in my last post was that in my final program I will probably have many more checkboxes. That means that the number of if statments I would have to make could grow factorially into the thousands. I needed to come up with a solution and with the help of Alden and Steve, I managed to dynamically build a query based on what checkboxes are selected. Here’s the new function:
[python]
def connectData(*args):
T.delete(1.0, END)
f = fruitVar.get()
v = vegVar.get()
if f == 0 and v == 0:
T.insert(END, “No results.”)
else:
myDict = {}
myDict[‘Fruit’] = f
myDict[‘Vegetable’] = v
container = []
for key, value in myDict.items():
if value == 1:
container.append(key)
myText = “SELECT Name FROM food”
for value in container:
if container.index(value) == 0:
myText = myText + ” WHERE type = ”
myText = myText + “\”” + value + “\””
if container.index(value) != container.index(container[len(container)-1]):
myText = myText + ” OR type = ”
for row in cursor.execute(myText):
T.insert(END, row)
T.insert(END, ‘\n’)
[/python]
EDIT: So Steve got a little ambitious and went out of his way to show me how he could improve the code. It’s pretty intense and I’ll go over what he did when I have more time. This is what he came up with:
[python]
import sqlite3
from tkinter import *
root = Tk()
conn = sqlite3.connect(“C:/code/py3/SQLitePractice/test2.db”)
cursor = conn.cursor()
def connectData(*args):
T.delete(1.0, END)
myList = []
for key, value in checkBoxes.items():
if value.get() == 1:
myList.append(key)
if not myList:
T.insert(END, “No results.”)
return
items = ‘,’.join( “‘” + type + “‘” for type in myList)
query = “SELECT Name FROM food WHERE type in (%s)” % items
for row in cursor.execute(query):
T.insert(END, row)
T.insert(END, ‘\n’)
def createCheckBox(name,display):
var = IntVar(root)
var.trace(‘w’, connectData)
checkbox=Checkbutton(root, text=display, variable=var)
checkbox.pack()
checkBoxes[name] = var;
return checkbox;
checkBoxes = {}
createCheckBox(“Fruit”,”Fruit!”)
createCheckBox(“Vegetable”,”Vegetable!”)
createCheckBox(“Meat”,”Meat!”)
createCheckBox(“Snack”,”Snack!”)
S = Scrollbar(root)
T = Text(root, height=8, width=50)
S.pack(side=RIGHT, fill=Y)
T.pack(side=LEFT, fill=Y)
S.config(command=T.yview)
T.config(yscrollcommand=S.set)
root.mainloop()
[/python]