Dynamic Qcombobox Fill Within Qtablewidget Sourced From Sqlite3 Db

I am trying to make a dynamic Qcombobox (arranged within Qtablewidget) fill sourced from SQLite3 database. The underlying data (for demonstration and simplicity sake) might be foun

Solution 1:

Here is the code I was after:

import sys
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *
import sqlite3
from pandas import DataFrame

conn = sqlite3.connect('DataBase.db')
c = conn.cursor()
c.execute('select Section, Label, Product_desc from source')
offer = c.fetchall()
c.close()
conn.close()

df = DataFrame(offer)
fin = {}
for i in df:
    fin[i] = df[i]
    fin[i] = df[i].drop_duplicates()
    fin[i] = list(fin[i])
    fin[i].insert(0,' ')

class Window(QMainWindow):

    def __init__(self, parent = None):
        super(Window,self).__init__(parent)
        self.Table_of_widgets()

    def Table_of_widgets(self):

        rowCount = 20
        columnCount = 9

        self.table = QTableWidget()
        self.table.setColumnCount(columnCount)
        self.table.setRowCount(rowCount)
        self.table.setHorizontalHeaderLabels(['Section', 'Label', 'Product description', 'Picture', 'Product ID', "Amount", "Unit price", "Store", "Total price"])
        self.table.verticalHeader().hide()

        for i in range(columnCount):
            self.table.horizontalHeader().setSectionResizeMode(i, QHeaderView.Stretch)

        self.table.showMaximized()

        self.offer1 = fin[0]
        self.offer2 = fin[1]
        self.offer3 = fin[2]

        for i in range(rowCount):
            comboA = QComboBox()
            comboB = QComboBox()
            comboC = QComboBox()
            comboA.addItems(self.offer1)
            comboB.addItems(self.offer2)
            comboC.addItems(self.offer3)
            self.table.setCellWidget(i, 0, comboA)
            self.table.setCellWidget(i, 1, comboB)
            self.table.setCellWidget(i, 2, comboC)
            comboA.currentTextChanged.connect(lambda text, row=i: self.onComboACurrentTextChanged(text, row))
            comboB.currentTextChanged.connect(lambda text, row=i: self.onComboBCurrentTextChanged(text, row))
            comboC.currentTextChanged.connect(lambda text, row=i: self.onComboCCurrentTextChanged(text, row))

    def updateCombox(self, combo1, combo2, combo3, offer1, offer2, offer3):
        text1 = combo1.currentText()
        text2 = combo2.currentText()
        text3 = combo3.currentText()
        combo1.blockSignals(True)
        combo2.blockSignals(True)
        combo3.blockSignals(True)
        combo1.clear()
        combo2.clear()
        combo3.clear()

        if text1 == ' ': a = list(df[0].drop_duplicates())
        else: a = [text1]
        if text2 == ' ': b = list(df[1].drop_duplicates())
        else: b = [text2]
        if text3 == ' ': c = list(df[2].drop_duplicates())
        else: c = [text3]

        offer1 = list(df.loc[df[0].isin(a) & df[1].isin(b) & df[2].isin(c)][0].drop_duplicates())
        offer1.insert(0, ' ')
        offer2 = list(df.loc[df[0].isin(a) & df[1].isin(b) & df[2].isin(c)][1].drop_duplicates())
        offer2.insert(0, ' ')
        offer3 = list(df.loc[df[0].isin(a) & df[1].isin(b) & df[2].isin(c)][2].drop_duplicates())
        offer3.insert(0, ' ')

        combo3.addItems(offer3)
        combo3.setCurrentText(text3)

        combo2.addItems(offer2)
        combo2.setCurrentText(text2)

        combo1.addItems(offer1)
        combo1.setCurrentText(text1)

        combo1.blockSignals(False)
        combo2.blockSignals(False)
        combo3.blockSignals(False)

    def onComboACurrentTextChanged(self, text, row):
        comboA = self.table.cellWidget(row, 0)
        comboB = self.table.cellWidget(row, 1)
        comboC = self.table.cellWidget(row, 2)
        self.updateCombox(comboA, comboB, comboC, self.offer1, self.offer2, self.offer3)

    def onComboBCurrentTextChanged(self, text, row):
        comboA = self.table.cellWidget(row, 0)
        comboB = self.table.cellWidget(row, 1)
        comboC = self.table.cellWidget(row, 2)
        self.updateCombox(comboA, comboB, comboC, self.offer1, self.offer2, self.offer3)

    def onComboCCurrentTextChanged(self, text, row):
        comboA = self.table.cellWidget(row, 0)
        comboB = self.table.cellWidget(row, 1)
        comboC = self.table.cellWidget(row, 2)
        self.updateCombox(comboA, comboB, comboC, self.offer1, self.offer2, self.offer3)

if __name__ == "__main__":

    app = QApplication(sys.argv)
    app.setApplicationName('MyWindow')
    main = Window()
    sys.exit(app.exec_())