PySide6/PyQt6库存系统：编辑删除功能仅对首行生效求助

我用PySide6、PyQt6（QtDesigner）开发库存系统的数据库模块，接近完成时遇到bug：编辑或删除任意行时，始终只有第一行会被选中并操作，导致异常行为。相关代码如下：

ButtonWidget代码 #

class ButtonWidget(QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)
        # 创建两个按钮并添加到水平布局
        edit_icon = QtGui.QIcon("Icons/edit.svg")
        self.edit_button = QtWidgets.QPushButton()
        self.edit_button.setIcon(edit_icon)
        delete_icon = QtGui.QIcon("Icons/trash3.svg")
        self.delete_button = QtWidgets.QPushButton()
        self.delete_button.setIcon(delete_icon)
        layout = QHBoxLayout()
        layout.addWidget(self.delete_button)
        layout.addWidget(self.edit_button)
        self.setLayout(layout)

EditDialog代码 #

class EditDialog(QDialog):
    def __init__(self, parent=None, row_data=None):
        super(EditDialog, self).__init__(parent)
        self.ui = Ui_Edit_Dialog()
        self.ui.setupUi(self)

        # 设置输入框初始值
        self.ui.item_id_lineEdit.setText(str(row_data[0]))
        self.ui.product_name_lineEdit.setText(row_data[1])
        self.ui.category_lineEdit.setText(row_data[2])
        self.ui.quantity_lineEdit.setText(row_data[3])
        self.ui.price_lineEdit.setText(row_data[4])

MainWindow代码 #

class MainWindow(QMainWindow):
    def __init__(self): 
        QMainWindow.__init__(self)
        self.ui = Ui_MainWindow()
        self.ui.setupUi(self)

        # 页面切换
        self.ui.btn_menu_1.clicked.connect(lambda: self.ui.stackedWidget.setCurrentWidget(self.ui.page_1))
        self.ui.btn_menu_2.clicked.connect(lambda: self.ui.stackedWidget.setCurrentWidget(self.ui.page_2))
        self.ui.btn_menu_3.clicked.connect(lambda: self.ui.stackedWidget.setCurrentWidget(self.ui.page_3))
        self.ui.btn_menu_4.clicked.connect(lambda: self.ui.stackedWidget.setCurrentWidget(self.ui.page_4))
        self.show()
        # 页面4功能绑定
        self.ui.add_btn.clicked.connect(self.add_btn_function)
        self.loadData()
        self.ui.search_lineEdit.returnPressed.connect(self.search_function)
    
    def add_btn_function(self):
        product_name = self.ui.product_name_lineEdit.text()
        category = self.ui.category_lineEdit.text()
        quantity = self.ui.quantity_lineEdit.text()
        price = self.ui.price_lineEdit.text()

        if not all([product_name, category, quantity, price]):
            QMessageBox.warning(self, '错误', '请填写所有字段。')
            return
        else:
            cursor.execute(f"INSERT INTO items (productname, category, quantity, price) VALUES (?, ?, ?, ?)", (product_name, category, quantity, price))
            conn.commit()
            print("成功创建条目: ", product_name, category, quantity, price)
            # 清空输入框
            self.ui.product_name_lineEdit.setText('')
            self.ui.category_lineEdit.setText('')
            self.ui.quantity_lineEdit.setText('')
            self.ui.price_lineEdit.setText('')
            # 刷新表格
            self.loadData()
    
    def search_function(self):
        search_term = self.ui.search_lineEdit.text()
        if not search_term:
            self.loadData()
            return

        # 清空表格现有内容
        self.ui.tableWidget.setRowCount(0)

        # 查询匹配搜索词的条目
        cursor.execute(f"SELECT * FROM items WHERE productname LIKE '%{search_term}%' OR category LIKE '%{search_term}%'")
        rows = cursor.fetchall()

        # 将匹配条目添加到表格
        for row in rows:
            row_position = self.ui.tableWidget.rowCount()
            self.ui.tableWidget.insertRow(row_position)
            for i, item in enumerate(row):
                self.ui.tableWidget.setItem(row_position, i, QTableWidgetItem(str(item)))
    
    # 加载数据
    def loadData(self):
        connection = sqlite3.connect("DBMS.db")
        cursor = connection.cursor()
        query = "SELECT * FROM items"
        result = cursor.execute(query)
        self.ui.tableWidget.setRowCount(0)
        tablerow = 0
        for row_number, row_data in enumerate(result):
            self.ui.tableWidget.insertRow(row_number)
            for column_number, data in enumerate(row_data):
                self.ui.tableWidget.setItem(row_number, column_number, QTableWidgetItem(str(data))) 

            btn_widget = ButtonWidget()
            btn_widget.delete_button.clicked.connect(lambda row=row_number: self.delete_function(row))
            btn_widget.edit_button.clicked.connect(lambda row=row_number: self.edit_function(row))
            self.ui.tableWidget.setCellWidget(row_number, 5, btn_widget)

    # 删除功能
    def delete_function(self, row):
        # 显示确认对话框
        confirm = QMessageBox.question(self, '确认删除', '确定要删除这个条目吗？', QMessageBox.Yes | QMessageBox.No)
        if confirm == QMessageBox.Yes:
            # 获取要删除的条目ID
            item_id = self.ui.tableWidget.item(row, 0).text()
            # 从数据库删除条目
            cursor.execute(f"DELETE FROM items WHERE id = ?", (item_id))
            conn.commit()
            print(f"成功删除ID为 {item_id} 的条目")
            # 从表格移除行
            self.ui.tableWidget.removeRow(row)

    def edit_function(self,row):
        # 获取选中行的数据
        row_data = []
        for column in range(self.ui.tableWidget.columnCount()):
            item = self.ui.tableWidget.item(row, column)
            if item is not None:
                row_data.append(item.text())
            else:
                row_data.append('')
        
        # 打开编辑对话框
        dialog = EditDialog(self, row_data=row_data)
        if dialog.exec() == QDialog.Accepted:
            # 获取修改后的值
            productname = dialog.ui.product_name_lineEdit.text()
            category = dialog.ui.category_lineEdit.text()
            quantity = dialog.ui.quantity_lineEdit.text()
            price = dialog.ui.price_lineEdit.text()
            
            # 更新数据库值
            connection = sqlite3.connect("DBMS.db")
            cursor = connection.cursor()
            query = ("UPDATE items SET productname = ?, category = ?, quantity = ?, price = ? WHERE id = ?")
            values = (productname, category, quantity, price, item_id) # 注释：ID从1开始，所以用row+1？
            cursor.execute(query, values)
            connection.commit()
            
            # 刷新表格
            self.loadData()

