Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. QSqlQueryModel insert a single row, retrieve and append it to the current model

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

Scheduled Pinned Locked Moved Solved General and Desktop
9 Posts 4 Posters 2.3k Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • R Offline
    R Offline
    Rhdr
    wrote on last edited by Rhdr
    #1

    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

    jsulmJ JonBJ 2 Replies Last reply
    0
    • R 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

      jsulmJ Offline
      jsulmJ Offline
      jsulm
      Lifetime Qt Champion
      wrote on last edited by
      #2

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

      https://forum.qt.io/topic/113070/qt-code-of-conduct

      1 Reply Last reply
      0
      • VRoninV Offline
        VRoninV Offline
        VRonin
        wrote on last edited by
        #3

        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

        "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
        ~Napoleon Bonaparte

        On a crusade to banish setIndexWidget() from the holy land of Qt

        R 1 Reply Last reply
        2
        • R Offline
          R Offline
          Rhdr
          wrote on last edited by
          #4

          @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))
          

          0_1564385924931_3b63d396-5a2c-4553-94fd-9becc1028ad9-image.png

          1 Reply Last reply
          0
          • VRoninV 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

            R Offline
            R Offline
            Rhdr
            wrote on last edited by
            #5

            @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

            VRoninV 1 Reply Last reply
            0
            • R 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

              JonBJ Offline
              JonBJ Offline
              JonB
              wrote on last edited by
              #6

              @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....

              R 1 Reply Last reply
              1
              • R 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

                VRoninV Offline
                VRoninV Offline
                VRonin
                wrote on last edited by
                #7

                @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

                "La mort n'est rien, mais vivre vaincu et sans gloire, c'est mourir tous les jours"
                ~Napoleon Bonaparte

                On a crusade to banish setIndexWidget() from the holy land of Qt

                1 Reply Last reply
                3
                • JonBJ 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....

                  R Offline
                  R Offline
                  Rhdr
                  wrote on last edited by Rhdr
                  #8

                  @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!

                  JonBJ 1 Reply Last reply
                  0
                  • R 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!

                    JonBJ Offline
                    JonBJ Offline
                    JonB
                    wrote on last edited by
                    #9

                    @rhdr

                    @jonb I need transaction support

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

                    1 Reply Last reply
                    0

                    • Login

                    • Login or register to search.
                    • First post
                      Last post
                    0
                    • Categories
                    • Recent
                    • Tags
                    • Popular
                    • Users
                    • Groups
                    • Search
                    • Get Qt Extensions
                    • Unsolved