2

When using QtableView + QSqlTableModel combination, QtableView fails to properly display the inserted row after insertion of a new one.

The id field is hidden. The EditStrategy is OnFieldChange. There is a button to insert a new empty row at the end of the model. After the user fills in all fields and hits enter or clicks on other rows, the expected behavior is for the row to be submitted to the database and the view keeping the new row. However, the QSqlTableModel succeeds to insert the data into the database table, but the tableview's newly inserted row becomes empty and fields uneditable.

The behavior changes to the desired one if the id field is not hidden and is explicitly provided. Yet if the id is not hidden and not populated manually, the behaviour fails again as mentioned above.

Why this happens?

Here is the minimum reproducible code. You will have to change database configuration fileds. And connect to the table with autoincrement id:

import sys

from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtCore import QModelIndex
from PyQt5.QtSql import QSqlDatabase, QSqlTableModel
from PyQt5.QtWidgets import QPushButton


class Ui_main(object):
    def setupUi(self, main):
        main.setObjectName("main")
        main.resize(781, 652)
        main.setContextMenuPolicy(QtCore.Qt.NoContextMenu)

        self.verticalLayoutWidget = QtWidgets.QWidget(main)
        self.verticalLayoutWidget.setGeometry(QtCore.QRect(10, 10, 761, 631))
        self.verticalLayoutWidget.setObjectName("verticalLayoutWidget")
        self.verticalLayout = QtWidgets.QVBoxLayout(self.verticalLayoutWidget)
        self.verticalLayout.setContentsMargins(0, 0, 0, 0)
        self.verticalLayout.setObjectName("verticalLayout")

        # Replace values with your database configurations
        # But the table you refer to must have autoincremented primary key
        database = QSqlDatabase.addDatabase('QPSQL')
        database.setHostName('localhost')
        database.setPort(5432)
        database.setDatabaseName('some_database')
        database.setUserName('some_user')
        database.setPassword('some_password')
        database.open()

        button_add = QPushButton("AddRow")
        button_add.clicked.connect(self.addRow)
        self.verticalLayout.addWidget(button_add)

        self.tableView = QtWidgets.QTableView(self.verticalLayoutWidget)
        self.tableView.setObjectName("tableView")
        self.tableView.verticalHeader().setVisible(False)
        self.verticalLayout.addWidget(self.tableView)

        self.table_model = QSqlTableModel(self.tableView, database)
        table_name = 'some_table'
        self.table_model.setTable(table_name)

        self.table_model.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.table_model.select()

        self.tableView.setModel(self.table_model)
        self.tableView.hideColumn(0)

        self.retranslateUi(main)
        QtCore.QMetaObject.connectSlotsByName(main)

    def retranslateUi(self, main):
        _translate = QtCore.QCoreApplication.translate
        main.setWindowTitle(_translate("main", "main"))

    def addRow(self):
        count = self.table_model.rowCount(QModelIndex())
        self.table_model.insertRow(count)
        self.tableView.scrollToBottom()

if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    main_window = QtWidgets.QWidget()
    window = Ui_main()
    window.setupUi(main_window)
    main_window.show()
    sys.exit(app.exec_())

Update:

The temporary workaround I am using right now is finding max id from the fetched table and append max_id + 1 to the newly inserted row. Thus, the row has an id on submission to the database. However, this seems to be not the right way as the database handles primary keys itself...

Update 2: Here is how my database table looks like:

Design:

book_of_accounts: table
+ columns
    id: bigint NN default nextval('book_of_accounts_id_seq'::regclass)
    code: varchar(256) NN
    name: varchar(1024) NN
    account_type: varchar(256) NN
    quantitative: boolean NN
    monetary: boolean NN
    subconoto_1: varchar(256)
    subconoto_2: varchar(256)
    subconoto_3: varchar(256)
+ indices
    book_of_accounts_pkey: unique (id)
    bookofaccounts_code: unique (code)
    bookofaccounts_name: index (name)
+ keys
    book_of_accounts_pkey: PK (id)

SQL

create table if not exists book_of_accounts
(
    id bigserial not null
        constraint book_of_accounts_pkey
        primary key,
    code varchar(256) not null,
    name varchar(1024) not null,
    account_type varchar(256) not null,
    quantitative boolean not null,
    monetary boolean not null,
    subconoto_1 varchar(256),
    subconoto_2 varchar(256),
    subconoto_3 varchar(256)
);

alter table book_of_accounts owner to hamlet;

create unique index if not exists bookofaccounts_code
    on book_of_accounts (code);

create index if not exists bookofaccounts_name
    on book_of_accounts (name);
