How to fetch database row and display in Qlabels with QcomboBox selected value in PyQt5

Lalremruata · Jul 10, 2021

I want to fetch database row values and display it in some labels based upon the selected value of combo box.

The combo box successfully display 'item_name' from table_1 in the combo box. Now, I want to display 'item_name' relevant row values (like item_price, item_code, item_qty etc) in label_1, label_2, label_3 etc., whenever the combo box value changes.

I fetch database and display in the combo box with the following code:

def populate_combobox(self):
conn = sqlite3.connect('DB.db')
c = conn.cursor()

c.execute("SELECT item_name FROM table_1")

data = c.fetchall()

self.comboBox_3.clear()
for item_name in data:
self.comboBox_3.addItem(item_name[0])

Examples: table_1
| id | item_name|item_code|item_qty |item_price|
| -- |----------|---------|---------|----------|
| 1 | mango |MGO | 20 | 150 |
| 2 | banana |BNNA | 5 | 120 |
| 3 | apple |APPL | 15 | 180 |
| 4 | grape |GRPE | 55 | 750 |
| 5 | coconut |CCN | 75 | 820 |
| 6 | pumpkin |PPK | 100 | 980 |

My expectation:
If combobox value 'mango' is selected:
label_1 = MGO
label_2 = 20
label_3 =

CodeMonkeyJ · Jul 12, 2021

You'll want to register/connect a function to the currentTextChanged event that queries the database with the new text, e.g.,

Code:

SELECT * FROM table_1 WHERE item_name = <combobox.currentText()>;

After you fetch the row, you can setText on each of the labels with the data returned.

Lalremruata · Jul 15, 2021

Here s my whole
codes, I will be grateful if you could help. I am a self learner and a newbie in python and sometime it is hard to grabs the meaning.

import sqlite3

from PyQt5 import QtWidgets, uic, QtCore
import sys

from PyQt5.QtCore import QTime, QDate, QTimer, QRegExp, Qt
from PyQt5.QtGui import QRegExpValidator, QDoubleValidator
from PyQt5.QtSql import QSqlDatabase
from PyQt5.QtWidgets import QTableWidgetItem
from qtpy import QtSql

class Ui(QtWidgets.QMainWindow):
def __init__(self):
super(Ui, self).__init__()
uic.loadUi('date.ui', self) # UI file hminga thlak zel tur
self.show()

validator = QDoubleValidator(0.99, 99.99, 2)
self.lineEdit.setValidator(validator)
self.lineEdit_2.setValidator(validator)

validator2 = QDoubleValidator(0.99, 99.99, 2)
self.itemName.setValidator(validator2)
self.itemPrice.setValidator(validator2)

self.le_date.hide()
self.le_time.hide()
self.lbl_sum.hide()
self.lineEdit.textChanged.connect(self.validate)
self.lineEdit_2.textChanged.connect(self.validate)
self.pushButton.setEnabled(False)
self.pushButton.clicked.connect(self._insertDB)

self.itemName.textChanged.connect(self.validate_2)
self.itemPrice.textChanged.connect(self.validate_2)
self.pushButton_2.setEnabled(True)
self.pushButton_2.clicked.connect(self._insertDB_2)

self.populate_table()
self.populate_table_2()
self.populate_combobox()

self.itemName.editingFinished.connect(lambda: self.checkstatus(self.itemName))

# date & time parameter
timer = QTimer(self)
timer.timeout.connect(self.showtime)
timer.start()

def showtime(self):
now = QDate.currentDate()
self.le_date.setText(now.toString('MMM-dd, yyyy'))
time = QTime.currentTime()
time.start()
self.le_time.setText(time.toString('h:mm:ss AP'))

def _insertDB(self):
conn = sqlite3.connect('DB.db')
c = conn.cursor()
print("Connected to SQLite")

Field2 = self.lineEdit.text()
Field3 = self.lineEdit_2.text()
date = self.le_date.text()
time = self.le_time.text()

c.execute("INSERT INTO table_1(Field2, Field3, date, time) VALUES (?,?,?,?)",
(Field2, Field3, date, time))

