How to insert data into a QSqlRelationalTableModel ?

[judethedude]

Hey everyone,

For the life of me, I cannot figure out how to insert data into a QSqlRelationalTableModel.
Model:

class ApptTableModel(QSqlRelationalTableModel):
    def __init__(self):
        super().__init__()
        self.setTable('appointments')
        self.setRelation(1, QSqlRelation("patient_profile", "id", "id, name_fname, name_lname"))
        self.setEditStrategy(QSqlTableModel.OnRowChange)
        self.select()

    def insert_into_model(self, update_row: int, data):
            """Set update_row to -1 if inserting new data, else set to row"""
            rec = self.record()
            for col in range(6):
                rec.setValue(col, data[col])
                print(rec.value(col))
            self.insertRecord(self.rowCount(), rec)
            self.select()

View:

class ApptView(QWidget):
    def __init__(self, parent, appt_model, patient_model, patient_proxy_model):
        super().__init__(parent)

        self.appt_ui = Ui_appt_form()
        self.appt_ui.setupUi(self)
        self.appt_ui.appt_tableview.setModel(self._appt_model)
        # ^model already intialized and passed in from parent QWidget
        self.appt_ui.appt_tableview.setItemDelegate(QSqlRelationalDelegate(self.appt_ui.appt_tableview))
        # ^this line doesn't seem to do anything but was recommended from StackOverflow?
    
    def add_appointment(self, update: bool):
            if len(self.appt_ui.patient_search_tableView.selectionModel().selectedRows()):
                appt_data = [
                    self._selected_patient_id,
                    self.appt_ui.calendarWidget.selectedDate().toString(Qt.ISODate),
                    self.appt_ui.appt_time_spin.time().toString(Qt.ISODate),
                    self.appt_ui.appt_duration_spin.value(),
                    self.appt_ui.appt_reason.text(),
                    self.appt_ui.appt_notes.toPlainText()
                ]
                print(appt_data)
                if not update:
                    self._appt_model.insert_into_model(-1, appt_data)

I've tried preparing a record and inserting it, tried calling setData. Both of which work fine without
self.setRelation(1, QSqlRelation("patient_profile", "id", "id, name_fname, name_lname"))

I should mention, the tableview shows the foreign columns just fine, it's just the inserting (or updating) of data that I can't get to work.

Any help at all would be greatly appreciated.

EDIT: Essentially, this (unanswered) StackOverflow question: here, his example is simpler

[JonB]

@judethedude
To be clear, both you and the SO topic you reference are trying to use multiple columns from the foreign key table. I believe that is not "supported" by the Qt code, it is "fortunate" that it works when selecting rows but not if attempting to update the table.

I presume if you reduce to:

self.setRelation(1, QSqlRelation("patient_profile", "id", "id"))

(or any one of the 3 foreign columns) then your updates work OK?

[judethedude]

@JonB Dang. Yes, reducing to a one-to-one works, but I was hoping to reduce redundant info in my table. Doesn't really matter though, I'll pursue it the way you suggested!

[JonB]

@judethedude
Well, to be clear I did not really suggest a "way" forward. You can have multiple QRelations on a QSqlRelationalTableModel but only if they reference different columns in the referring table, you cannot have more than one on the same column. So you cannot do yours that way. The "workaround" of specifying a comma-separated list ("id, name_fname, name_lname") is, I believe, not documented or supported. I suspect people came across it "by coincidence". It may be that it "works" because you can just paste that string into the middle of a SELECT statement, but when trying to generate INSERT or UPDATE statements it is not good enough.

Which leaves you with, I believe: if you use a multi-column QSqlRelation you can read but not update the table, and I don't have something which works for updating.

[judethedude]

@JonB Yeah I understand. I just mean I will restructure my appointment table to have something like "patient_id", "patient_fname_id" etc...

Thanks for your responses.

[JonB]

@judethedude said in How to insert data into a QSqlRelationalTableModel ?:

I will restructure my appointment table to have something like "patient_id", "patient_fname_id" etc...

Yeah, it is ugly to have to do that. You ought not need to change your database tables to keep client software happy. But I see your problem!