Python Forum
Display MySQL data in QLlineEdit text boxes
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Display MySQL data in QLlineEdit text boxes
#1
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_())
Reply
#2
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_())
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Scaling text QLabel following display mode windows '100%, 125% ...) VIGNEAUD 2 2,217 Jul-07-2021, 06:38 PM
Last Post: deanhystad
  Display text 3 words at a time algae 5 2,706 Jun-27-2020, 10:25 AM
Last Post: menator01
  Display and update the label text which display the serial value jenkins43 5 8,995 Feb-04-2019, 04:36 AM
Last Post: Larz60+
  Display more than one button in GUI to display MPU6000 Sensor readings barry76 4 3,836 Jan-05-2019, 01:48 PM
Last Post: wuf
  [PyQt] How to display multiple data(from 2 different related table) in one cell of QTableWid yangjae 4 4,320 Oct-17-2018, 07:54 AM
Last Post: Larz60+
  Extracting data from mysql xgrzeniu 2 2,643 May-18-2018, 03:03 PM
Last Post: xgrzeniu
  display reading sensor data hassan_elahi 5 4,841 May-05-2018, 04:28 PM
Last Post: hassan_elahi

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020