How to persist combobox selection in database? QDataWidgetMapper QSqlRelationalTableModel QComboBox

[BamboozledBaboon]

I'm brand new to qt and pyqt so please dumb it way down for me.

I've successfully converted the SQL Widget Mapper Example here to pyqt5.
I'm attempting to modify it to save the sqlite db to disk but have run into issues. The fields mapped to line edits save while the foreign key field mapped to the combobox does not. How do I make combobox updated selections save to the db?

Here is my code:

#!/usr/bin/env python3


# Ported from C++ example posted here:
# https://doc.qt.io/archives/qt-5.7/qtsql-sqlwidgetmapper-example.html

import sys
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel, QSqlRelationalTableModel, QSqlRelation
from PyQt5.QtWidgets import QDataWidgetMapper

# import the UI generated by pyuic5
from window_ui import Ui_Dialog


class MyWindow(QtWidgets.QDialog):
    def __init__(self):
        super(MyWindow, self).__init__()
        self.ui = Ui_Dialog()
        self.ui.setupUi(self)

        # setup the database connection
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName('contacts.db')
        self.db.open()

        # setup the model based off of an sqlite table
        self.model = QSqlRelationalTableModel()
        self.model.setTable('person')
        self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)

        # setup the reference to the related lookup table
        typeIndex = self.model.fieldIndex('typeindex')
        self.model.setRelation(typeIndex, QSqlRelation('addressType', 'id', 'description'))

        # query the db for the table -and- lookup table records
        self.model.select()

        # setup the combobox to display the lookup table data
        self.relModel = self.model.relationModel(typeIndex)
        self.ui.typeComboBox.setModel(self.relModel)
        self.ui.typeComboBox.setModelColumn(self.relModel.fieldIndex('description'))

        # map the model's fields to the window's controls. you can use the mapper to navigate records.
        self.mapper = QDataWidgetMapper()
        self.mapper.setModel(self.model)
        self.mapper.addMapping(self.ui.nameEdit, self.model.fieldIndex('name'))
        self.mapper.addMapping(self.ui.addressEdit, self.model.fieldIndex('address'))
        self.mapper.addMapping(self.ui.typeComboBox, typeIndex)

        # setup the events
        self.ui.previousButton.clicked.connect(self.mapper.toPrevious)
        self.ui.nextButton.clicked.connect(self.mapper.toNext)
        self.ui.saveButton.clicked.connect(self.model.submitAll)
        self.mapper.currentIndexChanged.connect(self.updateButtons)

        # show the first record
        self.mapper.toFirst()


    def updateButtons(self, row):
        self.ui.previousButton.setEnabled(row > 0)
        self.ui.nextButton.setEnabled(row < self.model.rowCount() - 1)


if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    win = MyWindow()
    win.show()
    sys.exit(app.exec())

For sake of completeness here is my UI file:

# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file 'window.ui'
#
# Created by: PyQt5 UI code generator 5.15.6
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again.  Do not edit this file unless you know what you are doing.


from PyQt5 import QtCore, QtGui, QtWidgets