Muslimbek Abduganiev
  • 828
  • 7
  • 21
  • Could you show the instruction with which you created the table (I want to know what type of fields exist and the characteristics of each field) – eyllanesc Jul 05 '21 at 14:16
  • @eyllanesc I have updated my question according to your request. – Muslimbek Abduganiev Jul 06 '21 at 05:55
  • Have you tried [insertRecord()](https://doc.qt.io/qt-5/qsqltablemodel.html#insertRecord) instead of insertRow()? Also, I'd add table.show() after every update. – s0mbre Jul 07 '21 at 04:53
  • @s0mbre At this point, the problem is not about making it work. I have a workaround for the problem. But I want to know what is wrong with this style/code? Why this particular problem arises? Answering your question, yes, I have tried *insertRecord* with empty record that gets then populated by user. It gave the same behavior. – Muslimbek Abduganiev Jul 07 '21 at 06:44

1 Answers1

0

OK, so the problem seems to be that (quoting from Qt docs):

Note: To prevent inserting only partly initialized rows into the database, OnFieldChange will behave like OnRowChange for newly inserted rows.

To make the table update and display the newly inserted row, you can call the model's submitAll() method on data change.

Complete updated code (updates marked in comments):

import sys

from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtCore import QModelIndex
from PyQt5.QtSql import QSqlDatabase, QSqlTableModel
from PyQt5.QtWidgets import QPushButton

class Ui_main(object):
    def setupUi(self, main):
        main.setObjectName("main")
        main.resize(781, 652)
        main.setContextMenuPolicy(QtCore.Qt.NoContextMenu)

        self.verticalLayoutWidget = QtWidgets.QWidget(main)
        self.verticalLayoutWidget.setGeometry(QtCore.QRect(10, 10, 761, 631))
        self.verticalLayoutWidget.setObjectName("verticalLayoutWidget")
        self.verticalLayout = QtWidgets.QVBoxLayout(self.verticalLayoutWidget)
        self.verticalLayout.setContentsMargins(0, 0, 0, 0)
        self.verticalLayout.setObjectName("verticalLayout")

        # Replace values with your database configurations
        # But the table you refer to must have autoincremented primary key
        database = QSqlDatabase.addDatabase('QPSQL')
        database.setHostName('localhost')
        database.setPort(5432)
        database.setDatabaseName('dbname')
        database.setUserName('postgres')
        database.setPassword('password')
        database.open()

        button_add = QPushButton("AddRow")
        button_add.clicked.connect(self.addRow)
        self.verticalLayout.addWidget(button_add)

        self.tableView = QtWidgets.QTableView(self.verticalLayoutWidget)
        self.tableView.setObjectName("tableView")
        self.tableView.verticalHeader().setVisible(False)
        self.verticalLayout.addWidget(self.tableView)

        self.table_model = QSqlTableModel(self.tableView, database)
        table_name = 'units'
        self.table_model.setTable(table_name)

        self.table_model.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.table_model.select()
        ## ========== ADDED ========== ##
        self.table_model.sort(0, 0)    # DEFAULT SORT TO SEE NEW ITEMS AT END       
        self.table_model.dataChanged.connect(self.on_dataChanged) # CONNECT DATA CHANGE SIGNAL TO CUSTOM SLOT
        ## =========================== ##

        self.tableView.setModel(self.table_model)
        self.tableView.hideColumn(0)

        self.retranslateUi(main)
        QtCore.QMetaObject.connectSlotsByName(main)

    def retranslateUi(self, main):
        _translate = QtCore.QCoreApplication.translate
        main.setWindowTitle(_translate("main", "main"))

    def addRow(self):
        count = self.table_model.rowCount(QModelIndex())
        self.table_model.insertRow(count)
        self.tableView.scrollToBottom()

    ## ========== ADDED ========== ##
    # SLOT FIRED WHEN DATA CHANGES
    def on_dataChanged(self, topLeft, bottomRight, roles):
        self.table_model.submitAll()  # COMMIT TO DB
        self.table_model.select()     # RESELECT FROM DB
        self.table_model.sort(0, 0)   # SORT
    ## =========================== ##

if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    main_window = QtWidgets.QWidget()
    window = Ui_main()
    window.setupUi(main_window)
    main_window.show()
    sys.exit(app.exec_())
s0mbre
  • 361
  • 2
  • 14
  • This code tries to submit the row on any field change. The submission fails as there are Not Null constraints on some other fields. And I don't think the problem is with the EditStrategy of the model as the model itself behaves as expected. It succeeds to submit data to the DB. The problem is with TableView which fails to show the newly inserted row properly. – Muslimbek Abduganiev Jul 08 '21 at 05:10
  • Well, I've tested this code on my DB (Postgres) and it works this way. Without `submitAll()` it doesn't work. – s0mbre Jul 12 '21 at 02:47
  • Do you have Not null constraints on any columns? I have tried your code on my table (postgresql schema provided in the answer as Update 2) and it did not solve the issue. – Muslimbek Abduganiev Jul 12 '21 at 06:37
  • Yes, I do have notnull: ```sql CREATE TABLE public.units ( id int4 NOT NULL GENERATED ALWAYS AS IDENTITY, val varchar(256) NOT NULL, "search" tsvector NULL, CONSTRAINT units_pkey PRIMARY KEY (id), CONSTRAINT units_unique UNIQUE (val) INCLUDE (val) ); ``` – s0mbre Jul 14 '21 at 06:04