[Python] Getting 2 listbox to work correctly like most norrmal Listbox

I have two listboxes the first pulls information from a table that has a current list of machines. I what once the box get focus the list drops down and allows you to start typing narrow the list then use arrows keys to scroll down to choice then hitting enter or tab to select. If the choice is not there accept new and update table

Then when going to next listbox based on choice from fisrt listbox sorts items froom the table and then follows the same fucntions as first. I have not programed for over 20 years so thought I would star playing around again thanks for any help here is all the code any suggestions is greatly appreatited.

import tkinter as tk
from tkinter import messagebox, ttk
import sqlite3
import pandas as pd

# ---------- Database Setup ----------
conn = sqlite3.connect('fmea.db')
cursor = conn.cursor()

cursor.execute('''
CREATE TABLE IF NOT EXISTS failures (
id INTEGER PRIMARY KEY AUTOINCREMENT,
machine TEXT,
failure_mode TEXT,
severity INTEGER,
occurrence INTEGER,
detection INTEGER,
rpn INTEGER,
fix TEXT
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS machines (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS failure_modes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
machine_id INTEGER,
mode TEXT,
UNIQUE(machine_id, mode),
FOREIGN KEY(machine_id) REFERENCES machines(id)
)
''')

# Insert starter machines if table is empty
cursor.execute('SELECT COUNT(*) FROM machines')
if cursor.fetchone()[0] == 0:
starter_machines = ["Press 1", "Press 2", "Oven 3", "Conveyor A", "Mixer 4"]
for name in starter_machines:
cursor.execute('INSERT INTO machines (name) VALUES (?)', (name,))
conn.commit()

# Insert starter failure modes (INSERT OR IGNORE avoids duplicates)
cursor.execute('SELECT id, name FROM machines')
machine_rows = cursor.fetchall()
starter_failure_modes = {
"Press 1": ["Hydraulic Leak", "Sensor Fault"],
"Press 2": ["Motor Overheat", "Pressure Drop"],
"Oven 3": ["Temp Spike", "Heater Failure"],
"Conveyor A": ["Belt Slippage", "Motor Jam"],
"Mixer 4": ["Shaft Misalignment", "Power Surge"]
}

for machine_id, name in machine_rows:
if name in starter_failure_modes:
for mode in starter_failure_modes[name]:
cursor.execute(
'INSERT OR IGNORE INTO failure_modes (machine_id, mode) VALUES (?, ?)',
(machine_id, mode)
)
conn.commit()

# Clean up any duplicate failure_modes (just in case)
cursor.execute('''
DELETE FROM failure_modes
WHERE id NOT IN (
SELECT MIN(id)
FROM failure_modes
GROUP BY machine_id, mode
)
''')
conn.commit()


# ---------- Functions ----------
def get_machine_list():
cursor.execute('SELECT name FROM machines ORDER BY name')
return [row[0] for row in cursor.fetchall()]


def get_failure_modes_for(machine_name):
cursor.execute('SELECT id FROM machines WHERE name = ?', (machine_name,))
result = cursor.fetchone()
if not result:
return []
machine_id = result[0]
cursor.execute(
'SELECT mode FROM failure_modes WHERE machine_id = ? ORDER BY mode',
(machine_id,)
)
return [row[0] for row in cursor.fetchall()]


def calculate_rpn():
try:
sev = int(severity_entry.get())
occ = int(occurrence_entry.get())
det = int(detection_entry.get())
rpn = sev * occ * det
rpn_var.set(f"RPN: {rpn}")
return rpn
except ValueError:
rpn_var.set("RPN: N/A")
return 0


def insert_failure_mode_if_new(machine_name, failure_mode):
if not machine_name or not failure_mode:
return
cursor.execute('SELECT id FROM machines WHERE name = ?', (machine_name,))
result = cursor.fetchone()
if not result:
return
machine_id = result[0]
cursor.execute(
'SELECT 1 FROM failure_modes WHERE machine_id = ? AND mode = ?',
(machine_id, failure_mode)
)
exists = cursor.fetchone()
if not exists:
cursor.execute(
'INSERT INTO failure_modes (machine_id, mode) VALUES (?, ?)',
(machine_id, failure_mode)
)
conn.commit()


def insert_machine_if_new(machine_name):
if not machine_name:
return
existing = get_machine_list()
if machine_name not in existing:
cursor.execute('INSERT INTO machines (name) VALUES (?)', (machine_name,))
conn.commit()


