Issue
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)
Solution
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.
Answered By - Schnitte
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.