QThread中的PyQt5 QtSql访问数据库

Question

好了，现在我在MDI应用程序中找到了整个持久qsql数据库，我尝试在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

运行时，我收到一个错误：

^{pr2}$

一些研究表明，我应该为线程启动一个新的DB连接，因此我做了以下更改：

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

然而，我仍然得到同样的错误。有什么不一样的事我需要做吗？在

修改代码：

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")

未修订代码：

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

你必须相信QSqlDatabase在你想要工作的线程中，你不能把它移到另一个线程，所以你必须为你想要数据库使用的每个线程创建一个新的连接。在

另一方面，QThread不是一个线程，而是它创建的线程的处理程序，因此构造函数方法属于创建QThread对象的线程，它不同于在run()方法中执行的处理QThread的线程，总之，如果你想在这个方法中使用它。在

另一方面，您不能从另一个线程创建GUI，在您的例子中，我看到您希望显示一条来自另一个线程的QMessageBox的消息，这在Qt中是禁止的。在

示例：

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_())