[FIXED] QSqlTableModel.insertRecord(row, record) not inserting at specified row

Issue

Using the answer provided here, I was able to insert records into my QSqlTableModel. While this appends records to the end (row=-1) just fine, changing the row does not change where the record is inserted. How do I cause a record to be inserted at the specified row?

import sys
from PySide6.QtWidgets import (QApplication, QMainWindow, QTableView)
from PySide6.QtSql import QSqlDatabase, QSqlTableModel, QSqlQuery


class SQLTableModel(QSqlTableModel):
    def __init__(self, parent=None):
        super().__init__(parent)
        query = QSqlQuery()
        query.exec(
            "CREATE TABLE table1"
            "(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, "
            "type TEXT)"
        )


class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()

        self.db = QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName("SQLTableDatabase.sqlite")
        self.db.open()

        self.table_view = QTableView()
        self.model = SQLTableModel()
        self.table_view.setModel(self.model)
        self.model.setTable("table1")

        for i in range(4):
            record = self.model.record()
            record.setValue("type", str(i))
            self.model.insertRecord(-1, record)

        record = self.model.record()
        record.setValue("type", "TEST")
        self.model.insertRecord(2, record)  # Why is this record not inserted at row 2?
        self.model.select()

        self.setCentralWidget(self.table_view)


app = QApplication(sys.argv)
mainwindow = MainWindow()
mainwindow.show()
sys.exit(app.exec())

Solution

Due to their nature, databases don't have the concept of insertion at a given index: records are always appended.

The default editStrategy (which dictates when the model actually submits changes to the database) is OnRowChange, and that also happens whenever a new record is inserted.

The result is that when you reach the end of the for loop, the model has already submitted the changes and the new records have been inserted, so, no matter what row you use, the database can only insert new records at the end.

The only (simple) way to insert records at a given row is to do that while previous have been inserted but not submitted yet, and using the OnManualSubmit strategy.

In your case:

class MainWindow(QMainWindow):
    def __init__(self):
        # ...
        self.model = SQLTableModel()
        self.model.setTable("table1")
        self.model.setEditStrategy(self.model.OnManualSubmit)

        # ...

        self.model.insertRecord(2, record)
        self.model.submitAll()
        self.model.select()
        self.model.setEditStrategy(self.model.OnRowChange)

This will properly insert the last record at row 2.

But, if the previous records already existed in the database (or were previously submitted), there's nothing you can do, except from regenerate the table, or manually UPDATE all records starting from the wanted row, by "shifting" the existing data of each record to the following row.

Answered By - musicamante