How to Filter SQL data in Python Program?

Question

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)

Schnitte · Accepted Answer · 2022-02-17 14:38:59Z

2

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 Feb 17, 2022 at 14:38

Schnitte

1,2276 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to Filter SQL data in Python Program?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related