python - PyQt5 QtSql 在 QThread 中访问数据库

Question

好的，现在我已经弄清楚了 MDI 应用程序中的整个持久 QSqlDatabase，我尝试在 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

运行时出现错误:

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

一些研究表明我应该为线程启动一个新的数据库连接，因此我进行了以下更改:

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 的线程不同，总之，如果您想使用它，您必须在 run 方法中创建连接在该方法中。

另一方面，你不能从另一个线程创建 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_())