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

[grm65]

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())