python - 如何在pyqt5中子类化QSqlTableModel？

Question

在浏览了一些带有 C++ 答案的帖子后，这些答案不适合我的问题，并且比解释更令人困惑，我尝试在这里提问。我正在尝试对 QSqlTableModel 进行子类化，因为我需要一些带有复选框的 bool 列。完整的工作程序为:

from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtSql import *
import sys

class ImportFilter (QDialog):
    def __init__(self):
        super().__init__()
        self.initUI()

    def initUI(self):
        print("Welcome to StandardDialog")
        # Load the Window
        #self.ui = uic.loadUi("ImportFilter.ui",self)
        #self.ui.setModal(True)
        self.buttonBox = QDialogButtonBox()
        self.tableView_typeOfValues = QTableView()
        layout = QVBoxLayout()
        layout.addWidget(self.buttonBox)
        layout.addWidget(self.tableView_typeOfValues)
        self.setLayout(layout)

        # Init Environmment
        self.db = createConnection()

        # create Models
        self.setupModel()

        #setup Views
        self.setupView()

        # connect Signals
        self.buttonBox.clicked.connect(self.pushButton_Box_clicked)

        self.show()
        print("<- initUI")

    def setupView(self):
        print("-> setupView")
        self.tableView_typeOfValues.setModel(self.tableView_typeOfValues_Model)
        self.tableView_typeOfValues.setColumnWidth(0,10)
        self.tableView_typeOfValues.setColumnWidth(1,130)
        self.tableView_typeOfValues.setColumnWidth(2,130)
        self.tableView_typeOfValues.setColumnWidth(3,60)
        self.tableView_typeOfValues.setColumnWidth(4,60)
        self.tableView_typeOfValues.setColumnWidth(5,60)
        self.tableView_typeOfValues.setColumnWidth(6,60)

        self.tableView_typeOfValues.hideColumn(0)
        self.tableView_typeOfValues.hideColumn(3)
        print("<- setupView")

    def setupModel(self):
        print("-> setupModel")
        # own model
        self.tableView_typeOfValues_Model = ImportSqlTableModel()
        print("   tables:", self.db.tables())
        print('   Before .setTable("typeOfValue") and select()')
        self.tableView_typeOfValues_Model.info()
        self.tableView_typeOfValues_Model.setTable("typeOfValue")
        self.tableView_typeOfValues_Model.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.tableView_typeOfValues_Model.select()
        print('   After .setTable("typeOfValue") and select()')
        self.tableView_typeOfValues_Model.info()
        self.headerData()
        print("   Table:",self.tableView_typeOfValues_Model.tableName())
        print("   count:",self.tableView_typeOfValues_Model.rowCount())

        self.tableView_typeOfValues_Model.info()
        print("<- setupModel")

    def setupModelQRY(self):
        print("-> setupModel with Query")
        # works so far
        #self.tableView_typeOfValues_Model = QSqlTableModel()  # edit but no checkboxes
        self.tableView_typeOfValues_Model = ImportSqlTableModel(self.db) # no edit
        # SET query
        qry = QSqlQuery(self.db)
        sql = "SELECT ID, name, unit, source, Import, ImportIfZero, visible FROM typeOfValue"
        qry.prepare(sql)
        qry.exec_(sql)
        self.tableView_typeOfValues_Model.setQuery(qry)
        self.tableView_typeOfValues_Model.select()
        print("   Filter:",self.tableView_typeOfValues_Model.filter())
        print("   SELECT:", self.tableView_typeOfValues_Model.selectStatement())
        self.tableView_typeOfValues_Model.setEditStrategy(QSqlTableModel.OnFieldChange)
        print("<- setupModel")

    def headerData(self):
        print("-> headerData")
        self.tableView_typeOfValues_Model.setHeaderData(0,Qt.Horizontal, "ID")
        self.tableView_typeOfValues_Model.setHeaderData(1,Qt.Horizontal, "name")
        self.tableView_typeOfValues_Model.setHeaderData(2,Qt.Horizontal, "unit")
        self.tableView_typeOfValues_Model.setHeaderData(3,Qt.Horizontal, "source")
        self.tableView_typeOfValues_Model.setHeaderData(4,Qt.Horizontal, "Import")
        self.tableView_typeOfValues_Model.setHeaderData(5,Qt.Horizontal, "ImportIfZero")
        self.tableView_typeOfValues_Model.setHeaderData(6,Qt.Horizontal, "visible")
        print("<- headerData")


    ###################################################################################################
    #                        functions
    ###################################################################################################
    def pushButton_Box_clicked(self,signal):
        print("okButtonClicked")
        print("buttonBox_clicked",signal)
        self.tableView_typeOfValues_Model.submitAll()
        self.exitcode = "ok, but not implemented"
        sys.exit()

    def returnCode(self):
        return self.exitcode

