[PyQt] Tutorial: Python and pymsql database navigation

[JayCee]

How to display MySQL database records in alphabetical order on a PyQT5 form using Python and pymysql without depending on record ID numbers:


Hello, guys! Greetings from Brazil. This is a bit tricky because records are usually not entered in alphabetical order, and using record IDs to control navigation doesn't work if ID numbers are not sequenced in increments of 1 and / or if they are sorted by a especific column. This can happen if, for example, a table that had ten records from 1 to 10, had records 3, 7 and 9 deleted, thus messing up the sequence.

The solution is to use row numbers, and this is how I did it:

1) I designed a database navigation form on PyQT5 with four QLineEdit boxes and four navigation buttons: Fisrt, Previous, Next and Last.

2) Then I created a FUNCTION in phpMyAdmin with a variable to generate the row numbers I will need in a VIEW. The FUNCTION is as follows:

    DELIMITER $$
    CREATE FUNCTION `row_numbers`() RETURNS INT(11)
    BEGIN
    SET @row_no := IFNULL(@row_no,0) + 1;
    RETURN @row_no;
    END$$
    DELIMITER;

3) Then I created a VIEW in phpMyAdmin for a table called 'users' to display the records in alphabetical order with their respective row numbers. The VIEW is as follows:

    CREATE VIEW users_view (number, name, email, pwd, market, id) AS
    SELECT row_numbers() AS number, name, email, pwd, market, id
    FROM users
    ORDER BY name

4) And finally, here is the complete code for the navigation form in Python:

    import sys
    import pymysql
    from PyQt5 import QtCore, QtWidgets
    from PyQt5.QtWidgets import QLineEdit
    from pymysql import Error

    rowNo = 1

    connection = pymysql.connect(
        host='server_name_or_address',
        user='user_name',
        password='your_password',
        db='database_name')

    cur = connection.cursor()

    sql0 = "CREATE TEMPORARY TABLE users_temp AS " \
           "SELECT number, name, email, pwd, market, id " \
           "FROM users_view " \
           "ORDER BY name"

    cur.execute(sql0)


    class Ui_Dialog(object):

        def __init__(self):
            self.lineEdit_name = QtWidgets.QLineEdit(Dialog)
            self.lineEdit_email = QtWidgets.QLineEdit(Dialog)
            self.lineEdit_pwd = QtWidgets.QLineEdit(Dialog)
            self.lineEdit_market = QtWidgets.QLineEdit(Dialog)
            self.pushButton_first = QtWidgets.QPushButton(Dialog)
            self.pushButton_previous = QtWidgets.QPushButton(Dialog)
            self.pushButton_next = QtWidgets.QPushButton(Dialog)
            self.pushButton_last = QtWidgets.QPushButton(Dialog)

        def setupUi(self, Dialog):
            Dialog.setObjectName("Dialog")
            Dialog.resize(450, 300)

            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.setGeometry(QtCore.QRect(130, 90, 191, 21))
            self.lineEdit_email.setInputMethodHints(QtCore.Qt.ImhEmailCharactersOnly)
            self.lineEdit_email.setObjectName("lineEdit_email")

            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.setGeometry(QtCore.QRect(130, 170, 131, 21))
            self.lineEdit_market.setInputMethodHints(QtCore.Qt.ImhUppercaseOnly)
            self.lineEdit_market.setObjectName("lineEdit_market")

            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.setGeometry(QtCore.QRect(150, 240, 61, 28))
            self.pushButton_previous.setObjectName("pushButton_previous")
            self.pushButton_previous.clicked.connect(ShowPrevious)

            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.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", "Users"))
            self.pushButton_first.setText(_translate("Dialog", "First"))
            self.pushButton_previous.setText(_translate("Dialog", "Previous"))
            self.pushButton_next.setText(_translate("Dialog", "Next"))
            self.pushButton_last.setText(_translate("Dialog", "Last"))

            LockForm(self)
            ShowFirst(self)


    def LockForm(self):
        for fields in Dialog.findChildren(QLineEdit):
            fields.setReadOnly(True)


    def ShowFirst(self):
        global rowNo
        sql = "SELECT number, name, email, pwd, market, id FROM users_temp"
        cur.execute(sql)
        row = cur.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[4])
            rowNo = row[0]
        else:
            print("Error in accessing table")


    def ShowPrevious(self):
        global rowNo
        rowNo -= 1
        sql = "SELECT number, name, email, pwd, market, id FROM users_temp WHERE number=%s"
        cur.execute(sql, rowNo)
        row = cur.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[4])
        else:
            rowNo += 1


    def ShowNext(self):
        global rowNo
        rowNo += 1
        sql = "SELECT number, name, email, pwd, market, id FROM users_temp WHERE number=%s"
        cur.execute(sql, rowNo)
        row = cur.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[4])
        else:
            rowNo -= 1


    def ShowLast(self):
        global rowNo
        sql = "SELECT number, name, email, pwd, market, id FROM users_temp"
        cur.execute(sql)
        for row in cur.fetchall():
            ui.lineEdit_name.setText(row[1])
            ui.lineEdit_email.setText(row[2])
            ui.lineEdit_pwd.setText(row[3])
            ui.lineEdit_market.setText(row[4])
            rowNo = row[0]


    if __name__ == "__main__":
        app = QtWidgets.QApplication(sys.argv)
        Dialog = QtWidgets.QDialog()
        ui = Ui_Dialog()
        ui.setupUi(Dialog)
        Dialog.show()
        sys.exit(app.exec_())

That's it, guys! I hope it helps. Best regards and stay well.