PyQt6 QSqlRelationalTableModel多对多表外键下拉框显示异常

问题描述 #

我使用SQLite数据库，需对关联表RecipeIngredients（字段：Id主键、RecipeId外键、IngredientId外键、QuantityTypeId外键、Quantity实数类型）进行增改操作，期望TableView的外键列显示对应文本的ComboBox，Quantity列用QLineEdit输入。但调用Model.setRelation设置表关联后，视图仅显示列名而非下拉框，此前单外键场景的代码可行，但多外键场景无法适配，以下是我的代码：

import sys
from PyQt6 import QtWidgets as qtw
from PyQt6 import QtGui as qtg
from PyQt6 import QtCore as qtc
from PyQt6 import QtSql as qts

class DateDelegate(qtw.QStyledItemDelegate):

    def createEditor(self, parent, option, proxyModelIndex):
        # make sure to explicitly set the parent
        # otherwise it pops up in a top-level window!
        date_inp = qtw.QDateEdit(parent, calendarPopup=True)
        return date_inp

class RecipeIngredientForm(qtw.QWidget):
    """Form to display/edit all info about an Ingredient"""

    def __init__(self, RecipeIngredientModel):
        super().__init__()
        self.setLayout(qtw.QFormLayout())

        # RecipeIngredient Fields
        self.Recipe = qtw.QComboBox()
        self.layout().addRow('Recipe: ', self.Recipe)
        self.Ingredient = qtw.QComboBox()
        self.layout().addRow('Ingredient: ', self.Ingredient)
        self.QuantityType = qtw.QComboBox()
        self.layout().addRow('QuantityType: ', self.QuantityType)
        self.Quantity = qtw.QLineEdit()
        self.layout().addRow('Quantity: ', self.Quantity)

        # Map the RecipeIngredient fields
        self.RecipeIngredientModel = RecipeIngredientModel
        self.mapper = qtw.QDataWidgetMapper(self)
        self.mapper.setModel(self.RecipeIngredientModel)
        self.mapper.setItemDelegate(
            qts.QSqlRelationalDelegate(self))
        
        self.mapper.addMapping(
            self.Recipe,
            self.RecipeIngredientModel.fieldIndex('RecipeId')
        )
        self.mapper.addMapping(
            self.QuantityType,
            self.RecipeIngredientModel.fieldIndex('QuantityTypeId')
        )
        self.mapper.addMapping(
            self.Ingredient,
            self.RecipeIngredientModel.fieldIndex('IngredientId') 
        )
        self.mapper.addMapping(
            self.Quantity,
            self.RecipeIngredientModel.fieldIndex('Quantity')
        )

        # set model for the Recipe table and setup the combo box
        RecipeModel = RecipeIngredientModel.relationModel(
            self.RecipeIngredientModel.fieldIndex('Name'))
        self.Recipe.setModel(RecipeModel)
        self.Recipe.setModelColumn(1)

        # set model for the Ingredient table and setup the ComboBox
        IngredientModel = RecipeIngredientModel.relationModel(
            self.RecipeIngredientModel.fieldIndex('Description'))
        self.Ingredient.setModel(IngredientModel)
        self.Ingredient.setModelColumn(1)
        
        # set model for the QuantityType tables and setup the ComboBox
        QuantityTypeModel = RecipeIngredientModel.relationModel(
            self.RecipeIngredientModel.fieldIndex('Unit'))
        self.QuantityType.setModel(QuantityTypeModel)
        self.QuantityType.setModelColumn(1)


    def ShowRecipeIngredient(self, RecipeIngredientIndex):
        self.mapper.setCurrentIndex(RecipeIngredientIndex.row())
       
class Mainwindow(qtw.QMainWindow):

    def __init__(self):
        """ MainWindow constructor"""
        super().__init__()
        # Main UI code goes here

        self.stack = qtw.QStackedWidget()
        self.setCentralWidget(self.stack)

        # connect to database
        self.db = qts.QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName('THMMenus.db')
        if not self.db.open():
            error = self.db.lastError().text()
            qtw.QMessageBox.critical(
                None, 'DB Connection Error',
                'Could not open database file: '
                f'{error}')
            sys.exit(1)

        required_tables = {'RecipeIngredient','Recipe', 'Ingredient', 'QuantityType'}
        tables = self.db.tables()
        missing_tables = required_tables - set(tables)
        if missing_tables:
            qtw.QMessageBox.critical(
                None, 'DB Integrity Error', 'Missing tables, please repair DB: '
                f'(missing_tables)')
            sys.exit(1)

        #create the models
        self.RecipeIngredientModel = qts.QSqlRelationalTableModel()
        self.RecipeIngredientModel.setTable('RecipeIngredient')
        self.RecipeIngredientModel.setRelation(
            self.RecipeIngredientModel.fieldIndex('RecipeId'),
            qts.QSqlRelation('Recipe', 'Id', 'Name')
        )
        self.RecipeIngredientModel.setRelation(
            self.RecipeIngredientModel.fieldIndex('IngredientId'),
            qts.QSqlRelation('Ingredient', 'Id', 'Description')
        )

        self.RecipeIngredientModel.setRelation(
            self.RecipeIngredientModel.fieldIndex('QuantityTypeId'),
            qts.QSqlRelation('QuantityType', 'Id', 'Unit')
        )

        self.RecipeIngredientModel.setEditStrategy(qts.QSqlTableModel.EditStrategy.OnFieldChange)
        self.RecipeIngredientList = qtw.QTableView()
        self.RecipeIngredientList.setModel(self.RecipeIngredientModel)
        self.stack.addWidget(self.RecipeIngredientList)

        self.RecipeIngredientModel.select()
        self.ShowList()

        #inserting and deleting rows.
        toolbar = self.addToolBar('Controls')
        toolbar.addAction('Delete Recipe Ingredient', self.DeleteRecipeIngredient)
        toolbar.addAction('Add Recipe Ingredient', self.AddRecipeIngredient)

        self.RecipeIngredientList.setItemDelegate(qts.QSqlRelationalDelegate())
        self.RecipeIngredientList.setSortingEnabled(True)

        # The RecipeIngredient form
        self.RecipeIngredientForm = RecipeIngredientForm(
            self.RecipeIngredientModel
        )
        self.stack.addWidget(self.RecipeIngredientForm)
        self.RecipeIngredientList.doubleClicked.connect(
            self.RecipeIngredientForm.ShowRecipeIngredient)
        self.RecipeIngredientList.doubleClicked.connect(
            lambda: self.stack.setCurrentWidget(self.RecipeIngredientForm))
        
        toolbar.addAction("Back to list", self.ShowList)

        # Code ends here
        self.show()

    def DeleteRecipeIngredient(self):
        selected = self.RecipeIngredientList.selectedIndexes()
        for index in selected or []:
            self.RecipeIngredientModel.removeRow(index.row())
        self.RecipeIngredientModel.select()

    def AddRecipeIngredient(self):
        self.stack.setCurrentWidget(self.RecipeIngredientList)
        self.RecipeIngredientModel.insertRows(
            self.RecipeIngredientModel.rowCount(), 1)

    def ShowList(self):
        self.RecipeIngredientList.resizeColumnsToContents()
        self.RecipeIngredientList.resizeRowsToContents()
        self.stack.setCurrentWidget(self.RecipeIngredientList)

