如何在Python程序中过滤SQL数据

Question

我有这样的代码，它从MSSQL数据库中获取数据，并将其显示在程序中的表中。我的问题是：我如何制作一个过滤器按钮，这样我就只能显示包含"；这个人名"；或"；这个日期"；或"；该订单编号"；等等，如果我愿意的话？

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)

Answer 1

我建议将潜在的SQL查询定义为f字符串：

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

然后，您可以设计按钮，单击这些按钮后，将根据要筛选的内容为变量criterion1和criterion2分配值。然后将f字符串.execute()和.fetchall()作为返回值，得到一个可以循环显示该表的列表。