pyqt pyqt5 qtablewidget qtablewidgetitem

How does sibling or siblingAtRow() function works to retrieve the value from hidden Column in QTableWidget?

I have a database from which data is coming into a QTableWidget. The table in the database has the following Columns,

ID (Primary key, auto-increment value)
Name
Location

The QTableWidget has the following columns (that I have added)

ID (this column, I have hidden. and it contains the value of "ID" column from the Database Table)
Sr # (Represents the Row Number of the table)
Name (Contains "name" from the database table)
Location (Contains "Location from the database table)
Actions (Contains a Delete Button for that Row)

By hidden, I mean to say that I have made this column hidden using the folliwng command,

self.ui.table.setColumnHidden(0, True);

This is how I am populating my QTableWidget and creating a Delete Function,

    def get_data(self):
        mycursor = self.DB.cursor()
        Subquery = "select id, name, location "
        Subquery += " from tbl_person"
        mycursor.execute(Subquery)
        numcols = len(mycursor.fetchall()[0])
        mycursor.execute(Subquery)
        numrows = len(mycursor.fetchall())
        self.ui.table.setRowCount(numrows)
        self.ui.table.setColumnCount(numcols+2)
        mycursor.execute(Subquery)
        tablerow = 0
        for row in mycursor.fetchall():
            layout = QHBoxLayout()
            layout.setContentsMargins(0, 0, 0, 0)
            layout.setSpacing(0)
            delete_button = QPushButton("Delete Data")
            delete_button.clicked.connect(self.executeDeleteFunction)
            # delete_button.setStyleSheet(delete_push_button) -> Only for styling
            self.ui.table.setItem(tablerow, 0, PySide2.QtWidgets.QTableWidgetItem(str(row[0])))
            self.ui.table.setItem(tablerow, 1, PySide2.QtWidgets.QTableWidgetItem(str(tablerow+1)))
            self.ui.table.setItem(tablerow, 2, PySide2.QtWidgets.QTableWidgetItem(str(row[1])))
            self.ui.table.setItem(tablerow, 3, PySide2.QtWidgets.QTableWidgetItem(str(row[2])))
            self.ui.table.setCellWidget(tablerow, 4, delete_button)
            tablerow += 1
        self.ui.table.setColumnHidden(0, True)
        #self.ui.registered_table.horizontalHeader().setSectionResizeMode(PySide2.QtWidgets.QHeaderView.Stretch)
        self.ui.table.resizeColumnsToContents()

def executeDeleteFunction(self):
    self.person_id = self.ui.table.selectionModel().selectedIndexes()[0]
    self.person_id = self.person_id.row()
    mycursor = self.DB.cursor()
    sql = "delete from tbl_person where id = %s"
    val = (id, )
    mycursor.execute(sql, val)
    print("Deletion Successful")

On the Deletion Function, what this code does is basically gets the value of the **Sr # ** Column from the QTableWidget and deletes the data according to that, i.e. it is getting me the value from the visible first column and not the actual first column. But, I want the data from the "ID" column of the QTableWidget which is hidden

I tried to look up on how to get the value from the first hidden column on the QTableWidget and ended up with this link: How to get data from hidden 'id' column in QtableWidget

This apparently solves my issue but I can not seem to make it work for my code. I don't want to retrieve values of multiple Rows but only of one row so how do I do this (as I am only deleting one row. But in the question mentioned, I believe that it is getting data from multiple rows due to that for each loop)?

Moreover, I tried to find help regarding the functionality of sibling function (which is provided in the answer of above question) however I could not find any good resource on this function (i.e. how to use this, or some practical example and etc.)

I tried the following with Sibling function to obtain the value of first hidden column of the Selected Row but it did not work,

self.value = self.table.selectedItems()[0]
self.value = sibling(self.value.row(), 0)

Solution

There are some conceptual problems with the given code.

First of all, the QtSql module should be preferred instead of artificially creating a model. For basic tables, QSqlTableModel is fine enough, while for custom queries, QSqlQueryModel is a good choice.

Now the problem is that UI-based selection is always based on visible items: if you select a row in a view that has hidden columns, you will not get the hidden indexes that belong to those columns.

In order to get the indexes (as in QModelIndex) of hidden columns on a table widget, the only way is the same for a table view: you need to access the model and get the index for the row, or you get the actual model index and then get the sibling (which is conceptually the same, as the underlying function does):

    item = self.table.selectedItems()[0]
    index = self.table.indexForItem(index)
    firstRowIndex = index.sibling(index.row(), 0)
    sqlIndex = firstRowIndex.data() # might be a string

Note that you can also use siblingAtColumn():

    firstRowIndex = index.siblingAtColumn(0)

That's because when you create QTableWidget items, you're actually creating a new model, and the row for that model doesn't reflect the actual "row" of that index in the source model; items in the second row will return 1 for row(), even if their actual row is different, and that's because that item has been added as second to the table widget, since it's the second item in the query.

So, the solution is that you either get the incremental row value for the first column index sibling, or you use one of the predefined Sql models.

For simple models, the latter solution is fine enough, but if you need more complex models, the first is certainly more accurate and reliable.