Python Forum
[Tkinter] Database with gui
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[Tkinter] Database with gui
#1
Hello all, I am learning python language. I am trying this exercise I found on web in a course, and I would to improve it. This program shows a window where the user can insert data about his routine and then view data he is searching for.
Problem 1. The search function seems not working well
Problem 2. When selecting one of text entries, I got a "tuple index out of range" error in the console (non affecting funcionality maybe)

I also would like add the option to update some items, instead of adding new ones.
If you find some othere possible improvements I would appreciate.
Thanks for help in advance

Frontend:
from tkinter import *
import backend

def get_selected_row(event):
    global selected_row
    index = list.curselection()[0]
    selected_row = list.get(index)
    e1.delete(0,END)
    e1.insert(END,selected_row[1])
    e2.delete(0,END)
    e2.insert(END,selected_row[2])
    e3.delete(0,END)
    e3.insert(END,selected_row[3])
    e4.delete(0,END)
    e4.insert(END,selected_row[4])
    e5.delete(0,END)
    e5.insert(END,selected_row[5])
    e6.delete(0,END)
    e6.insert(END,selected_row[6])

def delete_command():
    backend.delete(selected_row[0])
    view_command()

def view_command():
    list.delete(0,END)
    for row in backend.view():
        list.insert(END,row)

def search_command():
    list.delete(0,END)
    for row in backend.search(date_text.get(),earning_text.get(),exercise_text.get(),study_text.get(),diet_text.get(),python_text.get()):
        list.insert(END,row)

def add_command():
    backend.insert(date_text.get(),earning_text.get(),exercise_text.get(),study_text.get(),diet_text.get(),python_text.get())

    list.delete(0,END)
    list.insert(END,(date_text.get(),earning_text.get(),exercise_text.get(),study_text.get(),diet_text.get(),python_text.get()))

    view_command()

win = Tk()

win.wm_title('MY ROUTINE DATABASE')

l1 = Label(win, text='Date')
l1.grid(row=0,column=0)
l2 = Label(win, text='Earnings')
l2.grid(row=0,column=2)
l3 = Label(win, text='Exercise')
l3.grid(row=1,column=0)
l4 = Label(win, text='Study')
l4.grid(row=1,column=2)
l5 = Label(win, text='Diet')
l5.grid(row=2,column=0)
l6 = Label(win, text='Python')
l6.grid(row=2,column=2)

date_text = StringVar()
e1 = Entry(win, textvariable=date_text)
e1.grid(row=0,column=1)

earning_text = StringVar()
e2 = Entry(win, textvariable=earning_text)
e2.grid(row=0,column=3)

exercise_text = StringVar()
e3 = Entry(win, textvariable=exercise_text)
e3.grid(row=1,column=1)

study_text = StringVar()
e4 = Entry(win, textvariable=study_text)
e4.grid(row=1,column=3)

diet_text = StringVar()
e5 = Entry(win, textvariable=diet_text)
e5.grid(row=2,column=1)

python_text = StringVar()
e6 = Entry(win, textvariable=python_text)
e6.grid(row=2,column=3)

list = Listbox(win,height=8,width=35)
list.grid(row=3,column=0,rowspan=9,columnspan=2)

sb = Scrollbar(win)
sb.grid(row=3,column=2,rowspan=9)

list.bind('<<ListboxSelect>>',get_selected_row)

b1 = Button(win,text='ADD',width=12,pady=5,command=add_command)
b1.grid(row=3,column=3)

b2 = Button(win,text='Search',width=12,pady=5,command=search_command)
b2.grid(row=4,column=3)

b3 = Button(win,text='Delete date',width=12,pady=5,command=delete_command)
b3.grid(row=5,column=3)

b4 = Button(win,text='View all',width=12,pady=5,command=view_command)
b4.grid(row=6,column=3)

b5 = Button(win,text='Close',width=12,pady=5,command = win.destroy)
b5.grid(row=7,column=3)

win.mainloop()
Backend:
import sqlite3

def connect():
    conn = sqlite3.connect('routine.db')
    cur = conn.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS routine (Id INTEGER PRIMARY KEY , date text , earnings integer , exercise text , study text , diet text ,python text)")
    conn.commit()
    conn.close()

def insert(date , earnings , exercise , study , diet , python):
    conn = sqlite3.connect('routine.db')
    cur = conn.cursor()
    cur.execute("INSERT INTO routine VALUES (NULL , ?,?,?,?,?,?)" , (date , earnings , exercise , study , diet , python))
    conn.commit()
    conn.close()

def view():
    conn = sqlite3.connect('routine.db')
    cur = conn.cursor()
    cur.execute("SELECT * FROM routine")
    rows = cur.fetchall()
    conn.commit()
    conn.close()
    return rows

def delete(id):
    conn = sqlite3.connect('routine.db')
    cur = conn.cursor()
    cur.execute("DELETE FROM routine WHERE id=? ", (id,))
    conn.commit()
    conn.close()

def search(date='' , earnings='' , exercise='' , study='' , diet='' , python=''):
    conn = sqlite3.connect('routine.db')
    cur = conn.cursor()
    cur.execute("SELECT * FROM routine WHERE date=?  OR earnings=? OR exercise=? OR study=? OR diet=? OR python=?" , (date , earnings , exercise , study , diet , python))
    rows = cur.fetchall()
    conn.commit()
    conn.close()
    return rows

connect()
Reply
#2
Searching works fine as far as I can tell. What do you think is wrong?

You get the index error when doing this:
index = list.curselection()[0]
And there is nothing selected in the Listbox. Since this is something that can legitimately happen, you need to protect against it.
def get_selected_row(event):
    global selected_row
    index = list.curselection()
    if not index:
        return
    selected_row = list.get(index[0])
    ...
