QSqlTableModel Checkbox applying checkmark when click, and not updating table

[SIG_KILL]

I am having trouble implementing a Model/View that relays simple data to a sqlite database.
Essentially I have a table showing 1-16 ports with a corresponding True/False values to determine whether or not that port is active.
I would prefer this to be presented to the user as a checkbox.

I have a feeling I'm pretty close, and I can see this is a pretty popular topic online, but found myself moving in circles for the past 24 hours. But, at this point, I don't know what else to try. So any assistance on this matter would be greatly appreciated.

My database scheme

├Sqlite (database file)
└──Ports (table)
    ├──port_num (column, int)
    └──active (column, bool)

This table holds true/false data for all 16 ports. There will be no more and no less than that many records.
When the user clicks the checkbox, I need it to alter the active column for that particular port.
Here is an example of the table:

port_num	active
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	1
16	1

(Yes, 15 and 16 are always True)

My View (wrapped in a widget so I could place this table just about anywhere in my UI):

class _PortsView(QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)

        self.view = QTableView()
        self.view.setSortingEnabled(True)
        self.view.setModel(self.model)
        self.view.setAlternatingRowColors(True)
        self.view.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.view.verticalHeader().hide()
        self.view.horizontalHeader().show()
        self.view.setShowGrid(True)
        self.view.setFocusPolicy(Qt.NoFocus)
        [self.view.setRowHeight(r, 40) for r in range(20)]
        self.view.resizeColumnsToContents()

        hbox = QHBoxLayout(self)
        hbox.addWidget(self.view, 1)
        hbox.addStretch(0)

        self.setLayout(hbox)

    @cached_property
    def model(self):
        return PortsModel()


class PortsView(QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)

        tabs = QTabWidget(self)
        tabs.addTab(self.view, "Ports")

        vbox = QVBoxLayout(self)
        vbox.addWidget(tabs, 1)

        self.setLayout(vbox)

    @cached_property
    def view(self):
        return _PortsView(self)

My Model:

class PortsModel(QSqlTableModel):
    def __init__(self, *args, **kwargs):
        super(PortsModel, self).__init__(*args, **kwargs)
        self.setTable("Ports")
        self.setHeaderData(0, Qt.Horizontal, "Port")
        self.setHeaderData(1, Qt.Horizontal, "Active")
        self.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.select()

    def flags(self, index):
        flags = super(PortsModel, self).flags(index)
        if index.column() == 0:
            flags |= not Qt.ItemIsEditable
        elif index.column() == 1:
            flags |= Qt.ItemIsUserCheckable | Qt.ItemIsEnabled
        return flags

    def data(self, index, role=Qt.DisplayRole):
        if not index.isValid():
            return None
        if role == Qt.EditRole:
            return None
        elif role == Qt.DisplayRole:
            if index.column() == 0:
                return f"Port {index.row() + 1}"
        elif role == Qt.CheckStateRole:
            if index.column() == 1:
                v = self.record(index.row())
                if v.value("active") == 1:
                    return Qt.Checked
                else:
                    return Qt.Unchecked

    def setData(self, index, value, role) -> bool:
        if index.column() == 0:
            return False

        if role == Qt.CheckStateRole:
            if index.column() == 1:
                if value == Qt.Checked:
                    return self._update_active_ports(index, 1)
                else:
                    return self._update_active_ports(index, 0)
        else:
            return False

    def _update_active_ports(self, index, value) -> bool:
        f_port_num = QSqlField()
        f_port_num.setName("port_num")
        f_port_num.setTableName("Ports")
        f_port_num.setValue(index.row())

        f_active = QSqlField()
        f_active.setName("active")
        f_active.setTableName("Ports")
        f_active.setValue(value)

        rec = QSqlRecord()
        rec.append(f_port_num)
        rec.append(f_active)

        r = self.setRecord(index.row(), rec)
        if r:
            return True
        else:
            return False

[JonB]

@SIG_KILL said in QSqlTableModel Checkbox applying checkmark when click, and not updating table:

While setRecord does return True, the database never updates.

Concentrate only on this.

        rec = QSqlRecord()
        rec.append(f_port_num)
        rec.append(f_active)

You sure this is right? Maybe it is. I would have used QSqlRecord QSqlTableModel::record(int row) const to get the record I want to change, changed the "active" column value in it, and bool QSqlTableModel::setRecord(int row, const QSqlRecord &values) it back. Safer than relying on your code here having the right columns.

[SGaist]

Hi,

Can you explain which roadblock you are hitting ?

[JonB]

@SIG_KILL said in QSqlTableModel Checkbox applying checkmark when click, and not updating table:

flags |= not Qt.ItemIsEditable

may not be your issue, but this does not look right? More likely one of:

flags |= Qt.ItemIsEditable
flags &= not Qt.ItemIsEditable

?

[SIG_KILL]

@JonB You are correct, I needed the &= Thank you :)

[SIG_KILL]

@SGaist I am unable to get the checkboxes to correspond to the boolean value in the SQLite database.