#######################################################################################################################
#                                               C L A S S
#######################################################################################################################

class ImportSqlTableModel(QSqlTableModel):
    def __init__(self):
        super(ImportSqlTableModel, self).__init__()
        print("-> ImportSqlTableModel.__init__:")
        self.booleanSet =[4,5,6]
        self.readOnlySet = [1]
        print("   Inside:")
        self.info()
        print("<- ImportSqlTableModel.__init__:")


    def info(self):
        print("-> info")

        print("   ImportSqlTableModel tables inside :", self.database().tables())
        print("   ImportSqlTableModel self.db       :", self.database())
        print("   ImportSqlTableModel self.Table    :", self.tableName())
        print("   ImportSqlTableModel self.rowCount :", self.rowCount())
        print("   ImportSqlTableModel self.lastEror :", self.lastError().text())
        print("<- info")
    def columnCount(self, index):
        count = QSqlTableModel.columnCount(self, index)
        return count

    def dataChanged(self, QModelIndex, QModelIndex_1, Iterable, p_int=None, *args, **kwargs):
        print("-> Datachanged")

    def data(self, index, role=Qt.DisplayRole):
        print("-> ImportSqlModel.data",index, role)
        print("   1row   :", index.row())
        print("   col    :", index.column())
        print("   data   :", self.record().fieldName(index.column()))
        value = super(ImportSqlTableModel, self).data(index)
        print("  value2:",value)

        if index.column() in self.booleanSet:
            if role == Qt.CheckStateRole:
                if value == 2:
                    return QVariant(Qt.Unchecked)
                else:
                    return QVariant(Qt.Checked)
            else:
                QSqlTableModel.data(self, index, role)
        else:
            return QSqlTableModel.data(self, index, role)

    def setData(self, index, value, role=Qt.EditRole):
        # works with changing value, but not saving
        print("-> ImportSqlModel.setData",index,value,role)
        print("   value:", value)
        if not index.isValid():
            return False
        if role == Qt.EditRole:
            print("   = Qt.Editrole")
            QVariant(value)
            print("   Update table")
            self.select()
        if index.column() in self.booleanSet and role == Qt.CheckStateRole:
            print("   checkbox changed!")
            if value == Qt.Checked:
                print("   Qt.Checked")
                return QSqlTableModel.setData(self, index, 2 , Qt.EditRole)
            else:
                print("   not Qt.Checked")
                return QSqlTableModel.setData(self, index, 0 , Qt.EditRole)
        else:
            return QSqlTableModel.setData(self, index, value, role)

    def flags(self, index):
        print("-> ImportSqlModel.flags")
        print("   index.isValid()",index.isValid())
        if not index.isValid():
            return Qt.ItemIsEnabled
        if index.column() in self.booleanSet:
            return Qt.ItemIsUserCheckable  | Qt.ItemIsEnabled #  | Qt.ItemIsSelectable  | Qt.ItemIsEditable
        elif index.column() in self.readOnlySet:
            return Qt.ItemIsSelectable | Qt.ItemIsEnabled
        else:
            return QSqlTableModel.flags(self, index)
        print("<- ImportSqlModel.flags")


#######################################################################################################################
#                                               D E M O F U N C T I O N
#######################################################################################################################

def createConnection():
    db = QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName('memory')
    if not db.open():
        QMessageBox.critical(None, qApp.tr("Cannot open database"),
                             qApp.tr("Unable to establish a database connection.\n"
                                     "This example needs SQLite support. Please read "
                                     "the Qt SQL driver documentation for information "
                                     "how to build it.\n\n"
                                     "Click Cancel to exit."),
                             QMessageBox.Cancel)
        return False

    query = QSqlQuery()
    query.exec_("CREATE TABLE `typeOfValue` (`ID`   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, "\
    "`name` TEXT NOT NULL,  `unit`  TEXT NOT NULL,`source` TEXT,`import` INTEGER,`importIfZero` INTEGER,"\
    "`visible`  INTEGER);")

    query.exec_('insert into typeOfValue (name, unit, source, import, importIfZero, visible) values '\
                '("Sound", "dB", "live", 0,0,2)')
    query.exec_('insert into typeOfValue (name, unit, source, import, importIfZero, visible) values '\
                '("Flow", "m/min", "live", 0,2,2)')
    query.exec_('insert into typeOfValue (name, unit, source, import, importIfZero, visible) values '\
                '("Vibration", "mm/s", "live", 2,2,2)')
    query.exec_('insert into typeOfValue (name, unit, source, import, importIfZero, visible) values '\
                '("Voltage", "V", "live", 0,0,0)')
    query.exec_('insert into typeOfValue (name, unit, source, import, importIfZero, visible) values '\
                '("Ampere", "A", "live", 2,0,2)')
    return db
