Use QSortFilterProxyModel to filtering results in QTableView

Question

I have one problem,in fact a lot of problems but you can help me around this i hope :)

I'm trying to filter columns from the database and I succeeded in one column but i need to filtering three columns in same time if the results of first =0 go the other one, if second column result=0 go to the third column...

This is code with which i tried: The scripts shows data from a database but does not filter the results. where I'm wrong?

code:

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

def createConnection():
    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("imenik.db")
    if not db.open():
        QtWidgets.QMessageBox.critical(None, "Cannot open database",
                    "Unable to establish a database connection.\n"
                     "This example needs SQLite support. Please read "
                     "the Qt SQL driver documentation for information how "
                     "to build it.\n\n"
                     "Click Cancel to exit.", QtWidgets.QMessageBox.Cancel)
        return False
    return True

class MySortFilterProxyModel(QSortFilterProxyModel):
    def __init__(self, parent=None):
        super(MySortFilterProxyModel, self).__init__(parent)

    def filterAcceptsRow(self, sourceRow, sourceParent):
        index0 = self.sourceModel().index(sourceRow, 0, sourceParent)
        index1 = self.sourceModel().index(sourceRow, 1, sourceParent)
        index2 = self.sourceModel().index(sourceRow, 2, sourceParent)

        return ((self.filterRegExp().indexIn(self.sourceModel().data(index0)) >= 0
                 or self.filterRegExp().indexIn(self.sourceModel().data(index1)) >= 0
                or self.filterRegExp().indexIn(self.sourceModel().data(index2)) >= 0))



class Klasa(QtWidgets.QWidget):
    def __init__(self, parent=None):
        super(Klasa, self).__init__(parent)
        #podesavanje velicine okvira ujedno i velicine treevjuva jer se tri vjuv razvlaci do ivica Boksa

        self.proxyModel = MySortFilterProxyModel(self)
        self.verLW = QtWidgets.QWidget(self)
        self.verLW.setGeometry(QtCore.QRect(60,120,1000,490))
        #dodjeljuje tri vjuv prethodno definisanom okviru
        lay = QtWidgets.QVBoxLayout(self.verLW)
        #kreira liniju za unos teksta za pretragu
        self.lineEdit = QtWidgets.QLineEdit(self)
        self.lineEdit.setGeometry(QtCore.QRect(70, 100, 351, 20))
        self.lineEdit.textChanged.connect(self.textFilterChanged)

        self.tableView = QtWidgets.QTableView()

        #dodjeljuje okviru self.tableView
        lay.addWidget(self.tableView)
        #pravi promjenjivu za pretragu baze podataka
        self.model = QtSql.QSqlQueryModel()
        self.model.setQuery("SELECT Lokacija,Kancelarija,Prezime,Ime,Telefon,Lokal,Fax,Oblast FROM telImenik")
        #upisuje rezultate pretrage baze u self.tableView
        self.tableView.setModel(self.model)

        #odredjuje sirinu kolona u self.tableView-u prva kolona je 0-nulta,id nema potrebe dodavati jer ga sam odredjuje
        self.tableView.setColumnWidth(0, 150)
        self.tableView.setColumnWidth(1, 65)
        self.tableView.setColumnWidth(2, 100)
        self.tableView.setColumnWidth(3, 80)
        self.tableView.setColumnWidth(4, 80)
        self.tableView.setColumnWidth(5, 40)
        self.tableView.setColumnWidth(6, 80)
        self.tableView.setColumnWidth(7, 340)

    def setSourceModel(self):
        self.proxyModel.setSourceModel(self.model)
        self.tableView.setModel(self.proxyModel)

    def textFilterChanged(self,text):
        regExp = QRegExp(self.lineEdit.text())
        self.proxyModel.setFilterRegExp(regExp)


if __name__ == '__main__':
    import sys

    app = QtWidgets.QApplication(sys.argv)
    if not createConnection():
        sys.exit(-1)
    w = Klasa()
    w.show()
    sys.exit(app.exec_())

Answer 1

The problem is simple: you have never used the proxy, so it was never going to filter, in the next part I show you a more elegant solution:

from PyQt5 import QtCore, QtGui, QtWidgets, QtSql

def createConnection():
    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("imenik.db")
    if not db.open():
        QtWidgets.QMessageBox.critical(None, "Cannot open database",
                    "Unable to establish a database connection.\n"
                     "This example needs SQLite support. Please read "
                     "the Qt SQL driver documentation for information how "
                     "to build it.\n\n"
                     "Click Cancel to exit.", QtWidgets.QMessageBox.Cancel)
        return False
    return True

class MySortFilterProxyModel(QtCore.QSortFilterProxyModel):
    def filterAcceptsRow(self, sourceRow, sourceParent):
        ixs = [self.sourceModel().index(sourceRow, i, sourceParent) for i in range(3)]
        return any(self.filterRegExp().indexIn(ix.data()) >= 0 for ix in ixs)