conn.commit()
conn.close()
print("Python Variables inserted successfully into database table")
self.lbl_notice.setText("Successfully insert data into records.")
self._clearTextEdit()

def _clearTextEdit(self):
self.lineEdit.clear()
self.lineEdit_2.clear()
self.lineEdit.setFocus()

def validate(self):
value1 = self.lineEdit.text()
value2 = self.lineEdit_2.text()

sum = (value1) + (value2)
self.lbl_sum.setText(sum)
if sum == "":
self.pushButton.setEnabled(False)
else:
self.pushButton.setEnabled(True)

print(type(sum))
print(self.lbl_sum.text())

def populate_table(self):
conn = sqlite3.connect('DB.db')
c = conn.cursor()
c.execute('''SELECT id, Field2, Field3, date, time FROM table_1 ORDER BY id Desc''')
data = c.fetchall()

self.tableWidget.setRowCount(0)
self.tableWidget.insertRow(0)
self.tableWidget.resizeColumnsToContents()
self.tableWidget.horizontalHeaderItem(0).setTextAlignment(Qt.AlignLeft)
self.tableWidget.horizontalHeaderItem(1).setTextAlignment(Qt.AlignLeft)
self.tableWidget.horizontalHeaderItem(2).setTextAlignment(Qt.AlignLeft)
self.tableWidget.horizontalHeaderItem(3).setTextAlignment(Qt.AlignLeft)
self.tableWidget.horizontalHeaderItem(4).setTextAlignment(Qt.AlignLeft)

for row, form in enumerate(data):
for column, item in enumerate(form):
self.tableWidget.setItem(row, column, QTableWidgetItem(str(item)))
column += 1

row_position = self.tableWidget.rowCount()
self.tableWidget.insertRow(row_position)

conn.close()
QtCore.QTimer.singleShot(100, self.populate_table) # updates data at regular interval (instantly)

########################
def populate_table_2(self):
conn = sqlite3.connect('DB.db')
c = conn.cursor()
c.execute('''SELECT ref, itemName, itemPrice, date1, time1 FROM table_2 ORDER BY time1 Asc''')
data = c.fetchall()

self.tableWidget_2.setRowCount(0)
self.tableWidget_2.insertRow(0)
self.tableWidget_2.resizeColumnsToContents()
self.tableWidget_2.horizontalHeaderItem(0).setTextAlignment(Qt.AlignLeft)
self.tableWidget_2.horizontalHeaderItem(1).setTextAlignment(Qt.AlignLeft)
self.tableWidget_2.horizontalHeaderItem(2).setTextAlignment(Qt.AlignLeft)
self.tableWidget_2.horizontalHeaderItem(3).setTextAlignment(Qt.AlignLeft)
self.tableWidget_2.horizontalHeaderItem(4).setTextAlignment(Qt.AlignLeft)

for row, form in enumerate(data):
for column, item in enumerate(form):
self.tableWidget_2.setItem(row, column, QTableWidgetItem(str(item)))
column += 1

row_position = self.tableWidget_2.rowCount()
self.tableWidget_2.insertRow(row_position)

conn.close()
QtCore.QTimer.singleShot(100, self.populate_table_2) # updates data at regular interval (instantly)

def _insertDB_2(self):
conn = sqlite3.connect('DB.db')
c = conn.cursor()
print("Connected to SQLite")

v2 = self.itemName.text()
v3 = self.itemPrice.text()
date1 = self.le_date.text()
time1 = self.le_time.text()

c.execute("INSERT INTO table_2 select max(id), ?, ?, ?, ? from table_1",
(v2, v3, date1, time1))

conn.commit()
conn.close()
print("Python Variables inserted successfully into database table")
self.lbl_notice_2.setText("Successfully insert data into records.")
self._clearTextEdit_2()

def _clearTextEdit_2(self):
self.itemName.clear()
self.itemPrice.clear()
self.itemName.setFocus()

