What's the best way to have QTableView and Database in sync after inserting the record?

Question

Let's suppose I have a QTableView with QSqlTableModel/Database. I don't want to let user edit the cells in QTableView. There are CRUD buttons that open new dialog forms and the user is supposed to enter data. After the user clicks dialog's OK button, what is the best way to insert that new record to database and view (to have them in sync), because database can be unavailable at the time (for example, inserting to remote database while having internet connection problems)?

My primary concern is I don't want to show phantom records in view and I want the user to be aware the record is not entered in the database.

I put some python code (but for Qt my problem is the same) to illustrate this, and have some other questions in comments:

import sys
from PyQt4.QtGui import *
from PyQt4.QtCore import *
from PyQt4.QtSql import *

class Window(QWidget):
    def __init__(self, parent=None):
        QWidget.__init__(self, parent)
        self.model = QSqlTableModel(self)
        self.model.setTable("names")
        self.model.setHeaderData(0, Qt.Horizontal, "Id")
        self.model.setHeaderData(1, Qt.Horizontal, "Name")
        self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.model.select()

        self.view = QTableView()
        self.view.setModel(self.model)
        self.view.setSelectionMode(QAbstractItemView.SingleSelection)
        self.view.setSelectionBehavior(QAbstractItemView.SelectRows)
        #self.view.setColumnHidden(0, True)
        self.view.resizeColumnsToContents()
        self.view.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.view.horizontalHeader().setStretchLastSection(True)

        addButton = QPushButton("Add")
        editButton = QPushButton("Edit")
        deleteButton = QPushButton("Delete")
        exitButton = QPushButton("Exit")

        hbox = QHBoxLayout()
        hbox.addWidget(addButton)
        hbox.addWidget(editButton)
        hbox.addWidget(deleteButton)
        hbox.addStretch()
        hbox.addWidget(exitButton)

        vbox = QVBoxLayout()
        vbox.addWidget(self.view)
        vbox.addLayout(hbox)
        self.setLayout(vbox)

        addButton.clicked.connect(self.addRecord)
        #editButton.clicked.connect(self.editRecord) # omitted for simplicity
        #deleteButton.clicked.connect(self.deleteRecord) # omitted for simplicity
        exitButton.clicked.connect(self.close)

    def addRecord(self):
        # just QInputDialog for simplicity
        value, ok = QInputDialog.getText(self, 'Input Dialog', 'Enter the name:')
        if not ok:
            return

        # Now, what is the best way to insert the record?

        # 1st approach, first in database, then model.select()
        # it seems like the most natural way to me
        query = QSqlQuery()
        query.prepare("INSERT INTO names (name) VALUES(:name)")
        query.bindValue( ":name", value )
        if query.exec_():
            self.model.select() # now we know the record is inserted to db
            # the problem with this approach is that select() can be slow
            # somehow position the view to newly added record?!
        else:
            pass
            # message to user
            # if the record can't be inserted to database, 
            # there's no way I will show that record in view

        # 2nd approach, first in view (model cache), then in database
        # actually, I don't know how to do this
        # can somebody instruct me?
        # maybe:
        # record = ...
        # self.model.insertRecord(-1, record) #
        # submitAll()
        # what if database is unavailable?
        # what if submitAll() fails?
        # in that case, how to have view and model in sync?
        # is this the right approach?

        # 3. is there some other approach?

app = QApplication(sys.argv)
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(":memory:")
db.open()
query = QSqlQuery()
query.exec_("DROP TABLE names")
query.exec_("CREATE TABLE names(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name TEXT)")
query.exec_("INSERT INTO names VALUES(1, 'George')")
query.exec_("INSERT INTO names VALUES(2, 'Rita')")
query.exec_("INSERT INTO names VALUES(3, 'Jane')")
query.exec_("INSERT INTO names VALUES(4, 'Steve')")
query.exec_("INSERT INTO names VALUES(5, 'Maria')")
query.exec_("INSERT INTO names VALUES(6, 'Bill')")
window = Window()
window.resize(600, 400)
window.show()
app.exec_()

Answer 1

RobbieE is right that I can use form editing (with QDataWidgetMapper) instead of direct cell editing, but my question was not about form or cell editing.

My question was which approach from my example is better, 1st or 2nd.

