PyQt6, related QComboBoxes in a QSqlRelationalDelegate

Question

Suppose that in a QSqlRelationalDelegate we have two relation columns, country and city. The city table has a country_id field. By overriding the setEditorData method of QSqlRelationalDelegate, I'm able to filter the contents of the city QComboBox and it works!

Is it possible when the user selects a country, the city QComboBox to be auto-updated with the first city of the selected country?

    def setEditorData(self, editor, index):
        model = index.model()
        childModel = (model.relationModel(index.column()) if model is not None else None)
        if index.column() == 3 and childModel is not None: # City
            country_name = model.index(index.row(),2).data()
            if country_name is None or country_name == '':
                childModel.setFilter('')
            else:
                q = QSqlQuery("SELECT id FROM country where name='" + country_name + "' limit 1");
                country_id = None
                while q.next():
                    country_id = q.value(0)
                if country_id is not None:
                    childModel.setFilter('country_id=' + str(country_id))
        
        super(MyQSqlRelationalDelegate, self).setEditorData(editor, index)

Answer 1

Here's the code that finally worked!

import sys

from PyQt6.QtCore import Qt, QFile, QModelIndex
from PyQt6.QtWidgets import QApplication, QMainWindow, QMessageBox, QTableView, QVBoxLayout, QWidget, QAbstractItemView
from PyQt6.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel, QSqlRelation, QSqlRelationalDelegate, QSqlRelationalTableModel, QSqlDriver, QSqlQueryModel, QSqlError

COLUMN_COUNTRY = 3
COLUMN_CITY = 2

class MyQSqlRelationalDelegate(QSqlRelationalDelegate):
    def __init__(self, parent=None):
        self.tableView = parent
        super(MyQSqlRelationalDelegate, self).__init__(parent)
    
    def createEditor(self, parent, option, index):
        return super(MyQSqlRelationalDelegate, self).createEditor(parent, option, index)
    
    def setEditorData(self, editor, index):
        model = index.model()
        childModel = (model.relationModel(index.column()) if model is not None else None)
        if index.column() == COLUMN_CITY and childModel is not None: # City
            country_name = model.index(index.row(),COLUMN_COUNTRY).data() # Get the country name
            if country_name is None or country_name == '':
                childModel.setFilter('')
            else:
                q = QSqlQuery("SELECT id FROM country WHERE name='" + country_name + "' LIMIT 1");
                country_id = None
                while q.next():
                    country_id = q.value(0)
                if country_id is not None:
                    childModel.setFilter('country_id=' + str(country_id))
        
        super(MyQSqlRelationalDelegate, self).setEditorData(editor, index)
    
    def setModelData(self, editor, model, index):
        
        super(MyQSqlRelationalDelegate, self).setModelData(editor, model, index)
        
        if index.column() == COLUMN_COUNTRY: # Country
            country_name = index.data()
            if country_name is None or country_name == '':
                model.setData(model.index(index.row(),COLUMN_CITY), None)
            else:
                q = QSqlQuery("SELECT c2.id FROM country c1, city c2 WHERE c1.id = c2.country_id AND c1.name='" + country_name + "' LIMIT 1");
                city_id = None
                while q.next():
                    city_id = q.value(0)
                if city_id is not None:
                    model.setData(model.index(index.row(),COLUMN_CITY), city_id)


class FEmployees(QWidget):
    def __init__(self):
        super(FEmployees, self).__init__()
        
        self.initializeTableModel()
        self.tableView = QTableView()
        self.tableView.setModel(self.tableModel)
        self.tableView.setItemDelegate(MyQSqlRelationalDelegate(self.tableView))

        self.tableView.resizeColumnsToContents()

        self.tableView.setSelectionBehavior(QTableView.SelectionBehavior.SelectRows) # User can select whole rows
        self.tableView.setSelectionMode(QTableView.SelectionMode.SingleSelection) # User can select a single row
        self.tableView.setEditTriggers(QAbstractItemView.EditTrigger.AllEditTriggers)
        
        mainLayout = QVBoxLayout()
        mainLayout.addWidget(self.tableView)
        self.setLayout(mainLayout)
        
        # Set bold font for headers
        self.setBoldHeaders() # Swap country & city columns in view

    def initializeTableModel(self):
        self.tableModel = QSqlRelationalTableModel()
        """
        enum QSqlRelationalTableModel::JoinMode
        QSqlRelationalTableModel::InnerJoin 0   - Inner join mode, return rows when there is at least one match in both tables.
        QSqlRelationalTableModel::LeftJoin  1   - Left join mode, returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
        """
        self.tableModel.setJoinMode(QSqlRelationalTableModel.JoinMode.LeftJoin)
        self.tableModel.setTable("employee")
        self.tableModel.setEditStrategy(QSqlTableModel.EditStrategy.OnRowChange)
        
        self.tableModel.setRelation(COLUMN_CITY, QSqlRelation("city_null", "id", "name"))
        self.tableModel.setRelation(COLUMN_COUNTRY, QSqlRelation("country_null", "id", "name"))

        self.tableModel.select()

        # Connect signals for catching errors
        self.tableModel.dataChanged.connect(self.handle_data_changed)

    def handle_data_changed(self, topLeft, bottomRight, roles):
        # Attempt to submit the changes
        if not self.tableModel.submitAll():
            # If there is an error, fetch the error details
            last_error = self.tableModel.lastError()
            if last_error.isValid():
                self.show_error_message(last_error)

            # Revert changes to avoid leaving the model in an inconsistent state
            self.tableModel.revertAll()

    def show_error_message(self, error: QSqlError):
        error_text = error.text()
        # Check for constraint violation
        if "constraint" in error_text.lower():
            QMessageBox.critical(self, "Constraint Violation", "A database constraint was violated: " + error_text)
        else:
            QMessageBox.critical(self, "Database Error", "An error occurred: " + error_text)
        
    def setBoldHeaders(self):
        header = self.tableView.horizontalHeader()
        font = header.font()
        font.setBold(True)
        header.setFont(font)
        header.moveSection(COLUMN_CITY, COLUMN_COUNTRY) # Swap country & city columns in view


