How to create filter rows for Qtableview with using QSqlTableModel.?

Question

Is it possible to create row filter for Qtableview with QSqlTableModel. I am sing " QSqlTableModel" to show the data on Qtableview from SQLite thats working. But i am trying to filter rows. When i am executing the code i am getting below error is

line 44, in for row in range(self.model.rowCount()) AttributeError: 'QSqlTableModel' object has no attribute 'item'

I Tried but did not get any solution. Is it possible to make this with QSqlTableModel? i want to show filtered items from tableview when i clicked on any of the columns of Qtableview. I am using Qtableview with QSqlTableModel.

I want to do like shown in the image is

from PyQt5 import QtCore, QtGui, QtWidgets, QtSql
from PyQt5.QtSql import *


class myWindow(QtWidgets.QMainWindow):
    def __init__(self, parent=None):
        super(myWindow, self).__init__(parent)
        self.centralwidget  = QtWidgets.QWidget(self)
        self.view           = QtWidgets.QTableView(self.centralwidget)

        self.gridLayout = QtWidgets.QGridLayout(self.centralwidget)

        self.gridLayout.addWidget(self.view, 1, 0, 1, 3)

        self.setCentralWidget(self.centralwidget)

       # self.model = QtGui.QStandardItemModel(self)

        db = QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName("model.db")
        db.open()

        self.model = QSqlTableModel(self)
        self.model.setTable("sheet")
        self.model.select()
        self.view.setModel(self.model)

        self.proxy = QtCore.QSortFilterProxyModel(self)
        self.proxy.setSourceModel(self.model)

        self.view.setModel(self.proxy)


        self.horizontalHeader = self.view.horizontalHeader()
        self.horizontalHeader.sectionClicked.connect(self.on_view_horizontalHeader_sectionClicked)

    @QtCore.pyqtSlot(int)
    def on_view_horizontalHeader_sectionClicked(self, logicalIndex):
        self.logicalIndex   = logicalIndex
        self.menuValues     = QtWidgets.QMenu(self)
        self.signalMapper   = QtCore.QSignalMapper(self)  

        valuesUnique = [    self.model.item(row, self.logicalIndex).text()
                            for row in range(self.model.rowCount())
                            ]

        actionAll = QtWidgets.QAction("All", self)
        actionAll.triggered.connect(self.on_actionAll_triggered)
        self.menuValues.addAction(actionAll)
        self.menuValues.addSeparator()


        for actionNumber, actionName in enumerate(sorted(list(set(valuesUnique)))):              
            action = QtWidgets.QAction(actionName, self)
            self.signalMapper.setMapping(action, actionNumber)  
            action.triggered.connect(self.signalMapper.map)  
            self.menuValues.addAction(action)

        self.signalMapper.mapped.connect(self.on_signalMapper_mapped)  

        headerPos = self.view.mapToGlobal(self.horizontalHeader.pos())        

        posY = headerPos.y() + self.horizontalHeader.height()
        posX = headerPos.x() + self.horizontalHeader.sectionPosition(self.logicalIndex)

        self.menuValues.exec_(QtCore.QPoint(posX, posY))

    @QtCore.pyqtSlot()
    def on_actionAll_triggered(self):
        filterColumn = self.logicalIndex
        filterString = QtCore.QRegExp(  "",
                                        QtCore.Qt.CaseInsensitive,
                                        QtCore.QRegExp.RegExp
                                        )

        self.proxy.setFilterRegExp(filterString)
        self.proxy.setFilterKeyColumn(filterColumn)

    @QtCore.pyqtSlot(int)
    def on_signalMapper_mapped(self, i):
        stringAction = self.signalMapper.mapping(i).text()
        filterColumn = self.logicalIndex
        filterString = QtCore.QRegExp(  stringAction,
                                        QtCore.Qt.CaseSensitive,
                                        QtCore.QRegExp.FixedString
                                        )

        self.proxy.setFilterRegExp(filterString)
        self.proxy.setFilterKeyColumn(filterColumn)


if __name__ == "__main__":
    import sys

    app  = QtWidgets.QApplication(sys.argv)
    main = myWindow()
    main.show()
    main.resize(400, 600)
    sys.exit(app.exec_())

eyllanesc · Accepted Answer · 2020-08-02 16:40:27Z

3

+50

The QSortFilterProxyModel is implemented to compare strings but in your case you have different types of values so a custom filter will have to be implemented.

