Display MySQL data in QLlineEdit text boxes - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: GUI (https://python-forum.io/forum-10.html) +--- Thread: Display MySQL data in QLlineEdit text boxes (/thread-25289.html) |
Display MySQL data in QLlineEdit text boxes - JayCee - Mar-25-2020 Hello everyone, Greetings from Brazil! I'm a beginner learning Python, switching from C# .NET. Here's the scenario: I was able to create a GUI form to add records to a MySQL database on my Localhost. Now I'm trying to display the MySQL data from a SELECT statement into QLineEdit text boxes on my GUI form, but nothing worked so far. I am able to display the results of the SELECT stament in the console though. Please refer to my code below. Thank you very much for your time and help. I really appreciate it! Best regards. # -*- coding: utf-8 -*- # Form implementation generated from reading ui file 'ui/usuarios.ui' # Created by: PyQt5 UI code generator 5.14.1 # WARNING! All changes made in this file will be lost! import pymysql from PyQt5 import QtCore, QtWidgets connection = pymysql.connect( host='localhost', user='user_name', password='XXXXXXXXX', db='database_name' ) class Ui_MainWindow(object): def setupUi(self, MainWindow): MainWindow.setObjectName("MainWindow") MainWindow.resize(521, 326) self.centralwidget = QtWidgets.QWidget(MainWindow) self.centralwidget.setObjectName("centralwidget") self.pushButton_first = QtWidgets.QPushButton(self.centralwidget) self.pushButton_first.setGeometry(QtCore.QRect(65, 265, 90, 28)) self.pushButton_first.setObjectName("pushButton_first") self.pushButton_previous = QtWidgets.QPushButton(self.centralwidget) self.pushButton_previous.setGeometry(QtCore.QRect(165, 265, 90, 28)) self.pushButton_previous.setObjectName("pushButton_previous") self.pushButton_next = QtWidgets.QPushButton(self.centralwidget) self.pushButton_next.setGeometry(QtCore.QRect(265, 265, 90, 28)) self.pushButton_next.setObjectName("pushButton_next") self.pushButton_last = QtWidgets.QPushButton(self.centralwidget) self.pushButton_last.setGeometry(QtCore.QRect(365, 265, 90, 28)) self.pushButton_last.setObjectName("pushButton_last") self.lineEdit_name = QtWidgets.QLineEdit(self.centralwidget) self.lineEdit_name.setGeometry(QtCore.QRect(160, 55, 261, 28)) self.lineEdit_name.setInputMethodHints(QtCore.Qt.ImhUppercaseOnly) self.lineEdit_name.setObjectName("lineEdit_name") self.lineEdit_email = QtWidgets.QLineEdit(self.centralwidget) self.lineEdit_email.setGeometry(QtCore.QRect(160, 95, 181, 28)) self.lineEdit_email.setInputMethodHints(QtCore.Qt.ImhEmailCharactersOnly) self.lineEdit_email.setObjectName("lineEdit_email") self.lineEdit_pwd = QtWidgets.QLineEdit(self.centralwidget) self.lineEdit_pwd.setGeometry(QtCore.QRect(160, 135, 113, 28)) self.lineEdit_pwd.setInputMethodHints(QtCore.Qt.ImhSensitiveData) self.lineEdit_pwd.setObjectName("lineEdit_pwd") self.lineEdit_market = QtWidgets.QLineEdit(self.centralwidget) self.lineEdit_market.setGeometry(QtCore.QRect(160, 175, 113, 28)) self.lineEdit_market.setObjectName("lineEdit_market") self.label_name = QtWidgets.QLabel(self.centralwidget) self.label_name.setGeometry(QtCore.QRect(95, 60, 58, 16)) self.label_name.setAlignment(QtCore.Qt.AlignRight|QtCore.Qt.AlignTrailing|QtCore.Qt.AlignVCenter) self.label_name.setObjectName("label_name") self.label_email = QtWidgets.QLabel(self.centralwidget) self.label_email.setGeometry(QtCore.QRect(95, 100, 58, 16)) self.label_email.setAlignment(QtCore.Qt.AlignRight|QtCore.Qt.AlignTrailing|QtCore.Qt.AlignVCenter) self.label_email.setObjectName("label_email") self.label_pwd = QtWidgets.QLabel(self.centralwidget) self.label_pwd.setGeometry(QtCore.QRect(95, 140, 58, 16)) self.label_pwd.setAlignment(QtCore.Qt.AlignRight|QtCore.Qt.AlignTrailing|QtCore.Qt.AlignVCenter) self.label_pwd.setObjectName("label_pwd") self.label_market = QtWidgets.QLabel(self.centralwidget) self.label_market.setGeometry(QtCore.QRect(95, 180, 58, 16)) self.label_market.setAlignment(QtCore.Qt.AlignRight|QtCore.Qt.AlignTrailing|QtCore.Qt.AlignVCenter) self.label_market.setObjectName("label_market") MainWindow.setCentralWidget(self.centralwidget) self.statusbar = QtWidgets.QStatusBar(MainWindow) self.statusbar.setObjectName("statusbar") MainWindow.setStatusBar(self.statusbar) self.retranslateUi(MainWindow) QtCore.QMetaObject.connectSlotsByName(MainWindow) def retranslateUi(self, MainWindow): _translate = QtCore.QCoreApplication.translate MainWindow.setWindowTitle(_translate("MainWindow", "Usuários")) self.pushButton_next.setText(_translate("MainWindow", ">")) self.pushButton_previous.setText(_translate("MainWindow", "<")) self.pushButton_first.setText(_translate("MainWindow", "<<")) self.pushButton_last.setText(_translate("MainWindow", ">>")) self.label_name.setText(_translate("MainWindow", "Nome: ")) self.label_email.setText(_translate("MainWindow", "e-mail: ")) self.label_pwd.setText(_translate("MainWindow", "senha: ")) self.label_market.setText(_translate("MainWindow", "Market:")) try: with connection.cursor() as cursor: sql = "SELECT nome, email, senha, como_chegou FROM usuarios" try: cursor.execute(sql) print(cursor.rowcount) print("Record loaded successfully.") myresult = cursor.fetchall() for x in myresult: print(x) except: print("Oops! Something wrong.") connection.commit() finally: connection.close() 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_()) RE: Display MySQL data in QLlineEdit text boxes - JayCee - Mar-26-2020 I've found the solution! Please refer to the code below. Now I am able to navigate through the rows using a pyqt5 GUI form. (I deleted the labels from the form to make the code easier to read): import sys import pymysql from PyQt5 import QtCore, QtWidgets from pymysql import Error rowNo = 1 connection = pymysql.connect( host='localhost', user='user_name', password='XXXXXXXX', db='database_name' ) cursor = connection.cursor() sql = "SELECT id, nome, email, senha, data_cadastro, niveis_acesso_id, validacao, ativo, como_chegou FROM usuarios ORDER BY nome ASC" cursor.execute(sql) class Ui_Dialog(object): def setupUi(self, Dialog): Dialog.setObjectName("Dialog") Dialog.resize(448, 300) self.lineEdit_name = QtWidgets.QLineEdit(Dialog) self.lineEdit_name.setGeometry(QtCore.QRect(130, 50, 241, 21)) self.lineEdit_name.setInputMethodHints(QtCore.Qt.ImhUppercaseOnly) self.lineEdit_name.setObjectName("lineEdit_name") self.lineEdit_email = QtWidgets.QLineEdit(Dialog) self.lineEdit_email.setGeometry(QtCore.QRect(130, 90, 191, 21)) self.lineEdit_email.setInputMethodHints(QtCore.Qt.ImhEmailCharactersOnly) self.lineEdit_email.setObjectName("lineEdit_email") self.lineEdit_pwd = QtWidgets.QLineEdit(Dialog) self.lineEdit_pwd.setGeometry(QtCore.QRect(130, 130, 131, 21)) self.lineEdit_pwd.setInputMethodHints(QtCore.Qt.ImhSensitiveData | QtCore.Qt.ImhUppercaseOnly) self.lineEdit_pwd.setObjectName("lineEdit_pwd") self.lineEdit_market = QtWidgets.QLineEdit(Dialog) self.lineEdit_market.setGeometry(QtCore.QRect(130, 170, 131, 21)) self.lineEdit_market.setInputMethodHints(QtCore.Qt.ImhUppercaseOnly) self.lineEdit_market.setObjectName("lineEdit_market") self.pushButton_first = QtWidgets.QPushButton(Dialog) self.pushButton_first.setGeometry(QtCore.QRect(70, 240, 61, 28)) self.pushButton_first.setObjectName("pushButton_first") self.pushButton_first.clicked.connect(ShowFirst) self.pushButton_previous = QtWidgets.QPushButton(Dialog) self.pushButton_previous.setGeometry(QtCore.QRect(150, 240, 61, 28)) self.pushButton_previous.setObjectName("pushButton_previous") self.pushButton_previous.clicked.connect(ShowPrevious) self.pushButton_next = QtWidgets.QPushButton(Dialog) self.pushButton_next.setGeometry(QtCore.QRect(230, 240, 61, 28)) self.pushButton_next.setObjectName("pushButton_next") self.pushButton_next.clicked.connect(ShowNext) self.pushButton_last = QtWidgets.QPushButton(Dialog) self.pushButton_last.setGeometry(QtCore.QRect(310, 240, 61, 28)) self.pushButton_last.setObjectName("pushButton_last") self.pushButton_last.clicked.connect(ShowLast) self.retranslateUi(Dialog) def retranslateUi(self, Dialog): _translate = QtCore.QCoreApplication.translate Dialog.setWindowTitle(_translate("Dialog", "Dialog")) self.pushButton_first.setText(_translate("Dialog", "<<")) self.pushButton_previous.setText(_translate("Dialog", "<")) self.pushButton_next.setText(_translate("Dialog", ">")) self.pushButton_last.setText(_translate("Dialog", ">>")) ShowFirst(self) def ShowFirst(self): try: cursor.execute(sql) row = cursor.fetchone() if row: ui.lineEdit_name.setText(row[1]) ui.lineEdit_email.setText(row[2]) ui.lineEdit_pwd.setText(row[3]) ui.lineEdit_market.setText(row[8]) except Error as e: print("Error in accessing table") def ShowPrevious(self): global rowNo rowNo -= 1 sql = "SELECT id, nome, email, senha, data_cadastro, niveis_acesso_id, validacao, ativo, como_chegou FROM usuarios WHERE id=" + str(rowNo) + " ORDER BY nome ASC" cursor.execute(sql) row = cursor.fetchone() if row: ui.lineEdit_name.setText(row[1]) ui.lineEdit_email.setText(row[2]) ui.lineEdit_pwd.setText(row[3]) ui.lineEdit_market.setText(row[8]) else: rowNo += 1 def ShowNext(self): global rowNo rowNo += 1 sql = "SELECT id, nome, email, senha, data_cadastro, niveis_acesso_id, validacao, ativo, como_chegou FROM usuarios WHERE id=" + str(rowNo) + " ORDER BY nome ASC" cursor.execute(sql) row = cursor.fetchone() if row: ui.lineEdit_name.setText(row[1]) ui.lineEdit_email.setText(row[2]) ui.lineEdit_pwd.setText(row[3]) ui.lineEdit_market.setText(row[8]) else: rowNo -= 1 def ShowLast(self): cursor.execute(sql) for row in cursor.fetchall(): ui.lineEdit_name.setText(row[1]) ui.lineEdit_email.setText(row[2]) ui.lineEdit_pwd.setText(row[3]) ui.lineEdit_market.setText(row[8]) if __name__ == "__main__": app = QtWidgets.QApplication(sys.argv) Dialog = QtWidgets.QDialog() ui = Ui_Dialog() ui.setupUi(Dialog) Dialog.show() sys.exit(app.exec_()) |