class MainWindow(QMainWindow): def __init__(self): super().__init__() self.setTable() self.setLayout() self.setCentralWidget(self.widget) self.resize(600,400) def setTable(self): self.search = QLineEdit() self.search.textChanged.connect(self.setFilter) self.table = QTableView() self.model = QSqlRelationalTableModel(db=db) self.model.setTable('album') self.table.setModel(self.model) index = self.model.fieldIndex("Title") self.model.setSort(index, Qt.DescendingOrder) self.model.setRelation(2, QSqlRelation("artist", "ArtistId","Name")) self.model.select() def setLayout(self): self.layout = QVBoxLayout() self.layout.addWidget(self.search) self.layout.addWidget(self.table) self.widget = QWidget() self.widget.setLayout(self.layout) def setFilter(self, word): filterLastName = f'Name LIKE "%{word}%"' self.model.setFilter(filterLastName)
class AccountManager(QWidget): def __init__(self): super().__init__() self.initialize_ui() def initialize_ui(self): self.setMinimumSize(1000, 600) self.setWindowTitle('Account Manager GUI') self.createConnection() self.createTable() self.setupWidgets() self.show() def createConnection(self): database = QSqlDatabase.addDatabase("QSQLITE") database.setDatabaseName("files/accounts.db") if not database.open(): print("Unable to open data source file.") sys.exit(1) # Error code 1 - signifies error # Check if tables we need exist in the database tables_needed = {'accounts', 'countries'} tables_not_found = tables_needed - set(database.tables()) if tables_not_found: QMessageBox.critical( None, "Error", f"The following tables are missing from the database: {tables_not_found}" ) sys.exit(1) def createTable(self): """ Set up the model, headers and populate the model. """ self.model = QSqlRelationalTableModel() self.model.setTable('accounts') self.model.setRelation(self.model.fieldIndex('country_id'), QSqlRelation('countries', 'id', 'country')) self.model.setHeaderData(self.model.fieldIndex('id'), Qt.Horizontal, "ID") self.model.setHeaderData(self.model.fieldIndex('employee_id'), Qt.Horizontal, "Employee ID") self.model.setHeaderData(self.model.fieldIndex('first_name'), Qt.Horizontal, "First") self.model.setHeaderData(self.model.fieldIndex('last_name'), Qt.Horizontal, "Last") self.model.setHeaderData(self.model.fieldIndex('email'), Qt.Horizontal, "E-mail") self.model.setHeaderData(self.model.fieldIndex('department'), Qt.Horizontal, "Dept.") self.model.setHeaderData(self.model.fieldIndex('country_id'), Qt.Horizontal, "Country") # populate the model with data self.model.select() def setupWidgets(self): """ Create instances of widgets, the table view and set layouts. """ icons_path = "icons" title = QLabel("Account Management System") title.setSizePolicy(QSizePolicy.Fixed, QSizePolicy.Fixed) title.setStyleSheet("font: bold 24px") add_product_button = QPushButton("Add Employee") add_product_button.setIcon( QIcon(os.path.join(icons_path, "add_user.png"))) add_product_button.setStyleSheet("padding: 10px") add_product_button.clicked.connect(self.addItem) del_product_button = QPushButton("Delete") del_product_button.setIcon( QIcon(os.path.join(icons_path, "trash_can.png"))) del_product_button.setStyleSheet("padding: 10px") del_product_button.clicked.connect(self.deleteItem) # set up sorting combobox sorting_options = [ "Sort by ID", "Sort by Employee ID", "Sort by First Name", "Sort by Last Name", "Sort by Department", "Sort by Country" ] sort_name_cb = QComboBox() sort_name_cb.addItems(sorting_options) sort_name_cb.currentTextChanged.connect(self.setSortingOrder) buttons_h_box = QHBoxLayout() buttons_h_box.addWidget(add_product_button) buttons_h_box.addWidget(del_product_button) buttons_h_box.addStretch() buttons_h_box.addWidget(sort_name_cb) # Widget to contain editing buttons edit_buttons = QWidget() edit_buttons.setLayout(buttons_h_box) # Create table view and set model self.table_view = QTableView() self.table_view.setModel(self.model) self.table_view.horizontalHeader().setSectionResizeMode( QHeaderView.Stretch) self.table_view.verticalHeader().setSectionResizeMode( QHeaderView.Stretch) self.table_view.setSelectionMode(QTableView.SingleSelection) self.table_view.setSelectionBehavior(QTableView.SelectRows) # Instantiate the delegate delegate = QSqlRelationalDelegate(self.table_view) self.table_view.setItemDelegate(delegate) # Main layout main_v_box = QVBoxLayout() main_v_box.addWidget(title, Qt.AlignLeft) main_v_box.addWidget(edit_buttons) main_v_box.addWidget(self.table_view) self.setLayout(main_v_box) def addItem(self): """ Add a new record to the last row of the table. """ last_row = self.model.rowCount() self.model.insertRow(last_row) id = 0 query = QSqlQuery() query.exec_("SELECT MAX(id) FROM ACCOUNTS") if query.next(): print(query.value(0)) id = int(query.value(0)) def deleteItem(self): """ Delete an entire row from the table. """ current_item = self.table_view.selectedIndexes() for index in current_item: self.model.removeRow(index.row()) self.model.select() def setSortingOrder(self, text): """ Sort the rows in table. """ # mode = 0 if text == "Sort by ID": self.model.setSort(self.model.fieldIndex('id'), Qt.AscendingOrder) # self.model.setSort(self.model.fieldIndex('id'), mode if Qt.DescendingOrder else Qt.AscendingOrder) elif text == "Sort by Employee ID": self.model.setSort(self.model.fieldIndex('employee_id'), Qt.AscendingOrder) elif text == "Sort by First Name": self.model.setSort(self.model.fieldIndex('first_name'), Qt.AscendingOrder) elif text == "Sort by Last Name": self.model.setSort(self.model.fieldIndex('last_name'), Qt.AscendingOrder) elif text == "Sort by Department": self.model.setSort(self.model.fieldIndex('department'), Qt.AscendingOrder) elif text == "Sort by Country": self.model.setSort(self.model.fieldIndex('country'), Qt.AscendingOrder) self.model.select()
class PhoneLogDlg(QDialog): FIRST, PREV, NEXT, LAST = range(4) def __init__(self, parent=None): super(PhoneLogDlg, self).__init__(parent) callerLabel = QLabel("&Caller:") self.callerEdit = QLineEdit() callerLabel.setBuddy(self.callerEdit) today = QDate.currentDate() startLabel = QLabel("&Start:") self.startDateTime = QDateTimeEdit() startLabel.setBuddy(self.startDateTime) self.startDateTime.setDateRange(today, today) self.startDateTime.setDisplayFormat(DATETIME_FORMAT) endLabel = QLabel("&End:") self.endDateTime = QDateTimeEdit() endLabel.setBuddy(self.endDateTime) self.endDateTime.setDateRange(today, today) self.endDateTime.setDisplayFormat(DATETIME_FORMAT) topicLabel = QLabel("&Topic:") topicEdit = QLineEdit() topicLabel.setBuddy(topicEdit) outcomeLabel = QLabel("&Outcome:") self.outcomeComboBox = QComboBox() outcomeLabel.setBuddy(self.outcomeComboBox) firstButton = QPushButton() firstButton.setIcon(QIcon(":/first.png")) prevButton = QPushButton() prevButton.setIcon(QIcon(":/prev.png")) nextButton = QPushButton() nextButton.setIcon(QIcon(":/next.png")) lastButton = QPushButton() lastButton.setIcon(QIcon(":/last.png")) addButton = QPushButton("&Add") addButton.setIcon(QIcon(":/add.png")) deleteButton = QPushButton("&Delete") deleteButton.setIcon(QIcon(":/delete.png")) quitButton = QPushButton("&Quit") quitButton.setIcon(QIcon(":/quit.png")) if not MAC: addButton.setFocusPolicy(Qt.NoFocus) deleteButton.setFocusPolicy(Qt.NoFocus) fieldLayout = QGridLayout() fieldLayout.addWidget(callerLabel, 0, 0) fieldLayout.addWidget(self.callerEdit, 0, 1, 1, 3) fieldLayout.addWidget(startLabel, 1, 0) fieldLayout.addWidget(self.startDateTime, 1, 1) fieldLayout.addWidget(endLabel, 1, 2) fieldLayout.addWidget(self.endDateTime, 1, 3) fieldLayout.addWidget(topicLabel, 2, 0) fieldLayout.addWidget(topicEdit, 2, 1, 1, 3) fieldLayout.addWidget(outcomeLabel, 3, 0) fieldLayout.addWidget(self.outcomeComboBox, 3, 1, 1, 3) navigationLayout = QHBoxLayout() navigationLayout.addWidget(firstButton) navigationLayout.addWidget(prevButton) navigationLayout.addWidget(nextButton) navigationLayout.addWidget(lastButton) fieldLayout.addLayout(navigationLayout, 4, 0, 1, 2) buttonLayout = QVBoxLayout() buttonLayout.addWidget(addButton) buttonLayout.addWidget(deleteButton) buttonLayout.addStretch() buttonLayout.addWidget(quitButton) layout = QHBoxLayout() layout.addLayout(fieldLayout) layout.addLayout(buttonLayout) self.setLayout(layout) self.model = QSqlRelationalTableModel(self) self.model.setTable("calls") self.model.setRelation(OUTCOMEID, QSqlRelation("outcomes", "id", "name")) self.model.setSort(STARTTIME, Qt.AscendingOrder) self.model.select() self.mapper = QDataWidgetMapper(self) self.mapper.setSubmitPolicy(QDataWidgetMapper.ManualSubmit) self.mapper.setModel(self.model) self.mapper.setItemDelegate(QSqlRelationalDelegate(self)) self.mapper.addMapping(self.callerEdit, CALLER) self.mapper.addMapping(self.startDateTime, STARTTIME) self.mapper.addMapping(self.endDateTime, ENDTIME) self.mapper.addMapping(topicEdit, TOPIC) relationModel = self.model.relationModel(OUTCOMEID) self.outcomeComboBox.setModel(relationModel) self.outcomeComboBox.setModelColumn(relationModel.fieldIndex("name")) self.mapper.addMapping(self.outcomeComboBox, OUTCOMEID) self.mapper.toFirst() firstButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.FIRST)) prevButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.PREV)) nextButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.NEXT)) lastButton.clicked.connect(lambda: self.saveRecord(PhoneLogDlg.LAST)) addButton.clicked.connect(self.addRecord) deleteButton.clicked.connect(self.deleteRecord) quitButton.clicked.connect(self.done) self.setWindowTitle("Phone Log") def done(self, result=None): self.mapper.submit() QDialog.done(self, True) def addRecord(self): row = self.model.rowCount() self.mapper.submit() self.model.insertRow(row) self.mapper.setCurrentIndex(row) now = QDateTime.currentDateTime() self.startDateTime.setDateTime(now) self.endDateTime.setDateTime(now) self.outcomeComboBox.setCurrentIndex( self.outcomeComboBox.findText("Unresolved")) self.callerEdit.setFocus() def deleteRecord(self): caller = self.callerEdit.text() starttime = self.startDateTime.dateTime().toString(DATETIME_FORMAT) if (QMessageBox.question( self, "Delete", "Delete call made by<br>{0} on {1}?".format(caller, starttime), QMessageBox.Yes | QMessageBox.No) == QMessageBox.No): return row = self.mapper.currentIndex() self.model.removeRow(row) self.model.submitAll() self.model.select() if row + 1 >= self.model.rowCount(): row = self.model.rowCount() - 1 self.mapper.setCurrentIndex(row) def saveRecord(self, where): row = self.mapper.currentIndex() self.mapper.submit() if where == PhoneLogDlg.FIRST: row = 0 elif where == PhoneLogDlg.PREV: row = 0 if row <= 1 else row - 1 elif where == PhoneLogDlg.NEXT: row += 1 if row >= self.model.rowCount(): row = self.model.rowCount() - 1 elif where == PhoneLogDlg.LAST: row = self.model.rowCount() - 1 self.mapper.setCurrentIndex(row)
class QmyMainWindow(QMainWindow): def __init__(self, parent=None): super().__init__(parent) #调用父类构造函数,创建窗体 self.ui = Ui_MainWindow() #创建UI对象 self.ui.setupUi(self) #构造UI界面 self.setCentralWidget(self.ui.tableView) # tableView显示属性设置 self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectItems) self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection) self.ui.tableView.setAlternatingRowColors(True) self.ui.tableView.verticalHeader().setDefaultSectionSize(22) self.ui.tableView.horizontalHeader().setDefaultSectionSize(100) ## ==============自定义功能函数============ def __getFieldNames(self): ##获取所有字段名称 emptyRec = self.tabModel.record() #获取空记录,只有字段名 self.fldNum = {} #字段名与序号的字典 for i in range(emptyRec.count()): fieldName = emptyRec.fieldName(i) self.fldNum.setdefault(fieldName) self.fldNum[fieldName] = i print(self.fldNum) def __openTable(self): ##打开数据表 self.tabModel = QSqlRelationalTableModel(self, self.DB) #数据表 self.tabModel.setTable("studInfo") #设置数据表 self.tabModel.setEditStrategy(QSqlTableModel.OnManualSubmit ) #数据保存方式,OnManualSubmit , OnRowChange self.tabModel.setSort(self.tabModel.fieldIndex("studID"), Qt.AscendingOrder) #排序 if (self.tabModel.select() == False): #查询数据失败 QMessageBox.critical( self, "错误信息", "打开数据表错误,错误信息\n" + self.tabModel.lastError().text()) return self.__getFieldNames() #获取字段名和序号 ##字段显示名 self.tabModel.setHeaderData(self.fldNum["studID"], Qt.Horizontal, "学号") self.tabModel.setHeaderData(self.fldNum["name"], Qt.Horizontal, "姓名") self.tabModel.setHeaderData(self.fldNum["gender"], Qt.Horizontal, "性别") self.tabModel.setHeaderData(self.fldNum["departID"], Qt.Horizontal, "学院") self.tabModel.setHeaderData(self.fldNum["majorID"], Qt.Horizontal, "专业") ## 设置代码字段的查询关系数据表 self.tabModel.setRelation(self.fldNum["departID"], QSqlRelation("departments", "departID", "department")) #学院 self.tabModel.setRelation(self.fldNum["majorID"], QSqlRelation("majors", "majorID", "major")) #专业 self.selModel = QItemSelectionModel(self.tabModel) #关联选择模型 ##selModel当前项变化时触发currentChanged信号 self.selModel.currentChanged.connect(self.do_currentChanged) ##选择行变化时 ## self.selModel.currentRowChanged.connect(self.do_currentRowChanged) self.ui.tableView.setModel(self.tabModel) #设置数据模型 self.ui.tableView.setSelectionModel(self.selModel) #设置选择模型 delgate = QSqlRelationalDelegate(self.ui.tableView) self.ui.tableView.setItemDelegate(delgate) #为关系型字段设置缺省代理组件 self.tabModel.select() #必须重新查询数据 ##更新actions和界面组件的使能状态 self.ui.actOpenDB.setEnabled(False) self.ui.actRecAppend.setEnabled(True) self.ui.actRecInsert.setEnabled(True) self.ui.actRecDelete.setEnabled(True) self.ui.actFields.setEnabled(True) ## ==========由connectSlotsByName() 自动连接的槽函数================== @pyqtSlot() def on_actOpenDB_triggered(self): dbFilename, flt = QFileDialog.getOpenFileName( self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)") if (dbFilename == ''): return #打开数据库 self.DB = QSqlDatabase.addDatabase("QSQLITE") #添加 SQL LITE数据库驱动 self.DB.setDatabaseName(dbFilename) #设置数据库名称 ## DB.setHostName() ## DB.setUserName() ## DB.setPassword() if self.DB.open(): #打开数据库 self.__openTable() #打开数据表 else: QMessageBox.warning(self, "错误", "打开数据库失败") @pyqtSlot() ##保存修改 def on_actSubmit_triggered(self): res = self.tabModel.submitAll() if (res == False): QMessageBox.information( self, "消息", "数据保存错误,错误信息\n" + self.tabModel.lastError().text()) else: self.ui.actSubmit.setEnabled(False) self.ui.actRevert.setEnabled(False) @pyqtSlot() ##取消修改 def on_actRevert_triggered(self): self.tabModel.revertAll() self.ui.actSubmit.setEnabled(False) self.ui.actRevert.setEnabled(False) @pyqtSlot() ##添加记录 def on_actRecAppend_triggered(self): self.tabModel.insertRow(self.tabModel.rowCount(), QModelIndex()) #在末尾添加一个记录 curIndex = self.tabModel.index(self.tabModel.rowCount() - 1, 1) #创建最后一行的ModelIndex self.selModel.clearSelection() #清空选择项 self.selModel.setCurrentIndex( curIndex, QItemSelectionModel.Select) #设置刚插入的行为当前选择行 @pyqtSlot() ##插入记录 def on_actRecInsert_triggered(self): curIndex = self.ui.tableView.currentIndex() #QModelIndex self.tabModel.insertRow(curIndex.row(), QModelIndex()) self.selModel.clearSelection() #清除已有选择 self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select) @pyqtSlot() ##删除记录 def on_actRecDelete_triggered(self): curIndex = self.selModel.currentIndex() #获取当前选择单元格的模型索引 self.tabModel.removeRow(curIndex.row()) #删除最后一行 @pyqtSlot() ##显示字段列表 def on_actFields_triggered(self): emptyRec = self.tabModel.record() #获取空记录,只有字段名 str = '' for i in range(emptyRec.count()): str = str + emptyRec.fieldName(i) + '\n' QMessageBox.information(self, "所有字段名", str) ## =============自定义槽函数=============================== def do_currentChanged(self, current, previous): ##更新actPost和actCancel 的状态 self.ui.actSubmit.setEnabled(self.tabModel.isDirty()) #有未保存修改时可用 self.ui.actRevert.setEnabled(self.tabModel.isDirty())
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, "ID") self.assetModel.setHeaderData(NAME, Qt.Horizontal, "Name") self.assetModel.setHeaderData(CATEGORYID, Qt.Horizontal, "Category") self.assetModel.setHeaderData(ROOM, Qt.Horizontal, "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, "Date") self.logModel.setHeaderData(ACTIONID, Qt.Horizontal, "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) addAssetButton = QPushButton("&Add Asset") deleteAssetButton = QPushButton("&Delete Asset") addActionButton = QPushButton("Add A&ction") deleteActionButton = QPushButton("Delete Ac&tion") editActionsButton = QPushButton("&Edit Actions...") editCategoriesButton = QPushButton("Ed&it Categories...") quitButton = QPushButton("&Quit") for button in (addAssetButton, deleteAssetButton, addActionButton, deleteActionButton, 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(addAssetButton) buttonLayout.addWidget(deleteAssetButton) buttonLayout.addWidget(addActionButton) buttonLayout.addWidget(deleteActionButton) 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.assetView.selectionModel().currentRowChanged.connect( self.assetChanged) addAssetButton.clicked.connect(self.addAsset) deleteAssetButton.clicked.connect(self.deleteAsset) addActionButton.clicked.connect(self.addAction) deleteActionButton.clicked.connect(self.deleteAction) editActionsButton.clicked.connect(self.editActions) editCategoriesButton.clicked.connect(self.editCategories) quitButton.clicked.connect(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()) #print(index.row()) id = record.value("id") self.logModel.setFilter("assetid = {0}".format(id)) else: self.logModel.setFilter("assetid = -1") #self.logModel.reset() # workaround for Qt <= 4.3.3/SQLite bug #self.logModel.beginResetModel() 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) #self.logModel.endResetModel() 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) query.prepare("INSERT INTO logs (assetid, date, actionid) " "VALUES (:assetid, :date, :actionid)") query.bindValue(":assetid", assetid + 1) query.bindValue(":date", QDate.currentDate()) query.bindValue(":actionid", ACQUIRED) query.exec_() QSqlDatabase.database().commit() #self.logModel.select() self.assetView.edit(index) def deleteAsset(self): index = self.assetView.currentIndex() if not index.isValid(): return QSqlDatabase.database().transaction() record = self.assetModel.record(index.row()) assetid = record.value(ID) logrecords = 1 query = QSqlQuery( "SELECT COUNT(*) FROM logs WHERE assetid = {0}".format(assetid)) if query.next(): logrecords = query.value(0) msg = ("<font color=red>Delete</font><br><b>{0}</b>" "<br>from room {1}").format(record.value(NAME), record.value(ROOM)) if logrecords > 1: msg += (", along with {0} log records".format(logrecords)) msg += "?" if (QMessageBox.question(self, "Delete Asset", msg, QMessageBox.Yes | QMessageBox.No) == QMessageBox.No): QSqlDatabase.database().rollback() return #query.exec_("DELETE FROM logs WHERE assetid = {0}" # .format(assetid)) #use model API self.logModel.setFilter("assetid={0}".format(assetid)) self.logModel.select() if self.logModel.rowCount() > 0: self.logModel.removeRows(0, self.logModel.rowCount()) self.logModel.submitAll() self.assetModel.removeRow(index.row()) self.assetModel.submitAll() QSqlDatabase.database().commit() self.assetModel.select() self.assetChanged(self.assetView.currentIndex()) 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) row = self.logModel.rowCount() self.logModel.insertRow(row) self.logModel.setData(self.logModel.index(row, ASSETID), assetid) self.logModel.setData(self.logModel.index(row, DATE), QDate.currentDate()) QSqlDatabase.database().commit() index = self.logModel.index(row, ACTIONID) self.logView.setCurrentIndex(index) self.logView.edit(index) def deleteAction(self): index = self.logView.currentIndex() if not index.isValid(): return record = self.logModel.record(index.row()) action = record.value(ACTIONID) if action == "Acquired": QMessageBox.information( self, "Delete Log", "The 'Acquired' log record cannot be deleted.<br>" "You could delete the entire asset instead.") return when = str(record.value(DATE)) if (QMessageBox.question(self, "Delete Log", "Delete log<br>{0} {1}?".format(when, action), QMessageBox.Yes | QMessageBox.No) == QMessageBox.No): return self.logModel.removeRow(index.row()) self.logModel.submitAll() self.logModel.select() def editActions(self): form = ReferenceDataDlg("actions", "Action", self) form.exec_() def editCategories(self): form = ReferenceDataDlg("categories", "Category", self) form.exec_()
APP = QApplication(sys.argv) # виджет-окно W_OBJ = QWidget() W_OBJ.setWindowTitle("Связь с внешней таблицей") # соединяемся с БД CONN = QSqlDatabase.addDatabase('QSQLITE') CONN.setDatabaseName('test.sqlite3') CONN.open() # модель-связанная таблица R_T_MODEL = QSqlRelationalTableModel(parent=W_OBJ) R_T_MODEL.setEditStrategy(QSqlTableModel.OnManualSubmit) R_T_MODEL.setTable('product') R_T_MODEL.setSort(1, QtCore.Qt.AscendingOrder) # Задаем для поля категории связь с таблицей списка категорий R_T_MODEL.setRelation(1, QSqlRelation('category', 'id', 'name')) # 'category', 'id', 'category' (имя первичной таблицы, # имя поля первичного ключа, имя поля, выводящегося на экран) R_T_MODEL.select() R_T_MODEL.setHeaderData(1, QtCore.Qt.Horizontal, 'Категория товара') R_T_MODEL.setHeaderData(2, QtCore.Qt.Horizontal, 'Название товара') R_T_MODEL.dataChanged.connect(R_T_MODEL.submitAll) # Размещаем объекты VBOX_OBJ = QVBoxLayout() TV_OBJ = QTableView() TV_OBJ.setModel(R_T_MODEL) TV_OBJ.setItemDelegateForColumn(1, QSqlRelationalDelegate(TV_OBJ))
class QmyMainWindow(QtWidgets.QMainWindow): def __init__(self, parent=None): super().__init__(parent) self.ui = Ui_MainWindow() self.ui.setupUi(self) self.setCentralWidget(self.ui.tableView) self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectItems) self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection) self.ui.tableView.setAlternatingRowColors(True) self.ui.tableView.verticalHeader().setDefaultSectionSize(22) self.ui.tableView.horizontalHeader().setDefaultSectionSize(100) def __getFieldNames(self): emptyRec = self.tabModel.record() self.fldNum = {} for i in range(emptyRec.count()): fieldName = emptyRec.fieldName(i) self.fldNum.setdefault(fieldName) self.fldNum[fieldName] = i print(self.fldNum) def __openTable(self): self.tabModel = QSqlRelationalTableModel(self, self.DB) self.tabModel.setTable("studInfo") self.tabModel.setEditStrategy(QSqlTableModel.OnManualSubmit) self.tabModel.setSort(self.tabModel.fieldIndex("studID"), Qt.AscendingOrder) if (self.tabModel.select() == False): QMessageBox.critical( self, "错误信息", "打开数据表错误,错误信息\n" + self.tabModel.lastError().text()) return self.__getFieldNames() self.tabModel.setHeaderData(self.fldNum["studID"], Qt.Horizontal, "学号") self.tabModel.setHeaderData(self.fldNum["name"], Qt.Horizontal, "姓名") self.tabModel.setHeaderData(self.fldNum["gender"], Qt.Horizontal, "性别") self.tabModel.setHeaderData(self.fldNum["departID"], Qt.Horizontal, "学院") self.tabModel.setHeaderData(self.fldNum["majorID"], Qt.Horizontal, "专业") self.tabModel.setRelation( self.fldNum["departID"], QSqlRelation("departments", "departID", "department")) self.tabModel.setRelation(self.fldNum["majorID"], QSqlRelation("majors", "majorID", "major")) self.selModel = QItemSelectionModel(self.tabModel) self.selModel.currentChanged.connect(self.do_currentChanged) self.ui.tableView.setModel(self.tabModel) self.ui.tableView.setSelectionModel(self.selModel) delgate = QSqlRelationalDelegate(self.ui.tableView) self.ui.tableView.setItemDelegate(delgate) self.tabModel.select() self.ui.actOpenDB.setEnabled(False) self.ui.actRecAppend.setEnabled(True) self.ui.actRecInsert.setEnabled(True) self.ui.actRecDelete.setEnabled(True) self.ui.actFields.setEnabled(True) @pyqtSlot() def on_actOpenDB_triggered(self): dbFilename, flt = QFileDialog.getOpenFileName( self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)") if (dbFilename == ''): return self.DB = QSqlDatabase.addDatabase("QSQLITE") self.DB.setDatabaseName(dbFilename) if self.DB.open(): self.__openTable() else: QMessageBox.warning(self, "错误", "打开数据库失败了") @pyqtSlot() def on_actSubmit_triggered(self): res = self.tabModel.submitAll() if (res == False): QMessageBox.information( self, "消息", "数据保存错误,错误信息\n" + self.tabModel.lastError().text()) else: self.ui.actSubmit.setEnabled(False) self.ui.actRevert.setEnabled(False) @pyqtSlot() def on_actRevert_triggered(self): self.tabModel.revertAll() self.ui.actSubmit.setEnabled(False) self.ui.actRevert.setEnabled(False) @pyqtSlot() def on_actRecInsert_triggered(self): curIndex = self.ui.tableView.currentIndex() self.tabModel.insertRow(curIndex.row(), QModelIndex()) self.selModel.clearSelection() self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select) @pyqtSlot() def on_actRecDelete_triggered(self): curIndex = self.selModel.currentIndex() self.tabModel.removeRow(curIndex.row()) @pyqtSlot() def on_actFields_triggered(self): emptyRec = self.tabModel.record() str = '' for i in range(emptyRec.count()): str = str + emptyRec.fieldName(i) + '\n' QMessageBox.information(self, "所有字段名", str) def do_currentChanged(self, current, previous): self.ui.actSubmit.setEnabled(self.tabModel.isDirty()) self.ui.actRevert.setEnabled(self.tabModel.isDirty())
def init_tag(self): #Selected tags when item is selected self.selected_tags = set() tag_model = QSqlTableModel() tag_model.setTable("tag") tag_model.setEditStrategy(QSqlTableModel.OnRowChange) #It can't be sorted otherwise wrong tags are selected when item is selected #tag_model.setSort(1, Qt.AscendingOrder) tag_model.select() #Added proxy model which is sorted on name tag_proxy_model = QSortFilterProxyModel(self) tag_proxy_model.setSourceModel(tag_model) tag_proxy_model.sort(1, Qt.AscendingOrder) self.tag_proxy_model = tag_proxy_model self.tag_model = tag_model tag_item_model = QSqlRelationalTableModel() tag_item_model.setTable("tag_item") #tag_item_model.setRelation(1, QSqlRelation('nutrition', 'ndbno', 'desc')) #tag_item_model.setRelation(2, QSqlRelation('tag', 'id', 'name')) tag_item_model.setEditStrategy(QSqlTableModel.OnManualSubmit) #tag_item_model.select() self.tag_item_model = tag_item_model tag_hier_model = QSqlRelationalTableModel() tag_hier_model.setTable("tag_hierarchy") tag_hier_model.setRelation(0, QSqlRelation('tag', 'id', 'name')) tag_hier_model.setRelation(1, QSqlRelation('tag', 'id', 'name')) tag_hier_model.setSort(0, Qt.AscendingOrder) tag_hier_model.setEditStrategy(QSqlTableModel.OnManualSubmit) tag_hier_model.select() #From add food self.cb_tag_select.setModel(tag_proxy_model) self.cb_tag_select.setModelColumn(1) self.tv_tag.setModel(tag_model) #self.tv_tag_item.setModel(tag_item_model) self.tv_tag_hierarchy.setModel(tag_hier_model) #ListView to choose which tags have nutrition self.lv_tag.setModel(tag_proxy_model) self.lv_tag.setModelColumn(1) #self.tv_tag_item.setItemDelegate(QSqlRelationalDelegate(self.tv_tag_item)) self.tv_tag_hierarchy.setItemDelegate( QSqlRelationalDelegate(self.tv_tag_hierarchy)) def add_tag(): print("Adding tag:") row = self.tv_tag.model().rowCount() self.tv_tag.model().insertRow(row) def add_hier(): print("Adding hier:") row = self.tv_tag_hierarchy.model().rowCount() self.tv_tag_hierarchy.model().insertRow(row) def add_item_tag(): print("Adding item_tag:") row = self.tv_tag_item.model().rowCount() self.tv_tag_item.model().insertRow(row) def save_hier(): print("Saving hier") self.tv_tag_hierarchy.model().submitAll() def select_nutri_tag(selectedIndex): ndbno = self._get_selected_ndbno(self.cb_item_tag.model() \ .record(self.cb_item_tag.currentIndex())) print("Item:", self.cb_item_tag.currentText(), ndbno) tags = self.session.query(TagItem) \ .filter(TagItem.ndbno==ndbno) \ .order_by(TagItem.tag_id) if self.lv_tag.selectionModel(): self.lv_tag.selectionModel().clearSelection() self.selected_tags.clear() for tag in tags: #print (tag.tag.name, tag.tag_id) orig_index = tag_model.createIndex(tag.tag_id, 1) index = tag_proxy_model.mapFromSource(orig_index) if index.isValid(): print("Valid index") self.lv_tag.selectionModel().select(index, QItemSelectionModel.Select) data = self.lv_tag.model().data(index) self.selected_tags.add(tag.tag_id) #print(data) def save_tag_item(): print("Saving tag item") selected_tags_now = set() for index in self.lv_tag.selectedIndexes(): mapped = self.lv_tag.model().mapToSource(index) record = tag_model.record(mapped.row()) selected_tags_now.add(record.field("id").value()) print( str(record.field("id").value()) + " - " + record.field("name").value()) added_tag_ids = selected_tags_now.difference(self.selected_tags) removed_tag_ids = self.selected_tags.difference(selected_tags_now) #print ("Unselected tags:" + ", ".join(map(lambda x: str(x), #removed_tag_ids))) #print ("New selected tags:" + ", ".join(map(lambda x: str(x), #added_tag_ids))) ndbno = self._get_selected_ndbno(self.cb_item_tag.model() \ .record(self.cb_item_tag.currentIndex())) print("Item:", self.cb_item_tag.currentText(), ndbno) query = QSqlQuery() ret = query.prepare( "DELETE FROM tag_item WHERE ndbno = :ndbno and tag_id = :tag_id") if not ret: print("Failed to prepare query") for tag_id in added_tag_ids: row = self.tag_item_model.rowCount() self.tag_item_model.insertRow(row) self.tag_item_model.setData( self.tag_item_model.createIndex(row, 0), ndbno, Qt.EditRole) self.tag_item_model.setData( self.tag_item_model.createIndex(row, 1), tag_id, Qt.EditRole) print("Adding tag id:", tag_id) #print (record.field("name").value(), record.field("id").value()) #self.tv_tag_item.model().submitAll() if not self.tag_item_model.submitAll(): QMessageBox.critical( None, "Error submitting", "Couldn't update model: " + self.tag_item_model.lastError().text()) self.tag_item_model.revertAll() query.bindValue(":ndbno", ndbno) for tag_id in removed_tag_ids: query.bindValue(":tag_id", tag_id) print("Removing tag_id:", tag_id) if not query.exec_(): print("Failed to executr query") #print (query.executedQuery()) self.pb_add_tag.pressed.connect(add_tag) self.pb_add_item_tag.pressed.connect(add_item_tag) self.pb_add_hier.pressed.connect(add_hier) self.pb_save_hier.pressed.connect(save_hier) self.pb_save_tag_item.pressed.connect(save_tag_item) self.cb_item_tag.currentIndexChanged.connect(select_nutri_tag)