How filter SQLite3 table in PyQt5

user_10383535 0
Viewed 997

I'm trying to make a search bar to filter the data in the table but looks like the model ins't linked to the lineEdit the table works but I can't filter database data using it (it works with lists) what is wrong?

class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        mainLayout = QVBoxLayout()
        conn = sqlite3.connect("banco_cadastro.db.db")
        cursor = conn.cursor()
        list = (cursor.fetchall())
        model = QStandardItemModel(len(list), 5)
        model.setHorizontalHeaderLabels(['Clients'])
        for row, company in enumerate(list):
            item = QStandardItem(company)
            model.setItem(row, 0, item)
        filter_proxy_model = QSortFilterProxyModel()
        filter_proxy_model.setSourceModel(model)
        filter_proxy_model.setFilterCaseSensitivity(Qt.CaseInsensitive)
        filter_proxy_model.setFilterKeyColumn(0)
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(779, 693)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.lineEdit = QtWidgets.QLineEdit(self.centralwidget)
        self.lineEdit.textChanged.connect(filter_proxy_model.setFilterRegExp)
        mainLayout.addWidget(self.lineEdit)
        self.tableWidget = QtWidgets.QTableWidget(self.centralwidget)
        self.tableWidget.setObjectName("tableWidget")
        MainWindow.setCentralWidget(self.centralwidget)
        connection = sqlite3.connect('banco_cadastro.db')
        query = "SELECT * FROM cadastro"
        result = connection.execute(query)
        list = (cursor.fetchall())

        for row_number, row_data in enumerate(result):
            self.tableWidget.insertRow(row_number)

            for column_number, data in enumerate(row_data):
                self.tableWidget.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(str(data)))

        connection.close()

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))

if __name__ == "__main__":
    import sys

    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())
pyqt5 qtableview sqlite python
1 Answers

Disclaimer: In this post I am going to focus only on the background objective and not on the errors of the OP (for example combining a QStandardItemModel with QTableWidget, or the useless attempt to use QSortFilterProxyModel that does not connect to anything).

I'm going to take the opportunity to show different ways of how to filter the sqlite tables (the same concepts can be applied to other DBs) using PyQt5 (it also applies to PySide2).

- QTableWidget + sqlite3 module

A possible solution is to iterate over the rows and verify that it meets the desired condition, if it meets then the row will be visible and otherwise the row will be hidden.

import sqlite3

from PyQt5 import QtCore, QtWidgets


class Widget(QtWidgets.QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)

        self.edit = QtWidgets.QLineEdit()
        self.combo = QtWidgets.QComboBox()
        self.table = QtWidgets.QTableWidget()

        grid = QtWidgets.QGridLayout(self)
        grid.addWidget(self.edit, 0, 0)
        grid.addWidget(self.combo, 0, 1)
        grid.addWidget(self.table, 1, 0, 1, 2)

        self.connection = sqlite3.connect("database.db")

        self.populate_table("SELECT * FROM foo_table")
        self.edit.textChanged.connect(self.filter_table)

    def populate_table(self, query, values=None):
        cursor = self.connection.cursor()
        if values is None:
            cursor.execute(query)
        else:
            cursor.execute(query, values)

        name_of_columns = [e[0] for e in cursor.description]
        self.table.setColumnCount(len(name_of_columns))
        self.table.setRowCount(0)
        self.table.setHorizontalHeaderLabels(name_of_columns)
        self.combo.clear()
        self.combo.addItems(name_of_columns)

        for i, row_data in enumerate(cursor.fetchall()):
            self.table.insertRow(self.table.rowCount())
            for j, value in enumerate(row_data):
                item = QtWidgets.QTableWidgetItem()
                item.setData(QtCore.Qt.DisplayRole, value)
                self.table.setItem(i, j, item)

    def filter_table(self, text):
        if text:
            filter_column = self.combo.currentIndex()

            for i in range(self.table.rowCount()):
                item = self.table.item(i, filter_column)
                if self.filter_row(item, text):
                    self.table.showRow(i)
                else:
                    self.table.hideRow(i)
        else:
            for i in range(self.table.rowCount()):
                self.table.showRow(i)

    def filter_row(self, item, text):
        return text in item.text()


