How can I fix the search function in my PyQt5 window to show the desired output?

Question

when I search e in the lineEdit inside the window, the desired output does not show

As you can see this are all the products in a database I created Pyqt Window

but then if I search E, I expect the eww product to appear only but instead dmd appears Dmd

here is my code:

from PyQt5 import QtWidgets, uic, QtGui, QtCore 
from PyQt5.QtWidgets import *
import sql_database as sdb

class MainGUI(QMainWindow):
    def __init__(self):
        super(MainGUI, self).__init__()
        uic.loadUi("main_ui.ui", self)
        self.show()

        self.pushButton_4.clicked.connect(self.search)
        self.pushButton.clicked.connect(self.add)
        self.pushButton_5.clicked.connect(self.all_prod)
        self.pushButton_6.clicked.connect(self.delete_all_q)

    def delete_all_q(self):
        sdb.c.execute("SELECT * FROM products")
        database_num_prods = len(sdb.c.fetchall())
        if database_num_prods == 0:
            n = QMessageBox()
            n.setText("There is no Products in the database")
            n.setWindowTitle("Database Empty!")
            n.setWindowIcon(QtGui.QIcon('Logo-short.png'))
            n.setStyleSheet("""color: white;
                                background-color: rgb(0, 90, 225);
                                border-radius: 12px;
                                outline: none;""")
            n.exec_()
        else:
            self.d = DeleteAllGUI()
            self.d.show()

    def all_prod(self):
        sdb.c.execute("SELECT * FROM products")
        prod_li = sdb.c.fetchall()

        if len(prod_li) != 0:
            names = [x[0].lower() for x in prod_li]
            row_count = 0
            for x in range(len(names)):
                    row_count += 1
            self.tableWidget.setRowCount(row_count)

            row = 0
            for x in range(len(names)):
                col = 0
                for y in prod_li[x]:
                    chkBoxItem = QTableWidgetItem(str(y))
                    if prod_li[x].index(y) == 0:
                        chkBoxItem.setFlags(QtCore.Qt.ItemFlag.ItemIsUserCheckable | QtCore.Qt.ItemFlag.ItemIsEnabled)
                        chkBoxItem.setCheckState(QtCore.Qt.CheckState.Unchecked)
                        self.tableWidget.setItem(row, col, chkBoxItem)
                        col += 1
                    else:
                        self.tableWidget.setItem(row, col, chkBoxItem)
                        col += 1
                row += 1

            self.tableWidget.setEditTriggers(QtWidgets.QAbstractItemView.NoEditTriggers)
        else:
            n = QMessageBox()
            n.setText("There is no Products in the database")
            n.setWindowTitle("Database Empty!")
            n.setWindowIcon(QtGui.QIcon('Logo-short.png'))
            n.exec_()

    def add(self):
        self.w = AddProdGUI()
        self.w.show()

    def search(self):
        self.loaddata()


    def loaddata(self):
        rowc = self.tableWidget.rowCount()
        if rowc == 0:
            for x in range(rowc):
                self.tableWidget.removeRow(x)

        sdb.c.execute("SELECT * FROM products")
        prod_li = sdb.c.fetchall()

        names = [x[0].lower() for x in prod_li]
        row_count = 0

        value = self.lineEdit.text()

        for x in range(len(names)):
            if names[x].find(value.lower()) != -1:
                row_count += 1

        self.tableWidget.setRowCount(row_count)
        row = 0
        for x in range(len(names)):
            if names[x].find(value.lower()) != -1:
                col = 0
                for y in prod_li[x]:
                    chkBoxItem = QTableWidgetItem(str(y))
                    if prod_li[x].index(y) == 0:
                        chkBoxItem.setFlags(QtCore.Qt.ItemFlag.ItemIsUserCheckable | QtCore.Qt.ItemFlag.ItemIsEnabled)
                        chkBoxItem.setCheckState(QtCore.Qt.CheckState.Unchecked)
                        self.tableWidget.setItem(row, col, chkBoxItem)
                        col += 1
                    else:
                        self.tableWidget.setItem(row, col, chkBoxItem)
                        col += 1
            row += 1
        self.tableWidget.setEditTriggers(QtWidgets.QAbstractItemView.NoEditTriggers)

i seem to think the code is my fine, is there a problem on how I fetch data in the input or is it with how I fetch data in sql?

From what I can see, it seems that every time that you type something in the QLineEdit, it triggers the `search` function, which reloads the entire table from scratch, correct? That's an expensive approach, based on how many rows you must fetch from the database. If there's 1000 or more, that's the amount of rows you would have to remove/insert/draw every time the user types another character in the line edit. I would suggest to simply hide the rows based on your filter string, such as [written on this answer](https://stackoverflow.com/a/6785516/14956120). — Carl HR, Jun 01 '23 at 13:54
@CarlHR thank you so much, hiding the rows actually fixed it. — Jethro Moses Sala, Jun 01 '23 at 15:20

How can I fix the search function in my PyQt5 window to show the desired output?

0 Answers0