Tutorial Python and PyMySQL database navigation

Introduction

Displaying records in order can be 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. This can happen if, for example, in 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: First, 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(448, 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", "Usuários"))
               self.pushButton_first.setText(_translate("Dialog", "<<"))
               self.pushButton_previous.setText(_translate("Dialog", "<"))
               self.pushButton_next.setText(_translate("Dialog", ">"))
               self.pushButton_last.setText(_translate("Dialog", ">>"))
   
               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.

History

• 11^th April, 2020: Initial version