def refresh_comboboxes():
machines = get_machine_list()
machine_combo['values'] = machines
current_machine = machine_combo.get().strip()
if current_machine not in machines:
machine_combo.set('')
failure_combo.set('')


# Filter combobox autocomplete
def filter_machine_list(event):
original = machine_combo.get()
all_values = get_machine_list()
filtered = [m for m in all_values if m.lower().startswith(original.lower())]
if filtered:
machine_combo['values'] = filtered
else:
machine_combo['values'] = all_values
machine_combo.set(original)
machine_combo.icursor(tk.END)


def filter_failure_list(event):
machine = machine_combo.get().strip()
original = failure_combo.get()
all_modes = get_failure_modes_for(machine)
filtered = [m for m in all_modes if m.lower().startswith(original.lower())]
if filtered:
failure_combo['values'] = filtered
else:
failure_combo['values'] = all_modes
failure_combo.set(original)
failure_combo.icursor(tk.END)


# ---------- UI Setup ----------
root = tk.Tk()
root.title("FMEA Tracker")
root.geometry("1000x600")

# Variables
selected_entry_id = None
rpn_var = tk.StringVar(value="RPN: N/A")

# Labels
labels = ["Machine", "Failure Mode", "Severity", "Occurrence", "Detection", "Fix"]
for i, text in enumerate(labels):
tk.Label(root, text=text).grid(row=i, column=0, padx=10, pady=5, sticky='e')

# Machine Combobox
machine_list = get_machine_list()
machine_combo = ttk.Combobox(root, values=machine_list)
machine_combo.grid(row=0, column=1, padx=10, pady=5, sticky='we')
machine_combo.bind('<<ComboboxSelected>>',
lambda e: failure_combo.configure(values=get_failure_modes_for(machine_combo.get())))
machine_combo.bind('<KeyRelease>', filter_machine_list)

# Failure Mode Combobox
failure_combo = ttk.Combobox(root, values=[])
failure_combo.grid(row=1, column=1, padx=10, pady=5, sticky='we')
failure_combo.bind('<KeyRelease>', filter_failure_list)

# Severity, Occurrence, Detection Entries
severity_entry = tk.Entry(root)
severity_entry.grid(row=2, column=1, padx=10, pady=5, sticky='we')
severity_entry.bind('<KeyRelease>', lambda e: calculate_rpn())

occurrence_entry = tk.Entry(root)
occurrence_entry.grid(row=3, column=1, padx=10, pady=5, sticky='we')
occurrence_entry.bind('<KeyRelease>', lambda e: calculate_rpn())

detection_entry = tk.Entry(root)
detection_entry.grid(row=4, column=1, padx=10, pady=5, sticky='we')
detection_entry.bind('<KeyRelease>', lambda e: calculate_rpn())

fix_entry = tk.Entry(root)
fix_entry.grid(row=5, column=1, padx=10, pady=5, sticky='we')

# RPN Display
rpn_display = tk.Label(
root, textvariable=rpn_var, font=('Arial', 10, 'bold'), fg='blue'
)
rpn_display.grid(row=6, column=1, sticky='w', padx=10)

# Buttons Frame
btn_frame = tk.Frame(root)
btn_frame.grid(row=7, column=0, columnspan=3, pady=10)

# Button Widgets
save_btn = tk.Button(
btn_frame, text="Save Changes", command=lambda: save_changes(), state='disabled'
)
save_btn.grid(row=0, column=0, padx=5)
button_defs = [
("Add Entry", lambda: save_entry()),
("Edit Selected", lambda: edit_entry()),
("Delete Selected", lambda: delete_entry()),
("Export to Excel", lambda: export_to_excel()),
("Close", lambda: close_app())
]
for i, (label, cmd) in enumerate(button_defs, start=1):
tk.Button(btn_frame, text=label, command=cmd).grid(row=0, column=i, padx=5)

# Treeview for failures
tree_frame = tk.Frame(root)
tree_frame.grid(row=8, column=0, columnspan=3, padx=10, pady=10, sticky='nsew')
root.grid_rowconfigure(8, weight=1)
columns = ("Machine", "Failure Mode", "Severity", "Occurrence", "Detection", "RPN", "Fix")
vsb = tk.Scrollbar(tree_frame, orient="vertical")
hsb = tk.Scrollbar(tree_frame, orient="horizontal")
tree = ttk.Treeview(
tree_frame,
columns=columns,
show='headings',
yscrollcommand=vsb.set,
xscrollcommand=hsb.set
)
for col in columns:
tree.heading(col, text=col)
tree.column(col, anchor='w', minwidth=100, width=150, stretch=True)
tree.pack(expand=True, fill='both')
vsb.config(command=tree.yview)
hsb.config(command=tree.xview)
vsb.pack(side='right', fill='y')
hsb.pack(side='bottom', fill='x')


