python - 如何刷新代表 QSqlRelationalTablemodel 数据的组合框委托中的项目列表
问题描述
我最近检查了 Mark Summerfield 的“使用 Python 和 Qt 进行快速 GUI 编程”第 15 章中的一个示例，该示例展示了 SQL 数据库 TableView 建模的使用。在这个原始示例中，我发现了一些功能障碍，它阻止了表视图刷新组合框委托（即 QSqlRelation）。简单地说，表视图中表示 QSqlRelationalTableModel 数据的组合框委托，一旦关系表被修改，就不会更新。
原始文件名为 chap15/assetmanager.pyw，可以在其他地方找到。下面我提供了重现问题所需的代码片段。
在此示例中，为可以使用组合框委托（LogDelegate 或 AssetDelegate）修改的操作和类别（QSqlRelationalTableModel）建立了与 QSqlTableModel（self.assetModel 和 self.logModel）的关系。可以通过 ReferenceDataDlg 对话框添加新的操作或类别。
类别中添加的新记录
但是，一旦对话框 ReferenceDataDlg 关闭，新创建的记录就不会出现在组合框委托中，即使 SQL 表中存在用于操作或类别的新记录。
组合框委托未刷新
如何以编程方式刷新表视图中组合框委托中的项目列表：self.assetView 和 self.logView？
from __future__ import division
from __future__ import print_function
from __future__ import unicode_literals
from future_builtins import *
import os
import sys
from PyQt4.QtCore import (PYQT_VERSION_STR, QDate, QFile, QRegExp,
QString, QVariant, Qt, SIGNAL)
from PyQt4.QtGui import (QApplication, QCursor, QDateEdit, QDialog,
QHBoxLayout, QLabel, QLineEdit, QMessageBox, QPixmap,
QPushButton, QRegExpValidator, QStyleOptionViewItem, QTableView,
QVBoxLayout)
from PyQt4.QtSql import (QSqlDatabase, QSqlQuery, QSqlRelation,
QSqlRelationalDelegate, QSqlRelationalTableModel, QSqlTableModel)
MAC = True
try:
from PyQt4.QtGui import qt_mac_set_native_menubar
except ImportError:
MAC = False
ID = 0
NAME = ASSETID = 1
CATEGORYID = DATE = DESCRIPTION = 2
ROOM = ACTIONID = 3
ACQUIRED = 1
def createFakeData():
import random
print("Dropping tables...")
query = QSqlQuery()
query.exec_("DROP TABLE assets")
query.exec_("DROP TABLE logs")
query.exec_("DROP TABLE actions")
query.exec_("DROP TABLE categories")
QApplication.processEvents()
print("Creating tables...")
query.exec_("""CREATE TABLE actions (
id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(40) NOT NULL)""")
query.exec_("""CREATE TABLE categories (
id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(40) NOT NULL)""")
query.exec_("""CREATE TABLE assets (
id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
name VARCHAR(40) NOT NULL,
categoryid INTEGER NOT NULL,
room VARCHAR(4) NOT NULL,
FOREIGN KEY (categoryid) REFERENCES categories)""")
query.exec_("""CREATE TABLE logs (
id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
assetid INTEGER NOT NULL,
date DATE NOT NULL,
actionid INTEGER NOT NULL,
FOREIGN KEY (assetid) REFERENCES assets,
FOREIGN KEY (actionid) REFERENCES actions)""")
QApplication.processEvents()
print("Populating tables...")
query.exec_("INSERT INTO actions (name, description) "
"VALUES ('Acquired', 'When installed')")
query.exec_("INSERT INTO actions (name, description) "
"VALUES ('Broken', 'When failed and unusable')")
query.exec_("INSERT INTO actions (name, description) "
"VALUES ('Repaired', 'When back in service')")
query.exec_("INSERT INTO actions (name, description) "
"VALUES ('Routine maintenance', "
"'When tested, refilled, etc.')")
query.exec_("INSERT INTO categories (name, description) VALUES "
"('Computer Equipment', "
"'Monitors, System Units, Peripherals, etc.')")
query.exec_("INSERT INTO categories (name, description) VALUES "
"('Furniture', 'Chairs, Tables, Desks, etc.')")
query.exec_("INSERT INTO categories (name, description) VALUES "
"('Electrical Equipment', 'Non-computer electricals')")
today = QDate.currentDate()
floors = range(1, 12) + range(14, 28)
monitors = (('17" LCD Monitor', 1),
('20" LCD Monitor', 1),
('21" LCD Monitor', 1),
('21" CRT Monitor', 1),
('24" CRT Monitor', 1))
computers = (("Computer (32-bit/80GB/0.5GB)", 1),
("Computer (32-bit/100GB/1GB)", 1),
("Computer (32-bit/120GB/1GB)", 1),
("Computer (64-bit/240GB/2GB)", 1),
("Computer (64-bit/320GB/4GB)", 1))
printers = (("Laser Printer (4 ppm)", 1),
("Laser Printer (6 ppm)", 1),
("Laser Printer (8 ppm)", 1),
("Laser Printer (16 ppm)", 1))
chairs = (("Secretary Chair", 2),
("Executive Chair (Basic)", 2),
("Executive Chair (Ergonimic)", 2),
("Executive Chair (Hi-Tech)", 2))
desks = (("Desk (Basic, 3 drawer)", 2),
("Desk (Standard, 3 drawer)", 2),
("Desk (Executive, 3 drawer)", 2),
("Desk (Executive, 4 drawer)", 2),
("Desk (Large, 4 drawer)", 2))
furniture = (("Filing Cabinet (3 drawer)", 2),
("Filing Cabinet (4 drawer)", 2),
("Filing Cabinet (5 drawer)", 2),
("Bookcase (4 shelves)", 2),
("Bookcase (6 shelves)", 2),
("Table (4 seater)", 2),
("Table (8 seater)", 2),
("Table (12 seater)", 2))
electrical = (("Fan (3 speed)", 3),
("Fan (5 speed)", 3),
("Photocopier (4 ppm)", 3),
("Photocopier (6 ppm)", 3),
("Photocopier (8 ppm)", 3),
("Shredder", 3))
query.prepare("INSERT INTO assets (name, categoryid, room) "
"VALUES (:name, :categoryid, :room)")
logQuery = QSqlQuery()
logQuery.prepare("INSERT INTO logs (assetid, date, actionid) "
"VALUES (:assetid, :date, :actionid)")
assetid = 1
for i in range(20):
room = QVariant("{0:02d}{1:02d}".format(
random.choice(floors), random.randint(1, 62)))
for name, category in (random.choice(monitors),
random.choice(computers), random.choice(chairs),
random.choice(desks), random.choice(furniture)):
query.bindValue(":name", QVariant(name))
query.bindValue(":categoryid", QVariant(category))
query.bindValue(":room", room)
query.exec_()
logQuery.bindValue(":assetid", QVariant(assetid))
when = today.addDays(-random.randint(7, 1500))
logQuery.bindValue(":date", QVariant(when))
logQuery.bindValue(":actionid", QVariant(ACQUIRED))
logQuery.exec_()
if random.random() > 0.7:
logQuery.bindValue(":assetid", QVariant(assetid))
when = when.addDays(random.randint(1, 1500))
if when <= today:
logQuery.bindValue(":date", QVariant(when))
logQuery.bindValue(":actionid",
QVariant(random.choice((2, 4))))
logQuery.exec_()
assetid += 1
if random.random() > 0.8:
name, category = random.choice(printers)
query.bindValue(":name", QVariant(name))
query.bindValue(":categoryid", QVariant(category))
query.bindValue(":room", room)
query.exec_()
logQuery.bindValue(":assetid", QVariant(assetid))
when = today.addDays(-random.randint(7, 1500))
logQuery.bindValue(":date", QVariant(when))
logQuery.bindValue(":actionid", QVariant(ACQUIRED))
logQuery.exec_()
if random.random() > 0.6:
logQuery.bindValue(":assetid", QVariant(assetid))
when = when.addDays(random.randint(1, 1500))
if when <= today:
logQuery.bindValue(":date", QVariant(when))
logQuery.bindValue(":actionid",
QVariant(random.choice((2, 4))))
logQuery.exec_()
assetid += 1
if random.random() > 0.6:
name, category = random.choice(electrical)
query.bindValue(":name", QVariant(name))
query.bindValue(":categoryid", QVariant(category))
query.bindValue(":room", room)
query.exec_()
logQuery.bindValue(":assetid", QVariant(assetid))
when = today.addDays(-random.randint(7, 1500))
logQuery.bindValue(":date", QVariant(when))
logQuery.bindValue(":actionid", QVariant(ACQUIRED))
logQuery.exec_()
if random.random() > 0.5:
logQuery.bindValue(":assetid", QVariant(assetid))
when = when.addDays(random.randint(1, 1500))
if when <= today:
logQuery.bindValue(":date", QVariant(when))
logQuery.bindValue(":actionid",
QVariant(random.choice((2, 4))))
logQuery.exec_()
assetid += 1
QApplication.processEvents()
print("Assets:")
query.exec_("SELECT id, name, categoryid, room FROM assets "
"ORDER by id")
categoryQuery = QSqlQuery()
while query.next():
id = query.value(0).toInt()[0]
name = unicode(query.value(1).toString())
categoryid = query.value(2).toInt()[0]
room = unicode(query.value(3).toString())
categoryQuery.exec_(QString("SELECT name FROM categories "
"WHERE id = %1").arg(categoryid))
category = "{0}".format(categoryid)
if categoryQuery.next():
category = unicode(categoryQuery.value(0).toString())
print("{0}: {1} [{2}] {3}".format(id, name, category, room))
QApplication.processEvents()
class ReferenceDataDlg(QDialog):
def __init__(self, table, title, parent=None):
super(ReferenceDataDlg, self).__init__(parent)
self.model = QSqlTableModel(self)
self.model.setTable(table)
self.model.setSort(NAME, Qt.AscendingOrder)
self.model.setHeaderData(ID, Qt.Horizontal, QVariant("ID"))
self.model.setHeaderData(NAME, Qt.Horizontal, QVariant("Name"))
self.model.setHeaderData(DESCRIPTION, Qt.Horizontal,
QVariant("Description"))
self.model.select()
self.view = QTableView()
self.view.setModel(self.model)
self.view.setSelectionMode(QTableView.SingleSelection)
self.view.setSelectionBehavior(QTableView.SelectRows)
self.view.setColumnHidden(ID, True)
self.view.resizeColumnsToContents()
addButton = QPushButton("&Add")
deleteButton = QPushButton("&Delete")
okButton = QPushButton("&OK")
if not MAC:
addButton.setFocusPolicy(Qt.NoFocus)
deleteButton.setFocusPolicy(Qt.NoFocus)
buttonLayout = QHBoxLayout()
buttonLayout.addWidget(addButton)
buttonLayout.addWidget(deleteButton)
buttonLayout.addStretch()
buttonLayout.addWidget(okButton)
layout = QVBoxLayout()
layout.addWidget(self.view)
layout.addLayout(buttonLayout)
self.setLayout(layout)
self.connect(addButton, SIGNAL("clicked()"), self.addRecord)
self.connect(okButton, SIGNAL("clicked()"), self.accept)
self.setWindowTitle(
"Asset Manager - Edit {0} Reference Data".format(title))
def addRecord(self):
row = self.model.rowCount()
self.model.insertRow(row)
index = self.model.index(row, NAME)
self.view.setCurrentIndex(index)
self.view.edit(index)
class AssetDelegate(QSqlRelationalDelegate):
def __init__(self, parent=None):
super(AssetDelegate, self).__init__(parent)
def paint(self, painter, option, index):
myoption = QStyleOptionViewItem(option)
if index.column() == ROOM:
myoption.displayAlignment |= (Qt.AlignRight|Qt.AlignVCenter)
QSqlRelationalDelegate.paint(self, painter, myoption, index)
def createEditor(self, parent, option, index):
if index.column() == ROOM:
editor = QLineEdit(parent)
regex = QRegExp(r"(?:0[1-9]|1[0124-9]|2[0-7])"
r"(?:0[1-9]|[1-5][0-9]|6[012])")
validator = QRegExpValidator(regex, parent)
editor.setValidator(validator)
editor.setInputMask("9999")
editor.setAlignment(Qt.AlignRight|Qt.AlignVCenter)
return editor
else:
return QSqlRelationalDelegate.createEditor(self, parent,
option, index)
def setEditorData(self, editor, index):
if index.column() == ROOM:
text = index.model().data(index, Qt.DisplayRole).toString()
editor.setText(text)
else:
QSqlRelationalDelegate.setEditorData(self, editor, index)
def setModelData(self, editor, model, index):
if index.column() == ROOM:
model.setData(index, QVariant(editor.text()))
else:
QSqlRelationalDelegate.setModelData(self, editor, model,
index)
class LogDelegate(QSqlRelationalDelegate):
def __init__(self, parent=None):
super(LogDelegate, self).__init__(parent)
def paint(self, painter, option, index):
myoption = QStyleOptionViewItem(option)
if index.column() == DATE:
myoption.displayAlignment |= (Qt.AlignRight|Qt.AlignVCenter)
QSqlRelationalDelegate.paint(self, painter, myoption, index)
def createEditor(self, parent, option, index):
if (index.column() == ACTIONID and
index.model().data(index, Qt.DisplayRole).toInt()[0] ==
ACQUIRED): # Acquired is read-only
return
if index.column() == DATE:
editor = QDateEdit(parent)
editor.setMaximumDate(QDate.currentDate())
editor.setDisplayFormat("yyyy-MM-dd")
if PYQT_VERSION_STR >= "4.1.0":
editor.setCalendarPopup(True)
editor.setAlignment(Qt.AlignRight|
Qt.AlignVCenter)
return editor
else:
return QSqlRelationalDelegate.createEditor(self, parent,
option, index)
def setEditorData(self, editor, index):
if index.column() == DATE:
date = index.model().data(index, Qt.DisplayRole).toDate()
editor.setDate(date)
else:
QSqlRelationalDelegate.setEditorData(self, editor, index)
def setModelData(self, editor, model, index):
if index.column() == DATE:
model.setData(index, QVariant(editor.date()))
else:
QSqlRelationalDelegate.setModelData(self, editor, model,
index)
class MainForm(QDialog):
def __init__(self):
super(MainForm, self).__init__()
self.assetModel = QSqlRelationalTableModel(self)
self.assetModel.setTable("assets")
self.assetModel.setRelation(CATEGORYID,
QSqlRelation("categories", "id", "name"))
self.assetModel.setSort(ROOM, Qt.AscendingOrder)
self.assetModel.setHeaderData(ID, Qt.Horizontal, QVariant("ID"))
self.assetModel.setHeaderData(NAME, Qt.Horizontal,
QVariant("Name"))
self.assetModel.setHeaderData(CATEGORYID, Qt.Horizontal,
QVariant("Category"))
self.assetModel.setHeaderData(ROOM, Qt.Horizontal,
QVariant("Room"))
self.assetModel.select()
self.assetView = QTableView()
self.assetView.setModel(self.assetModel)
self.assetView.setItemDelegate(AssetDelegate(self))
self.assetView.setSelectionMode(QTableView.SingleSelection)
self.assetView.setSelectionBehavior(QTableView.SelectRows)
self.assetView.setColumnHidden(ID, True)
self.assetView.resizeColumnsToContents()
assetLabel = QLabel("A&ssets")
assetLabel.setBuddy(self.assetView)
self.logModel = QSqlRelationalTableModel(self)
self.logModel.setTable("logs")
self.logModel.setRelation(ACTIONID,
QSqlRelation("actions", "id", "name"))
self.logModel.setSort(DATE, Qt.AscendingOrder)
self.logModel.setHeaderData(DATE, Qt.Horizontal, QVariant("Date"))
self.logModel.setHeaderData(ACTIONID, Qt.Horizontal,
QVariant("Action"))
self.logModel.select()
self.logView = QTableView()
self.logView.setModel(self.logModel)
self.logView.setItemDelegate(LogDelegate(self))
self.logView.setSelectionMode(QTableView.SingleSelection)
self.logView.setSelectionBehavior(QTableView.SelectRows)
self.logView.setColumnHidden(ID, True)
self.logView.setColumnHidden(ASSETID, True)
self.logView.resizeColumnsToContents()
self.logView.horizontalHeader().setStretchLastSection(True)
logLabel = QLabel("&Logs")
logLabel.setBuddy(self.logView)
editActionsButton = QPushButton("&Edit Actions...")
editCategoriesButton = QPushButton("Ed&it Categories...")
quitButton = QPushButton("&Quit")
for button in (editActionsButton, editCategoriesButton, quitButton):
if MAC:
button.setDefault(False)
button.setAutoDefault(False)
else:
button.setFocusPolicy(Qt.NoFocus)
dataLayout = QVBoxLayout()
dataLayout.addWidget(assetLabel)
dataLayout.addWidget(self.assetView, 1)
dataLayout.addWidget(logLabel)
dataLayout.addWidget(self.logView)
buttonLayout = QVBoxLayout()
buttonLayout.addWidget(editActionsButton)
buttonLayout.addWidget(editCategoriesButton)
buttonLayout.addStretch()
buttonLayout.addWidget(quitButton)
layout = QHBoxLayout()
layout.addLayout(dataLayout, 1)
layout.addLayout(buttonLayout)
self.setLayout(layout)
self.connect(self.assetView.selectionModel(),
SIGNAL(("currentRowChanged(QModelIndex,QModelIndex)")),
self.assetChanged)
self.connect(editActionsButton, SIGNAL("clicked()"),
self.editActions)
self.connect(editCategoriesButton, SIGNAL("clicked()"),
self.editCategories)
self.connect(quitButton, SIGNAL("clicked()"), self.done)
self.assetChanged(self.assetView.currentIndex())
self.setMinimumWidth(650)
self.setWindowTitle("Asset Manager")
def done(self, result=1):
query = QSqlQuery()
query.exec_("DELETE FROM logs WHERE logs.assetid NOT IN"
"(SELECT id FROM assets)")
QDialog.done(self, 1)
def assetChanged(self, index):
if index.isValid():
record = self.assetModel.record(index.row())
id = record.value("id").toInt()[0]
self.logModel.setFilter(QString("assetid = %1").arg(id))
else:
self.logModel.setFilter("assetid = -1")
self.logModel.reset() # workaround for Qt <= 4.3.3/SQLite bug
self.logModel.select()
self.logView.horizontalHeader().setVisible(
self.logModel.rowCount() > 0)
if PYQT_VERSION_STR < "4.1.0":
self.logView.setColumnHidden(ID, True)
self.logView.setColumnHidden(ASSETID, True)
def addAsset(self):
row = (self.assetView.currentIndex().row()
if self.assetView.currentIndex().isValid() else 0)
QSqlDatabase.database().transaction()
self.assetModel.insertRow(row)
index = self.assetModel.index(row, NAME)
self.assetView.setCurrentIndex(index)
assetid = 1
query = QSqlQuery()
query.exec_("SELECT MAX(id) FROM assets")
if query.next():
assetid = query.value(0).toInt()[0]
query.prepare("INSERT INTO logs (assetid, date, actionid) "
"VALUES (:assetid, :date, :actionid)")
query.bindValue(":assetid", QVariant(assetid + 1))
query.bindValue(":date", QVariant(QDate.currentDate()))
query.bindValue(":actionid", QVariant(ACQUIRED))
query.exec_()
QSqlDatabase.database().commit()
self.assetView.edit(index)
def addAction(self):
index = self.assetView.currentIndex()
if not index.isValid():
return
QSqlDatabase.database().transaction()
record = self.assetModel.record(index.row())
assetid = record.value(ID).toInt()[0]
row = self.logModel.rowCount()
self.logModel.insertRow(row)
self.logModel.setData(self.logModel.index(row, ASSETID),
QVariant(assetid))
self.logModel.setData(self.logModel.index(row, DATE),
QVariant(QDate.currentDate()))
QSqlDatabase.database().commit()
index = self.logModel.index(row, ACTIONID)
self.logView.setCurrentIndex(index)
self.logView.edit(index)
def editActions(self):
form = ReferenceDataDlg("actions", "Action", self)
form.exec_()
def editCategories(self):
form = ReferenceDataDlg("categories", "Category", self)
form.exec_()
def main():
app = QApplication(sys.argv)
filename = os.path.join(os.path.dirname(__file__), "assetsdb.db")
create = not QFile.exists(filename)
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(filename)
if not db.open():
QMessageBox.warning(None, "Asset Manager",
QString("Database Error: %1")
.arg(db.lastError().text()))
sys.exit(1)
if create:
createFakeData()
form = MainForm()
form.show()
if create:
app.processEvents()
app.restoreOverrideCursor()
app.exec_()
del form
del db
解决方案
我想出于效率的原因，每次显示时都不会重新加载与QComboBoxof关联的模型，解决方案是使用以下方法重新加载该模型：QSqlRelationalDelegateQComboBoxselect()
class AssetDelegate(QSqlRelationalDelegate):
# ...
def createEditor(self, parent, option, index):
if index.column() == ROOM:
editor = QLineEdit(parent)
regex = QRegExp(
r"(?:0[1-9]|1[0124-9]|2[0-7])" r"(?:0[1-9]|[1-5][0-9]|6[012])"
)
validator = QRegExpValidator(regex, parent)
editor.setValidator(validator)
editor.setInputMask("9999")
editor.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
return editor
else:
editor = QSqlRelationalDelegate.createEditor(self, parent, option, index)
if isinstance(editor, QComboBox) and instance(
editor.model(), QSqlTableModel
):
editor.model().select()
return editor推荐阅读
- html - 图像未通过 css 显示
- javascript - 如何使用 axios 和 async / await 断言错误
- windows - 批处理脚本将具有相同扩展名的文件移动到另一个目录的父子路径上
- javascript - 保存在画布上绘制的图像
- anaconda - 我不能使用 anaconda 安装软件包
- java - 为什么我的程序只打印一个等级？
- java - 如何在 1 个测试类中使用相同的方法测试多个对象？
- powershell - PowerShell：将命令组合在一起，用于重定向输出
- java - 读取返回 StreamingResponseBody 作为响应的 API
- html - how do I compare textarea enter character