Ports 15 and 16 should be "checked" in this screenshot. I can register the click in my setData method but no check mark appears and the database does not change.

def setData(self, index, value, role) -> bool:
    if index.column() == 0:
        return False

    if role == Qt.CheckStateRole:
        if index.column() == 1:
            print("clicked check mark")  # prints every time I click a box
            if value == Qt.Checked:
                return self._update_active_ports(index, 1)
            else:
                return self._update_active_ports(index, 0)
    else:
        return False

[JonB]

@SIG_KILL
Suggest you start with debugger/debug statements to determine whether your issue here lies at setData() stage or the later data() stage.

UPDATE.
Where does your setData() override emit the dataChanged() signal? Without that view won't know to update for the change.

[SIG_KILL]

@JonB

All the examples I saw had no implementation of dataChanged so I never implemented it. There seems to be no effect, and I think its how I am updating the database under _update_active_ports. While setRecord does return True, the database never updates. So if the logic to show/hide a check mark is working, it wont be seen.

class PortsModel(QSqlTableModel):
    def __init__(self, *args, **kwargs):
        super(PortsModel, self).__init__(*args, **kwargs)
        self.setTable("Ports")
        self.setHeaderData(0, Qt.Horizontal, "Port")
        self.setHeaderData(1, Qt.Horizontal, "Active")
        self.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.select()

    def flags(self, index):
        flags = super(PortsModel, self).flags(index)
        if index.column() == 0:
            flags &= not Qt.ItemIsEditable
        elif index.column() == 1:
            flags |= Qt.ItemIsUserCheckable | Qt.ItemIsEnabled
        return flags

    def data(self, index, role=Qt.DisplayRole):
        if not index.isValid():
            return None
        if role == Qt.EditRole:
            return None
        elif role == Qt.DisplayRole:
            if index.column() == 0:
                return f"Port {index.row() + 1}"
        elif role == Qt.CheckStateRole:
            if index.column() == 1:
                v = self.record(index.row())
                if v.value("active") == 1:
                    return Qt.Checked
                else:
                    return Qt.Unchecked

    def setData(self, index, value, role) -> bool:
        if index.column() == 0:
            return False

        if role == Qt.CheckStateRole:
            if index.column() == 1:
                if value == Qt.Checked:
                    print("Checked this")
                    self.dataChanged.emit(index, index, [Qt.CheckStateRole])
                    return self._update_active_ports(index, 1)
                else:
                    print("Unchecked this")
                    self.dataChanged.emit(index, index, [Qt.CheckStateRole])
                    return self._update_active_ports(index, 0)
        else:
            return False

    def _update_active_ports(self, index, value) -> bool:
        f_port_num = QSqlField()
        f_port_num.setName("port_num")
        f_port_num.setTableName("Ports")
        f_port_num.setValue(index.row())

        f_active = QSqlField()
        f_active.setName("active")
        f_active.setTableName("Ports")
        f_active.setValue(value)

        rec = QSqlRecord()
        rec.append(f_port_num)
        rec.append(f_active)

        r = self.setRecord(index.row(), rec)
        print(f"Port {index.row() + 1} should now be {value}")

        if r:
            return True
        else:
            return False

Side note, I just noticed this, and it does not seem right at all. Could this be related? If not, lets ignore it for now.

[JonB]

@SIG_KILL said in QSqlTableModel Checkbox applying checkmark when click, and not updating table:

While setRecord does return True, the database never updates.

Concentrate only on this.

        rec = QSqlRecord()
        rec.append(f_port_num)
        rec.append(f_active)

You sure this is right? Maybe it is. I would have used QSqlRecord QSqlTableModel::record(int row) const to get the record I want to change, changed the "active" column value in it, and bool QSqlTableModel::setRecord(int row, const QSqlRecord &values) it back. Safer than relying on your code here having the right columns.

[SIG_KILL]

@JonB Ok this is clearly a part of the problem. And even though I have not reached my goal yet, I can update/read the database from the GUI just by deleting my setData method, as well as changing how I set the record.

I was able to change Port 2 from inactive to active. And that's progress!

I'm going to give you the solution since this did solve the important problem. I will comeback to checkboxes after more reading.

Thanks so much for your assistance Jon! :D

[JonB]

@SIG_KILL said in QSqlTableModel Checkbox applying checkmark when click, and not updating table:

Side note, I just noticed this, and it does not seem right at all. Could this be related? If not, lets ignore it for now.

Glad you seem to be getting somewhere on doing the update.

FYI, your comment earlier about being able to "edit the text next to the checkbox". Because you are using the "checkbox state" of an item to show a checkbox, this technically means you have a normal item (with text) plus a checkbox to the left of it. This is simpler than trying to have an item which is just a checkbox (I think that would have to be done by writing a dedicated QStyledItemDelegate for the column if you want that). It does mean that there is text in that item which the user could edit, as you can see: I'm not sure if there is an easy way of making the text uneditable while leaving the checkbox editable in this case.