插入记录后，如何同步QTableView和数据库？

Question

假设我有一个QTableView，它是用QSqlTableModel/Database来管理的。我不想让用户直接编辑QTableView里的单元格。这里有一些增删改查的按钮，点击后会打开新的对话框，用户需要在对话框里输入数据。当用户点击对话框的确认按钮后，怎么才能把新记录插入到数据库和视图中（保持它们同步），因为在某些情况下，数据库可能无法访问（比如在网络不好的时候，想要插入远程数据库）？

我最关心的是，我不想在视图中显示虚假的记录，并且我希望用户知道这条记录并没有真正存入数据库。

我放了一些Python代码（不过对于Qt来说，我的问题是一样的），并在评论中还有其他一些问题：

import sys
from PyQt4.QtGui import *
from PyQt4.QtCore import *
from PyQt4.QtSql import *

class Window(QWidget):
    def __init__(self, parent=None):
        QWidget.__init__(self, parent)
        self.model = QSqlTableModel(self)
        self.model.setTable("names")
        self.model.setHeaderData(0, Qt.Horizontal, "Id")
        self.model.setHeaderData(1, Qt.Horizontal, "Name")
        self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.model.select()

        self.view = QTableView()
        self.view.setModel(self.model)
        self.view.setSelectionMode(QAbstractItemView.SingleSelection)
        self.view.setSelectionBehavior(QAbstractItemView.SelectRows)
        #self.view.setColumnHidden(0, True)
        self.view.resizeColumnsToContents()
        self.view.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.view.horizontalHeader().setStretchLastSection(True)

        addButton = QPushButton("Add")
        editButton = QPushButton("Edit")
        deleteButton = QPushButton("Delete")
        exitButton = QPushButton("Exit")

        hbox = QHBoxLayout()
        hbox.addWidget(addButton)
        hbox.addWidget(editButton)
        hbox.addWidget(deleteButton)
        hbox.addStretch()
        hbox.addWidget(exitButton)

        vbox = QVBoxLayout()
        vbox.addWidget(self.view)
        vbox.addLayout(hbox)
        self.setLayout(vbox)

        addButton.clicked.connect(self.addRecord)
        #editButton.clicked.connect(self.editRecord) # omitted for simplicity
        #deleteButton.clicked.connect(self.deleteRecord) # omitted for simplicity
        exitButton.clicked.connect(self.close)

    def addRecord(self):
        # just QInputDialog for simplicity
        value, ok = QInputDialog.getText(self, 'Input Dialog', 'Enter the name:')
        if not ok:
            return

        # Now, what is the best way to insert the record?

        # 1st approach, first in database, then model.select()
        # it seems like the most natural way to me
        query = QSqlQuery()
        query.prepare("INSERT INTO names (name) VALUES(:name)")
        query.bindValue( ":name", value )
        if query.exec_():
            self.model.select() # now we know the record is inserted to db
            # the problem with this approach is that select() can be slow
            # somehow position the view to newly added record?!
        else:
            pass
            # message to user
            # if the record can't be inserted to database, 
            # there's no way I will show that record in view

        # 2nd approach, first in view (model cache), then in database
        # actually, I don't know how to do this
        # can somebody instruct me?
        # maybe:
        # record = ...
        # self.model.insertRecord(-1, record) #
        # submitAll()
        # what if database is unavailable?
        # what if submitAll() fails?
        # in that case, how to have view and model in sync?
        # is this the right approach?

        # 3. is there some other approach?

app = QApplication(sys.argv)
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(":memory:")
db.open()
query = QSqlQuery()
query.exec_("DROP TABLE names")
query.exec_("CREATE TABLE names(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name TEXT)")
query.exec_("INSERT INTO names VALUES(1, 'George')")
query.exec_("INSERT INTO names VALUES(2, 'Rita')")
query.exec_("INSERT INTO names VALUES(3, 'Jane')")
query.exec_("INSERT INTO names VALUES(4, 'Steve')")
query.exec_("INSERT INTO names VALUES(5, 'Maria')")
query.exec_("INSERT INTO names VALUES(6, 'Bill')")
window = Window()
window.resize(600, 400)
window.show()
app.exec_()

