problem inserting record in Sqlite table using QSQLTable model and QDataWidget mapper

Question

I'm struggling with the code shown below that loads data from a SQLite table which has 3 columns ("codice" (primary key, autoincrement), "descrizione", "peso") using QSQLTable model with QDataWidget mapper.

My problem is I defined a QPushButton to insert new record in such table but I'm not able to get it work. I tried different ways using self.model.insertRows or self.model.insertRecord but I was not successfully. I'm not getting any error but the record is not inserted.

For sure there are conceptual errors I can't catch.

import sys

from PyQt5.QtCore import QSize, Qt
from PyQt5.QtSql import QSqlDatabase, QSqlTableModel

from PyQt5.QtWidgets import (
    QApplication,
    QComboBox,
    QDataWidgetMapper,
    QDoubleSpinBox,
    QFormLayout,
    QHBoxLayout,
    QLabel,
    QLineEdit,
    QMainWindow,
    QPushButton,
    QSpinBox,
    QTableView,
    QVBoxLayout,
    QWidget,
)
from connect_SQLITE import Database

db=Database.con
db.open()

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

        layout = QVBoxLayout()

        form = QFormLayout()

        self.codice = QSpinBox()
        self.codice.setRange(0, 2147483647)
        self.codice.setDisabled(True)
        
        self.descrizione = QLineEdit()
        
        self.peso = QDoubleSpinBox()
        self.peso.setDecimals(5)
        self.peso.setRange(0, 9999.99999)
        self.peso.setSingleStep(0.01)

        form.addRow(QLabel("Codice Materiale"), self.codice)
        form.addRow(QLabel("Nome Materiale"), self.descrizione)
        form.addRow(QLabel("Peso specifico Materiale (Kg/dm3)"), self.peso)

        self.model = QSqlTableModel(db=db)
        self.mapper = QDataWidgetMapper()
        self.mapper.setModel(self.model)

        self.mapper.addMapping(self.codice, 0)
        self.mapper.addMapping(self.descrizione, 1)
        self.mapper.addMapping(self.peso, 2)

        self.model.setTable("Prova")
        self.model.select()

        self.mapper.toFirst()

        # tag::controls[]
        self.setMinimumSize(QSize(400, 400))

        controls = QHBoxLayout()

        prev_rec = QPushButton("Precedente")
        prev_rec.clicked.connect(self.mapper.toPrevious)

        next_rec = QPushButton("Successivo")
        next_rec.clicked.connect(self.mapper.toNext)

        ins_rec = QPushButton("Inserimento")
        ins_rec.clicked.connect(self.inserimento_materiale)
        
        save_rec = QPushButton("Salvataggio modifiche")
        save_rec.clicked.connect(self.mapper.submit)

        controls.addWidget(prev_rec)
        controls.addWidget(next_rec)
        controls.addWidget(ins_rec)
        controls.addWidget(save_rec)

        layout.addLayout(form)
        layout.addLayout(controls)

        widget = QWidget()
        widget.setLayout(layout)
        self.setCentralWidget(widget)
        # end::controls[]

    def inserimento_materiale(self):
        self.model.insertRows(self.model.rowCount(), 1)
        self.model.setData(self.model.index(0,1), self.descrizione.text())
        self.model.setData(self.model.index(0,2), self.peso)
        self.model.submit()
     

app = QApplication(sys.argv)
window = MainWindow()
window.show()
app.exec_()

user_2001654 · Answer

The problem is that by default the mapper uses the AutoSubmit submitPolicy():

Whenever a widget loses focus, the widget's current value is set to the item model.

And QSqlTableModel uses the OnRowChange editStrategy():

Changes to a row will be applied when the user selects a different row.

Also, you're calling setData() on the first row, instead of the new one.

The result is that when inserimento_materiale is called and a new row is inserted, the current index of the mapper is still the previous one (the first on startup, due to toFirst() or any other set after toPrevious() or toNext()), setData() will not work in any case:

in your example, the mapper has already set the cached data due to its AutoSubmit policy, but it has not been actually submitted in the database, and setData() returns False because "For OnRowChange, an index may receive a change only if no other row has a cached change";
even setting the proper row for setData() will not work, as inserting a new row causes the mapper to submit the previous data of the current widgets in the previous row, and will not properly update the new mapper index;

^{Be aware that your code also has a typo: you're trying to call setData() with self.peso which is a widget.}

A possible solution is to read the current widget values before doing anything, revert() the model data (to avoid saving the new data on the previous index), submit the new data to the model and set the new index in the mapper:

    def inserimento_materiale(self):
        descrizione = self.descrizione.text()
        peso = self.peso.value()
        self.model.revert()
        row = self.model.rowCount()
        self.model.insertRow(row)
        self.model.setData(self.model.index(row, 1), descrizione)
        self.model.setData(self.model.index(row, 2), peso)
        self.model.submitAll()
        self.mapper.setCurrentIndex(row)

Note that since you have a dedicated submit button, you should change the mapper's submit policy to ManualSubmit, which makes things easier:

class MainWindow(QMainWindow):
    def __init__(self):
        # ...
        self.mapper.setSubmitPolicy(self.mapper.ManualSubmit)

    def inserimento_materiale(self):
        row = self.model.rowCount()
        self.model.insertRow(row)
        self.model.setData(self.model.index(row, 1), self.descrizione.text())
        self.model.setData(self.model.index(row, 2), self.peso.value())
        self.model.submitAll()
        self.mapper.setCurrentIndex(row)

problem inserting record in Sqlite table using QSQLTable model and QDataWidget mapper

1 Answers