class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setGeometry(350, 150, 1200, 800)
        employee = FEmployees()
        self.setCentralWidget(employee)


def createConnection():
    dbname = "..\BaseAppEnv.db"
    if not QFile.exists(dbname):
        #file does not exist
        QMessageBox.critical(None, "Error!", "Database file does not exist!", QMessageBox.StandardButton.Ok)
    else:
        db = QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName(dbname)
        if not db.open():
            #Database open error
            QMessageBox.critical(None, "Error!", "Database open error!", QMessageBox.StandardButton.Ok)
        else:
            q = QSqlQuery()
            if not q.exec("SELECT name, sql FROM sqlite_master WHERE type='table' "):
                #corrupt or invalid sqlite file
                QMessageBox.critical(None, "Error!", "Corrupt or invalid sqlite file!", QMessageBox.StandardButton.Ok)
            else:
                return(True)
    return(False)


if __name__ == '__main__':
    app = QApplication(sys.argv)
if not createConnection():
    QMessageBox.critical(None, "Database Error", self.db.lastError().text())
    sys.exit(1)
mainWindow = MainWindow()
mainWindow.show()
app.exec()

And the sqlite script:

--
-- File generated with SQLiteStudio v3.4.4 on Κυρ Ιουλ 7 13:58:13 2024
--
-- Text encoding used: UTF-8
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- Table: city
CREATE TABLE IF NOT EXISTS city (id INTEGER PRIMARY KEY, name TEXT (50), country_id INTEGER REFERENCES country (id));
INSERT INTO city (id, name, country_id) VALUES (12, 'NEW YORK', 1);
INSERT INTO city (id, name, country_id) VALUES (13, 'ATHENS', 60);
INSERT INTO city (id, name, country_id) VALUES (14, 'STOCKHOLM', 47);
INSERT INTO city (id, name, country_id) VALUES (15, 'NANTES', 12);
INSERT INTO city (id, name, country_id) VALUES (16, 'PATRA', 60);
INSERT INTO city (id, name, country_id) VALUES (17, 'SOFIA', 22);
INSERT INTO city (id, name, country_id) VALUES (18, 'MALMO', 47);
INSERT INTO city (id, name, country_id) VALUES (19, 'PARIS', 12);
INSERT INTO city (id, name, country_id) VALUES (20, 'WASHINGTON', 1);
INSERT INTO city (id, name, country_id) VALUES (21, 'BERLIN', 49);
INSERT INTO city (id, name, country_id) VALUES (22, 'HAMBURG', 49);
INSERT INTO city (id, name, country_id) VALUES (23, 'HERAKLION', 60);
INSERT INTO city (id, name, country_id) VALUES (24, 'VARNA', 22);
INSERT INTO city (id, name, country_id) VALUES (25, 'THESSALONIKI', 60);

-- Table: country
CREATE TABLE IF NOT EXISTS country (id INTEGER PRIMARY KEY, name TEXT (50));
INSERT INTO country (id, name) VALUES (1, 'UNITED STATES of AMERICA');
INSERT INTO country (id, name) VALUES (12, 'FRANCE');
INSERT INTO country (id, name) VALUES (22, 'BULGARIA');
INSERT INTO country (id, name) VALUES (47, 'SWEDEN');
INSERT INTO country (id, name) VALUES (49, 'GERMANY');
INSERT INTO country (id, name) VALUES (60, 'GREECE');

-- Table: employee
CREATE TABLE IF NOT EXISTS employee (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, city_id INTEGER REFERENCES city (id), country_id INTEGER REFERENCES country (id));
INSERT INTO employee (id, name, city_id, country_id) VALUES (1, 'Espen', NULL, NULL);
INSERT INTO employee (id, name, city_id, country_id) VALUES (2, 'Harald', NULL, NULL);
INSERT INTO employee (id, name, city_id, country_id) VALUES (3, 'Tomson', NULL, NULL);
INSERT INTO employee (id, name, city_id, country_id) VALUES (4, 'Holland', NULL, NULL);
INSERT INTO employee (id, name, city_id, country_id) VALUES (5, 'Green', NULL, NULL);

-- Index: employee_city_index
CREATE INDEX IF NOT EXISTS employee_city_index ON employee (city_id);

-- Index: employee_country_index
CREATE INDEX IF NOT EXISTS employee_country_index ON employee (country_id);

-- View: city_null
CREATE VIEW IF NOT EXISTS city_null AS select null id, null name, -1 country_id
union
select id, name, country_id from city;

-- View: country_null
CREATE VIEW IF NOT EXISTS country_null AS select null id, null name
union
select id, name from country;

COMMIT TRANSACTION;
PRAGMA foreign_keys = on;