def main():
    import sys

    app = QtWidgets.QApplication(sys.argv)
    ex = Widget()
    ex.show()
    sys.exit(app.exec_())


if __name__ == "__main__":
    main()

Another possible solution is to delete all the data and make a new request with the filter.

import sqlite3

from PyQt5 import QtCore, QtWidgets


class Widget(QtWidgets.QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)

        self.edit = QtWidgets.QLineEdit()
        self.combo = QtWidgets.QComboBox()
        self.table = QtWidgets.QTableWidget()

        grid = QtWidgets.QGridLayout(self)
        grid.addWidget(self.edit, 0, 0)
        grid.addWidget(self.combo, 0, 1)
        grid.addWidget(self.table, 1, 0, 1, 2)

        self.connection = sqlite3.connect("database.db")

        self.populate_table("SELECT * FROM foo_table")
        self.edit.textChanged.connect(self.filter_table)

    def populate_table(self, query, values=None):
        cursor = self.connection.cursor()
        if values is None:
            cursor.execute(query)
        else:
            cursor.execute(query, values)

        name_of_columns = [e[0] for e in cursor.description]
        self.table.setColumnCount(len(name_of_columns))
        self.table.setRowCount(0)
        self.table.setHorizontalHeaderLabels(name_of_columns)
        self.combo.clear()
        self.combo.addItems(name_of_columns)

        for i, row_data in enumerate(cursor.fetchall()):
            self.table.insertRow(self.table.rowCount())
            for j, value in enumerate(row_data):
                item = QtWidgets.QTableWidgetItem()
                item.setData(QtCore.Qt.DisplayRole, value)
                self.table.setItem(i, j, item)

    def filter_table(self, text):
        if text:
            self.populate_table(
                "SELECT * FROM foo_table WHERE {} LIKE ?".format(
                    self.combo.currentText()
                ),
                ["%{}%".format(text)],
            )
        else:
            self.populate_table("SELECT * FROM foo_table")


def main():
    import sys

    app = QtWidgets.QApplication(sys.argv)
    ex = Widget()
    ex.show()
    sys.exit(app.exec_())


if __name__ == "__main__":
    main()

- QTableView + QStandardItemModel + sqlite3 module

The same logic as above:

import sqlite3

from PyQt5 import QtCore, QtGui, QtWidgets


class Widget(QtWidgets.QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)

        self.edit = QtWidgets.QLineEdit()
        self.combo = QtWidgets.QComboBox()
        self.table = QtWidgets.QTableView()
        self.model = QtGui.QStandardItemModel()
        self.table.setModel(self.model)

        grid = QtWidgets.QGridLayout(self)
        grid.addWidget(self.edit, 0, 0)
        grid.addWidget(self.combo, 0, 1)
        grid.addWidget(self.table, 1, 0, 1, 2)

        self.connection = sqlite3.connect("database.db")

        self.populate_table("SELECT * FROM foo_table")
        self.edit.textChanged.connect(self.filter_table)

    def populate_table(self, query):
        cursor = self.connection.cursor()
        cursor.execute(query)

        name_of_columns = [e[0] for e in cursor.description]
        self.model.setColumnCount(len(name_of_columns))
        self.model.setRowCount(0)
        self.model.setHorizontalHeaderLabels(name_of_columns)
        self.combo.clear()
        self.combo.addItems(name_of_columns)

        for i, row_data in enumerate(cursor.fetchall()):
            self.model.insertRow(self.model.rowCount())
            for j, value in enumerate(row_data):
                item = QtGui.QStandardItem()
                item.setData(value, QtCore.Qt.DisplayRole)
                self.model.setItem(i, j, item)

    def filter_table(self, text):
        if text:
            filter_column = self.combo.currentIndex()

            for i in range(self.model.rowCount()):
                item = self.model.item(i, filter_column)
                if self.filter_row(item, text):
                    self.table.showRow(i)
                else:
                    self.table.hideRow(i)
        else:
            for i in range(self.model.rowCount()):
                self.table.showRow(i)

    def filter_row(self, item, text):
        return text in item.text()


