How to Filter SQL data in Python Program?-CodePudding

I have this code, which takes data from MSSQL database and shows it in a table in my program. My question is: How could I make a filter button so I could only show items containing "this persons name" or "this date" or "this order nr" etc if I wish?


def View():
    for item in set.get_children():
      set.delete(item)
      

    con1 = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=1.1.1.1;DATABASE=TEST;UID=#####;PWD=#####')
    cur2 = "SELECT [Date], [Order], [Position], [Client], [Product], [Operation], [Amount], [OpeningSide], [Operator], [Machine] FROM TEST ORDER BY [Date] DESC"

    cur1 = con1.cursor()
    cur1.execute(cur2)
    rows = cur1.fetchall() 

    for row in rows:

        test = row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9]
        set.insert("", tk.END, values=test) 

    con1.close()

#TABLE!
set = ttk.Treeview(ws)
set.grid(row=1,column=0, sticky=(N, S, E, W), columnspan=10, rowspan=2)

set['columns']= ('Date','Order','Pos','Client','Product','Operation','Amount','Hand','Operator','Machine',)
set.column("#0", width=0,  stretch=NO)
set.column("Date",anchor=CENTER, width=80)
set.column("Order",anchor=CENTER, width=50)
set.column("Pos",anchor=CENTER, width=30)
set.column("Client",anchor=CENTER, width=110)
set.column("Product",anchor=CENTER, width=80)
set.column("Operation",anchor=CENTER, width=110)
set.column("Amount",anchor=CENTER, width=50)
set.column("Hand",anchor=CENTER, width=20)
set.column("Operator",anchor=CENTER, width=110)
set.column("Machine",anchor=CENTER, width=90)

set.heading("#0",text="",anchor=CENTER)
set.heading("Date",text="Date",anchor=CENTER)
set.heading("Order",text="Order",anchor=CENTER)
set.heading("Pos",text="Pos",anchor=CENTER)
set.heading("Client",text="Client",anchor=CENTER)
set.heading("Product",text="Product",anchor=CENTER)
set.heading("Operation",text="Operation",anchor=CENTER)
set.heading("Amount",text="Amount",anchor=CENTER)
set.heading("Hand",text="Hand",anchor=CENTER)
set.heading("Operator",text="Operator",anchor=CENTER)
set.heading("Machine",text="Machine",anchor=CENTER)

nupp1 = ttk.Button(ws, text="Renew", command=View)
nupp1.grid(row=3,column=5, sticky=(N, S, E, W), pady=5, padx=5)
nupp2 = ttk.Button(ws, text="Filters", command=NotFinished)
nupp2.grid(row=3,column=6, sticky=(N, S, E, W), pady=5, padx=5)

CodePudding user response：

I would suggest to define a potential SQL query as an f string:

sql_query = f"SELECT {criterion1} FROM PROD_MachiningEvents WHERE {criterion2};"

You can then design buttons that will, when clicked, assign values to the variables criterion1 and criterion2 depending on what you want to filter for. Then you .execute() and .fetchall() the f string and get, as a return value, a list that you can loop over to display the table.