def validate_2(self):
# if self.itemName.text() == "":
# self.label_5.setText("Empty!.")
# self.pushButton_2.setEnabled(False)
# elif self.itemPrice.text() == "":
# self.label_6.setText("Empty!.")
# self.pushButton_2.setEnabled(False)

v1 = self.itemName.text()
v2 = self.itemPrice.text()
s = (v1) + (v2)
self.lbl_check.setText(s)
if s == "":
self.pushButton_2.setEnabled(False)
else:
self.pushButton_2.setEnabled(True)

print(type(sum))
print(self.lbl_check.text())

#############################
def populate_combobox(self):
conn = sqlite3.connect('DB.db')
c = conn.cursor()

c.execute("SELECT itemName, itemPrice, date1, time1 FROM table_2")

data = c.fetchall()

self.combo1.clear()
for itemName in data:
self.combo1.addItem(itemName[0])
#self.combo1.addItem(itemName[1])
print(itemName[0]) # item_name
print(itemName[1]) # item_code
print(itemName[2]) # item_qty
print(itemName[3]) # item_price
#display item price here in label_7
#display item qnty here in label_8
#display item code here in label_9

########################

app = QtWidgets.QApplication(sys.argv)
window = Ui()
app.exec_()

CodeMonkeyJ · Jul 15, 2021

Can you post the date.ui file, as well, and any other external files? Remember to wrap it in code tags to keep formatting.

Lalremruata · Jul 17, 2021

CodeMonkeyJ said:
Can you post the date.ui file, as well, and any other external files? Remember to wrap it in code tags to keep formatting.

Here is the converted ui file. There is no other external file.

# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file 'date.ui'
#
# Created by: PyQt5 UI code generator 5.15.4
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again. Do not edit this file unless you know what you are doing.

from PyQt5 import QtCore, QtGui, QtWidgets