class Ui_Dialog(object):
    def setupUi(self, Dialog):
        Dialog.setObjectName("Dialog")
        Dialog.resize(440, 182)
        self.horizontalLayout = QtWidgets.QHBoxLayout(Dialog)
        self.horizontalLayout.setObjectName("horizontalLayout")
        self.gridLayout = QtWidgets.QGridLayout()
        self.gridLayout.setObjectName("gridLayout")
        self.nameLabel = QtWidgets.QLabel(Dialog)
        self.nameLabel.setObjectName("nameLabel")
        self.gridLayout.addWidget(self.nameLabel, 0, 0, 1, 1)
        self.nameEdit = QtWidgets.QLineEdit(Dialog)
        self.nameEdit.setObjectName("nameEdit")
        self.gridLayout.addWidget(self.nameEdit, 0, 1, 1, 1)
        self.addressEdit = QtWidgets.QTextEdit(Dialog)
        self.addressEdit.setObjectName("addressEdit")
        self.gridLayout.addWidget(self.addressEdit, 1, 1, 1, 1)
        self.typeLabel = QtWidgets.QLabel(Dialog)
        self.typeLabel.setObjectName("typeLabel")
        self.gridLayout.addWidget(self.typeLabel, 2, 0, 1, 1)
        self.addressLabel = QtWidgets.QLabel(Dialog)
        self.addressLabel.setAlignment(QtCore.Qt.AlignLeading|QtCore.Qt.AlignLeft|QtCore.Qt.AlignTop)
        self.addressLabel.setObjectName("addressLabel")
        self.gridLayout.addWidget(self.addressLabel, 1, 0, 1, 1)
        self.typeComboBox = QtWidgets.QComboBox(Dialog)
        self.typeComboBox.setObjectName("typeComboBox")
        self.gridLayout.addWidget(self.typeComboBox, 2, 1, 1, 1)
        self.horizontalLayout.addLayout(self.gridLayout)
        self.verticalLayout = QtWidgets.QVBoxLayout()
        self.verticalLayout.setObjectName("verticalLayout")
        self.previousButton = QtWidgets.QPushButton(Dialog)
        self.previousButton.setObjectName("previousButton")
        self.verticalLayout.addWidget(self.previousButton)
        self.nextButton = QtWidgets.QPushButton(Dialog)
        self.nextButton.setObjectName("nextButton")
        self.verticalLayout.addWidget(self.nextButton)
        spacerItem = QtWidgets.QSpacerItem(20, 40, QtWidgets.QSizePolicy.Minimum, QtWidgets.QSizePolicy.Expanding)
        self.verticalLayout.addItem(spacerItem)
        self.newButton = QtWidgets.QPushButton(Dialog)
        self.newButton.setObjectName("newButton")
        self.verticalLayout.addWidget(self.newButton)
        self.saveButton = QtWidgets.QPushButton(Dialog)
        self.saveButton.setObjectName("saveButton")
        self.verticalLayout.addWidget(self.saveButton)
        self.horizontalLayout.addLayout(self.verticalLayout)
        self.nameLabel.setBuddy(self.nameEdit)
        self.typeLabel.setBuddy(self.typeComboBox)
        self.addressLabel.setBuddy(self.addressEdit)

        self.retranslateUi(Dialog)
        QtCore.QMetaObject.connectSlotsByName(Dialog)
        Dialog.setTabOrder(self.nameEdit, self.addressEdit)
        Dialog.setTabOrder(self.addressEdit, self.typeComboBox)
        Dialog.setTabOrder(self.typeComboBox, self.previousButton)
        Dialog.setTabOrder(self.previousButton, self.nextButton)
        Dialog.setTabOrder(self.nextButton, self.newButton)
        Dialog.setTabOrder(self.newButton, self.saveButton)

    def retranslateUi(self, Dialog):
        _translate = QtCore.QCoreApplication.translate
        Dialog.setWindowTitle(_translate("Dialog", "SQL Widget Mapper"))
        self.nameLabel.setText(_translate("Dialog", "Na&me:"))
        self.typeLabel.setText(_translate("Dialog", "&Type:"))
        self.addressLabel.setText(_translate("Dialog", "&Address:"))
        self.previousButton.setText(_translate("Dialog", "&Previous"))
        self.nextButton.setText(_translate("Dialog", "&Next"))
        self.newButton.setText(_translate("Dialog", "N&ew"))
        self.saveButton.setText(_translate("Dialog", "&Save"))


if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    Dialog = QtWidgets.QDialog()
    ui = Ui_Dialog()
    ui.setupUi(Dialog)
    Dialog.show()
    sys.exit(app.exec_())

[JonB]

@BamboozledBaboon
Did you try the Relational Table Model Example, look at its code, which should show combobox changes being reflected to the model?

I don't know how that relates to you using a QDataWidgetMapper. There claims to be a working example from https://forum.qt.io/topic/131734/qsqlrelationaltablemodel-with-qdatawidgetmapper-does-not-save-to-sqlite3-database and https://forum.qt.io/topic/131893/subclass-qitemdelegate-not-working. More complex than you might like, but worth examining the principles. Otherwise Googling qsqlrelationaltablemodel qdatawidgetmapper may give you some ideas.