def main():
    import sys

    app = QtWidgets.QApplication(sys.argv)
    ex = Widget()
    ex.show()
    sys.exit(app.exec_())


if __name__ == "__main__":
    main()

import sqlite3

from PyQt5 import QtCore, QtGui, QtWidgets


class Widget(QtWidgets.QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)

        self.edit = QtWidgets.QLineEdit()
        self.combo = QtWidgets.QComboBox()
        self.table = QtWidgets.QTableView()
        self.model = QtGui.QStandardItemModel()
        self.table.setModel(self.model)

        grid = QtWidgets.QGridLayout(self)
        grid.addWidget(self.edit, 0, 0)
        grid.addWidget(self.combo, 0, 1)
        grid.addWidget(self.table, 1, 0, 1, 2)

        self.connection = sqlite3.connect("database.db")

        self.populate_table("SELECT * FROM foo_table")
        self.edit.textChanged.connect(self.filter_table)

    def populate_table(self, query, values=None):
        cursor = self.connection.cursor()
        if values is None:
            cursor.execute(query)
        else:
            cursor.execute(query, values)

        name_of_columns = [e[0] for e in cursor.description]
        self.model.setColumnCount(len(name_of_columns))
        self.model.setRowCount(0)
        self.model.setHorizontalHeaderLabels(name_of_columns)
        self.combo.clear()
        self.combo.addItems(name_of_columns)

        for i, row_data in enumerate(cursor.fetchall()):
            self.model.insertRow(self.model.rowCount())
            for j, value in enumerate(row_data):
                item = QtGui.QStandardItem()
                item.setData(value, QtCore.Qt.DisplayRole)
                self.model.setItem(i, j, item)

    def filter_table(self, text):
        if text:
            self.populate_table(
                "SELECT * FROM foo_table WHERE {} LIKE ?".format(
                    self.combo.currentText()
                ),
                ["%{}%".format(text)],
            )
        else:
            self.populate_table("SELECT * FROM foo_table")


def main():
    import sys

    app = QtWidgets.QApplication(sys.argv)
    ex = Widget()
    ex.show()
    sys.exit(app.exec_())


if __name__ == "__main__":
    main()

There is also the option to use a QSortFilterProxyModel:

import sqlite3

from PyQt5 import QtCore, QtGui, QtWidgets


class Widget(QtWidgets.QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)

        self.edit = QtWidgets.QLineEdit()
        self.combo = QtWidgets.QComboBox()
        self.table = QtWidgets.QTableView()
        self.model = QtGui.QStandardItemModel()

        self.proxy = QtCore.QSortFilterProxyModel()
        self.proxy.setSourceModel(self.model)
        self.proxy.setFilterCaseSensitivity(QtCore.Qt.CaseInsensitive)
        self.table.setModel(self.proxy)

        grid = QtWidgets.QGridLayout(self)
        grid.addWidget(self.edit, 0, 0)
        grid.addWidget(self.combo, 0, 1)
        grid.addWidget(self.table, 1, 0, 1, 2)

        self.connection = sqlite3.connect("database.db")

        self.populate_table("SELECT * FROM foo_table")
        self.combo.currentIndexChanged.connect(self.proxy.setFilterKeyColumn)
        self.edit.textChanged.connect(self.proxy.setFilterRegExp)

    def populate_table(self, query, values=None):
        cursor = self.connection.cursor()
        if values is None:
            cursor.execute(query)
        else:
            cursor.execute(query, values)

        name_of_columns = [e[0] for e in cursor.description]
        self.model.setColumnCount(len(name_of_columns))
        self.model.setRowCount(0)
        self.model.setHorizontalHeaderLabels(name_of_columns)
        self.combo.clear()
        self.combo.addItems(name_of_columns)

        for i, row_data in enumerate(cursor.fetchall()):
            self.model.insertRow(self.model.rowCount())
            for j, value in enumerate(row_data):
                item = QtGui.QStandardItem()
                item.setData(value, QtCore.Qt.DisplayRole)
                self.model.setItem(i, j, item)