class Ui_MainWindow(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(1175, 823)
MainWindow.setStyleSheet("font: 14pt \"MS Shell Dlg 2\";")
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.formLayoutWidget = QtWidgets.QWidget(self.centralwidget)
self.formLayoutWidget.setGeometry(QtCore.QRect(20, 160, 311, 121))
self.formLayoutWidget.setObjectName("formLayoutWidget")
self.formLayout = QtWidgets.QFormLayout(self.formLayoutWidget)
self.formLayout.setContentsMargins(0, 0, 0, 0)
self.formLayout.setObjectName("formLayout")
self.label = QtWidgets.QLabel(self.formLayoutWidget)
self.label.setObjectName("label")
self.formLayout.setWidget(0, QtWidgets.QFormLayout.LabelRole, self.label)
self.label_2 = QtWidgets.QLabel(self.formLayoutWidget)
self.label_2.setObjectName("label_2")
self.formLayout.setWidget(1, QtWidgets.QFormLayout.LabelRole, self.label_2)
self.lineEdit = QtWidgets.QLineEdit(self.formLayoutWidget)
self.lineEdit.setObjectName("lineEdit")
self.formLayout.setWidget(0, QtWidgets.QFormLayout.FieldRole, self.lineEdit)
self.lineEdit_2 = QtWidgets.QLineEdit(self.formLayoutWidget)
self.lineEdit_2.setObjectName("lineEdit_2")
self.formLayout.setWidget(1, QtWidgets.QFormLayout.FieldRole, self.lineEdit_2)
self.pushButton = QtWidgets.QPushButton(self.formLayoutWidget)
self.pushButton.setObjectName("pushButton")
self.formLayout.setWidget(2, QtWidgets.QFormLayout.FieldRole, self.pushButton)
self.le_date = QtWidgets.QLineEdit(self.centralwidget)
self.le_date.setEnabled(True)
self.le_date.setGeometry(QtCore.QRect(30, 60, 81, 41))
self.le_date.setObjectName("le_date")
self.le_time = QtWidgets.QLineEdit(self.centralwidget)
self.le_time.setEnabled(True)
self.le_time.setGeometry(QtCore.QRect(20, 110, 71, 41))
self.le_time.setObjectName("le_time")
self.lbl_sum = QtWidgets.QLabel(self.centralwidget)
self.lbl_sum.setGeometry(QtCore.QRect(100, 40, 55, 16))
self.lbl_sum.setObjectName("lbl_sum")
self.lbl_notice = QtWidgets.QLabel(self.centralwidget)
self.lbl_notice.setGeometry(QtCore.QRect(350, 130, 321, 161))
self.lbl_notice.setText("")
self.lbl_notice.setWordWrap(True)
self.lbl_notice.setObjectName("lbl_notice")
self.tableWidget = QtWidgets.QTableWidget(self.centralwidget)
self.tableWidget.setGeometry(QtCore.QRect(20, 300, 741, 311))
self.tableWidget.setObjectName("tableWidget")
self.tableWidget.setColumnCount(5)
self.tableWidget.setRowCount(0)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setHorizontalHeaderItem(0, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setHorizontalHeaderItem(1, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setHorizontalHeaderItem(2, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setHorizontalHeaderItem(3, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setHorizontalHeaderItem(4, item)
self.tableWidget.horizontalHeader().setCascadingSectionResizes(False)
self.tableWidget.horizontalHeader().setMinimumSectionSize(66)
self.tableWidget.horizontalHeader().setSortIndicatorShown(False)
self.tableWidget.horizontalHeader().setStretchLastSection(False)
self.tableWidget.verticalHeader().setVisible(False)
self.tableWidget.verticalHeader().setCascadingSectionResizes(False)
self.tableWidget.verticalHeader().setDefaultSectionSize(51)
self.tableWidget.verticalHeader().setHighlightSections(False)
self.tableWidget.verticalHeader().setMinimumSectionSize(50)
self.tableWidget.verticalHeader().setStretchLastSection(False)
self.formLayoutWidget_2 = QtWidgets.QWidget(self.centralwidget)
self.formLayoutWidget_2.setGeometry(QtCore.QRect(610, 40, 232, 121))
self.formLayoutWidget_2.setObjectName("formLayoutWidget_2")
self.formLayout_2 = QtWidgets.QFormLayout(self.formLayoutWidget_2)
self.formLayout_2.setContentsMargins(0, 0, 0, 0)
self.formLayout_2.setObjectName("formLayout_2")
self.label_3 = QtWidgets.QLabel(self.formLayoutWidget_2)
self.label_3.setObjectName("label_3")
self.formLayout_2.setWidget(0, QtWidgets.QFormLayout.LabelRole, self.label_3)
self.label_4 = QtWidgets.QLabel(self.formLayoutWidget_2)
self.label_4.setObjectName("label_4")
self.formLayout_2.setWidget(1, QtWidgets.QFormLayout.LabelRole, self.label_4)
self.itemName = QtWidgets.QLineEdit(self.formLayoutWidget_2)
self.itemName.setObjectName("itemName")
self.formLayout_2.setWidget(0, QtWidgets.QFormLayout.FieldRole, self.itemName)
self.itemPrice = QtWidgets.QLineEdit(self.formLayoutWidget_2)
self.itemPrice.setObjectName("itemPrice")
self.formLayout_2.setWidget(1, QtWidgets.QFormLayout.FieldRole, self.itemPrice)
self.pushButton_2 = QtWidgets.QPushButton(self.formLayoutWidget_2)
self.pushButton_2.setObjectName("pushButton_2")
self.formLayout_2.setWidget(2, QtWidgets.QFormLayout.FieldRole, self.pushButton_2)
self.tableWidget_2 = QtWidgets.QTableWidget(self.centralwidget)
self.tableWidget_2.setGeometry(QtCore.QRect(25, 631, 731, 141))
self.tableWidget_2.setObjectName("tableWidget_2")
self.tableWidget_2.setColumnCount(5)
self.tableWidget_2.setRowCount(0)
item = QtWidgets.QTableWidgetItem()
self.tableWidget_2.setHorizontalHeaderItem(0, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget_2.setHorizontalHeaderItem(1, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget_2.setHorizontalHeaderItem(2, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget_2.setHorizontalHeaderItem(3, item)
item = QtWidgets.QTableWidgetItem()
self.tableWidget_2.setHorizontalHeaderItem(4, item)
self.tableWidget_2.horizontalHeader().setHighlightSections(False)
self.tableWidget_2.verticalHeader().setVisible(False)
self.tableWidget_2.verticalHeader().setHighlightSections(False)
self.lbl_check = QtWidgets.QLabel(self.centralwidget)
self.lbl_check.setGeometry(QtCore.QRect(810, 320, 161, 21))
self.lbl_check.setObjectName("lbl_check")
self.lbl_notice_2 = QtWidgets.QLabel(self.centralwidget)
self.lbl_notice_2.setGeometry(QtCore.QRect(830, 360, 321, 61))
self.lbl_notice_2.setText("")
self.lbl_notice_2.setWordWrap(True)
self.lbl_notice_2.setObjectName("lbl_notice_2")
self.label_5 = QtWidgets.QLabel(self.centralwidget)
self.label_5.setGeometry(QtCore.QRect(860, 50, 251, 21))
self.label_5.setObjectName("label_5")
self.label_6 = QtWidgets.QLabel(self.centralwidget)
self.label_6.setGeometry(QtCore.QRect(860, 90, 251, 21))
self.label_6.setObjectName("label_6")
self.combo1 = QtWidgets.QComboBox(self.centralwidget)
self.combo1.setGeometry(QtCore.QRect(800, 360, 281, 31))
self.combo1.setObjectName("combo1")
self.label_7 = QtWidgets.QLabel(self.centralwidget)
self.label_7.setGeometry(QtCore.QRect(800, 410, 55, 16))
self.label_7.setObjectName("label_7")
self.label_8 = QtWidgets.QLabel(self.centralwidget)
self.label_8.setGeometry(QtCore.QRect(800, 440, 55, 16))
self.label_8.setObjectName("label_8")
self.label_9 = QtWidgets.QLabel(self.centralwidget)
self.label_9.setGeometry(QtCore.QRect(800, 470, 55, 16))
self.label_9.setObjectName("label_9")
MainWindow.setCentralWidget(self.centralwidget)

self.retranslateUi(MainWindow)
QtCore.QMetaObject.connectSlotsByName(MainWindow)

def retranslateUi(self, MainWindow):
_translate = QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
self.label.setText(_translate("MainWindow", "TextLabel"))
self.label_2.setText(_translate("MainWindow", "TextLabel"))
self.pushButton.setText(_translate("MainWindow", "PushButton"))
self.lbl_sum.setText(_translate("MainWindow", "TextLabel"))
item = self.tableWidget.horizontalHeaderItem(0)
item.setText(_translate("MainWindow", "ID"))
item = self.tableWidget.horizontalHeaderItem(1)
item.setText(_translate("MainWindow", "Cost Price"))
item = self.tableWidget.horizontalHeaderItem(2)
item.setText(_translate("MainWindow", "Selling Price"))
item = self.tableWidget.horizontalHeaderItem(3)
item.setText(_translate("MainWindow", "Purchasing Date"))
item = self.tableWidget.horizontalHeaderItem(4)
item.setText(_translate("MainWindow", "Record Time"))
self.label_3.setText(_translate("MainWindow", "TextLabel"))
self.label_4.setText(_translate("MainWindow", "TextLabel"))
self.pushButton_2.setText(_translate("MainWindow", "PushButton"))
item = self.tableWidget_2.horizontalHeaderItem(0)
item.setText(_translate("MainWindow", "Ref. No:"))
item = self.tableWidget_2.horizontalHeaderItem(1)
item.setText(_translate("MainWindow", "Item Name"))
item = self.tableWidget_2.horizontalHeaderItem(2)
item.setText(_translate("MainWindow", "Item Price"))
item = self.tableWidget_2.horizontalHeaderItem(3)
item.setText(_translate("MainWindow", "Purchase Date"))
item = self.tableWidget_2.horizontalHeaderItem(4)
item.setText(_translate("MainWindow", "Recod Time"))
self.lbl_check.setText(_translate("MainWindow", "TextLabel"))
self.label_5.setText(_translate("MainWindow", "TextLabel"))
self.label_6.setText(_translate("MainWindow", "TextLabel"))
self.label_7.setText(_translate("MainWindow", "TextLabel"))
self.label_8.setText(_translate("MainWindow", "TextLabel"))
self.label_9.setText(_translate("MainWindow", "TextLabel"))

if __name__ == "__main__":
import sys
app = QtWidgets.QApplication(sys.argv)
MainWindow = QtWidgets.QMainWindow()
ui = Ui_MainWindow()
ui.setupUi(MainWindow)
MainWindow.show()
sys.exit(app.exec_())

CodeMonkeyJ · Jul 20, 2021

It's expecting the date.ui file to be XML but it's a Python script generated from the XML, apparently? I think I'll also need a SQLite schema to build DB.db from. Sorry it's annoying, but I can't help much more without being able to run the script as-is. Don't forget to use CODE tags when pasting, it'll help me copy and paste it into my editor.

Lalremruata · Jul 20, 2021

CodeMonkeyJ said:
It's expecting the date.ui file to be XML but it's a Python script generated from the XML, apparently? I think I'll also need a SQLite schema to build DB.db from. Sorry it's annoying, but I can't help much more without being able to run the script as-is. Don't forget to use CODE tags when pasting, it'll help me copy and paste it into my editor.

Sorry. It's my in-experience and poor English. Here, I attached the original files. I am eagerly waiting for your valuable help.

CodeMonkeyJ · Jul 20, 2021

No worries! So, as I said, you'll need to connect to the combo box's currentTextChanged, in order to call a function when something is
selected (this should probably go in __init__):

Python:

self.combo1.currentTextChanged.connect(self.combo1Changed)

Now, let's create this function:

Python:

def combo1Changed(self, value):
    print(f"combo changed {value}")
    conn = sqlite3.connect('DB.db')
    c = conn.cursor()
    c.execute(f"SELECT itemName, itemPrice, date1, time1 FROM table_2 WHERE itemName = ?", [value])
    data = c.fetchone()
    print(f"{data[0]}, {data[1]}, {data[2]}, {data[3]}")
    self.label_7.setText(f"{data[1]}")
    self.label_8.setText(f"{data[2]}")
    self.label_9.setText(f"{data[3]}")

For debugging sake, I've left in some print statements. But, create a connection to the database and call SELECT statement, as you have elsewhere in the script. But, this time, we're using "parameter binding" to find one with an itemName of the new combo box value. We fetch the first row it finds, then call setText on each of the labels we want to update.

Lalremruata · Jul 20, 2021

CodeMonkeyJ said:
No worries! So, as I said, you'll need to connect to the combo box's currentTextChanged, in order to call a function when something is
selected (this should probably go in __init__):

Python:

self.combo1.currentTextChanged.connect(self.combo1Changed)

Now, let's create this function:

Python:

def combo1Changed(self, value): print(f"combo changed {value}") conn = sqlite3.connect('DB.db') c = conn.cursor() c.execute(f"SELECT itemName, itemPrice, date1, time1 FROM table_2 WHERE itemName = ?", [value]) data = c.fetchone() print(f"{data[0]}, {data[1]}, {data[2]}, {data[3]}") self.label_7.setText(f"{data[1]}") self.label_8.setText(f"{data[2]}") self.label_9.setText(f"{data[3]}")

For debugging sake, I've left in some print statements. But, create a connection to the database and call SELECT statement, as you have elsewhere in the script. But, this time, we're using "parameter binding" to find one with an itemName of the new combo box value. We fetch the first row it finds, then call setText on each of the labels we want to update.

Thanks. It works like a charm. I am really indebted to you.

How to fetch database row and display in Qlabels with QcomboBox selected value in PyQt5

Lalremruata

CodeMonkeyJ

Lalremruata

CodeMonkeyJ

Lalremruata

CodeMonkeyJ

Lalremruata

Attachments

CodeMonkeyJ

Lalremruata

Members online

Forum statistics

Latest Threads