Filter a python table with sql data using "dynamic" sql queries-CodePudding

I have an PyQt5 dialog with a table, 3 buttons (delete row, add row and filter) and 5 comboboxes, in those comboboxes I have all the items written in an external SQL databes (for example I have customers, drivers ecc..), the user can choose an item from each combobox, afetr choosing it when the users press the filter button(the object name is 'bottonefiltra') automatically the table will be filtered based on which customer, driver ecc the user selected. Here's my code and here's where I'm stuck :

def __init__(self):
    super(modificaviaggi, self).__init__()
    uic.loadUi('modifica_reg.ui', self)
    bottonefiltra = self.bottone_filtra
    bottonefiltra.clicked.connect(self.filtertable)
def filtertable(self):
    self.tabella_registrazioni.clear()
    self.tabella_registrazioni.blockSignals(True)
    startdate = self.input_dal.date().toPyDate()
    enddate = self.input_al.date().toPyDate()
    farticle = self.filtro_articolo.currentText()
    fwarehouse = self.filtro_magazzino.currentText()
    fcustomer = self.filtro_cliente.currentText()
    fplate = self.filtro_targa.currentText()
    fdriver = self.filtro_autista.currentText()

How can I create a query that changes based on the item selected without creating 30 if statements? I'll explain it better, to do what I want I'd most likely create an if to check which combobox has an item selected like this :

if farticle != " " :
        if fwarehouse != " ":
            if fcustomer != " " :
                if fplate != " " :
                    if fdriver != " " :

then I'll execute a query where like :

c.execute('SELECT * FROM Viaggi WHERE DataDDT < ? AND DataDDT > ? AND Ragione_sociale = ? AND Targa = ? AND Autista = ? AND Magazzino = ? AND Articolo = ?', 
                        (fcustomer, fplate, fdriver, fwarehouse, farticle))

but everytime I have to write like 10-15 different queries based on the combination of which items are selected(maybe 1 combo has an item selected and third no ecc...). How can I do it without writing 20 queries and without making 20 If statements to check which combo has an item selected ?

CodePudding user response：

A possibility would be to pair each combo with a field name, then cycle through them to build both the query and the value list.

Somewhere in the __init__ create a list with those pairs:

self.comboFields = (
    (self.first_combo, 'FirstField'),
    (self.second_combo, 'SecondField'),
    ...
)

Then iterate through it and build the query:

query = 'SELECT * FROM Viaggi WHERE DataDDT < ? AND DataDDT > ?'
values = [startdate, enddate]
for combo, field in self.comboFields:
    value = combo.currentText()
    if value != ' ':
        query  = ' AND {} = ?'.format(field)
        values.append(value)

c.execute(query, values)