Answer 1

正如我在评论中提到的，你的第一种方法比第二种更好，因为它可以避免做不必要的工作。不过，如果你担心通过 QSqlTableModel.select 传输的数据量会让你的应用变慢，你可以使用 QSqlTableModel.insertRecord 来代替（下面有我的示例）。这个方法尝试将记录插入数据库，同时即使插入失败，它也会在模型中注册这条记录。所以如果插入失败，你需要手动通过 QSqlTableModel.revertAll() 来移除它。

不过，你可以利用这个特性，让模型独立添加数据，这样用户就不需要再负责重新输入那些插入失败的数据。这意味着数据会被插入到模型中，然后你可以稍后尝试将其提交到数据库（用户不需要重新输入）。这里有一个简单的示例（只包含关键部分）：

（我使用了一个有两列的表，列类型为 INT NOT NULL AUTO_INCREMENT 和 VARCHAR(32)）

record = QtSql.QSqlRecord()  # create a new record
record.append(QtSql.QSqlField('id', QtCore.QVariant.Int))  # add the columns
record.append(QtSql.QSqlField('value', QtCore.QVariant.String))
record.setValue('id', 0)  # set the values for every column
record.setValue('value', value)

if not self.model.insertRecord(-1, record):  # insert the record (-1 means at the end)
    self.queue = QueueRecord(self.model, self.table, self.model.rowCount()-1)  # in case of failure a new class is invoked which will manage the submission of this record (see below)
    # However, the record was added to the model and will therefore appear in the table
    self.connect(self, QtCore.SIGNAL('qstart()'), self.queue.insert)  # queue.insert is the method which handles submitting the record, the signal qstart() was created beforehand using QtCore.pyqtSignal()
    self.qstart.emit()  # start the submission handler

这是处理待提交记录的类：

class QueueRecord(QtCore.QObject):
    def __init__(self, model, table, row, parent=None):
        QtCore.QObject.__init__(self, parent)

        self.model = model
        self.table = table
        self.delegate = table.itemDelegateForRow(row)  # get the item delegate for the pending row (to reset it later)
        self.row = row

        table.setItemDelegateForRow(row, PendingItemDelegate())  # set the item delegate of the pending row to new one (see below). In this case all cells will just display 'pending ...' so the user knows that this record isn't submitted yet.

        self.t1 = QtCore.QThread()  # we need a new thread so we won't block our main application
        self.moveToThread(self.t1)
        self.t1.start()

    def insert(self):
        while not self.model.submitAll():  # try to submit the record ...
            time.sleep(2)  # ... if it fails retry after 2 seconds.

        # record successfully submitted
        self.table.setItemDelegateForRow(self.row, self.delegate)  # reset the delegate
        self.t1.quit()  # exit the thread

这是代理类：

class PendingItemDelegate(QtGui.QStyledItemDelegate):
    def __init__(self, parent=None):
        QtGui.QStyledItemDelegate.__init__(self, parent)

    def displayText(self, value, locale):
        return 'pending ...'  # return 'pending ...' for every cell

这段代码的基本功能是使用 insertRecord 将新数据插入模型/数据库。如果插入失败，记录仍然会被添加到模型中，我们会创建一个新的类（在单独的线程中运行）来处理数据的重新提交。这个新类会改变待处理行的显示，提示用户这条记录尚未注册，并尝试提交数据直到成功。代理会被重置，线程也会结束。

这样，你就避免了调用 select()，而只是在表中插入一条记录。此外，用户也不再需要负责重新提供数据，这将由一个单独的类来处理。

不过 这个例子非常简单，需要谨慎对待！例如，类 QueueRecord 使用通过 model.rowCount()-1 提供的行号来引用待处理的元素，但如果你在此期间删除了元素，行数会发生变化，你就会引用到错误的元素。

