I successfully fetch SQLite database and display it in a combobox. However, I don't know how to fetch it's related values (database column) and display it in a labels. Hoping someone could help, following is my whole code:
date.ui.zip (Size: 1.23 KB / Downloads: 397)
date.py.zip (Size: 1.84 KB / Downloads: 240)
DB.zip (Size: 1.87 KB / Downloads: 249)
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 price here in label_8
#display item price here in label_9
########################
app = QtWidgets.QApplication(sys.argv)
window = Ui()
app.exec_()
date.ui.zip (Size: 1.23 KB / Downloads: 397)
date.py.zip (Size: 1.84 KB / Downloads: 240)
DB.zip (Size: 1.87 KB / Downloads: 249)
Larz60+ write Jul-20-2021, 10:34 AM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
fixed for you this time. Please use bbocde tags on future posts.
*** Second notice ***
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
fixed for you this time. Please use bbocde tags on future posts.
*** Second notice ***