# ---------- Functions Continued ----------
def load_entries():
tree.delete(*tree.get_children())
cursor.execute(
'SELECT id, machine, failure_mode, severity, occurrence, detection, rpn, fix FROM failures'
)
for row in cursor.fetchall():
tree.insert('', 'end', iid=row[0], values=row[1:])


def save_entry():
machine = machine_combo.get().strip()
failure = failure_combo.get().strip()
# Insert new machine if it doesn't exist
insert_machine_if_new(machine)
refresh_comboboxes()
try:
severity = int(severity_entry.get())
occurrence = int(occurrence_entry.get())
detection = int(detection_entry.get())
except ValueError:
messagebox.showerror(
"Error", "Severity, Occurrence, and Detection must be integers."
)
return
rpn = calculate_rpn()
fix = fix_entry.get()

insert_failure_mode_if_new(machine, failure)
failure_combo['values'] = get_failure_modes_for(machine)

cursor.execute('''
INSERT INTO failures (machine, failure_mode, severity, occurrence, detection, rpn, fix)
VALUES (?, ?, ?, ?, ?, ?, ?)
''', (machine, failure, severity, occurrence, detection, rpn, fix))
conn.commit()
load_entries()
clear_form()


def clear_form():
global selected_entry_id
selected_entry_id = None
machine_combo.set('')
failure_combo.set('')
severity_entry.delete(0, tk.END)
occurrence_entry.delete(0, tk.END)
detection_entry.delete(0, tk.END)
fix_entry.delete(0, tk.END)
rpn_var.set("RPN: N/A")
save_btn.config(state='disabled')


def edit_entry():
global selected_entry_id
selected = tree.selection()
if not selected:
return
selected_entry_id = selected[0]
values = tree.item(selected_entry_id, 'values')
machine_combo.set(values[0])
failure_combo['values'] = get_failure_modes_for(values[0])
failure_combo.set(values[1])
severity_entry.delete(0, tk.END)
severity_entry.insert(0, values[2])
occurrence_entry.delete(0, tk.END)
occurrence_entry.insert(0, values[3])
detection_entry.delete(0, tk.END)
detection_entry.insert(0, values[4])
fix_entry.delete(0, tk.END)
fix_entry.insert(0, values[6])
rpn_var.set(f"RPN: {values[5]}")
save_btn.config(state='normal')


def save_changes():
global selected_entry_id
if not selected_entry_id:
return
machine = machine_combo.get().strip()
failure = failure_combo.get().strip()
insert_machine_if_new(machine)
refresh_comboboxes()
insert_failure_mode_if_new(machine, failure)
failure_combo['values'] = get_failure_modes_for(machine)
try:
severity = int(severity_entry.get())
occurrence = int(occurrence_entry.get())
detection = int(detection_entry.get())
except ValueError:
messagebox.showerror(
"Error", "Severity, Occurrence, and Detection must be integers."
)
return
rpn = calculate_rpn()
fix = fix_entry.get()

cursor.execute('''
UPDATE failures
SET machine = ?, failure_mode = ?, severity = ?, occurrence = ?, detection = ?, rpn = ?, fix = ?
WHERE id = ?
''', (machine, failure, severity, occurrence, detection, rpn, fix, selected_entry_id))
conn.commit()
load_entries()
clear_form()


def delete_entry():
selected = tree.selection()
if not selected:
return
confirm = messagebox.askyesno(
"Confirm Delete", "Are you sure you want to delete the selected entry?"
)
if confirm:
entry_id = selected[0]
cursor.execute('DELETE FROM failures WHERE id = ?', (entry_id,))
conn.commit()
tree.delete(entry_id)


def export_to_excel():
cursor.execute(
'SELECT machine, failure_mode, severity, occurrence, detection, rpn, fix FROM failures'
)
rows = cursor.fetchall()
df = pd.DataFrame(
rows, columns=['Machine', 'Failure Mode', 'Severity', 'Occurrence', 'Detection', 'RPN', 'Fix']
)
df.to_excel("fmea_export.xlsx", index=False)
messagebox.showinfo("Export Complete", "Data exported to fmea_export.xlsx")


def close_app():
root.quit()


# Load initial data
load_entries()
root.mainloop()
conn.close()

Continue reading...