To handle the different types of data associated with each option, then in QAction the value is stored and accessed using setData() and data(), respectively.

On the other hand, I consider it unnecessary to use QSignalMapper or the signals since the exec_() Method returns the QAction if it is selected or None otherwise.

To access the value of each item, the model's data() method must be used:

from PyQt5 import QtCore, QtGui, QtWidgets, QtSql


class FilterProxyModel(QtCore.QSortFilterProxyModel):
    def __init__(self, parent=None):
        super().__init__(parent)
        self._filter_value = None

    @property
    def filter_value(self):
        return self._filter_value

    @filter_value.setter
    def filter_value(self, value):
        self._filter_value = value
        self.invalidateFilter()

    def filterAcceptsRow(self, sourceRow, sourceParent):
        if self.filter_value is None:
            return True
        value = (
            self.sourceModel()
            .index(sourceRow, self.filterKeyColumn(), sourceParent)
            .data(self.filterRole())
        )
        return value == self.filter_value


class myWindow(QtWidgets.QMainWindow):
    def __init__(self, parent=None):
        super(myWindow, self).__init__(parent)
        self.centralwidget = QtWidgets.QWidget(self)
        self.view = QtWidgets.QTableView(self.centralwidget)

        self.gridLayout = QtWidgets.QGridLayout(self.centralwidget)

        self.gridLayout.addWidget(self.view, 1, 0, 1, 3)

        self.setCentralWidget(self.centralwidget)

        db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName("model.db")
        db.open()

        self.model = QtSql.QSqlTableModel(self)
        self.model.setTable("sheet")
        self.model.select()
        self.view.setModel(self.model)

        self.proxy = FilterProxyModel(self)
        self.proxy.setSourceModel(self.model)

        self.view.setModel(self.proxy)

        self.horizontalHeader = self.view.horizontalHeader()
        self.horizontalHeader.sectionClicked.connect(
            self.on_view_horizontalHeader_sectionClicked
        )

    @QtCore.pyqtSlot(int)
    def on_view_horizontalHeader_sectionClicked(self, logicalIndex):
        menu = QtWidgets.QMenu(self)

        values = []

        for row in range(self.model.rowCount()):
            value = self.model.index(row, logicalIndex).data(self.proxy.filterRole())
            values.append(value)

        action_all = QtWidgets.QAction("All", self)
        action_all.setData(None)
        menu.addAction(action_all)
        menu.addSeparator()

        for value in sorted(list(set(values))):
            action = QtWidgets.QAction(str(value), self)
            action.setData(value)
            menu.addAction(action)

        headerPos = self.view.mapToGlobal(self.horizontalHeader.pos())
        posY = headerPos.y() + self.horizontalHeader.height()
        posX = headerPos.x() + self.horizontalHeader.sectionPosition(logicalIndex)

        action = menu.exec_(QtCore.QPoint(posX, posY))
        if action is not None:
            self.proxy.setFilterKeyColumn(logicalIndex)
            self.proxy.filter_value = action.data()


if __name__ == "__main__":
    import sys

    app = QtWidgets.QApplication(sys.argv)
    main = myWindow()
    main.show()
    main.resize(400, 600)
    sys.exit(app.exec_())

edited Aug 2, 2020 at 16:40

answered Aug 2, 2020 at 15:09

eyllanesc

246k19 gold badges205 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user3030327 Over a year ago

Second column has only numbers. Getting the error. action = QtWidgets.QAction(actionName, self) TypeError: arguments did not match any overloaded call: QAction(parent: QObject = None): argument 1 has unexpected type 'int' QAction(str, parent: QObject = None): argument 1 has unexpected type 'int' QAction(QIcon, str, parent: QObject = None): argument 1 has unexpected type 'int'

eyllanesc Over a year ago

@user3030327 the image you show does not represent what you point out, you could share the model.db file

user3030327 Over a year ago

Yes. I did not share the database content that is my mistake. Actually i though that was an example. Actually my database content have some columns text and as well as some column have only Numeric values also.

eyllanesc Over a year ago

@user3030327 try again

eyllanesc Over a year ago

@user3030327 The images do not help, try my new solution, if it works then you do not need to share the .db, if it does not work then I will need your .db so if you could share it through drive, drobox, etc or other similar means.

|

Collectives™ on Stack Overflow

How to create filter rows for Qtableview with using QSqlTableModel.?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related