Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Remove Row in subclassed QSQLTableModel not updating database



  • I have subclassed QSQLTableModel to control different display stuff in Table View (such as red color for negative values, dynamic number of decimal digits, etc..) and that works fine.

    When I've tried to remove a row the method is not working, not deleting row in the underlying db.

    class CustomSqlTableModel(QSqlTableModel):

         def __init__(self, table, *args, **kwargs):
             super(CustomSqlTableModel, self).__init__(*args, **kwargs)
             self.setTable(table)              
             self.setEditStrategy(QSqlTableModel.OnManualSubmit) #OnFieldChange
             self.select()
       
         def data(self, index, role ):
    
         # here several ifs to control display they work fine in TableView
         # even if I delete them and just leave out the bellow standard line it will not remove the row
    
            return QSqlTableModel.data(self, index, role)
    
         def setData(self, index, value, role = Qt.EditRole):
              if not index.isValid():
                  return False
    
              return QSqlTableModel.setData(self, index, value, role) 
    

    model = CustomSqlTableModel(db = my_conn, table = 'my_table')

    print(model.rowCount()) # returns the number of rows

    a = model.removeRow(2)

    print(a) # returns True

    b = model.submitAll()

    print(b) # returns True

    c = model.select()

    print(c) # returns True

    print(model.rowCount()) # returns the same number of rows

    If I delete the Data and/or setData reimplementation of the QSqlTableModel the RemoveRow does work and deletes the row from the database.

    But if in the Data() method I just leave the standard line replicating the Base class method the RemoveRow will also not work. So it must be something very basic in the subclassing that I am doing wrong.

    I've seen in other responses elsewhere hints that the EditRole and/or Record() method should be considered in the Data() method reimplement but I could not figured that out. Thanks!


  • Lifetime Qt Champion

    Hi and welcome to devnet,

    If you only want to modify some view related stuff, it would likely be simpler to use a custom QIdentityProxyModel between your view and SQL model.



  • Thanks @SGaist I will try that yes, as for now it is just view related adjustments that I need.
    Just out of curiosity, and might need later on, if indeed I would like to subclass the SQL model do you have any idea why I am struggling to find a way to delete a row ? Thanks, glad do be here.


  • Lifetime Qt Champion

    Your data method signature does not match exactly the one form QSqlTableModel.

    I would also be consistant with the use of super since you already used it for your init method.



  • Thank you. Isn't this the exact data method from QSqlTableModel ?

    def data(self, index, role ):
    return QSqlTableModel.data(self, index, role)


  • Lifetime Qt Champion

    The method has a default value for role. I currently do not know the Python consequences of not having it in your method signature.



  • With or without role = Qt.DisplayRole I could not put the remove row to function. I am working in the QIdentityProxyModel alternative you suggested and it seems to work! Thanks



  • @NunoA I think you have an XY problem because if you want to customize how the information is displayed in the view then you should not modify the model but rather implement a custom delegate. In the following example I show how to set a red background color for negative numbers and set the number of decimal places to be 5 for floats. This way you no longer modify the default behavior of the model.

    import os
    
    from PyQt5 import QtCore, QtGui, QtWidgets, QtSql
    
    CURRENT_DIR = os.path.dirname(os.path.realpath(__file__))
    
    
    def create_connection(name):
        db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName(name)
        if db.open():
            return True
        return False
    
    
    class CustomDelegate(QtWidgets.QStyledItemDelegate):
        def initStyleOption(self, option, index):
            super().initStyleOption(option, index)
            value = index.data()
            if isinstance(value, (int, float)) and value < 0:
                option.backgroundBrush = QtGui.QColor("red")
    
        def displayText(self, value, locale):
            if isinstance(value, float):
                return locale.toString(value, "f", 5)
            return super().displayText(value, locale)
    
    
    if __name__ == "__main__":
        import sys
    
        app = QtWidgets.QApplication(sys.argv)
    
        if not create_connection(os.path.join(CURRENT_DIR, "data.db")):
            sys.exit(-1)
    
        w = QtWidgets.QTableView()
        delegate = CustomDelegate(w)
        w.setItemDelegate(delegate)
        model = QtSql.QSqlTableModel()
        model.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
        model.setTable("my_table")
        model.select()
    
        if model.removeRow(2) and not model.submitAll():
                print(model.lastError().text())
    
        w.setModel(model)
        w.resize(640, 480)
        w.show()
    
        sys.exit(app.exec_())
    

  • Lifetime Qt Champion

    @eyllanesc has good point as well if you are using one of the classic view.



  • Thanks @SGaist @eyllanesc.

    Yes I am using a TableView to display the table of data and QFormLayout and QDataWidgetMapper to update, delete and insert records. I am passing configuration parameters to format column titles with friendlier text and format numbers and texts like @eyllanesc showed.

    As my app will have several different tables I just thought that a reimplementation of QSqlTableModel could be a one stop shop for everything and got stuck with the delete record. Thanks guys!


Log in to reply