#######################################################################################################################
#                                               M A I N
#######################################################################################################################

if __name__ == '__main__':
    createConnection()
    app = QApplication(sys.argv)
    prog = ImportFilter()
    prog.show()
    sys.exit(app.exec_())

我遇到的问题是，当我使用查询填充表时，我能够查看该表:

def setupModel(self):
    print("-> setupModel")
    # SET query
    qry = QSqlQuery(self.gVar.db)
    sql = "SELECT ID, name, unit, source, Import, ImportIfZero, visible FROM typeOfValue"
    qry.prepare(sql)
    qry.exec_(sql)
    self.tableView_typeOfValues_Model.setQuery(qry)

但我发现了错误，这会导致只读表。手册中描述了不应该这样做:参见 here所以我改为 .setTable("typeOfValue") # = Tablename

        self.tableView_typeOfValues_Model = ImportSqlTableModel(self.gVar.db)
        self.tableView_typeOfValues_Model.setTable("typeOfValue")
   self.tableView_typeOfValues_Model.setEditStrategy(QSqlTableModel.OnFieldChange)

        self.tableView_typeOfValues_Model.select()

但是现在我有一个空 View ，如果我调用 self.lastError().text() 我收到一条消息，表明找不到该表，因为 self.database() .tables()(在模型内部调用)带来一个空列表。这对我来说意味着数据库没有正确初始化，但是 self.database() 带来了结果为 0x042C4D30 处的 PyQt5.QtSql.QSqlDatabase 对象。

请有人给我提示来纠正 QSqlTableModel 子类化。谢谢!

Answer 1

该问题是由于未为 bool 类型列启用 Qt.ItemIsEditable 标志引起的，这在指令中是必需的:

if index.column() in self.booleanSet and role == Qt.CheckStateRole:
    print("   checkbox changed!")
    if value == Qt.Checked:
        print("   Qt.Checked")
        return QSqlTableModel.setData(self, index, 2 , Qt.EditRole)
    else:
        print("   not Qt.Checked")
        return QSqlTableModel.setData(self, index, 0 , Qt.EditRole)

因为它验证了该字段可以编辑。

因此，解决方案是启用此标志，但为了避免您编写一些文本，我们将通过委托(delegate)禁用编辑器:

class ReadOnlyDelegate(QItemDelegate):
    def createEditor(self, parent, option, index):
        lb = QLabel(parent)
        return lb

完整示例:

import sys

from PyQt5.QtCore import QVariant, Qt
from PyQt5.QtSql import QSqlTableModel, QSqlDatabase
from PyQt5.QtWidgets import QApplication, QTableView, QLabel, QItemDelegate


class ImportSqlTableModel(QSqlTableModel):
    def __init__(self, *args, **kwargs):
        super(ImportSqlTableModel, self).__init__(*args, **kwargs)
        self.booleanSet = [4, 5, 6]  # column with checkboxes
        self.readOnlySet = [1]  # columns which must not be changed
        self.setTable("typeOfValue")
        self.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.select()

    def data(self, index, role=Qt.DisplayRole):
        value = super(ImportSqlTableModel, self).data(index)
        if index.column() in self.booleanSet:
            if role == Qt.CheckStateRole:
                return Qt.Unchecked if value == 2 else Qt.Checked
            else:
                return QVariant()
        return QSqlTableModel.data(self, index, role)

    def setData(self, index, value, role=Qt.EditRole):
        if not index.isValid():
            return False
        if index.column() in self.booleanSet:
            if role == Qt.CheckStateRole:
                val = 2 if value == Qt.Unchecked else 0
                return QSqlTableModel.setData(self, index, val, Qt.EditRole)
            else:
                return False
        else:
            return QSqlTableModel.setData(self, index, value, role)

    def flags(self, index):
        if not index.isValid():
            return Qt.NoItemFlags
        if index.column() in self.booleanSet:
            return Qt.ItemIsUserCheckable | Qt.ItemIsEnabled | Qt.ItemIsEditable
        elif index.column() in self.readOnlySet:
            return Qt.ItemIsSelectable | Qt.ItemIsEnabled
        else:
            return QSqlTableModel.flags(self, index)


class ReadOnlyDelegate(QItemDelegate):
    def createEditor(self, parent, option, index):
        lb = QLabel(parent)
        return lb


if __name__ == '__main__':
    app = QApplication(sys.argv)

    db = QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("/path/of/your_database.db")
    if not db.open():
        sys.exit(-1)
    model = ImportSqlTableModel()
    w = QTableView()
    w.setModel(model)
    for col in model.booleanSet:
        w.setItemDelegateForColumn(col, ReadOnlyDelegate(w))
    w.show()
    sys.exit(app.exec_())