• Guest, before posting your code please take these rules into consideration:
    • It is required to use our BBCode feature to display your code. While within the editor click < / > or >_ and place your code within the BB Code prompt. This helps others with finding a solution by making it easier to read and easier to copy.
    • Don't share a wall of code. All we want is the problem area, the code related to your issue.


    To learn more about how to use our BBCode feature, please click here.

    Thank you, Code Forum.

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

Lalremruata

New Coder
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:


Python:
        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 = 150

and if combobox value 'apple' is selected:
label_1 = APPL
label_2 = 15
label_3 =180

Thanks in advance.
 
Last edited by a moderator:

simong1993

Active Coder
Staff Team
Guardian
Hey man and welcome. So i think the issue you are having is you are only selecting item_name from your table this this

Python:
c.execute("SELECT item_name FROM table_1")

Where as what you want is

Python:
c.execute("SELECT item_name, item_code, item_qty, item_price FROM table_1")

data = c.fetchall()

self.comboBox_3.clear()

for item_name in data:
          print(item_name[0]) # item_name
          print(item_name[1]) # item_code
          print(item_name[2]) # item_qty
          print(item_name[3]) # item_price

So now what we are doing is selecting 4 columns from your table, fetching all of them in the database and then we go through each row one by one
 

Lalremruata

New Coder
Thanks for your reply. What I want to do is displaying other relevant data (column value) to be displayed in the label/textbox whenever the combobox value is changed.

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

and if combobox value 'apple' is selected:
label_1 = APPL
label_2 = 15
label_3 =180
 

simong1993

Active Coder
Staff Team
Guardian
So that can all be done like this:-
Python:
ItemNameSearch = apple

sql = "SELECT item_name, item_code, item_qty, item_price FROM table_1 WHERE item_name = %s LIMIT 1"
val = (ItemNameSearch)
cursor.execute(sql, val)
 
data = c.fetchall()

self.comboBox_3.clear()

for item_name in data:
          print(item_name[0]) # item_name
          print(item_name[1]) # item_code
          print(item_name[2]) # item_qty
          print(item_name[3]) # item_price

So what i have done here is i am selecting from the database your content but i am only looking for the first instance where you item_name is apple, once found as i have LIMIT 1 is stops the query then displays it. I have put ItemNameSearch as your variable as this way you can change that by say
ItemNameSearch = coconut, rerun the script and it will now show you the line with coconut in it. Change ItemNameSearch = pumpkin and it shows pumpkin and you get the idea. Then if we wanted to get really really fancy we could do this


Python:
def populate_combobox(ItemNameSearch):

    conn = sqlite3.connect('DB.db')

    c = conn.cursor() 

    sql = "SELECT item_name, item_code, item_qty, item_price FROM table_1 WHERE item_name = %s LIMIT 1"

    val = (ItemNameSearch)

    cursor.execute(sql, val)

    data = c.fetchall() 

    self.comboBox_3.clear() 

    for item_name in data:

              print(item_name[0]) # item_name

              print(item_name[1]) # item_code

              print(item_name[2]) # item_qty

              print(item_name[3]) # item_price


while True:
     
    ItemNameSearch = input("Enter Your Option")
    print(populate_combobox(ItemNameSearch))

I cannot check too much as i do not have your whole code but what this should do is ask you what to search for, you type it in and then it finds it displays it and then repeats the whole thing again.
 

Lalremruata

New Coder
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("<font color='blue'>Successfully insert data into records.</font>")
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("<font color='red'>Successfully insert data into records.</font>")
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("<font color='red'>Empty!.</font>")
# self.pushButton_2.setEnabled(False)
# elif self.itemPrice.text() == "":
# self.label_6.setText("<font color='red'>Empty!.</font>")
# 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_()
 

Top