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:
   SQL

   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:
   SQL

   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:
   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