QGIS – Fill ComboBox with SQL Server Query Results

pyqgispyqt5pythonqgis-plugins

I need to fill a comboBox with the result of a query to sqlServer, this result is filtered according to the data that was entered in
the batch field

Try doing it with pyodbc, but when the form is opened, the list is empty, when I give OK the data appears, but in each execution they are duplicated.
I'm starting with pyqt5 and maybe it's not the right way to do it.

def run(self):

        sqlcmd = ("SELECT sec.Nombre  FROM [Lote] lot \
                                           inner join [Seccion] sec on sec.idInforme = lot.idInforme \
                                           Where idLote = '%s'") % \
                 (self.dlg.lote.text())

        cursorLista.execute(sqlcmd)

        for i in cursorLista:
            self.dlg.comboBox.addItem(str(i[0]))



        # show the dialog

        self.dlg.show()
        # Run the dialog event loop
        result = self.dlg.exec_()
        # See if OK was pressed
        if result:

            # Do something useful here - delete the line containing pass and
            # substitute with your code.

            pass

Best Answer

My advice about that would be to encapsulate the populating of the combobox in a function that you call when the plugin is launched. Each time you call this function you clear the combobox before adding new items to avoid duplicate.

add a function like that in your plugin:

def populate_combobox(self, cursorLista):

    self.dlg.comboBox.clear() #to remove items and avoid duplicate

    sqlcmd = ("SELECT sec.Nombre  FROM [Lote] lot \
                                           inner join [Seccion] sec on sec.idInforme = lot.idInforme \
                                           Where idLote = '%s'") % \
                 (self.dlg.lote.text())

    cursorLista.execute(sqlcmd)

    for i in cursorLista:
        self.dlg.comboBox.addItem(str(i[0]))

then in the run function add a call to this function

def run(self):

    self.dlg.show()
    # Run the dialog event loop
    result = self.dlg.exec_()
    # See if OK was pressed
    if result:

         # Do something useful here - delete the line containing pass       and
         # substitute with your code.

         self.populate_combobox(cursorLista)

then if you need to update this list just call the populate_combobox function from your plugin.

Related Question