PYTHON SQLITE selecting multiple where conditions that may or may not exist-CodePudding

Working on some code that uses pysimplegui as the UI and SQlite for the data sorting. I'm using SQLite's execute function to select data based on input from the user in the UI through variables. For example user wants to search for part name they input all or part of the name into the box, hit the search button which then runs my "parts_search" method, which will then only filter the result based on part name. OR the user enters information in multiple boxes which then filters based on the boxes that have information.

This here is runnable code provided you add a file base1.db in the same folder location as the script itself

import PySimpleGUI as sg
import os.path
import sqlite3

# sql var
c = None
conn = None
setup = None

# list var
parts = []


def sql():
    global setup
    conn_sql()
    c.execute("""CREATE TABLE IF NOT EXISTS parts (part_name TEXT, part_number TEXT, part_series TEXT, 
    part_size INTEGER, job_type TEXT)""")
    conn.commit()
    if conn:
        conn.close()


def conn_sql():
    global c
    global conn

    # SQL connection var
    if os.path.isfile('./base1.db'):
        conn = sqlite3.connect('base1.db')
        c = conn.cursor()


def main_gui_parts():
    global parts

    layout = [[sg.Text('Part Name:  '), sg.Input(size=(20, 1), key='-PName-'), sg.Text('Part Series:'),
                  sg.Input(size=(10, 1), key='-PSeries-')],
                 [sg.Text('Part Number:'), sg.Input(size=(20, 1), key='-PNumber-'), sg.Text('Part Size:'),
                  sg.Input(size=(10, 1), key='-PSize-')],
                 [sg.Checkbox('Fit', key='-PFit-'), sg.Checkbox('Weld', key='-PWeld-'),
                  sg.Checkbox('Assemble', key='-PAssemble-'),
                  sg.Button('Search', key='-PSearch-')],
                 [sg.Listbox(parts, size=(58, 10), key='-PParts-')], [sg.Button('Back', key='-PBack-')]]

    window = sg.Window('parts list', layout, grab_anywhere=True)

    sql()

    while True:
        event, values = window.read()

        if event == 'Close' or event == sg.WIN_CLOSED:
            break

        # PART WINDOW
        part_name = values['-PName-']
        part_series = values['-PSeries-']
        part_number = values['-PNumber-']
        part_size = values['-PSize-']
        fit = values['-PFit-']
        weld = values['-PWeld-']
        assemble = values['-PAssemble-']
        if event == '-PSearch-':
            print('search parts')
            part_search(part_name, part_series, part_number, part_size, fit, weld, assemble)
        if event == '-PBack-':
            break

    window.close()


def part_search(part_name, part_series, part_number, part_size, fit, weld, assemble):
    global parts
    conn_sql()
    filter_original = """SELECT * FROM parts WHERE """
    filter = filter_original
    if part_name:
        print('part name: '   part_name)
        if filter == filter_original:
            filter  = """part_name LIKE ? """
        else:
            filter  = """AND part_name LIKE ? """
    if part_series:
        print('part series: '   part_series)
        if filter == filter_original:
            filter  = """part_series=(?) """
        else:
            filter  = """AND part_series=(?) """
    if part_number:
        print('part number: '   part_number)
        if filter == filter_original:
            filter  = """part_number LIKE ? """ ### DONT USE LIKE???
        else:
            filter  = """AND part_number LIKE ? """  ### DONT USE LIKE???
    if part_size:
        print('part size: '   part_size)
        if filter == filter_original:
            filter  = """part_size=(?) """
        else:
            filter  = """AND part_size=(?) """
    if fit:
        print('job type: '   str(fit))
        if filter == filter_original:
            filter  = """job_type = fit """
        else:
            filter  = """AND job_type = fit """
    if weld:
        print('job type: '   str(weld))
        if filter == filter_original:
            filter  = """job_type = weld """
        else:
            filter  = """AND job_type = weld """
    if assemble:
        print('job type: '   str(assemble))
        if filter == filter_original:
            filter  = """job_type = assemble"""
        else:
            filter  = """AND job_type = assemble"""
    print(filter)
    #if filter != filter_original:
        #c.execute(filter, ())
    #else:
        #c.execute("""SELECT * FROM parts""")


main_gui_parts()

THE PROBLEM: The commented code at the bottom is where I'm having trouble figuring out (in the "part_search" method). I don't use all of the variables all the time. Only filter with the variables provided by the user. which means the tuple should only have the variables which was input by the user.

If all the variables were used this is what it would look like. c.execute(filter, (part_name, part_series, part_number, part_size, fit, weld, assemble)) but more often than not only some of those variable will have been used and may need to look like this instead. c.execute(filter, (part_name, part_series, weld)) Somehow I need the variables here to be removeable(for lack of better word)

I've been learning a lot about SQLite but I could be seeing tunnel vision and can't think of a different way to go about this.

CodePudding user response：

Probably the easiest way to deal with this is to put all the filter conditions and values into lists, and then only add a WHERE clause if the length of the filters list is non-zero. For example:

query = """SELECT * FROM parts"""
filters = []
values = []
if part_name:
    filters.append("""part_name LIKE ?""")
    values.append(part_name)
...
if len(filters):
    query  = ' WHERE '   ' AND '.join(filters)
c.execute(query, tuple(values))

Note: should your filters ever include OR conditions, you need to parenthesise them when building the query to ensure correct operation i.e.

query  = ' WHERE ('   ') AND ('.join(filters)   ')'