[Tkinter] Database with gui

[deanhystad]

The database search is or, or any, and for or it works great. If you want to do and, or all, then you need to change the query. I tried using a wildcard of some sort for empty fields, but either that isn't supported or I can't find any documentation about it. So I built a unique query based on the field values.

In the code below look at find_any() or find_all() to see how I built up a SELECT command that only includes fields that aren't empty (!= '').

import tkinter as tk
import sqlite3

field_names = ('Date', 'Earnings', 'Exercise', 'Study', 'Diet', 'Python')
selection = None

def db_execute(cmd, args=None):
    """Execute a DB command"""
    conn = sqlite3.connect('routine.db')
    cur = conn.cursor()
    if args:
        cur.execute(cmd, args)
    else:
        cur.execute(cmd)
    retvalue = cur.fetchall()
    conn.commit()
    conn.close()
    return retvalue

def select_record():
    """Load selected record into fields"""
    global selection
    if index := records.curselection():
        selection = records.get(index[0])
        for field, value in zip(fields, selection[1:]):
            field.set(value)

def add_record():
    """Add new record using values in fields"""
    args = [field.get() for field in fields]
    db_execute("INSERT INTO routine VALUES (NULL , ?,?,?,?,?,?)", args)
    view_all()

def delete_record():
    """Delete last selected record"""
    db_execute("DELETE FROM routine WHERE id=? ", (selection[0],))
    view_all()

def search(search_cmd):
    """Search for matching records"""
    records.delete(0, tk.END)
    for record in db_execute(search_cmd):
        records.insert(tk.END, record)

def find_all():
    """Find records that match all non-empty fields"""
    args = [f'{n}="{v.get()}"' for n, v in zip(field_names, fields) if v.get() != '']
    if len(args) > 0:
        search('SELECT * FROM routine WHERE ' + ' AND '.join(args))

def find_any():
    """Find records that match any non-empty fields"""
    args = [f'{n}="{v.get()}"' for n, v in zip(field_names, fields) if v.get() != '']
    if len(args) > 0:
        search('SELECT * FROM routine WHERE ' + ' OR '.join(args))

def view_all():
    """Get all records"""
    search("SELECT * FROM routine")

root = tk.Tk()

# Create field editor
frame = tk.Frame(root)
frame.grid(row=0, column=0, sticky='NEWS', padx=5, pady=5)
fields = []
for i, label in enumerate(field_names):
    row = i % 3
    col = (0, 2)[i // 3]
    tk.Label(frame, text=label).grid(row=row, column=col)
    field = tk.StringVar()
    tk.Entry(frame, textvariable=field).grid(row=row, column=col+1)
    fields.append(field)

# Create record view
records = tk.Listbox(frame, height=8, width=35)
records.grid(row=3, column=0, columnspan=4, sticky="NEWS")
records.bind('<<ListboxSelect>>', lambda event: select_record())

# Create command buttons
frame = tk.Frame(root)
frame.grid(row=0, column=1, sticky='NEWS', padx=5, pady=5)
tk.Button(frame, text='Add', command=add_record).grid(row=0, column=0, sticky='NEWS')
tk.Button(frame, text='Find All', command=find_all).grid(row=1, column=0, sticky='NEWS')
tk.Button(frame, text='Find Any', command=find_any).grid(row=2, column=0, sticky='NEWS')
tk.Button(frame, text='Delete', command=delete_record).grid(row=3, column=0, sticky='NEWS')
tk.Button(frame, text='View All', command=view_all).grid(row=4, column=0, sticky='NEWS')
tk.Button(frame, text='Close', command=root.destroy).grid(row=5, column=0, sticky='NEWS')

db_execute("CREATE TABLE IF NOT EXISTS routine (Id INTEGER PRIMARY KEY, date text, earnings integer, exercise text, study text, diet text, python text)")
view_all()
root.mainloop()

The db_execute() function let me get rid of a ton of duplicate code.