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:
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 name4) 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.