这个例子只是为了说明，可以用于进一步开发，但在当前状态下并不适合用于实际应用。

你可以考虑设置一个最大重试次数，超过后“待处理...”会变成“失败”，并出现一个重新提交的按钮，这样用户只需按下这个按钮，就可以在数据库连接恢复后重新添加数据。

顺便说一下，为了测试这些功能，我在主窗口添加了一个按钮，用于关闭/打开数据库。我启动应用时自动打开数据库，然后关闭它，插入一个值，再次打开数据库。

Answer 2

RobbieE说得对，我可以用表单编辑（通过QDataWidgetMapper）来代替直接编辑单元格，但我问的并不是关于表单或单元格编辑的问题。

我想知道的是，我给出的两个方法中，哪个更好，第一种还是第二种。

我修改了代码，实施了第二种方法（虽然我不太喜欢）。这样做算不算好？

不过问题还是没解决。你们这些（Py）Qt开发者是怎么用QtSql来做CRUD操作的？是先处理数据库，再处理模型/视图，还是先处理模型/视图，再处理数据库？

补充：我更新了例子，增加了第三种方法（还不完整）和模拟数据库关闭的功能。现在测试这三种方法都更简单了。

import sys
from PyQt4.QtGui import *
from PyQt4.QtCore import *
from PyQt4.QtSql import *

class Window(QWidget):
    def __init__(self, parent=None):
        QWidget.__init__(self, parent)
        self.model = QSqlTableModel(self)
        self.model.setTable("names")
        self.model.setHeaderData(0, Qt.Horizontal, "Id")
        self.model.setHeaderData(1, Qt.Horizontal, "Name")
        self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.model.select()

        self.view = QTableView()
        self.view.setModel(self.model)
        self.view.setSelectionMode(QAbstractItemView.SingleSelection)
        self.view.setSelectionBehavior(QAbstractItemView.SelectRows)
        #self.view.setColumnHidden(0, True)
        self.view.resizeColumnsToContents()
        self.view.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.view.horizontalHeader().setStretchLastSection(True)

        addButton = QPushButton("Add")
        editButton = QPushButton("Edit")
        deleteButton = QPushButton("Delete")
        exitButton = QPushButton("Exit")
        self.combo = QComboBox()
        self.combo.addItem("1) 1.Database, 2.Model (select)")
        self.combo.addItem("2) 1.Model, 2.Database")
        self.combo.addItem("3) 1.Database, 2.Model (insert)")
        self.combo.setCurrentIndex (0)
        self.checkbox = QCheckBox("Database Closed")

        hbox = QHBoxLayout()
        hbox.addWidget(addButton)
        hbox.addWidget(editButton)
        hbox.addWidget(deleteButton)
        hbox.addWidget(self.combo)
        hbox.addWidget(self.checkbox)
        hbox.addStretch()
        hbox.addWidget(exitButton)

        vbox = QVBoxLayout()
        vbox.addWidget(self.view)
        vbox.addLayout(hbox)
        self.setLayout(vbox)

        addButton.clicked.connect(self.addRecord)
        #editButton.clicked.connect(self.editRecord) # omitted for simplicity
        #deleteButton.clicked.connect(self.deleteRecord) # omitted for simplicity
        self.checkbox.clicked.connect(self.checkBoxCloseDatabase)
        exitButton.clicked.connect(self.close)

    def checkBoxCloseDatabase(self):
        if self.checkbox.isChecked():
            closeDatabase()
        else:
            pass
            #db.open() # it doesn't work

    def addRecord(self):
        # just QInputDialog for simplicity
        value, ok = QInputDialog.getText(self, 'Input Dialog', 'Enter the name:')
        if not ok:
            return

        # Now, what is the best way to insert the record?

        if self.combo.currentIndex() == 0:
            # 1st approach, first in database, then model.select()
            # it seems like the most natural way to me
            query = QSqlQuery()
            query.prepare("INSERT INTO names (name) VALUES(:name)")
            query.bindValue( ":name", value )
            if query.exec_():
                self.model.select() # now we know the record is inserted to db
                # the problem with this approach is that select() can be slow
                # somehow position the view to newly added record?!
            else:
                pass
                # message to user
                # if the record can't be inserted to database,
                # there's no way I will show that record in view
        elif self.combo.currentIndex() == 1:
            # 2nd approach, first in view (model cache), then in database
            QSqlDatabase.database().transaction()
            row = self.model.rowCount()
            self.model.insertRow(row)
            self.model.setData(self.model.index(row, 1), value)
            #self.model.submit()
            if self.model.submitAll():
                QSqlDatabase.database().commit()
                self.view.setCurrentIndex(self.model.index(row, 1))
            else:
                self.model.revertAll()
                QSqlDatabase.database().rollback()
                QMessageBox.warning(self, "Error", "Database not available. Please, try again later.")

        else:
            # 3rd approach, first in database, then model.insertRow()
            # it is not a complete solution and is not so practical
            query = QSqlQuery()
            query.prepare("INSERT INTO names (name) VALUES(:name)")
            query.bindValue( ":name", value )
            if query.exec_():
                #id = ... # somehow find id from the newly added record in db
                row = self.model.rowCount()
                self.model.insertRow(row)
                #self.model.setData(self.model.index(row, 0), id) # we don't know it
                self.model.setData(self.model.index(row, 1), value)
                # not a complete solution
            else:
                pass
                # do nothing, because model isn't changed
                # message to user