问题分析与修复方案 #

核心问题1：全局数据库连接/游标混用 #

你在add_btn_function、delete_function中直接使用全局的cursor和conn，但loadData又创建了局部连接，这种混用会导致数据库状态不一致，可能引发数据读取错误，进而影响行定位逻辑。

修复：
所有数据库操作统一使用局部连接，避免全局变量干扰：

def delete_function(self, row):
    confirm = QMessageBox.question(self, '确认删除', '确定要删除这个条目吗？', QMessageBox.Yes | QMessageBox.No)
    if confirm == QMessageBox.Yes:
        item_id = self.ui.tableWidget.item(row, 0).text()
        # 新建局部连接
        connection = sqlite3.connect("DBMS.db")
        cursor = connection.cursor()
        # 注意元组格式，末尾加逗号避免类型错误
        cursor.execute("DELETE FROM items WHERE id = ?", (item_id,))
        connection.commit()
        connection.close() # 操作完成后关闭连接
        print(f"成功删除ID为 {item_id} 的条目")
        self.ui.tableWidget.removeRow(row)

核心问题2：edit_function中未定义item_id #

在编辑功能的数据库更新代码里，直接使用了未定义的item_id变量，会触发NameError，导致更新失败，可能被误判为行选中问题。

修复：
从选中行的数据中提取item_id：

def edit_function(self,row):
    row_data = []
    for column in range(self.ui.tableWidget.columnCount()):
        item = self.ui.tableWidget.item(row, column)
        if item is not None:
            row_data.append(item.text())
        else:
            row_data.append('')
    
    dialog = EditDialog(self, row_data=row_data)
    if dialog.exec() == QDialog.Accepted:
        productname = dialog.ui.product_name_lineEdit.text()
        category = dialog.ui.category_lineEdit.text()
        quantity = dialog.ui.quantity_lineEdit.text()
        price = dialog.ui.price_lineEdit.text()
        # 从行数据中获取item_id
        item_id = row_data[0]
        
        connection = sqlite3.connect("DBMS.db")
        cursor = connection.cursor()
        query = ("UPDATE items SET productname = ?, category = ?, quantity = ?, price = ? WHERE id = ?")
        values = (productname, category, quantity, price, item_id)
        cursor.execute(query, values)
        connection.commit()
        connection.close()
        
        self.loadData()

核心问题3：SQL注入风险（额外优化） #

search_function中使用字符串拼接构造SQL语句，存在SQL注入风险，同时可能导致搜索逻辑异常。

修复：
改用参数化查询：

def search_function(self):
    search_term = self.ui.search_lineEdit.text()
    if not search_term:
        self.loadData()
        return

    self.ui.tableWidget.setRowCount(0)
    
    connection = sqlite3.connect("DBMS.db")
    cursor = connection.cursor()
    # 参数化查询，避免注入风险
    cursor.execute("SELECT * FROM items WHERE productname LIKE ? OR category LIKE ?", (f'%{search_term}%', f'%{search_term}%'))
    rows = cursor.fetchall()
    connection.close()

    for row in rows:
        row_position = self.ui.tableWidget.rowCount()
        self.ui.tableWidget.insertRow(row_position)
        for i, item in enumerate(row):
            self.ui.tableWidget.setItem(row_position, i, QTableWidgetItem(str(item)))

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