def main():
    import sys

    app = QtWidgets.QApplication(sys.argv)
    ex = Widget()
    ex.show()
    sys.exit(app.exec_())


if __name__ == "__main__":
    main()

- QTableView + QSqlQueryModel

In this case the best option is to make the filter using sql changing the query:

from PyQt5 import QtCore, QtGui, QtWidgets, QtSql


class Widget(QtWidgets.QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)

        self.edit = QtWidgets.QLineEdit()
        self.combo = QtWidgets.QComboBox()
        self.table = QtWidgets.QTableView()
        self.model = QtSql.QSqlQueryModel()
        self.table.setModel(self.model)

        grid = QtWidgets.QGridLayout(self)
        grid.addWidget(self.edit, 0, 0)
        grid.addWidget(self.combo, 0, 1)
        grid.addWidget(self.table, 1, 0, 1, 2)

        self.populate_table("SELECT * FROM foo_table")
        self.edit.textChanged.connect(self.filter_table)

    def populate_table(self, query, values=None):
        q = QtSql.QSqlQuery(query)
        if values is not None:
            for value in values:
                q.addBindValue(value)
                print(value)
        if not q.exec_():
            print(q.lastError().text())
        self.model.setQuery(q)
        self.combo.clear()
        for i in range(self.model.columnCount()):
            self.combo.addItem(self.model.headerData(i, QtCore.Qt.Horizontal))

    def filter_table(self, text):
        if text:
            self.populate_table(
                "SELECT * FROM foo_table WHERE {} LIKE ?".format(
                    self.combo.currentText()
                ),
                ["%{}%".format(text)],
            )
        else:
            self.populate_table("SELECT * FROM foo_table")


def main():
    import sys

    app = QtWidgets.QApplication(sys.argv)

    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("database.db")
    if not db.open():
        sys.exit(-1)

    ex = Widget()
    ex.show()
    sys.exit(app.exec_())


if __name__ == "__main__":
    main()

Another option is to apply the techniques of hiding rows or using a QSortFilterProxyModel.

- QTableView + QSqlTableModel

In this case the setFilter method should be used:

from PyQt5 import QtCore, QtGui, QtWidgets, QtSql


class Widget(QtWidgets.QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)

        self.edit = QtWidgets.QLineEdit()
        self.combo = QtWidgets.QComboBox()
        self.table = QtWidgets.QTableView()
        self.model = QtSql.QSqlTableModel()
        self.table.setModel(self.model)

        grid = QtWidgets.QGridLayout(self)
        grid.addWidget(self.edit, 0, 0)
        grid.addWidget(self.combo, 0, 1)
        grid.addWidget(self.table, 1, 0, 1, 2)

        self.model.setTable("foo_table")
        self.model.select()

        self.combo.clear()
        for i in range(self.model.columnCount()):
            self.combo.addItem(self.model.headerData(i, QtCore.Qt.Horizontal))

        self.edit.textChanged.connect(self.filter_table)

    def filter_table(self, text):
        f = " {} LIKE '%{}%'".format(self.combo.currentText(), text) if text else text
        self.model.setFilter(f)
        self.model.select()


def main():
    import sys

    app = QtWidgets.QApplication(sys.argv)

    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("database.db")
    if not db.open():
        sys.exit(-1)

    ex = Widget()
    ex.show()
    sys.exit(app.exec_())


if __name__ == "__main__":
    main()

Another option is to apply the techniques of hiding rows or using a QSortFilterProxyModel.

user_66225870
Related