def closeDatabase():
    db.close()

def createFakeData():
    query = QSqlQuery()
    query.exec_("DROP TABLE names")
    query.exec_("CREATE TABLE names(id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, name TEXT)")
    query.exec_("INSERT INTO names VALUES(1, 'George')")
    query.exec_("INSERT INTO names VALUES(2, 'Rita')")
    query.exec_("INSERT INTO names VALUES(3, 'Jane')")
    query.exec_("INSERT INTO names VALUES(4, 'Steve')")
    query.exec_("INSERT INTO names VALUES(5, 'Maria')")
    query.exec_("INSERT INTO names VALUES(6, 'Bill')")
    #import random
    #for i in range(1000):
    #    name = chr(random.randint(65, 90))
    #    for j in range(random.randrange(3, 10)):
    #        name += chr(random.randint(97, 122))
    #
    #    query.prepare("INSERT INTO names (name) VALUES(:name)")
    #    query.bindValue( ":name", name )
    #    query.exec_()

app = QApplication(sys.argv)
db = QSqlDatabase.addDatabase("QSQLITE")
#db.setDatabaseName("test.db")
db.setDatabaseName(":memory:")
#openDatabase()
db.open()
createFakeData()
window = Window()
window.resize(800, 500)
window.show()
app.exec_()

补充2 2019年10月：我终于不再使用QSqlTableModel来处理关系型数据库了。我现在只用QSqlQueryModel，不需要QSqlTableModel或QSqlRelationalTableModel。我不进行单元格编辑（就像在Excel中那样），而是通过表单编辑整条记录（行）。在表单上点击确定后，我会更新数据库并重新选择QSqlQueryModel。有趣的是，重新选择后，当前行又会被聚焦。QSql（关系）表模型在处理数据库字段时有很多问题，简直是搞砸了，几乎对认真工作没什么用。他们本可以做得比现在有用得多。

我的建议是：对于认真工作和商业应用，使用QSqlQueryModel。

Answer 3

你仍然可以使用 QSqlTableModel。你可以在表格视图中关闭所有的编辑触发器，然后把这个模型传递给你的数据捕获表单，让这些小部件通过 QDataWidgetMapper 绑定到模型上。记得把提交模式设置为手动，这样你就可以先验证字段的内容。