[BamboozledBaboon]

I just managed to get it working by referring to the anwer here: https://stackoverflow.com/questions/68638923/qcombobox-populated-from-a-relation-model-and-connected-to-a-qdatawidgetmapperbe

Here is my updated and working code but this seems rather complicated for what I would have guessed quite an easy task. Hopefully someone can chime in and tell me if there is a better way.

#!/usr/bin/env python3


# Ported from C++ example posted here:
# https://doc.qt.io/archives/qt-5.7/qtsql-sqlwidgetmapper-example.html

import sys
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel, QSqlRelationalTableModel, QSqlRelation, QSqlRelationalDelegate
from PyQt5.QtWidgets import QDataWidgetMapper

# import the UI generated by pyuic5
from window_ui import Ui_Dialog


# this class is a workaround for comboboxes not updating correctly with QDataWidgetMapper... i guess
# https://stackoverflow.com/questions/68638923/qcombobox-populated-from-a-relation-model-and-connected-to-a-qdatawidgetmapperbe
class Delegate(QSqlRelationalDelegate):
    def setModelData(self, editor, model, index):
        if isinstance(editor, QtWidgets.QComboBox):
            value = editor.currentText()
            if not value:
                return
            childModel = model.relationModel(index.column())
            for column in range(2):
                match = childModel.match(childModel.index(0, column), 
                    QtCore.Qt.DisplayRole, value, QtCore.Qt.MatchStartsWith)
                if match:
                    match = match[0]
                    displayValue = match.sibling(match.row(), 1).data()
                    editValue = match.sibling(match.row(), 0).data()
                    model.setData(index, displayValue, QtCore.Qt.DisplayRole)
                    model.setData(index, editValue, QtCore.Qt.EditRole)
                    return
        super(Delegate, self).setModelData(editor, model, index)


class MyWindow(QtWidgets.QDialog):
    def __init__(self):
        super(MyWindow, self).__init__()
        self.ui = Ui_Dialog()
        self.ui.setupUi(self)

        # setup the database connection
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName('contacts.db')
        self.db.open()

        # setup the model based off of an sqlite table
        self.model = QSqlRelationalTableModel()
        self.model.setTable('person')
        self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)

        # setup the reference to the related lookup table
        typeIndex = self.model.fieldIndex('typeindex')
        self.model.setRelation(typeIndex, QSqlRelation('addressType', 'id', 'description'))

        # query the db for the table -and- lookup table records
        self.model.select()

        # map the model's fields to the window's controls. you can use the mapper to navigate records.
        self.mapper = QDataWidgetMapper()
        self.mapper.setItemDelegate(Delegate())
        self.mapper.setModel(self.model) #<---- needed to get combobox to work as expected with mapper & model
        self.mapper.addMapping(self.ui.nameEdit, self.model.fieldIndex('name'))
        self.mapper.addMapping(self.ui.addressEdit, self.model.fieldIndex('address'))
        self.mapper.addMapping(self.ui.typeComboBox, typeIndex)

        # setup the combobox to display the lookup table data
        self.relModel = self.model.relationModel(typeIndex)
        self.ui.typeComboBox.setModel(self.relModel)
        self.ui.typeComboBox.setModelColumn(self.relModel.fieldIndex('description'))

        # setup the events
        self.ui.previousButton.clicked.connect(self.mapper.toPrevious)
        self.ui.nextButton.clicked.connect(self.mapper.toNext)
        self.ui.saveButton.clicked.connect(self.submit)
        self.mapper.currentIndexChanged.connect(self.updateButtons)

        # show the first record
        self.mapper.toFirst()


    def updateButtons(self, row):
        self.ui.previousButton.setEnabled(row > 0)
        self.ui.nextButton.setEnabled(row < self.model.rowCount() - 1)


    def submit(self):
        current = self.mapper.currentIndex()
        self.mapper.submit()
        self.model.submitAll()
        self.mapper.setCurrentIndex(current)


if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    win = MyWindow()
    win.show()
    sys.exit(app.exec())