如何使用QSqlTableModel在可编辑的QTableView中将值设置为NULL

Question

我有一个QTableView，它显示SQLite数据库中表中的数据子集。该表仅可用于可为null的数字列。我创建了两个委托-一个用于只读列：

class ReadOnlyDelegate(QtWidgets.QItemDelegate):
def editorEvent(self, *args, **kwargs):
return False
def createEditor(self, *args, **kwargs):
return None

一个用于可编辑列：

class LabelDelegate(QtWidgets.QItemDelegate):
def createEditor(self, parent, options, index):
self.le = QtWidgets.QLineEdit(parent)
return self.le

该表由定制的QSqlTableModel提供，其中我覆盖submitAll方法：

class MySqlTableModel(QtSql.QSqlTableModel):
def submitAll(self):
for row in range(self.rowCount()):
for col in range(self.columnCount()):
if self.isDirty(self.index(row, col)):
val = self.record(row).value(col)
if val == '':
self.record(row).setNull(col)
else:
try:
self.record(row).setValue(col, float(val))
except (TypeError, ValueError):
display_error_msg('Can not convert to float',
f'The value {val} could not be converted to float')
raise
super().submitAll()

预期的行为是(1(在发送到数据库之前将值转换为浮点，(2(拒绝无法转换为浮点的输入，(3(将空字符串转换为NULL。(1( 以及(2(按预期工作，但是最后一位不工作。当调试方法.submitAll()时，它在self.record(row).setNull(col)行上没有引发异常，但似乎也没有任何效果。将发送一个空字符串并将其持久化到数据库中。有什么想法为什么以及如何修复它吗？

Answer 1

我认为没有必要覆盖submitAll()方法，相反，您可以在setModelData()方法中实现逻辑：

import sys
from PySide2 import QtCore, QtWidgets, QtSql
TABLENAME = "t1"

def create_connection():
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("database.db")
if not db.open():
QtWidgets.QMessageBox.critical(
None,
QtWidgets.QApplication.instance().tr("Cannot open database"),
QtWidgets.QApplication.instance().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.nn"
"Click Cancel to exit."
),
QtWidgets.QMessageBox.Cancel,
)
return False
if TABLENAME in db.tables():
return True
query = QtSql.QSqlQuery()
if not query.exec_(f"CREATE TABLE IF NOT EXISTS {TABLENAME}(a REAL, b text);"):
print(query.lastError().text())
queries = (
f"INSERT INTO {TABLENAME} VALUES(1, '1');",
f"INSERT INTO {TABLENAME} VALUES(NULL, '2');",
f"INSERT INTO {TABLENAME} VALUES(3, '3');",
f"INSERT INTO {TABLENAME} VALUES(NULL, '4');",
f"INSERT INTO {TABLENAME} VALUES(5, '4');",
f"INSERT INTO {TABLENAME} VALUES(NULL, '5');",
f"INSERT INTO {TABLENAME} VALUES(NULL, '7');",
)
for query in queries:
q = QtSql.QSqlQuery()
if not q.exec_(query):
print(q.lastError().text(), query)
return False
return True

class ReadOnlyDelegate(QtWidgets.QStyledItemDelegate):
def editorEvent(self, *args, **kwargs):
return False
def createEditor(self, *args, **kwargs):
return None

class LabelDelegate(QtWidgets.QStyledItemDelegate):
def createEditor(self, parent, options, index):
le = QtWidgets.QLineEdit(parent)
return le
def setModelData(self, editor, model, index):
value = editor.text()
if not value:
model.setData(index, None, QtCore.Qt.EditRole)
else:
try:
number = float(value)
except (TypeError, ValueError):
print(
f"Can not convert to float, The value {value} could not be converted to float'"
)
else:
model.setData(index, number, QtCore.Qt.EditRole)

def main(args):
app = QtWidgets.QApplication(args)
if not create_connection():
sys.exit(-1)
view = QtWidgets.QTableView()
model = QtSql.QSqlTableModel()
model.setTable(TABLENAME)
model.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
model.select()
view.setModel(model)
label_delegate = LabelDelegate(view)
view.setItemDelegateForColumn(0, label_delegate)
readonly_delegate = ReadOnlyDelegate(view)
view.setItemDelegateForColumn(1, readonly_delegate)
button = QtWidgets.QPushButton("Submit all")
widget = QtWidgets.QWidget()
lay = QtWidgets.QVBoxLayout(widget)
lay.addWidget(button)
lay.addWidget(view)
widget.resize(640, 480)
widget.show()
button.clicked.connect(model.submitAll)
ret = app.exec_()
sys.exit(ret)

if __name__ == "__main__":
main(sys.argv)

在Linux上测试：

• PyQt5 5.15.4
• PySide2 5.15.2
• Python 3.9.4