How to select one variable based on another in Tkinter/SQL?

Question

my problem is that I want the second dropdown menu "Operatsioon1" to get it's values based on what is selected in "Pink1". The database PROD_MachineOperations includes both Machine names and the Operatsions. But PROD_Machine also includes the same Machines.

The concept is that I have the names of the machines in a SQL database PROD_Machines, that is the first selection, then based on the machine that is selected it will list the operations that are for that machine in the PROD_MachineOperations database as MACHINE_NAME, OPERATION

from tkinter import *
from tkinter import ttk
import tkinter as tk
import pyodbc

DEFAULT_ENCODING = 'utf-8'

ws=Tk()
ws.title('Test')
ws.geometry('150x100')
ws['bg'] = '#26658f'
ws.resizable(False, False)

con1 = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=192.168.1.8;DATABASE=HUT;UID=######;PWD=######')
    
cur2 = "SELECT [Machine] FROM PROD_Machines"
cur1 = con1.cursor()
cur1.execute(cur2)
proov1 = [x[0] for x in cur1.fetchall()]

def callback_pink(event):
    """Callback event called by combobox 'Pink1' via Pink1.bind("<<ComboboxSelected>>", callback_pink)"""

    con1 = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=192.168.1.8;DATABASE=HUT;UID=######;PWD=#######')
    cur3 = "SELECT [Operation] FROM PROD_MachineOperations WHERE [Machine] LIKE (?)"
    val2 = (event.widget.get())

    cur4 = con1.cursor()
    cur4.execute(cur3,val2)
    proov2 = [x[0] for x in cur4.fetchall()]

    Operatsioon1["values"] = proov2

    Operatsioon1.current(len(Operatsioon1["values"])-1)
    Operatsioon1.current(0)

    cur3 = "SELECT [Operator] FROM PROD_MachineOperators WHERE [Machine] LIKE (?)"
    val2 = (event.widget.get())

    cur4 = con1.cursor()
    cur4.execute(cur3,val2)
    proov2 = [x[0] for x in cur4.fetchall()]

    Operaator1["values"] = proov2

    Operaator1.current(len(Operaator1["values"])-1) 
    Operaator1.current(0)




Pink_entry = tk.StringVar()
Pink1 = ttk.Combobox(ws, textvariable=Pink_entry, width = 17)
Pink1['values'] = proov1
Pink1['state'] = 'readonly'
Pink1.grid(row=4,column=2,padx=(10, 10))
Pink1.bind("<<ComboboxSelected>>", callback_pink, callback_pink)


Operatsioon_entry = tk.StringVar()
Operatsioon1 = ttk.Combobox(ws, textvariable=Operatsioon_entry, width = 17)
Operatsioon1['state'] = 'readonly'
Operatsioon1.grid(row=6,column=5,padx=(10, 10))

Operaator_entry = tk.StringVar()
Operaator1 = ttk.Combobox(ws, textvariable=Operaator_entry, width = 17)
Operaator1['state'] = 'readonly'
Operaator1.grid(row=4,column=3,padx=(10, 10))

ws.mainloop()

Answer 1

Use events and callbacks. For example:

from tkinter import *
from tkinter import ttk
import tkinter as tk
# import pyodbc

DEFAULT_ENCODING = 'utf-8'

ws=Tk()
ws.title('Haapsalu Uksetehas')
ws.geometry('150x100')
ws['bg'] = '#26658f'
ws.resizable(False, False)


def callback_pink(event):
    """Callback event called by combobox 'Pink1' via Pink1.bind("<<ComboboxSelected>>", callback_pink)"""
    # here you get the currently selected value. You can use this function for any combobox
    selected_value = event.widget.get()

    # here you do your SQL query based on selected_value; selected_value holds your machine name; for example:
    # filter = selected_value
    # sql = "SELECT property FROM machine WHERE machine LIKE ?"
    # param = f"%{filter}%"
    # rows = cur1.execute(sql, param).fetchall()

    # here i simulate your received results
    rows = ["turn on", "turn off", "restart", "calibrate"]

    # here i replace the values of the second list with a list of tuples with joined strings of selected_value and rows
    Operatsioon1["values"] = [(f"{selected_value}, {row}",) for row in rows]

    # Operatsioon1.current(len(Operatsioon1["values"])-1)  # select the last available value
    Operatsioon1.current(0)  # select the first available value


# commented so i can test it
# con1 = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=192.168.1.8;DATABASE=HUT;UID=######;PWD=#####')
# cur2 = "SELECT [Machine] FROM PROD_Machines"
# cur1 = con1.cursor()
# cur1.execute(cur2)
# proov1 = [x[0] for x in cur1.fetchall()]

# this simulates the return from your PROD_MachineOperations table
proov1 = ["Machine 1", "Machine 2", "Machine 3", "Machine 4"]


Pink_entry = tk.StringVar()
Pink1 = ttk.Combobox(ws, textvariable=Pink_entry, width = 17)
Pink1['values'] = proov1
Pink1['state'] = 'readonly'
Pink1.grid(row=0, column=0, padx=(10, 10))
Pink1.bind("<<ComboboxSelected>>", callback_pink)

cur3 = "SELECT [Operation] FROM PROD_MachineOperations WHERE [Machine] LIKE (?)"
val2 = (Pink_entry.get())

# this now happens in your callback function
# cur4 = con1.cursor()
# cur4.execute(cur3,val2)
# proov2 = [x[0] for x in cur4.fetchall()]

# Operatsioon_entry = tk.StringVar()  # this doesnt do anything
Operatsioon1 = ttk.Combobox(ws, width=17)  # removed textvar=.. here
Operatsioon1['state'] = 'readonly'
Operatsioon1.grid(row=1, column=0, padx=(10, 10))

ws.mainloop()