PyQT5/QT Designer - Threading with Signal/Slots

Question

I wrote a PyQt5 app that searches user inputs in a SQL database. Everything works but I've had no luck trying to add a worker thread to handle the search process and prevent the app from becoming unresponsive. I'm using QT Designer to generate the code for the UI which consists of a main window, QLineEdit boxes for inputs and a QPushButton to start searching. As for the progress bar (and Cancel button to stop the search), I'm considering two options: have it "anchored" in the main window or have it popped-up in a dialog box. In the QMainWindow class, I have defined a function to grab the user inputs (set_validators) and a function to process the search and display the results (save_pushed). I understand the basis of Signal/Slot and I've looked at several examples but I'm just not sure how to put it all together. Part of the issue is the variable scope I think. Can anyone provide general guidance?

QA1, QA2, QA3

#!/usr/bin/env python

import pyodbc
import sys

from PyQt5.QtWidgets import QMainWindow, QApplication, QTableWidgetItem, QMessageBox
from PyQt5.QtGui import QRegExpValidator
from PyQt5.QtCore import QRegExp
from PyQt5 import uic

from ui_pysearch import Ui_pySearch

class pySearchMain(QMainWindow):
    def __init__(self):
        QMainWindow.__init__(self)
        self.ui = Ui_pySearch()
        self.ui.setupUi(self)
        self.ui.tableWidget_ResultsIncident.setHorizontalHeaderLabels(["A", "B", "C"])
        self.ui.tableWidget_ResultsCompany.setHorizontalHeaderLabels(["A", "B", "C"])

        self._set_validators()
        self.ui.Button_Search.clicked.connect(self._save_pushed)

    def _set_validators(self):
        reg_ex = QRegExp(r"[A-Za-z0-9]{0,6}")
        val_company_custid = QRegExpValidator(reg_ex, self.ui.Input_lineEdit_CompanyPIN)
        self.ui.Input_lineEdit_CompanyPIN.setValidator(val_company_custid)

        reg_ex = QRegExp(r"[A-Za-z ]{0,25}")
        val_company_name = QRegExpValidator(reg_ex, self.ui.Input_lineEdit_CompanyName)
        self.ui.Input_lineEdit_CompanyName.setValidator(val_company_name)

        reg_ex = QRegExp(r"[A-Za-z ]{0,25}")
        val_incident_service = QRegExpValidator(reg_ex, self.ui.Input_lineEdit_IncidentService)
        self.ui.Input_lineEdit_IncidentService.setValidator(val_incident_service)

    def _save_pushed(self):

        self.ui.tableWidget_ResultsIncident.clearContents()
        self.ui.tableWidget_ResultsCompany.clearContents()

        Company_CustID = self.ui.Input_lineEdit_CompanyPIN.text()
        Company_Name = self.ui.Input_lineEdit_CompanyName.text()
        Incident_Service = self.ui.Input_lineEdit_IncidentService.text()

        Set_Company_RecID = set()
        Set_Company_CustID = set()
        Set_Company_CustID_Substring = set()
        Set_Company_Name = set()
        Set_Company_Name_Substring = set()
        Set_IncidentNumber_CompanyName = set()
        Set_IncidentNumber_Service = set()

        Match_Input_lineEdit = 1
        Match_RecID_CompanyCustID = -1
        Match_RecID_CompanyName = -1

        if len(str(Company_CustID)) == 6:
            with pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=ServerIP;DATABASE=Sqldbname;UID=db_username;PWD=db_password') as DB_Connect:
                SearchCursor = DB_Connect.cursor()
                SearchCursor.execute("select RecId, CustID, Name from Company")
                for row in SearchCursor.fetchall():
                    if str(row[1]).lower() == str(Company_CustID).lower():
                        Match_RecID_CompanyCustID = row[0]
                        Set_Company_RecID.add(str(row[1]))
        if len(str(Company_CustID)) > 1:
            with pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=ServerIP;DATABASE=Sqldbname;UID=db_username;PWD=db_password') as DB_Connect:
                SearchCursor = DB_Connect.cursor()
                SearchCursor.execute("select RecId, CustID, Name from Company")
                for row in SearchCursor.fetchall():
                    if str(row[1]).lower().rfind(str(Company_CustID).lower()) != -1:
                        Set_Company_CustID_Substring.add(row[0])
                        Set_Company_RecID.add(str(row[1]))
        if len(str(Company_Name)) > 2:
            with pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=ServerIP;DATABASE=Sqldbname;UID=db_username;PWD=db_password') as DB_Connect:
                SearchCursor = DB_Connect.cursor()
                SearchCursor.execute("select RecId, CustID, Name from Company")
                for row in SearchCursor.fetchall():
                    if str(row[2]).lower() == str(Company_Name).lower():
                        Match_RecID_CompanyName = row[0]
                        Set_Company_RecID.add(str(row[1]))
                    if str(row[2]).lower().rfind(str(Company_Name).lower()) != -1:
                        Set_Company_Name_Substring.add(row[0])
                        Set_Company_RecID.add(str(row[1]))
        if Match_RecID_CompanyCustID != -1:
            with pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=ServerIP;DATABASE=Sqldbname;UID=db_username;PWD=db_password') as DB_Connect:
                SearchCursor = DB_Connect.cursor()
                SearchCursor.execute("select IncidentNumber from Incident")
                for row in SearchCursor.fetchall():
                    if row[0] == Match_RecID_CompanyCustID:
                        Set_Company_CustID.add(int(row[0]))
        if Match_RecID_CompanyName != -1:
            with pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=ServerIP;DATABASE=Sqldbname;UID=db_username;PWD=db_password') as DB_Connect:
                SearchCursor = DB_Connect.cursor()
                SearchCursor.execute("select IncidentNumber, CompanyLink_RecID from Incident")
                for row in SearchCursor.fetchall():
                    if row[1] == Match_RecID_CompanyName:
                        Set_Company_Name.add(int(row[0]))
        if len(str(Company_Name)) > 2:
            with pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=ServerIP;DATABASE=Sqldbname;UID=db_username;PWD=db_password') as DB_Connect:
                SearchCursor = DB_Connect.cursor()
                SearchCursor.execute("select IncidentNumber, CompanyName from Incident")
                for row in SearchCursor.fetchall():
                    if str(row[1]).lower() == str(Company_Name).lower():
                        Set_IncidentNumber_CompanyName.add(int(row[0]))
                    if str(row[1]).lower().rfind(str(Company_Name).lower()) != -1:
                        Set_IncidentNumber_CompanyName.add(int(row[0]))
        if len(Incident_Service) > 2:
            with pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=ServerIP;DATABASE=Sqldbname;UID=db_username;PWD=db_password') as DB_Connect:
                SearchCursor = DB_Connect.cursor()
                SearchCursor.execute("select IncidentNumber, Service from Incident")
                for row in SearchCursor.fetchall():
                    if (row[1].lower()).rfind(Incident_Service.lower()) != -1:
                        Set_IncidentNumber_Service.add(int(row[0]))
        if len(Set_Company_CustID_Substring) != 0:
            with pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=ServerIP;DATABASE=Sqldbname;UID=db_username;PWD=db_password') as DB_Connect:
                SearchCursor = DB_Connect.cursor()
                SearchCursor.execute("select IncidentNumber, CompanyLink_RecID from Incident")
                for row in SearchCursor.fetchall():
                    for Set_Company_CustID_Substring_Item in Set_Company_CustID_Substring:
                        if row[1] == Set_Company_CustID_Substring_Item:
                            Set_Company_CustID.add(int(row[0]))
        if len(Set_Company_Name_Substring) != 0:
            with pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=ServerIP;DATABASE=Sqldbname;UID=db_username;PWD=db_password') as DB_Connect:
                SearchCursor = DB_Connect.cursor()
                SearchCursor.execute("select IncidentNumber, CompanyLink_RecID from Incident")
                for row in SearchCursor.fetchall():
                    for Set_Company_Name_Substring_Item in Set_Company_Name_Substring:
                        if row[1] == Set_Company_Name_Substring_Item:
                            Set_Company_Name.add(int(row[0]))


        if len(Set_Company_CustID) == 0 and len(Company_CustID) != 0:
            Match_Input_lineEdit = 0
        if len(Set_Company_Name) == 0 and len(Company_Name) != 0:
            Match_Input_lineEdit = 0
        if len(Set_IncidentNumber_Service) == 0 and len(Incident_Service) != 0:
            Match_Input_lineEdit = 0
        if len(Set_IncidentNumber_CompanyName) == 0 and len(Company_Name) != 0:
            Match_Input_lineEdit = 0
            Match_Input_lineEdit = 0
        if Match_Input_lineEdit == 0:
            alert = QMessageBox()
            alert.setText('No results')
            self.ui.tableWidget_ResultsIncident.clearContents()
            self.ui.tableWidget_ResultsIncident.setRowCount(0)
            alert.exec_()
            return
        List_Of_All_Sets = [Set_Company_CustID, Set_Company_Name, Set_IncidentNumber_Service, Set_IncidentNumber_CompanyName]

        try:
            Set_Intersection = set.intersection(*(s for s in List_Of_All_Sets if s))
            if len(Set_Intersection) == 0:
                alert = QMessageBox()
                alert.setText('No results')
                self.ui.tableWidget_ResultsIncident.clearContents()
                self.ui.tableWidget_ResultsIncident.setRowCount(0)
                alert.exec_()
        except TypeError:
            alert = QMessageBox()
            alert.setText('No results')
            self.ui.tableWidget_ResultsIncident.clearContents()
            self.ui.tableWidget_ResultsIncident.setRowCount(0)
            alert.exec_()
        else:
            if self.ui.radioButton_Search_Incident.isChecked():
                self.ui.tableWidget_ResultsIncident.setRowCount(0)
                tableWidget_ResultsIncident_row_num = self.ui.tableWidget_ResultsIncident.rowCount()
                with pyodbc.connect(
                    'DRIVER={ODBC Driver 17 for SQL Server};SERVER=ServerIP;DATABASE=Sqldbname;UID=db_username;PWD=db_password') as DB_Connect:
                    Set_Intersection_Cursor = DB_Connect.cursor()

                    Set_Intersection_Cursor.execute("select CompanyLink_RecID, IncidentNumber from Incident")
                    for Set_Intersection_Cursor_Incident_Item in Set_Intersection_Cursor.fetchall():
                        for Set_Intersection_Item in Set_Intersection:
                            if Set_Intersection_Cursor_Incident_Item[1] == Set_Intersection_Item:
                                Match_Intersection_RecID = Set_Intersection_Cursor_Incident_Item[0]

                                Set_Intersection_Cursor.execute("select RecId, CustID, Name from Company")
                                for Set_Intersection_Cursor_Company_Item in Set_Intersection_Cursor.fetchall():
                                    if Set_Intersection_Cursor_Company_Item[0] == Match_Intersection_RecID:
                                        self.ui.tableWidget_ResultsIncident.insertRow(tableWidget_ResultsIncident_row_num)
                                        self.ui.tableWidget_ResultsIncident.setItem(tableWidget_ResultsIncident_row_num, 0, QTableWidgetItem(str(Set_Intersection_Item)))
                                        self.ui.tableWidget_ResultsIncident.setItem(tableWidget_ResultsIncident_row_num, 1, QTableWidgetItem(str(Set_Intersection_Cursor_Company_Item[1])))

                                        Set_Intersection_Cursor.execute("select IncidentNumber, Priority, Service, TypeOfIncident, CellNum, CreatedDateTime, Status, TACPrime, Owner from Incident")
                                        for row in Set_Intersection_Cursor.fetchall():
                                            if row[0] == Set_Intersection_Item:
                                                self.ui.tableWidget_ResultsIncident.setItem(tableWidget_ResultsIncident_row_num, 2, QTableWidgetItem(str(row[1]))) #Priority
                                            if row[0] == Set_Intersection_Item:
                                                self.ui.tableWidget_ResultsIncident.setItem(tableWidget_ResultsIncident_row_num, 3, QTableWidgetItem(str(row[2]))) #Service

            if len(Set_Company_RecID) != 0 and self.ui.radioButton_Search_Company.isChecked():
                self.ui.tableWidget_ResultsCompany.setColumnWidth(0, 215)
                self.ui.tableWidget_ResultsCompany.setColumnWidth(1, 215)
                self.ui.tableWidget_ResultsCompany.setColumnWidth(2, 215)

                self.ui.tableWidget_ResultsCompany.setRowCount(0)
                tableWidget_ResultsCompany_row_num = self.ui.tableWidget_ResultsCompany.rowCount()
                with pyodbc.connect(
                    'DRIVER={ODBC Driver 17 for SQL Server};SERVER=ServerIP;DATABASE=Sqldbname;UID=db_username;PWD=db_password') as DB_Connect:
                    Company_RecID_Cursor = DB_Connect.cursor()

                    Company_RecID_Cursor.execute("select RecID, CustID, Name from Company")
                    for Company_RecID_Cursor_Item in Company_RecID_Cursor.fetchall():
                        for Set_Company_RecID_Item in Set_Company_RecID:
                            if Company_RecID_Cursor_Item[1] == Set_Company_RecID_Item:
                                self.ui.tableWidget_ResultsCompany.insertRow(tableWidget_ResultsCompany_row_num)
                                self.ui.tableWidget_ResultsCompany.setItem(tableWidget_ResultsCompany_row_num, 0, QTableWidgetItem(str(Set_Company_RecID_Item)))
                                self.ui.tableWidget_ResultsCompany.setItem(tableWidget_ResultsCompany_row_num, 1, QTableWidgetItem(str(Company_RecID_Cursor_Item[2])))
                                self.ui.tableWidget_ResultsCompany.setItem(tableWidget_ResultsCompany_row_num, 2, QTableWidgetItem(str(Company_RecID_Cursor_Item[0])))

