Python Forum

Full Version: Display MySQL data in QLlineEdit text boxes
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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_())
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_())