介绍
按顺序显示记录可能会有些棘手，因为通常不会按字母顺序输入记录，并且如果ID编号未按1递增，使用记录ID来控制导航将不起作用。一个表中有10条记录（从1到10），已删除记录3、7和9，从而弄乱了顺序。
解决的办法是使用行号，这就是我的方法：
1、我在PyQT5上设计了一个数据库导航表单，其中包含四个QLineEdit框和四个导航按钮：First，Previous，Next和Last。
2、然后，我用一个变量在phpMyAdmin中创建了一个FUNCTION，以生成一个VIEW中需要的行号。该FUNCTION如下：
DELIMITER $$
CREATE FUNCTION `row_numbers`() RETURNS INT(11)
BEGIN
SET @row_no := IFNULL(@row_no,0) + 1;
RETURN @row_no;
END$$
DELIMITER;3、然后，我在phpMyAdmin中为名为“users”的表创建了一个VIEW，以按字母顺序显示记录及其各自的行号。该VIEW如下：
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、最后，这是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", ""))
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_())就是这样，伙计们！希望对您有所帮助。
