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

0

Issue

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

enter image description here

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

Solution

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

Answered By – eyllanesc

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More