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 found within the table produced by following code:
import sqlite3
conn = sqlite3.connect('DataBase.db')
c = conn.cursor()
def create_table():
c.execute('CREATE TABLE IF NOT EXISTS source("Section",\
"Product_ID",\
"Label",\
"Product_desc",\
"Unit_price")' )
list1 = [
['Butterfly','16/1/001','PP','Pepito Butterfly','350'],
['Butterfly','16/1/002','PP','Brown Butterfly','350'],
['Butterfly','16/1/003','PP','Blue Butterfly','350'],
['Butterfly','bra01','BR','White Butterfly','500'],
['Backpack','bra02','BR','Backpack-blue','1500'],
['Backpack','bra03','BR','Backpack-black','1250'],
['Toy','klv01','KL','Bear','200'],
['Toy','klv02','KL','Fish','500'],
['Toy','klv03','KL','Rabbit','400'],
['Toy','klv04','KL','Owl','450'],
]
def data_entry():
for element in list1:
c.execute("INSERT INTO source VALUES(?,?,?,?,?)", (element))
conn.commit()
c.close()
conn.close()
create_table()
data_entry()
My aim is to update all comboboxes (in a given row) and fill them with updated selection options whenever the user selects something in any combobox. The logic should be following:
Scenario1: one chooses Butterfly within combo1, selection options within combo2 and combo3 will be updated as follows: combo2 displays three options (blank,PP,BR) and is set to blank by default, combo3 will display (blank,Pepito Butterfly,Brown butterfly,Blue Butterfly, White Butterfly) and is set to blank by default, when afterwards user selects BR within combo2 the selection options of combo3 will offer only blank and White Buttefly (set to blank by default).
Scenario2: one chooses Backpack-black in combo3, the selection options for combo2 will be just blank and BR (set to blank by default), the selection options for combo1 will be just blank and Backpack (set to blank by default).
Scenario3: The same as Scenario1 but in second instance (after selecting Butterfly in combo1) user selects White butterfly in combo3 and combo2 should only offer blank and BR (set to blank by default).
The blank values should serve as restart to enable user reset the selection options.
There is a post similar to this one in some parts and might be found here: Dynamic QComboBox fill dependent on user input PyQt5.
During my research I found some other useful posts: sqlite3 table into QTableWidget, sqlite3, PyQt5, however, I still did not manage to implement the bits of code so that it worked and decided to extract the data directly with sqlite3.
I got stuck in initial phase at point where I need to form a structure of data that will be updated after signal is passed (i.e. selection is made). Here below is the code of Qtablewidget with Qcomboboxes I failed to source appropriately:
Further edit - it is nearly done, the offered selections seem to be alright but one cannot choose them for some reason:
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 text1, row=i: self.onComboACurrentTextChanged(text1, row))
comboB.currentTextChanged.connect(lambda text2, row=i: self.onComboBCurrentTextChanged(text2, row))
comboC.currentTextChanged.connect(lambda text3, row=i: self.onComboCCurrentTextChanged(text3, row))
def updateCombox(self, combo1, combo2, combo3, item1, item2, item3):
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, text1, row): # Determines changes in given row iniciated by comboA
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, text2, row): # Determines changes in given row iniciated by comboB
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, text3, row): # Determines changes in given row iniciated by comboC
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_())
I will be thankful for any suggestions/solutions/tips! Thanks
