PyQt5 QtSql access database in QThread

Question

OK, now that I figured out the the whole persistent QSqlDatabase in a MDI application, I attempted to access it in a QThread:

class LoadWorker(QThread):
    totalSignal = pyqtSignal(int)
    countSignal = pyqtSignal(int)

    def __init__(self, parent=None):
        super(LoadWorker, self).__init__(parent)
        self.threadActive = True
        self.commitsize = 200
        self.filename = ""

    def run(self):
        query = QSqlQuery()
        query.exec("DELETE FROM mytable")
        #rest of code here

When running, I get an error:

QSqlDatabasePrivate::database: requested database does not belong to the calling thread.
QSqlQuery::exec: database not open

Some research indicated that I should start a new DB connection for the thread so I made the following changes:

class LoadWorker(QThread):
    totalSignal = pyqtSignal(int)
    countSignal = pyqtSignal(int)

    def __init__(self, parent=None):
        super(LoadWorker, self).__init__(parent)
        self.threadActive = True
        self.commitsize = 200
        self.filename = ""
        self.database = QSqlDatabase.addDatabase('QSQLITE',"loader")
        if self.database:
            self.database.setDatabaseName("database/clireports.db")
        else:
            QMessageBox.critical(None, "Database Error", "Unable To Connect To The Database!")
            self.stop()

    def run(self):
        query = QSqlQuery()
        query.exec("DELETE FROM mytable")
        #rest of code here

However, I am still getting the same error. Is there something different I need to do?

Modified Code:

class LoadWorker(QThread):
    totalSignal = pyqtSignal(int)
    countSignal = pyqtSignal(int)

    def __init__(self, parent=None):
        super(LoadWorker, self).__init__(parent)
        self.threadActive = True
        self.commitsize = 200
        self.filename = ""

    def run(self):
        database = QSqlDatabase.addDatabase("QSQLITE","loader")
        database.setDatabaseName("database/clireports.db") # <---
        if not database.open():
            print("Database Error","Unable to connect to the database!")
            self.stop()

        # database.setDatabaseName("database/clireports.db")
        query = QSqlQuery()
        query.exec("DELETE FROM mytable")

Un-redacted Code:

class LoadWorker(QThread):
    totalSignal = pyqtSignal(int)
    countSignal = pyqtSignal(int)

    def __init__(self, parent=None):
        super(LoadWorker, self).__init__(parent)
        self.threadActive = True
        self.commitsize = 200
        self.filename = ""

    def run(self):
        database = QSqlDatabase.addDatabase("QSQLITE","loader")
        database.setDatabaseName("database/clireports.db")
        if not database.open():
            print("Database Error","Unable to connect to the database!")
            self.stop()
        query = QSqlQuery()
        query.exec("DELETE FROM cptdata")
        with open(self.filename, 'r', encoding="ISO-8859-1") as f:
            reader = csv.reader(f,delimiter='\t')
            data = list(reader)
            self.totalSignal.emit(len(data))
            f.close()
            counter = 0
            query.exec_("BEGIN TRANSACTION")
            for row in data:
                if self.threadActive == False: break
                counter +=1
                self.countSignal.emit(counter)
                if len(row) < 3: continue
                if "/" in row[0] or "Code" in row[0]: continue
                recordid = str(uuid.uuid1())
                cptcode = row[0].strip().upper()
                formatcode = cptcode[:5]
                description = row[2].strip().upper()
                if cptcode == "": continue
                query.prepare("INSERT INTO cptdata (recordid,cptcode,description) VALUES(:recordid,:cptcode,:description)")
                query.bindValue(":recordid", str(recordid))
                query.bindValue(":cptcode", str(formatcode))
                query.bindValue(":description", str(description))
                if query.exec_():
                    if counter % 200==0:
                        database.commit()
                        query.exec_("BEGIN TRANSACTION")
                database.commit()

    def stop(self):
        self.threadActive = False
        self.wait()


class CptLoader(QDialog):
    def __init__(self, parent=None):
        super(CptLoader, self).__init__(parent)
        loadUi("GlobalUI/fileloader.ui", self)

    def beginLoad(self,filename):
        self.thread = LoadWorker(self)
        self.thread.filename = filename
        self.thread.totalSignal.connect(self.prgLoader.setMaximum)
        self.thread.countSignal.connect(self.prgLoader.setValue)
        self.thread.start()

Answer 1

You must believe QSqlDatabase within the thread you want to work, you can not move it to another thread so you must create a new connection for each thread you want the database to use.

On the other hand QThread is not a thread but a handler of a thread that it creates, therefore the constructor method belongs to the thread where the QThread object is created that is different from the thread that handles QThread that is executed in the run() method, in conclusion you must create the connection within the run method if you want to use it within that method.

On the other hand you can not make the GUI from another thread, in your case I see that you want to show a message with a QMessageBox from another thread and that is forbidden in Qt.

Example:

from PyQt5 import QtCore, QtSql

class LoadWorker(QtCore.QThread):
    def run(self):
        database = QtSql.QSqlDatabase.addDatabase('QSQLITE')
        database.setDatabaseName("database/clireports.db")
        if not database.open():
            print("Database Error", "Unable To Connect To The Database!")
            self.stop()
        else:
            print("select")
            query = QtSql.QSqlQuery("SELECT * FROM mytable")
            rec = query.record()
            while query.next():
                for i in range(rec.count()):
                    print(query.value(i))

            print("delete")
            query.exec_("DELETE FROM mytable")

            print("select")
            query = QtSql.QSqlQuery("SELECT * FROM mytable")
            rec = query.record()
            while query.next():
                for i in range(rec.count()):
                    print(query.value(i))

    def stop(self):
        self.quit()
        self.wait()

if __name__ == '__main__':
    import sys

    app = QtCore.QCoreApplication(sys.argv)

    mthread = LoadWorker()
    mthread.start()
    sys.exit(app.exec_())