How to synch models on relational database where tables are linked by a foreign key



  • I have created two models for a simple relational database: a group model (groups A, B, C) and an item model, where items may be associated with multiple groups.

    The groupId from the group table is a foreign key in the items table.

    I am using the OnManualSubmit strategy.

    Running PyQt5.8.1, python3.5.2

    I can add a group, cancel the addition of the group, commit the new group to the database and delete an existing group from the database. I added a simple combo box, that is repopulated directly from the database after any group change, so I know the addition/deletion updates the groups table correctly.

    Similarly, for the group items.

    My problem is when I add a new group, then attempt to attach an item to that group, the new group does not appear in the drop-down list generated by the QSqlRelationalDelegate class associated with the items model view..

    I have tried a couple of approaches, flagged in the attached code, but without success. It's clear that I am not understanding something here, and would greatly appreciate some guidance.

    Three modules below:
    SimpleGroupModel
    SimpleItemModel
    simpleManageData - generates dummy database

    #simpleGroupModel.py
    #!/usr/bin/env python

    import sys

    from PyQt5.QtCore import (QVariant, Qt, pyqtSignal)
    from PyQt5.QtSql import (QSqlQuery, QSqlTableModel)
    from PyQt5.QtWidgets import (QDialog, QTableView,QMessageBox, QHBoxLayout,
    QVBoxLayout, QGroupBox, QPushButton, QItemDelegate)

    #database table parameters
    TABLE = "groups"
    TITLE = "Groups"
    ID = 0
    GROUP_NAME = 1

    class SimpleGroupModel(QDialog):

    groupsChanged = pyqtSignal()
    
    def __init__(self, parent=None):
        super(SimpleGroupModel, self).__init__(parent)
    
        self.setFixedWidth(250)
        self.setFixedHeight(200)
        self.rowsToDelete = []
    
        self.model = QSqlTableModel(self)
        self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.model.setTable(TABLE)
    
        self.model.setHeaderData(ID, Qt.Horizontal, "ID")
        self.model.setHeaderData(GROUP_NAME, Qt.Horizontal, "Group Name")
    
        self.view = QTableView()
        self.view.setModel(self.model)
        self.view.verticalHeader().setVisible(False)
        self.view.setColumnHidden(ID, True)
        self.view.horizontalHeader().setStretchLastSection(True)
    
        self.viewDelegate = QItemDelegate()
        self.view.setItemDelegate(self.viewDelegate)
    
        self.model.select()
        self.fetchData()
    
        self.__controls()
        self.__layouts()
        self.__signals()
        return
    
    
    def fetchData(self):
        self.model.select()
        while self.model.canFetchMore():
            self.model.fetchMore()
        return
    
    def addItem(self):
        row = self.model.rowCount()
        row = 0
        self.model.insertRow(row)
        index = self.model.index(row, GROUP_NAME)
        self.view.setCurrentIndex(index)
        self.view.edit(index)
        return
    
    def deleteItem(self):
        index = self.view.currentIndex()
        if not index.isValid():
            return QVariant()
        self.model.beginRemoveRows(index, index.row(), 1)
        self.model.removeRow(index.row())
        self.model.endRemoveRows()
        self.model.dataChanged.emit(index, index)
        self.rowsToDelete.append(index)
        return
    
    def cancelChanges(self):
        self.rowsToDelete = []
        self.model.revertAll()
        self.model.database().rollback()
        self.model.select()
        self.fetchData()
        return
    
    def commitChanges(self):
        self.model.database().transaction()
        for modelIndex in self.rowsToDelete:
            self.model.beginRemoveRows(modelIndex, modelIndex.row(), 1)
            self.model.removeRow(modelIndex.row())
            self.model.dataChanged.emit(modelIndex, modelIndex)
            self.model.endRemoveRows()
        self.rowsToDelete = []
        if self.model.submitAll():
            self.model.database().commit()
            self.model.select()
            self.groupsChanged.emit()
            return
        else:
            self.model.database().rollback()
            error = self.model.lastError().text()
            msg = "The database reported an error: %s" % error
            QMessageBox.warning(self, "Cached Table", msg)
            return
    
    def __controls(self):
        self.addBtn = QPushButton("Add Item")
        self.deleteBtn = QPushButton("Delete Item")
        self.commitBtn = QPushButton("Commit Changes")
        self.cancelBtn = QPushButton("Cancel Changes")
        return
    
    def __layouts(self):
        self.vButtonLayout = QVBoxLayout()
        self.vButtonLayout.addWidget(self.addBtn)
        self.vButtonLayout.addWidget(self.deleteBtn)
        self.vButtonLayout.addWidget(self.cancelBtn)
        self.vButtonLayout.addWidget(self.commitBtn)
        self.vButtonLayout.addStretch()
    
        self.mainLayout = QHBoxLayout()
        self.mainLayout.addWidget(self.view)
        self.mainLayout.addLayout(self.vButtonLayout)
    
        groupBoxLayout = QVBoxLayout()
        groupBoxLayout.addLayout(self.mainLayout)
        groupBoxLayout.addStretch()
    
        groupBox = QGroupBox("Manage Groups")
        groupBox.setStyleSheet(""".QGroupBox {font-size: 14px;
                                              font-weight:bold}""")
        groupBox.setLayout(groupBoxLayout)
    
        self.layout = QHBoxLayout()
        self.layout.addWidget(groupBox)
        self.layout.addStretch()
    
        self.setLayout(self.layout)
        return
    
    def __signals(self):
        self.addBtn.clicked.connect(self.addItem)
        self.deleteBtn.clicked.connect(self.deleteItem)
        self.cancelBtn.clicked.connect(self.cancelChanges)
        self.commitBtn.clicked.connect(self.commitChanges)
        return
    

    def getGroups():
    groups = []
    query = QSqlQuery()
    query.prepare("SELECT id, group_name FROM groups")
    query.exec_()
    while query.next():
    groups.append([query.value(0), query.value(1)])
    return groups

    #simpleItemModel.py
    #!/usr/bin/env python

    from PyQt5.QtCore import (QVariant, Qt, pyqtSignal, QModelIndex)
    from PyQt5.QtSql import (QSqlRelation, QSqlRelationalDelegate, QSqlTableModel,
    QSqlRelationalTableModel)
    from PyQt5.QtWidgets import (QDialog, QTableView, QHBoxLayout, QVBoxLayout,
    QGroupBox, QPushButton, QMessageBox)

    from groupSelector import *

    #database table parameters
    TABLE = "items"
    TITLE = "Items"
    ID = 0
    ITEM_NAME = 1
    GROUP_ID = 2

    class SimpleItemModel(QDialog):

    Define pyqt signals

    itemsChanged = pyqtSignal()
    
    def __init__(self, parent=None):
        super(SimpleItemModel, self).__init__()
    
        self.setFixedWidth(375)
        self.setFixedHeight(500)
    
        self.groupSelector = GroupSelector()
    
        self.rowsToDelete = []
    
        self.model = QSqlRelationalTableModel(self)
        self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.model.setTable(TABLE)
    
        self.model.setRelation(GROUP_ID, QSqlRelation("groups",
                                                      "id",
                                                      "group_name"))
    
        self.model.setHeaderData(ID, Qt.Horizontal, "ID")
        self.model.setHeaderData(ITEM_NAME, Qt.Horizontal, "Item Name")
        self.model.setHeaderData(GROUP_ID, Qt.Horizontal, "Group Name")
    
        self.fetchData()
    
        self.view = QTableView()
        self.view.setFixedHeight(380)
        self.view.setModel(self.model)
        self.view.verticalHeader().setVisible(False)
        self.view.setColumnHidden(ID, True)
        self.view.horizontalHeader().setStretchLastSection(True)
    
        self.viewDelegate = QSqlRelationalDelegate()
        self.view.setItemDelegate(self.viewDelegate)
    
        self.__controls()
        self.__layouts()
        self.__signals()
    
        return
    
    def fetchData(self):
        self.model.select()
        while self.model.canFetchMore():
            self.model.fetchMore()
        self.model.dataChanged.emit(QModelIndex(), QModelIndex())
        return
    
    def addItem(self):
        row = self.model.rowCount()
        row = 0
        self.model.insertRow(row)
        index = self.model.index(row, ITEM_NAME)
        self.view.setCurrentIndex(index)
        self.view.edit(index)
        return
    
    def deleteItem(self):
        index = self.view.currentIndex()
        if not index.isValid():
            return QVariant()
        self.model.beginRemoveRows(index, index.row(), 1)
        self.model.removeRow(index.row())
        self.model.endRemoveRows()
        self.model.dataChanged.emit(index, index)
        self.rowsToDelete.append(index)
        return
    
    def cancelChanges(self):
        self.rowsToDelete = []
        self.model.revertAll()
        self.model.database().rollback()
        self.model.select()
        self.fetchData()
        return
    
    def commitChanges(self):
        self.model.database().transaction()
        for modelIndex in self.rowsToDelete:
            self.model.beginRemoveRows(modelIndex, modelIndex.row(), 1)
            self.model.removeRow(modelIndex.row())
            self.model.dataChanged.emit(modelIndex, modelIndex)
            self.model.endRemoveRows()
        self.rowsToDelete = []
        if self.model.submitAll():
            self.model.database().commit()
            self.model.select()
            self.itemsChanged.emit()
            return
        else:
            self.model.database().rollback()
            error = self.model.lastError().text()
            msg = "The database reported an error: %s" % error
            QMessageBox.warning(self, "Cached Table", msg)
            return
    
    def updateGroupFilter(self, filterIndex, filterName):
        print("filterIndex, filterName: ", filterIndex, filterName)
        return
    
    def __controls(self):
        self.addBtn = QPushButton("Add Item")
        self.deleteBtn = QPushButton("Delete Item")
        self.commitBtn = QPushButton("Commit Changes")
        self.cancelBtn = QPushButton("Cancel Changes")
        return
    
    def __layouts(self):
        self.vButtonLayout = QVBoxLayout()
        self.vButtonLayout.addWidget(self.addBtn)
        self.vButtonLayout.addWidget(self.deleteBtn)
        self.vButtonLayout.addWidget(self.cancelBtn)
        self.vButtonLayout.addWidget(self.commitBtn)
        self.vButtonLayout.addStretch()
    
        self.viewLayout = QHBoxLayout()
        self.viewLayout.addWidget(self.view)
        self.viewLayout.addStretch()
    
        self.mainLayout = QHBoxLayout()
        self.mainLayout.addLayout(self.viewLayout)
        self.mainLayout.addLayout(self.vButtonLayout)
    
        groupBoxLayout = QVBoxLayout()
        groupBoxLayout.addWidget(self.groupSelector)
        groupBoxLayout.addLayout(self.mainLayout)
        groupBoxLayout.addStretch()
    
        groupBox = QGroupBox("Manage Items")
        groupBox.setStyleSheet(""".QGroupBox {font-size: 14px;
                                              font-weight:bold}""")
        groupBox.setLayout(groupBoxLayout)
    
        self.layout = QHBoxLayout()
        self.layout.addWidget(groupBox)
        self.layout.addStretch()
        self.setLayout(self.layout)
        return
    
    def __signals(self):
        self.addBtn.clicked.connect(self.addItem)
        self.deleteBtn.clicked.connect(self.deleteItem)
        self.cancelBtn.clicked.connect(self.cancelChanges)
        self.commitBtn.clicked.connect(self.commitChanges)
        return
    

    #simpleManageData.py
    #!/usr/bin/env python

    import sys
    from PyQt5.QtGui import (QFont)
    from PyQt5.QtCore import (pyqtSignal, QFile)
    from PyQt5.QtSql import (QSqlDatabase)
    from PyQt5.QtWidgets import (QApplication, QDialog, QVBoxLayout)

    from simpleItemModel import *
    from simpleGroupModel import *

    class ManageData(QDialog):

    Define pyqt signals

    dataChanged = pyqtSignal()
    
    def __init__(self, parent=None):
        super(ManageData, self).__init__()
    
        self.groupModel = SimpleGroupModel()
        self.itemModel = SimpleItemModel()
    
        self.__layouts()
        self.__signals()
        return
    
    def __layouts(self):
        self.layout = QVBoxLayout()
        self.layout.addWidget(self.groupModel)
        self.layout.addWidget(self.itemModel)
    
        self.setLayout(self.layout)
        return
    
    def __signals(self):
        self.groupModel.groupsChanged.connect(self.updateModels)
        return
    
    def updateModels(self):
        self.itemModel.groupSelector.populateGroupSelector()
    
        attempt = 0
        if attempt == 0:
            # repopulate data in item model and emit dataChanged signal - no
            # luck
            self.itemModel.fetchData()
        elif attempt == 1:
            # remove the item model from the layout, create a new item mode and
            # plug it back into the layout - visually disruptive and doesn't
            # work
            self.itemModel.viewLayout.removeWidget(self.itemModel.view)
            self.itemModel = SimpleItemModel()
            self.itemModel.viewLayout.addWidget(self.itemModel.view)
    
    
        return
    

    def createDatabase():
    filename = "test1.sqlite"
    create = not QFile.exists(filename)

    db = QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName(filename)
    db.open()
    
    print("Dropping tables...")
    query = QSqlQuery()
    query.exec_("DROP TABLE groups")
    query.exec_("DROP TABLE items")
    
    QApplication.processEvents()
    
    print("Creating tables...")
    
    query.exec_("""CREATE TABLE items (
                id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                item_name VARCHAR(40) NOT NULL,
                group_id INTEGER NOT NULL,
                FOREIGN KEY (group_id) REFERENCES groups)""")
    
    query.exec_("""CREATE TABLE groups (
                         id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                         group_name VARCHAR(40) NOT NULL)""")
    
    QApplication.processEvents()
    return
    

    def populateDatabase():
    groups = ["A", "B", "C"]
    items = [["one", 1], ["two", 1],
    ["one", 2], ["three", 2],
    ["two", 3], ["four", 3]]

    groupQuery = QSqlQuery()
    groupQuery.prepare("INSERT INTO groups (group_name) "
                       "VALUES (:groupName)")
    for groupName in groups:
        groupQuery.bindValue(":groupName", groupName)
        groupQuery.exec_()
    
    itemQuery = QSqlQuery()
    itemQuery.prepare("INSERT INTO items (item_name, group_id) "
                      "VALUES (:item_name, :group_id)")
    for item in items:
        itemName = item[0]
        groupId = item[1]
        itemQuery.bindValue(":item_name", itemName)
        itemQuery.bindValue(":group_id", groupId)
        itemQuery.exec_()
    return
    

    def main():
    import os
    app = QApplication(sys.argv)
    app.setStyle("fusion")

    createDatabase()
    populateDatabase()
    
    myFont = QFont()
    myFont.setPointSize(9)
    app.setFont(myFont)
    
    form = ManageData()
    form.show()
    
    app.exec_()
    

    if name == 'main':
    sys.exit(main())