I changed the code and implemented the 2nd approach (which I don't like). Is it a good implementation?

But the question still remains. How do you (Py)Qt developers do CRUD with QtSql? First database, then model/view or first model/view, then database?

EDIT: I edited the example, added 3. approach (incomplete) and the possibility to simulate database closing. Now, it's easier to test all 3 approaches.

import sys
from PyQt4.QtGui import *
from PyQt4.QtCore import *
from PyQt4.QtSql import *

class Window(QWidget):
    def __init__(self, parent=None):
        QWidget.__init__(self, parent)
        self.model = QSqlTableModel(self)
        self.model.setTable("names")
        self.model.setHeaderData(0, Qt.Horizontal, "Id")
        self.model.setHeaderData(1, Qt.Horizontal, "Name")
        self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.model.select()

        self.view = QTableView()
        self.view.setModel(self.model)
        self.view.setSelectionMode(QAbstractItemView.SingleSelection)
        self.view.setSelectionBehavior(QAbstractItemView.SelectRows)
        #self.view.setColumnHidden(0, True)
        self.view.resizeColumnsToContents()
        self.view.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.view.horizontalHeader().setStretchLastSection(True)

        addButton = QPushButton("Add")
        editButton = QPushButton("Edit")
        deleteButton = QPushButton("Delete")
        exitButton = QPushButton("Exit")
        self.combo = QComboBox()
        self.combo.addItem("1) 1.Database, 2.Model (select)")
        self.combo.addItem("2) 1.Model, 2.Database")
        self.combo.addItem("3) 1.Database, 2.Model (insert)")
        self.combo.setCurrentIndex (0)
        self.checkbox = QCheckBox("Database Closed")

        hbox = QHBoxLayout()
        hbox.addWidget(addButton)
        hbox.addWidget(editButton)
        hbox.addWidget(deleteButton)
        hbox.addWidget(self.combo)
        hbox.addWidget(self.checkbox)
        hbox.addStretch()
        hbox.addWidget(exitButton)

        vbox = QVBoxLayout()
        vbox.addWidget(self.view)
        vbox.addLayout(hbox)
        self.setLayout(vbox)

        addButton.clicked.connect(self.addRecord)
        #editButton.clicked.connect(self.editRecord) # omitted for simplicity
        #deleteButton.clicked.connect(self.deleteRecord) # omitted for simplicity
        self.checkbox.clicked.connect(self.checkBoxCloseDatabase)
        exitButton.clicked.connect(self.close)

    def checkBoxCloseDatabase(self):
        if self.checkbox.isChecked():
            closeDatabase()
        else:
            pass
            #db.open() # it doesn't work

    def addRecord(self):
        # just QInputDialog for simplicity
        value, ok = QInputDialog.getText(self, 'Input Dialog', 'Enter the name:')
        if not ok:
            return

        # Now, what is the best way to insert the record?

        if self.combo.currentIndex() == 0:
            # 1st approach, first in database, then model.select()
            # it seems like the most natural way to me
            query = QSqlQuery()
            query.prepare("INSERT INTO names (name) VALUES(:name)")
            query.bindValue( ":name", value )
            if query.exec_():
                self.model.select() # now we know the record is inserted to db
                # the problem with this approach is that select() can be slow
                # somehow position the view to newly added record?!
            else:
                pass
                # message to user
                # if the record can't be inserted to database,
                # there's no way I will show that record in view
        elif self.combo.currentIndex() == 1:
            # 2nd approach, first in view (model cache), then in database
            QSqlDatabase.database().transaction()
            row = self.model.rowCount()
            self.model.insertRow(row)
            self.model.setData(self.model.index(row, 1), value)
            #self.model.submit()
            if self.model.submitAll():
                QSqlDatabase.database().commit()
                self.view.setCurrentIndex(self.model.index(row, 1))
            else:
                self.model.revertAll()
                QSqlDatabase.database().rollback()
                QMessageBox.warning(self, "Error", "Database not available. Please, try again later.")

        else:
            # 3rd approach, first in database, then model.insertRow()
            # it is not a complete solution and is not so practical
            query = QSqlQuery()
            query.prepare("INSERT INTO names (name) VALUES(:name)")
            query.bindValue( ":name", value )
            if query.exec_():
                #id = ... # somehow find id from the newly added record in db
                row = self.model.rowCount()
                self.model.insertRow(row)
                #self.model.setData(self.model.index(row, 0), id) # we don't know it
                self.model.setData(self.model.index(row, 1), value)
                # not a complete solution
            else:
                pass
                # do nothing, because model isn't changed
                # message to user

def closeDatabase():
    db.close()

def createFakeData():
    query = QSqlQuery()
    query.exec_("DROP TABLE names")
    query.exec_("CREATE TABLE names(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name TEXT)")
    query.exec_("INSERT INTO names VALUES(1, 'George')")
    query.exec_("INSERT INTO names VALUES(2, 'Rita')")
    query.exec_("INSERT INTO names VALUES(3, 'Jane')")
    query.exec_("INSERT INTO names VALUES(4, 'Steve')")
    query.exec_("INSERT INTO names VALUES(5, 'Maria')")
    query.exec_("INSERT INTO names VALUES(6, 'Bill')")
    #import random
    #for i in range(1000):
    #    name = chr(random.randint(65, 90))
    #    for j in range(random.randrange(3, 10)):
    #        name += chr(random.randint(97, 122))
    #
    #    query.prepare("INSERT INTO names (name) VALUES(:name)")
    #    query.bindValue( ":name", name )
    #    query.exec_()

app = QApplication(sys.argv)
db = QSqlDatabase.addDatabase("QSQLITE")
#db.setDatabaseName("test.db")
db.setDatabaseName(":memory:")
#openDatabase()
db.open()
createFakeData()
window = Window()
window.resize(800, 500)
window.show()
app.exec_()

EDIT2 10/2019: I finally stopped using QSqlTableModel with RDBMSs. I use only QSqlQueryModel and don't need QSqlTableModel or QSqlRelationalTableModel. I don't use cell editing (like in Excel) and edit only whole records (rows) through forms. After clicking OK on form, I update the database and reselect QSqlQueryModel. Interestingly, after reselecting, the current row is again focused. QSql(Relational)TableModel has so much problems with handling database fields, they blew it completely, it's almost useless for serious work. They could have made it so much more useful than it is now.

My advice: for serious work and business apps, use QSqlQueryModel.

Answer 2

As already mentioned in my comment your 1st approach is favorable over your 2nd one since it preventes you from doing unnecessary work. However if you are concerned about the amount of data which will be transfered by QSqlTableModel.select (what might make your application slow) you can use QSqlTableModel.insertRecord instead (see below my example for details). This tries to insert the record in the database and at the same time it will be registered in the model, even if the insert failed. So you would have to remove it manually again (in case of failure) by QSqlTableModel.revertAll().

However you can use this fact that it is independently added to model to take the responsibility for re-adding data, for which the insert failed, from the user. That means the data get's inserted in the model and you try to submit it to the database later (the user has not to re-enter it). Here a small example (only the crucial parts):

(I used a two column table with columns INT NOT NULL AUTO_INCREMENT and VARCHAR(32))

record = QtSql.QSqlRecord()  # create a new record
record.append(QtSql.QSqlField('id', QtCore.QVariant.Int))  # add the columns
record.append(QtSql.QSqlField('value', QtCore.QVariant.String))
record.setValue('id', 0)  # set the values for every column
record.setValue('value', value)

if not self.model.insertRecord(-1, record):  # insert the record (-1 means at the end)
    self.queue = QueueRecord(self.model, self.table, self.model.rowCount()-1)  # in case of failure a new class is invoked which will manage the submission of this record (see below)
    # However, the record was added to the model and will therefore appear in the table
    self.connect(self, QtCore.SIGNAL('qstart()'), self.queue.insert)  # queue.insert is the method which handles submitting the record, the signal qstart() was created beforehand using QtCore.pyqtSignal()
    self.qstart.emit()  # start the submission handler

This is the class which handles the submission of the pending record:

class QueueRecord(QtCore.QObject):
    def __init__(self, model, table, row, parent=None):
        QtCore.QObject.__init__(self, parent)

        self.model = model
        self.table = table
        self.delegate = table.itemDelegateForRow(row)  # get the item delegate for the pending row (to reset it later)
        self.row = row

        table.setItemDelegateForRow(row, PendingItemDelegate())  # set the item delegate of the pending row to new one (see below). In this case all cells will just display 'pending ...' so the user knows that this record isn't submitted yet.

        self.t1 = QtCore.QThread()  # we need a new thread so we won't block our main application
        self.moveToThread(self.t1)
        self.t1.start()

    def insert(self):
        while not self.model.submitAll():  # try to submit the record ...
            time.sleep(2)  # ... if it fails retry after 2 seconds.

        # record successfully submitted
        self.table.setItemDelegateForRow(self.row, self.delegate)  # reset the delegate
        self.t1.quit()  # exit the thread

Here's the delegate:

class PendingItemDelegate(QtGui.QStyledItemDelegate):
    def __init__(self, parent=None):
        QtGui.QStyledItemDelegate.__init__(self, parent)

    def displayText(self, value, locale):
        return 'pending ...'  # return 'pending ...' for every cell

So what this code basically does is it uses insertRecord to insert new data in the model / database. In case of failure the record will be anyway added to the model and we create a new class (which runs in a separate thread) to handle the re-submission of the data. This new class will change the display of pending row to indicate the user that this record has not been registered yet and try to submitt the data until it was successful. The delegate is reset and the thread left.

Like this you avoid calling select() but just insert one record in the table. Furthermore the user is no more responsible for providing the data again but this will be handled by a separate class.

However this is a really simple example and it should be treated with caution! For example the class QueueRecord uses the row number provided via model.rowCount()-1 to refer to the pending element but if you delete elements in the meantime the row count will change and you will refer to the wrong element.

This example is for illustration purposes and can be used for further development but in its current state it's not meant to be used in actual applications.

You could for example change that there is a max. number of attempts for re-submission and after that 'pending ...' will change to 'failed' and a button for re-submission emerges, so the user just has to press this button to re-add the data after the connection to the database was established again.

By the way, to test this stuff I added a button to my mainwindow which closes / opens the database, so I started the application (opening automatically the database) then I closed it, inserted a value and openend it again.

Answer 3

You can still use QSqlTableModel. You can turn off all edit triggers in your table view and then pass the model to your data capturing forms and let the widgets bind to the model using QDataWidgetMapper, making sure the submit mode is set to manual so you can validate fields first.