QSqlQueryModel insert a single row, retrieve and append it to the current model

[Rhdr]

If you insert a row into a QSqlQueryModel, the model is not updated (only the database) and you have to re-run the Select query (model.setQuery("Select* From MyTable") ) just to view that one extra row.

Please tell me there is a better way, than re-querying the database every time a user insert a row?

Current Workaround (idea):
If all else fail, I was thinking of dumping the query into a temp container of some kind and use that container instead of the query as the model. Then on insert you can add the row to the container and the database, the container should show the records correctly to the view and the database wont have to be re-queried. However this operation would be costly

Edit: Another idea might be to create a physical query/view in the database and create custom model that use QSqlTableModel for the Select part and have custom queries with transaction support for the inserts and updates. The major drawback is having to build physical queries for everything and cluttering the database

[jsulm]

@rhdr Shouldn't you emit dataChanged() signal after changing data model?

[VRonin]

There is no way for the model to know if a row added to a db table will actually end up as part of the query without re-running the entire statement.

Your workaround idea #1 is what I usually use: a QStandardItemModel (or a custom model) that holds the data you get from the first select and when it gets updated it takes care of inserting into the original db

[Rhdr]

@jsulm I have played around with the dataChanged() signal but could not get it to update the model, I even tried to submit a signal for the whole model with no luck.

Here is my code: (sorry for the PyQt...)

def insertRows(self, rows, parent = QtCore.QModelIndex()):
    pos = self.rowCount()
    self.beginInsertRows(parent, pos, pos + rows - 1)
    
    for r in range(rows):
        q = QtSql.QSqlQuery()
        q.prepare(self.__objectSql.insertSql)
        q.exec_()        
                    
    self.endInsertRows()
    self.dataChanged.emit(self.index(0, 0), self.index(self.rowCount()+1, 2))

[Rhdr]

@vronin nice, this works faster than expected:

        q = QtSql.QSqlQuery(selectSql)
        self.__model.setRowCount(q.size())
        self.__model.setColumnCount(headerLen - 1)
        row = 0
        while q.next():            
            for col in range(headerLen):
                item = QtGui.QStandardItem(str(q.record().value(col)))
                self.__model.setItem(row, col, item)
            row+=1

Say you have mobile users (using expensive data). How would you implement lazy retrieval?

Pseudo Attempt/idea: Do a SELECT TOP 50 query, and then in the data(DisplayRole ) function check for the current row. If the current row hits say, row 40 do a SELECT TOP 50 WHERE Row>50 and then add the second query's result to the model using a loop

[JonB]

@rhdr
You perhaps already know this, but QSqlQueryModel being read-only what are you trying to do inserting rows? I would expect to use a QSqlTableModel if I wanted to do updates, and then you get the update visible to your client from the model and you choose when to commit the changes to the physical database....

[VRonin]

@rhdr said in QSqlQueryModel insert a single row, retrieve and append it to the current model:

Say you have mobile users (using expensive data). How would you implement lazy retrieval?

Use a custom model implementing canFetchMore and fetchMore

[Rhdr]

@jonb I need transaction support and many views contains data from multiple tables. Also in some cases, I would like to implement de-serialization of objects whilst retrieving data, hence the need for a custom model.

@VRonin I'll look into it thanks!

[JonB]

@rhdr

@jonb I need transaction support

I don't see why that would mitigate against QSqlTableModel. But I accept you have your other reasons.