Python教程和PyMySQL数据库导航

介绍

按顺序显示记录可能会有些棘手，因为通常不会按字母顺序输入记录，并且如果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 name

4、最后，这是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_())

就是这样，伙计们！希望对您有所帮助。