How use QSortFilterProxyModel in database?

Question

I have a QTableWidget with data from database and I want to search its content in real time (when I'm typing a letter he show me results)? Every help is welcome. Thanks a lot.

This is my code:

class Filter:
    def __init__(self):
        object.__init__(self)
        self.db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName("baza.db")
        self.filter_model= QSortFilterProxyModel()



    def sqlPodaci(self,okvir):
        okvir.setObjectName("Okvir aplikacije")
        okvir.resize(700,320)
        line_edit = QLineEdit(okvir)
        line_edit.setGeometry(20, 20, 205, 23)
        self.tableWidget = QtWidgets.QTableWidget(okvir)
        self.tableWidget.setGeometry(QtCore.QRect(20, 50, 620, 201))
        self.tableWidget.setAlternatingRowColors(True)
        self.tableWidget.setRowCount(3)
        self.tableWidget.horizontalHeader().setVisible(True)
        self.tableWidget.verticalHeader().setVisible(False)

        ###
        self.filter_model.setFilterKeyColumn(1)
        line_edit.textChanged.connect(self.filter_model.setFilterRegExp)
        ####
        self.Ucitavanje_podataka()

This is the code for reading data from the database:

def Ucitavanje_podataka(self):

status = self.db.open()
if status == False:
    QtWidgets.QMessageBox.warning(self, "Error", self.db.lastError().text(), QtWidgets.QMessageBox.Discard)
else:
    self.tableWidget.setColumnCount(6)
    self.tableWidget.setHorizontalHeaderLabels(['Id','Ime','Prezime','Godine','Adresa','Plata'])

    row = 0
    sql = " SELECT * FROM Zaposleni"
    query = QtSql.QSqlQuery(sql)
    while query.next():
        self.tableWidget.insertRow(row)
        ID=QtWidgets.QTableWidgetItem(str(query.value(0)))
        Ime = QtWidgets.QTableWidgetItem(str(query.value(1)))
        Prezime = QtWidgets.QTableWidgetItem(str(query.value(2)))
        Godine = QtWidgets.QTableWidgetItem(str(query.value(3)))
        Adresa = QtWidgets.QTableWidgetItem(str(query.value(4)))
        Plata = QtWidgets.QTableWidgetItem(str(query.value(5)))

        self.tableWidget.setItem(row,0,ID)
        self.tableWidget.setItem(row, 1, Ime)
        self.tableWidget.setItem(row, 2, Prezime)
        self.tableWidget.setItem(row, 3, Godine)
        self.tableWidget.setItem(row, 4, Adresa)
        self.tableWidget.setItem(row, 5, Plata)
        row=row+1

Answer 1

QTableWidget has a preset model that can not be replaced so using QSortFilterProxyModel would be impossible, so you should use QTableView, a possible solution to your main problem that is filtering is to use only QSqlQueryModel and make the filter using the LIKE command.

from PyQt5 import QtWidgets, QtSql

def createConnection():
    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("baza.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 Widget(QtWidgets.QWidget):
    def __init__(self, parent=None):
        super(Widget, self).__init__(parent)
        lay = QtWidgets.QVBoxLayout(self)
        lineEdit = QtWidgets.QLineEdit()
        tableView = QtWidgets.QTableView()
        lay.addWidget(lineEdit)
        lay.addWidget(tableView)
        lineEdit.textChanged.connect(self.onTextChanged)

        self.model = QtSql.QSqlQueryModel()
        tableView.setModel(self.model)
        self.onTextChanged("")

    def onTextChanged(self, text):
        query = QtSql.QSqlQuery()
        query.prepare("SELECT * FROM Zaposleni WHERE Ime LIKE ?")
        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 = Widget()
    w.show()
    sys.exit(app.exec_())

In the previous example, we want the word to start with the text of the QLineEdit, if you want to find if it contains the word you must replace it with:

query.addBindValue("%{}%".format(text))

---

If you still want to use QSortFilterProxyModel you can see the following example:

from PyQt5 import QtCore, QtWidgets, QtSql

def createConnection():
    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("baza.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 Widget(QtWidgets.QWidget):
    def __init__(self, parent=None):
        super(Widget, self).__init__(parent)
        lay = QtWidgets.QVBoxLayout(self)
        lineEdit = QtWidgets.QLineEdit()
        tableView = QtWidgets.QTableView()
        lay.addWidget(lineEdit)
        lay.addWidget(tableView)
        lineEdit.textChanged.connect(self.onTextChanged)

        self.model = QtSql.QSqlQueryModel()
        self.model.setQuery("SELECT * FROM Zaposleni")

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

        tableView.setModel(self.proxy)

    def onTextChanged(self, text):
        regex = "^{}".format(text)
        self.proxy.setFilterRegExp(QtCore.QRegExp(regex, QtCore.Qt.CaseInsensitive))


if __name__ == '__main__':
    import sys

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

In the previous example, we want the word to start with the text of the QLineEdit, if you want to find if it contains the word you must replace it with:

regex = "[{}]".format(text) if text else ""