if __name__ == '__main__':
    app = QApplication(sys.argv)
    main = pySearchMain()
    main.show()
    sys.exit(app.exec_())

Please provide a fully [minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example), as it's really unclear what you're asking or what issue is there, if any exists. Also, it looks like you're using a file generated by pyuic to create your program, but those files should **NEVER** be modified. Rebuild them and follow the guidelines about [using Designer](https://www.riverbankcomputing.com/static/Docs/PyQt5/designer.html) instead. — musicamante, Apr 09 '20 at 20:23
Thank you. I have updated my post. Yes, I'm using pyuic-generated code (single inheritance approach). — Reginald, Apr 09 '20 at 21:13
Sorry, but I still don't understand what your question actually is. — musicamante, Apr 09 '20 at 21:27
The app works, but searching the database can be long and it causes the app to hang (Not responding). I would like to create a worker thread in order to prevent the main thread/GUI from locking up. As it is now, there is no threading in my code. — Reginald, Apr 09 '20 at 21:36
Then maybe you should use the database dedicated models provided by Qt (like [QSqlTableModel](https://doc.qt.io/qt-5/qsqltablemodel.html) or its direct ancestor [QSqlQueryModel](https://doc.qt.io/qt-5/qsqlquerymodel.html), and then use a subclass of [QSortFilterProxyModel](https://doc.qt.io/qt-5/qsortfilterproxymodel.html) for search results. It might be a bit more complex to develop, but will probably increase the overall performance. — musicamante, Apr 09 '20 at 21:44
Wouldn't the overall performance still be about the same? I'd still have to query and connect to the remote SQL db and that's when the app becomes non-responsive. — Reginald, Apr 09 '20 at 21:50
Not exactly: Qt's sql models are very well optimized and actually fast (mostly because they directly interface themselves with the database using C++ implementation, while using Python obviously decreases performance). Of course, it all depends on the size of your database(s), but if you're dealing with very big amounts of data the performance would be slow anyway, unless you use other ways of dealing with the results through threading. — musicamante, Apr 09 '20 at 21:55
Yes, it's a fairly large database (~300 new inserts/day). It's a good use case for threading I believe. — Reginald, Apr 09 '20 at 22:02
Then you'll probably need to use a separate thread that makes the queries and uses the signal/slot mechanism to update the actual results with the interface. But, anyway, don't dismiss the whole Qt-based Sql models way, they are designed for high performance. — musicamante, Apr 09 '20 at 22:04
Yes, that's what I meant by worker thread and that's the high-level way of implemented it. I'm just not sure where to actually insert the Signal/Slot lines and what part of the code has to be moved where and whether it will affect variable scope. — Reginald, Apr 09 '20 at 22:11
I'd suggest you to think about it *a lot*, then try your solution and eventually come up with a new dedicated answer if you're not satisfied with its results. — musicamante, Apr 09 '20 at 22:13
I have thought about it a lot, it's why I came here. At this point, I'm just not sure whether I can easily port threading over to my code or whether I need to entirely re-write it from scratch. I'm new to Python (and PyQT) and didn't once think about threading while I was writing the app. It's only after it was completed that I realized how threading would improve user-experience but every attempt I've made at it has failed. — Reginald, Apr 09 '20 at 23:01
Well, that's one of the reasons SO exists :-) Dealing with threading isn't easy, as it requires you to take yourself to another (further) level of thinking, but it's worth it. Take your time, mingle with it, test it (along with yourself). Then come back here: we'll wait for you ;-) A suggestion: since you're using Qt, avoid using Python's threading (unless you *really* know what you're doing and why) and use Qt's threading instead (the most common practice is QThread subclassing, but have a look at QThreadingPool and QRunnable too) and *always* use signals/slots to communicate between threads. — musicamante, Apr 09 '20 at 23:14

PyQT5/QT Designer - Threading with Signal/Slots

0 Answers0