PyQt5+MySQL数据库GUI程序点击按钮崩溃无报错求助

问题根源分析 #

1. 类成员方法缺失self参数：select()、insert()、delete()、view()作为类的实例方法，定义时未传入self参数，导致调用时无法访问self.table、self.lineCMS等实例属性，触发未捕获的异常直接崩溃。
2. SQL语句拼接存在安全风险与语法隐患：使用字符串format拼接SQL，不仅容易引发SQL注入攻击，还会因输入含特殊字符（如单引号）导致SQL语法错误。
3. 事务未提交：执行INSERT、DELETE操作后未调用mydb.commit()，MySQL默认手动提交事务，导致操作不生效，还可能引发连接异常。
4. 异常捕获范围不足：仅捕获mc.Error，但像属性错误、SQL语法错误等其他异常未被处理，程序直接崩溃且无报错输出。
5. 数据库资源未释放：每次操作后未关闭游标和连接，可能造成连接泄漏。

修复后的完整代码 #

from PyQt5 import QtCore, QtGui, QtWidgets
import mysql.connector as mc
from PyQt5.QtWidgets import QTableWidgetItem

class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(538, 487)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.verticalLayout = QtWidgets.QVBoxLayout(self.centralwidget)
        self.verticalLayout.setObjectName("verticalLayout")
        self.gridLayout_2 = QtWidgets.QGridLayout()
        self.gridLayout_2.setObjectName("gridLayout_2")
        self.lineCMS = QtWidgets.QLineEdit(self.centralwidget)
        self.lineCMS.setObjectName("lineCMS")
        self.gridLayout_2.addWidget(self.lineCMS, 0, 1, 1, 1)
        self.lineRoom = QtWidgets.QLineEdit(self.centralwidget)
        self.lineRoom.setObjectName("lineRoom")
        self.gridLayout_2.addWidget(self.lineRoom, 3, 1, 1, 1)
        self.CMS = QtWidgets.QLabel(self.centralwidget)
        self.CMS.setObjectName("CMS")
        self.gridLayout_2.addWidget(self.CMS, 0, 0, 1, 1)
        self.lineName = QtWidgets.QLineEdit(self.centralwidget)
        self.lineName.setObjectName("lineName")
        self.gridLayout_2.addWidget(self.lineName, 1, 1, 1, 1)
        self.Room = QtWidgets.QLabel(self.centralwidget)
        self.Room.setObjectName("Room")
        self.gridLayout_2.addWidget(self.Room, 3, 0, 1, 1)
        self.Department = QtWidgets.QLabel(self.centralwidget)
        self.Department.setObjectName("Department")
        self.gridLayout_2.addWidget(self.Department, 2, 0, 1, 1)
        self.Name = QtWidgets.QLabel(self.centralwidget)
        self.Name.setObjectName("Name")
        self.gridLayout_2.addWidget(self.Name, 1, 0, 1, 1)
        self.lineDepartment = QtWidgets.QLineEdit(self.centralwidget)
        self.lineDepartment.setObjectName("lineDepartment")
        self.gridLayout_2.addWidget(self.lineDepartment, 2, 1, 1, 1)
        self.lineDOB = QtWidgets.QLineEdit(self.centralwidget)
        self.lineDOB.setObjectName("lineDOB")
        self.gridLayout_2.addWidget(self.lineDOB, 4, 1, 1, 1)
        self.DOB = QtWidgets.QLabel(self.centralwidget)
        self.DOB.setObjectName("DOB")
        self.gridLayout_2.addWidget(self.DOB, 4, 0, 1, 1)
        self.verticalLayout.addLayout(self.gridLayout_2)
        self.horizontalLayout = QtWidgets.QHBoxLayout()
        self.horizontalLayout.setObjectName("horizontalLayout")
        self.Select = QtWidgets.QPushButton(self.centralwidget)
        self.Select.setObjectName("Select")
        self.horizontalLayout.addWidget(self.Select)
        self.line = QtWidgets.QFrame(self.centralwidget)
        self.line.setFrameShape(QtWidgets.QFrame.VLine)
        self.line.setFrameShadow(QtWidgets.QFrame.Sunken)
        self.line.setObjectName("line")
        self.horizontalLayout.addWidget(self.line)
        self.Insert = QtWidgets.QPushButton(self.centralwidget)
        self.Insert.setObjectName("Insert")
        self.horizontalLayout.addWidget(self.Insert)
        self.line_2 = QtWidgets.QFrame(self.centralwidget)
        self.line_2.setFrameShape(QtWidgets.QFrame.VLine)
        self.line_2.setFrameShadow(QtWidgets.QFrame.Sunken)
        self.line_2.setObjectName("line_2")
        self.horizontalLayout.addWidget(self.line_2)
        self.Delete = QtWidgets.QPushButton(self.centralwidget)
        self.Delete.setObjectName("Delete")
        self.horizontalLayout.addWidget(self.Delete)
        self.line_3 = QtWidgets.QFrame(self.centralwidget)
        self.line_3.setFrameShape(QtWidgets.QFrame.VLine)
        self.line_3.setFrameShadow(QtWidgets.QFrame.Sunken)
        self.line_3.setObjectName("line_3")
        self.horizontalLayout.addWidget(self.line_3)
        self.Clear = QtWidgets.QPushButton(self.centralwidget)
        self.Clear.setObjectName("Clear")
        self.horizontalLayout.addWidget(self.Clear)
        self.verticalLayout.addLayout(self.horizontalLayout)
        self.table = QtWidgets.QTableWidget(self.centralwidget)
        self.table.setObjectName("table")
        self.table.setColumnCount(5)
        self.table.setRowCount(0)
        item = QtWidgets.QTableWidgetItem()
        self.table.setHorizontalHeaderItem(0, item)
        item = QtWidgets.QTableWidgetItem()
        self.table.setHorizontalHeaderItem(1, item)
        item = QtWidgets.QTableWidgetItem()
        self.table.setHorizontalHeaderItem(2, item)
        item = QtWidgets.QTableWidgetItem()
        self.table.setHorizontalHeaderItem(3, item)
        item = QtWidgets.QTableWidgetItem()
        self.table.setHorizontalHeaderItem(4, item)
        self.verticalLayout.addWidget(self.table)
        self.horizontalLayout_2 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_2.setObjectName("horizontalLayout_2")
        self.verticalLayout.addLayout(self.horizontalLayout_2)
        self.generalView = QtWidgets.QPushButton(self.centralwidget)
        self.generalView.setObjectName("generalView")
        self.verticalLayout.addWidget(self.generalView)
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 538, 21))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)
        self.Select.clicked.connect(self.select)
        self.Insert.clicked.connect(self.insert)
        self.Delete.clicked.connect(self.delete)
        self.generalView.clicked.connect(self.view)
        self.Clear.clicked.connect(self.clear)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "Student"))
        self.CMS.setText(_translate("MainWindow", "CMS"))
        self.Room.setText(_translate("MainWindow", "Room No."))
        self.Department.setText(_translate("MainWindow", "Department"))
        self.Name.setText(_translate("MainWindow", "Name"))
        self.DOB.setText(_translate("MainWindow", "Date of Birth"))
        self.Select.setText(_translate("MainWindow", "SELECT"))
        self.Insert.setText(_translate("MainWindow", "INSERT"))
        self.Delete.setText(_translate("MainWindow", "DELETE"))
        self.Clear.setText(_translate("MainWindow", "CLEAR"))
        item = self.table.horizontalHeaderItem(0)
        item.setText(_translate("MainWindow", "CMS"))
        item = self.table.horizontalHeaderItem(1)
        item.setText(_translate("MainWindow", "Name"))
        item = self.table.horizontalHeaderItem(2)
        item.setText(_translate("MainWindow", "Department"))
        item = self.table.horizontalHeaderItem(3)
        item.setText(_translate("MainWindow", "Room No."))
        item = self.table.horizontalHeaderItem(4)
        item.setText(_translate("MainWindow", "Date of Birth"))
        self.generalView.setText(_translate("MainWindow", "GENERAL VIEW"))
        
    def select(self):
        try:
            self.table.setRowCount(0)
            QCMS = self.lineCMS.text() + "%"
            QDepartment = self.lineDepartment.text() + "%"
            QName = self.lineName.text() + "%"
            QRoom = self.lineRoom.text() + "%"
            QDOB = self.lineDOB.text() + "%"

            mydb = mc.connect(host="localhost",user="root",password="",database="sakila")
            mycursor = mydb.cursor()
            # 使用参数化查询避免SQL注入
            selectQuery = """SELECT * FROM Student 
                            WHERE CMS like %s 
                            and Department like %s 
                            and Name like %s 
                            and RoomNo like %s 
                            and DateOfBirth like %s"""
            mycursor.execute(selectQuery, (QCMS, QDepartment, QName, QRoom, QDOB))
            queryResult = mycursor.fetchall()
            for row_number, row_data in enumerate(queryResult):
                self.table.insertRow(row_number)
                for column_number, data in enumerate(row_data):
                    self.table.setItem(row_number,column_number,QTableWidgetItem(str(data)))
            # 关闭资源
            mycursor.close()
            mydb.close()
        except Exception as e:
            print(f"查询错误: {str(e)}")

    def insert(self):
        try:
            self.table.setRowCount(0)
            QCMS = self.lineCMS.text()
            QDepartment = self.lineDepartment.text()
            QName = self.lineName.text()
            QRoom = self.lineRoom.text()
            QDOB = self.lineDOB.text()

            mydb = mc.connect(host="localhost",user="root",password="",database="sakila")
            mycursor = mydb.cursor()
            # 参数化插入语句
            insertQuery = """INSERT INTO Student (CMS, Name, Department, RoomNo, DateOfBirth) 
                            VALUES (%s, %s, %s, %s, %s)"""
            mycursor.execute(insertQuery, (QCMS, QName, QDepartment, QRoom, QDOB))
            # 提交事务
            mydb.commit()
            # 查询插入后的结果
            triggerQuery = """SELECT * FROM Student 
                            WHERE CMS = %s 
                            and Department = %s 
                            and Name = %s 
                            and RoomNo = %s 
                            and DateOfBirth = %s"""
            mycursor.execute(triggerQuery, (QCMS, QDepartment, QName, QRoom, QDOB))
            triggerResult = mycursor.fetchall()
            for row_number, row_data in enumerate(triggerResult):
                self.table.insertRow(row_number)
                for column_number, data in enumerate(row_data):
                    self.table.setItem(row_number,column_number,QTableWidgetItem(str(data)))
            # 关闭资源
            mycursor.close()
            mydb.close()
        except Exception as e:
            print(f"插入错误: {str(e)}")
            # 回滚事务
            if 'mydb' in locals():
                mydb.rollback()

    def delete(self):
        try:
            self.table.setRowCount(0)
            QCMS = self.lineCMS.text() + "%"
            QDepartment = self.lineDepartment.text() + "%"
            QName = self.lineName.text() + "%"
            QRoom = self.lineRoom.text() + "%"
            QDOB = self.lineDOB.text() + "%"

            mydb = mc.connect(host="localhost",user="root",password="",database="sakila")
            mycursor = mydb.cursor()
            # 参数化删除语句
            deleteQuery = """DELETE FROM Student 
                            WHERE CMS like %s 
                            and Department like %s 
                            and Name like %s 
                            and RoomNo like %s 
                            and DateOfBirth like %s"""
            mycursor.execute(deleteQuery, (QCMS, QDepartment, QName, QRoom, QDOB))
            # 提交事务
            mydb.commit()
            # 查询删除后的结果
            triggerQuery = """SELECT * FROM Student 
                            WHERE CMS like %s 
                            and Department like %s 
                            and Name like %s 
                            and RoomNo like %s 
                            and DateOfBirth like %s"""
            mycursor.execute(triggerQuery, (QCMS, QDepartment, QName, QRoom, QDOB))
            triggerResult = mycursor.fetchall()
            for row_number, row_data in enumerate(triggerResult):
                self.table.insertRow(row_number)
                for column_number, data in enumerate(row_data):
                    self.table.setItem(row_number,column_number,QTableWidgetItem(str(data)))
            # 关闭资源
            mycursor.close()
            mydb.close()
        except Exception as e:
            print(f"删除错误: {str(e)}")
            # 回滚事务
            if 'mydb' in locals():
                mydb.rollback()

    def view(self):
        try:
            self.table.setRowCount(0)
            mydb = mc.connect(host="localhost",user="root",password="",database="sakila")
            mycursor = mydb.cursor()
            query = "SELECT * FROM Student"
            mycursor.execute(query)
            queryResult = mycursor.fetchall()
            
            for row_number, row_data in enumerate(queryResult):
                self.table.insertRow(row_number)
                for column_number, data in enumerate(row_data):
                    self.table.setItem(row_number,column_number,QTableWidgetItem(str(data)))
            # 关闭资源
            mycursor.close()
            mydb.close()
        except Exception as e:
            print(f"查看错误: {str(e)}")

    def clear(self):
        self.table.setRowCount(0)

if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())

关键修复说明 #

• 所有类实例方法添加self参数，确保能访问实例属性。
• 改用参数化SQL查询，彻底避免SQL注入，同时解决特殊字符导致的语法错误。
• INSERT、DELETE操作后添加mydb.commit()提交事务，异常时执行rollback()回滚。
• 扩大异常捕获范围到Exception，并打印具体错误信息，便于调试。
• 每次数据库操作完成后关闭游标和连接，释放资源。

内容的提问来源于stack exchange，提问作者dacoder