[Tkinter] Database with gui

[deanhystad]

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"')