if __name__== '__main__':
    app = qtw.QApplication(sys.argv)
    mw = Mainwindow()
    sys.exit(app.exec())

问题根源 #

代码中RecipeIngredientForm类获取关联模型时，误用了关联表的字段索引而非当前表的外键字段索引：

• 获取Recipe关联模型时，错误使用Name（Recipe表字段）的索引，实际应该用RecipeId（RecipeIngredient表外键）的索引
• 获取Ingredient关联模型时，错误使用Description（Ingredient表字段）的索引，实际应该用IngredientId的索引
• 获取QuantityType关联模型时，错误使用Unit（QuantityType表字段）的索引，实际应该用QuantityTypeId的索引

这种错误导致无法正确加载关联模型，ComboBox无法显示下拉选项。

修复后的代码 #

修改RecipeIngredientForm中获取关联模型的部分：

class RecipeIngredientForm(qtw.QWidget):
    """Form to display/edit all info about an Ingredient"""

    def __init__(self, RecipeIngredientModel):
        super().__init__()
        self.setLayout(qtw.QFormLayout())

        # RecipeIngredient Fields
        self.Recipe = qtw.QComboBox()
        self.layout().addRow('Recipe: ', self.Recipe)
        self.Ingredient = qtw.QComboBox()
        self.layout().addRow('Ingredient: ', self.Ingredient)
        self.QuantityType = qtw.QComboBox()
        self.layout().addRow('QuantityType: ', self.QuantityType)
        self.Quantity = qtw.QLineEdit()
        self.layout().addRow('Quantity: ', self.Quantity)

        # Map the RecipeIngredient fields
        self.RecipeIngredientModel = RecipeIngredientModel
        self.mapper = qtw.QDataWidgetMapper(self)
        self.mapper.setModel(self.RecipeIngredientModel)
        self.mapper.setItemDelegate(
            qts.QSqlRelationalDelegate(self))
        
        self.mapper.addMapping(
            self.Recipe,
            self.RecipeIngredientModel.fieldIndex('RecipeId')
        )
        self.mapper.addMapping(
            self.QuantityType,
            self.RecipeIngredientModel.fieldIndex('QuantityTypeId')
        )
        self.mapper.addMapping(
            self.Ingredient,
            self.RecipeIngredientModel.fieldIndex('IngredientId') 
        )
        self.mapper.addMapping(
            self.Quantity,
            self.RecipeIngredientModel.fieldIndex('Quantity')
        )

        # set model for the Recipe table and setup the combo box
        # 修复：使用RecipeId的字段索引获取关联模型
        RecipeModel = RecipeIngredientModel.relationModel(
            self.RecipeIngredientModel.fieldIndex('RecipeId'))
        self.Recipe.setModel(RecipeModel)
        self.Recipe.setModelColumn(1)

        # set model for the Ingredient table and setup the ComboBox
        # 修复：使用IngredientId的字段索引获取关联模型
        IngredientModel = RecipeIngredientModel.relationModel(
            self.RecipeIngredientModel.fieldIndex('IngredientId'))
        self.Ingredient.setModel(IngredientModel)
        self.Ingredient.setModelColumn(1)
        
        # set model for the QuantityType tables and setup the ComboBox
        # 修复：使用QuantityTypeId的字段索引获取关联模型
        QuantityTypeModel = RecipeIngredientModel.relationModel(
            self.RecipeIngredientModel.fieldIndex('QuantityTypeId'))
        self.QuantityType.setModel(QuantityTypeModel)
        self.QuantityType.setModelColumn(1)


    def ShowRecipeIngredient(self, RecipeIngredientIndex):
        self.mapper.setCurrentIndex(RecipeIngredientIndex.row())

额外说明：如果使用QSqlRelationalDelegate配合QDataWidgetMapper，其实可以省略手动给ComboBox设置模型的步骤，mapper会自动根据关联关系加载数据并绑定到控件上。但如果需要自定义ComboBox显示的列，仍需保留正确的模型设置代码。

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