How to set value to NULL in editable QTableView with QSqlTableModel

Question

I have a QTableView that displays subset of the data from a table in SQLite database. The table is editable only for nullable numeric columns. I created two delegates - one for read-only columns:

class ReadOnlyDelegate(QtWidgets.QItemDelegate):
    def editorEvent(self, *args, **kwargs):
        return False

    def createEditor(self, *args, **kwargs):
        return None

and one for editable columns:

class LabelDelegate(QtWidgets.QItemDelegate):
    def createEditor(self, parent, options, index):
        self.le = QtWidgets.QLineEdit(parent)
        return self.le

The table is fed by customized QSqlTableModel, where I overwrite submitAll method:

class MySqlTableModel(QtSql.QSqlTableModel):
    def submitAll(self):
        for row in range(self.rowCount()):
            for col in range(self.columnCount()):
                if self.isDirty(self.index(row, col)):
                    val = self.record(row).value(col)
                    if val == '':
                        self.record(row).setNull(col)
                    else:
                        try:
                            self.record(row).setValue(col, float(val))
                        except (TypeError, ValueError):
                            display_error_msg('Can not convert to float',
                                              f'The value {val} could not be converted to float')
                            raise
        super().submitAll()

Expected behaviour is (1) to convert values to float before sending to database, (2) reject inputs that can't be converted to float and (3) to convert empty string to NULL. (1) and (2) work as expected, however the last bit is not working. When debugging method .submitAll() it raises no exception on the line self.record(row).setNull(col) but it also seems to have no effect. An empty string is sent and persisted in database. Any ideas why and how to fix it?

Answer 1

I don't see the need to override the submitAll() method, instead you can implement the logic in the setModelData() method:

import sys

from PySide2 import QtCore, QtWidgets, QtSql

TABLENAME = "t1"


def create_connection():
    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("database.db")
    if not db.open():
        QtWidgets.QMessageBox.critical(
            None,
            QtWidgets.QApplication.instance().tr("Cannot open database"),
            QtWidgets.QApplication.instance().tr(
                "Unable to establish a database connection.\n"
                "This example needs SQLite support. Please read "
                "the Qt SQL driver documentation for information "
                "how to build it.\n\n"
                "Click Cancel to exit."
            ),
            QtWidgets.QMessageBox.Cancel,
        )
        return False

    if TABLENAME in db.tables():
        return True

    query = QtSql.QSqlQuery()
    if not query.exec_(f"CREATE TABLE IF NOT EXISTS {TABLENAME}(a REAL, b text);"):
        print(query.lastError().text())

    queries = (
        f"INSERT INTO {TABLENAME} VALUES(1, '1');",
        f"INSERT INTO {TABLENAME} VALUES(NULL, '2');",
        f"INSERT INTO {TABLENAME} VALUES(3, '3');",
        f"INSERT INTO {TABLENAME} VALUES(NULL, '4');",
        f"INSERT INTO {TABLENAME} VALUES(5, '4');",
        f"INSERT INTO {TABLENAME} VALUES(NULL, '5');",
        f"INSERT INTO {TABLENAME} VALUES(NULL, '7');",
    )

    for query in queries:
        q = QtSql.QSqlQuery()
        if not q.exec_(query):
            print(q.lastError().text(), query)
            return False

    return True


class ReadOnlyDelegate(QtWidgets.QStyledItemDelegate):
    def editorEvent(self, *args, **kwargs):
        return False

    def createEditor(self, *args, **kwargs):
        return None


class LabelDelegate(QtWidgets.QStyledItemDelegate):
    def createEditor(self, parent, options, index):
        le = QtWidgets.QLineEdit(parent)
        return le

    def setModelData(self, editor, model, index):
        value = editor.text()
        if not value:
            model.setData(index, None, QtCore.Qt.EditRole)
        else:
            try:
                number = float(value)
            except (TypeError, ValueError):
                print(
                    f"Can not convert to float, The value {value} could not be converted to float'"
                )
            else:
                model.setData(index, number, QtCore.Qt.EditRole)


def main(args):
    app = QtWidgets.QApplication(args)

    if not create_connection():
        sys.exit(-1)

    view = QtWidgets.QTableView()

    model = QtSql.QSqlTableModel()
    model.setTable(TABLENAME)
    model.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
    model.select()

    view.setModel(model)

    label_delegate = LabelDelegate(view)
    view.setItemDelegateForColumn(0, label_delegate)

    readonly_delegate = ReadOnlyDelegate(view)
    view.setItemDelegateForColumn(1, readonly_delegate)

    button = QtWidgets.QPushButton("Submit all")

    widget = QtWidgets.QWidget()
    lay = QtWidgets.QVBoxLayout(widget)
    lay.addWidget(button)
    lay.addWidget(view)

    widget.resize(640, 480)
    widget.show()

    button.clicked.connect(model.submitAll)

    ret = app.exec_()
    sys.exit(ret)


if __name__ == "__main__":
    main(sys.argv)

Tested on Linux with:

• PyQt5 5.15.4
• PySide2 5.15.2
• Python 3.9.4