class Klasa(QtWidgets.QWidget):
    def __init__(self, parent=None):
        super(Klasa, self).__init__(parent)
        #podesavanje velicine okvira ujedno i velicine treevjuva jer se tri vjuv razvlaci do ivica Boksa

        self.proxyModel = MySortFilterProxyModel(self)
        #dodjeljuje tri vjuv prethodno definisanom okviru
        lay = QtWidgets.QVBoxLayout(self)
        #kreira liniju za unos teksta za pretragu
        self.lineEdit = QtWidgets.QLineEdit()
        self.lineEdit.textChanged.connect(self.textFilterChanged)

        self.tableView = QtWidgets.QTableView()

        #dodjeljuje okviru self.tableView
        lay.addWidget(self.lineEdit)
        lay.addWidget(self.tableView)
        #pravi promjenjivu za pretragu baze podataka
        self.model = QtSql.QSqlQueryModel()
        self.model.setQuery("SELECT Lokacija,Kancelarija,Prezime,Ime,Telefon,Lokal,Fax,Oblast FROM telImenik")
        #upisuje rezultate pretrage baze u self.tableView
        self.proxyModel.setSourceModel(self.model)
        self.tableView.setModel(self.proxyModel)

        #odredjuje sirinu kolona u self.tableView-u prva kolona je 0-nulta,id nema potrebe dodavati jer ga sam odredjuje
        for i, width in enumerate([150, 65, 100, 80, 90, 40, 80, 340]):
            self.tableView.setColumnWidth(i, width)

    def setSourceModel(self):
        self.proxyModel.setSourceModel(self.model)
        self.tableView.setModel(self.proxyModel)

    def textFilterChanged(self,text):
        regExp = QtCore.QRegExp(self.lineEdit.text(), QtCore.Qt.CaseInsensitive)
        self.proxyModel.setFilterRegExp(regExp)


if __name__ == '__main__':
    import sys

    app = QtWidgets.QApplication(sys.argv)
    if not createConnection():
        sys.exit(-1)
    w = Klasa()
    w.show()
    sys.exit(app.exec_())

Or as I said, the simplest thing is to have SQL filter it using WHERE and LIKE:

from PyQt5 import QtCore, QtGui, QtWidgets, QtSql

def createConnection():
    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("imenik.db")
    if not db.open():
        QtWidgets.QMessageBox.critical(None, "Cannot open database",
                    "Unable to establish a database connection.\n"
                     "This example needs SQLite support. Please read "
                     "the Qt SQL driver documentation for information how "
                     "to build it.\n\n"
                     "Click Cancel to exit.", QtWidgets.QMessageBox.Cancel)
        return False
    return True

class Klasa(QtWidgets.QWidget):
    def __init__(self, parent=None):
        super(Klasa, self).__init__(parent)
        #podesavanje velicine okvira ujedno i velicine treevjuva jer se tri vjuv razvlaci do ivica Boksa
        #dodjeljuje tri vjuv prethodno definisanom okviru
        lay = QtWidgets.QVBoxLayout(self)
        #kreira liniju za unos teksta za pretragu
        self.lineEdit = QtWidgets.QLineEdit()
        self.lineEdit.textChanged.connect(self.textFilterChanged)

        self.tableView = QtWidgets.QTableView()

        #dodjeljuje okviru self.tableView
        lay.addWidget(self.lineEdit)
        lay.addWidget(self.tableView)
        #pravi promjenjivu za pretragu baze podataka
        self.model = QtSql.QSqlQueryModel()
        #upisuje rezultate pretrage baze u self.tableView
        self.tableView.setModel(self.model)

        #odredjuje sirinu kolona u self.tableView-u prva kolona je 0-nulta,id nema potrebe dodavati jer ga sam odredjuje
        for i, width in enumerate([150, 65, 100, 80, 90, 40, 80, 340]):
            self.tableView.setColumnWidth(i, width)
        self.textFilterChanged("")

    def setSourceModel(self):
        self.proxyModel.setSourceModel(self.model)
        self.tableView.setModel(self.proxyModel)

    def textFilterChanged(self,text):
        query = QtSql.QSqlQuery()
        query.prepare("SELECT * FROM telImenik WHERE Lokacija LIKE ? OR Kancelarija LIKE ? OR Prezime LIKE ?")
        query.addBindValue("{}%".format(text))
        query.addBindValue("{}%".format(text))
        query.addBindValue("{}%".format(text))
        query.exec_()
        self.model.setQuery(query)


if __name__ == '__main__':
    import sys

    app = QtWidgets.QApplication(sys.argv)
    if not createConnection():
        sys.exit(-1)
    w = Klasa()
    w.show()
    sys.exit(app.exec_())