By the way, list is a built-in Python function and should not be used as a variable name.
Reply
#3
(Mar-20-2021, 02:23 PM)deanhystad Wrote: Searching works fine as far as I can tell. What do you think is wrong?

You get the index error when doing this:
index = list.curselection()[0]
And there is nothing selected in the Listbox. Since this is something that can legitimately happen, you need to protect against it.
def get_selected_row(event):
    global selected_row
    index = list.curselection()
    if not index:
        return
    selected_row = list.get(index[0])
    ...
By the way, list is a built-in Python function and should not be used as a variable name.

If I add:
if not index:
    return
The error persist.
If I use:
selected_row = list.get(index[0])
after that, it shows me this error: 'int' object is not subscriptable

About the search function: what if I want to search on just an entered field or matching the only fields filled? So if user types one, two or more fields, they must all match, even if some fields are not filled. I tried with AND in the query, but the result is not what I expected.
Reply
#4
You missed something. In my code I first ask for the selection.
index = list.curselection()
If there is no selection, this returns an empty collection of some type ({} I think). Then I test if the selection is empty and return out of the function if it is.
if not index:
    return
If index is not empty then I know it is save to ask for index[0].

I think you are doing this:
index = list.curselection()[0]
This does nothing to protect against an index error, and generates an index error if you try to get "index[0]" because index is already an integer.

You did not answer my question about search. What do you think is wrong with the search function? I played around with it a bit and it appears to work very well.
Reply
#5
Ok, writing like that:
def get_selected_row(event):
    global selected_row
    index = list.curselection()
    if not index:
        return
    selected_row = list.get(index)
    e1.delete(0,END)
    e1.insert(END,selected_row[1])
Seems working fine now. Thank you.
About the search function..what if I want that when I type more than 1 field, it must check rows matching all those field, even if some are blank.
If use ADD in backend.search(), all fields must be filled. Perhaps to do what I mean it would take a lot more lines and logical checks. I hope you understand what I mean.
And thank you again.
Reply
#6
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.
Reply
#7
Still wrong. It works, but you don't know why it works and in my opinion that is worse than not working at all.

Your original code did this:
index = list.curselection()[0]
This gave you numbers like 0, 1, 2, 3... These are good values to pass as arguments to Listbox.get()
Quote:get ( first, last=None )
Returns a tuple containing the text of the lines with indices from first to last, inclusive. If the second argument is omitted, returns the text of the line closest to first.
Now you are doing this (essentially):
index = list.curselection()
This gives you tuples like (0,), (1,), (2,), (3,)... I would expect it to be an error to call
list.get((1,))
but for some reason it works. I don't know why. I haven't been able to figure out why. When I look at the Python code for Listbox.get() I found this:
    def get(self, first, last=None):
        """Get list of items from FIRST to LAST (included)."""
        if last is not None:
            return self.tk.splitlist(self.tk.call(
                self._w, 'get', first, last))
        else:
            return self.tk.call(self._w, 'get', first)
self.tk.call calls a tkinter function written in C/C++, so if I really wanted to know why I get away passing a tuple I need to get the source code for the tkinter C++ libraries. After analyzing the code I could make an informed decision about if I wanted to continue using an undocumented feature, or I could change my code so it passes an integer.
Reply
#8
I followed your directions in code to try to resolve the error about "tuple index out of range".
Thanks for the improved code! although I must admit that it is a little more complex for me to understand..
Reply
#9
It took me a while to figure out how to do the SELECT using only non-empty fields, and the result is a little dense. But it is far simpler than trying to do it any other way I could think of.
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))
The list comprehension makes a list of strings that look like this:
args = ['Date="June 5"', 'Study="History"']
The construction of the field name="value" strings is done by this part:
f'{n}="{v.get()}"'
f' is the new f string way of formatting strings in Python. Curly brackets {} surround expressions that are evaluted and converted to strings. This could be rewritten as:
n+'="'+v.get()+'"'
This part passes n and v values to the string constructor part.
for n, v in zip(field_names, fields) if v.get() != ''
field_names is a list of strings, the same strings used as field names for the records in the database. There's probably a way to get this info from the database, but this is my first sqlite3 program and I did not find how to do that yet.

fields are StringVar's that are bound to the Entry widgets in the GUI. The Entry's are used to enter/view the field values for a database record.
for n, v in zip(field_names, fields)
This part zips together values from the field_names and fields lists. If you have two lists letters = ['A', 'B', 'C', 'D'] and numbers[1, 2, 3, 4]. zip(letters, numbers) would generate tuples ('A', 1), ('B', 2), ('C', 3) and ('D', 4). This is a really easy way to combine two lists and shorter than using a loop.
pairs = []
for i in range(len(letters)):
    pairs.append((letters[i], numbers[i]))
This last part pares down the args list to only contain entries where the field value is not ''.
if v.get() != ''
The result of the comprehension is a list of field="value" strings for each non-empty field. Now I need to put these strings together separated by ' AND ' or ' OR '. That is surprisingly tricky to do using loops, but really simple using str.join().
' AND '.join(args)
If args = ['Date="June 5"', 'Study="History"'], this would produce args = 'Date="June 5" AND Study="History"'. If args = ['Date="June 5"'], join() is smart enough to not use the joining string and returns 'Date="June 5"'

The final step is to put the SELECT command part together with the args part.
search('SELECT * FROM routine WHERE ' + ' AND '.join(args))
Using the args from previous examples this is the same as:
search('SELECT * FROM routine WHERE ' + 'Date="June 5" AND Study="History"')
or
search('SELECT * FROM routine WHERE Date="June 5" AND Study="History"')
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020