This is a tutorial on how to display MySQL database records in alphabetical order on a PyQT5 form without depending on record ID numbers, using generated row numbers instead.
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:
- I designed a database navigation form on PyQT5 with four
QLineEdit boxes and four navigation buttons: First, Previous, Next and Last. - 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;
- 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
- 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
- 11th April, 2020: Initial version
