Remove Row in subclassed QSQLTableModel not updating database

NunoA

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!

SGaist

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.

NunoA

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.

SGaist

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.

NunoA

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

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

SGaist

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

NunoA

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

eyllanesc

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

SGaist

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

NunoA

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!