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 Contacts(QMainWindow): def __init__(self, parent=None): super().__init__(parent) self.setWindowTitle("QTableView Example") self.resize(800, 600) # Set up the model self.model = QSqlRelationalTableModel(self) self.model.setTable("queue") self.model.setEditStrategy(QSqlTableModel.OnFieldChange) self.model.setHeaderData(0, Qt.Horizontal, "ID") self.model.setHeaderData(1, Qt.Horizontal, "Number") self.model.setHeaderData(2, Qt.Horizontal, "Enter Time") self.model.setHeaderData(3, Qt.Horizontal, "Call Time") self.model.setHeaderData(4, Qt.Horizontal, "Status") self.model.setHeaderData(5, Qt.Horizontal, "Destination") # ใช้ QSqlRelation อ่านคีย์นอกจากในตาราง queue แล้ว maps เข้ากับตาราง destination เลือก index กับ text ที่ต้องการแสดงในตาราง queue self.model.setRelation( 5, QSqlRelation("destination", "des_id", "des_name")) self.model.select() # Set up the view self.view = QTableView() self.view.setModel(self.model) self.view.setItemDelegate(QSqlRelationalDelegate(self.view)) self.view.setColumnHidden(0, True) self.view.setCornerButtonEnabled(False) #self.view.setEditTriggers(QAbstractItemView()) #self.headerView = QHeaderView() #self.view.setHorizontalHeader() #print(str(self.view.editTriggers())) self.view.resizeColumnsToContents() # self.cbxModel = QSqlQueryModel(self) # self.cbxModel.setQuery = "SELECT des_name FROM destination" # self.cbxModel.query() # สร้าง comboboxmodel ดึงข้อมูลจาก ex1.destination #self.cbxModel = QSqlTableModel(self) #self.cbxModel.setTable("destination") #self.cbxModel.select() # สร้าง comboboxview #self.cbxView = QComboBox() #self.cbxView.setModel(self.cbxModel) # เลือก column ที่จะมาแสดง #self.cbxView.setModelColumn(1) # วาด comboboxview ลงบน tableview ติดปัญหา comboboxview จะวาดลงใน record สุดท้ายเสมอ # for i in range(self.model.rowCount()): # i = self.view.model().index(1, 5) # self.view.setIndexWidget(i,self.cbxView) self.setCentralWidget(self.view)
class DBViewWindow(QWidget, Ui_DB_View_Form): def __init__(self, database_name): super().__init__() # uic.loadUi('db_view.ui', self) self.setupUi(self) # self.db_name = database_name # # Подключение БД к таблице отображения # # Подключение через QSqlRelationalTableModel # self.db = QSqlDatabase.addDatabase('QSQLITE') # self.db.setDatabaseName(self.db_name) # self.db.open() self.model = QSqlRelationalTableModel(self) self.model.setTable('goods') self.model.setRelation( 5, QSqlRelation('statuses', 'id_status', 'status_name')) self.model.setRelation( 6, QSqlRelation('goods_types', 'id_goods_type', 'goods_type_name')) self.model.setRelation( 7, QSqlRelation('goods_subtypes', 'id_goods_subtype', 'goods_subtype_name')) self.model.setRelation( 8, QSqlRelation('location', 'id_location', 'location_name')) self.model.setRelation( 9, QSqlRelation('responsibles', 'id_responsible', 'FIO')) self.refresh() self.tableView.doubleClicked.connect(self.table_clicked) # self.tableView.clicked.connect() self.refreshBtn.clicked.connect(self.refresh) self.save_all_btn.clicked.connect(self.submitall) def table_clicked(self): row = self.tableView.currentIndex().row() if row != -1: self.tableView.selectRow(row) index = self.tableView.currentIndex() self.edit_form = EditForm(index, self.model) self.edit_form.show() # Предположительно на выходе будет закрываться БД def __exit__(self, exc_type, exc_val, exc_tb): self.db.close() super().__exit__() def refresh(self): self.model.select() self.tableView.setModel(self.model) self.tableView.setItemDelegate(QSqlRelationalDelegate(self.tableView)) def submitall(self): if not self.model.submitAll(): print(self.model.lastError()) else: self.refresh()
def createTable(self): """ Create the table using model/view architecture. """ # Create the model model = QSqlRelationalTableModel() model.setTable('accounts') # Set up relationship for foreign keys model.setRelation(model.fieldIndex('country_id'), QSqlRelation('countries', 'id', 'country')) table_view = QTableView() table_view.setModel(model) table_view.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) # Populate the model with data model.select() # Main layout main_v_box = QVBoxLayout() main_v_box.addWidget(table_view) self.setLayout(main_v_box)
class TableModel(): def __init__(self, db): self.model = QSqlRelationalTableModel(db=db) self.model.setTable('balance') self.model.setEditStrategy(QSqlRelationalTableModel.OnFieldChange) self.model.setRelation(1, QSqlRelation('items', 'id', 'name')) self.model.setRelation(2, QSqlRelation('categories', 'id', 'name')) self.model.setRelation(4, QSqlRelation('currencies', 'id', 'name')) self.model.setRelation(5, QSqlRelation('users', 'id', 'full_name')) self.model.setRelation(6, QSqlRelation('places', 'id', 'name')) self.model.setRelation(8, QSqlRelation('measures', 'id', 'short')) self.model.setHeaderData(0, Qt.Horizontal, QtGui.qApp.tr("ID")) self.model.setHeaderData(1, Qt.Horizontal, QtGui.qApp.tr("Item")) self.model.setHeaderData(2, Qt.Horizontal, QtGui.qApp.tr("Category")) self.model.setHeaderData(3, Qt.Horizontal, QtGui.qApp.tr("Cost")) self.model.setHeaderData(4, Qt.Horizontal, QtGui.qApp.tr("Currency")) self.model.setHeaderData(5, Qt.Horizontal, QtGui.qApp.tr("By whom")) self.model.setHeaderData(6, Qt.Horizontal, QtGui.qApp.tr("Where")) self.model.setHeaderData(7, Qt.Horizontal, QtGui.qApp.tr("Qty/Amount")) self.model.setHeaderData(8, Qt.Horizontal, QtGui.qApp.tr("Units")) self.model.setHeaderData(9, Qt.Horizontal, QtGui.qApp.tr("is spending")) self.model.setHeaderData(10, Qt.Horizontal, QtGui.qApp.tr("Note")) self.model.setHeaderData(11, Qt.Horizontal, QtGui.qApp.tr("Date and " "Time")) self.model.removeColumn(12) self.model.removeColumn(13) if not self.model.select(): _log("Table model selection error[%s]: %s" % ( self.model.lastError().type(), self.model.lastError().text() )) def get_model(self): return self.model
class MainWindow(QMainWindow): def __init__(self): super().__init__() container = QWidget() layout = QVBoxLayout() self.search = QLineEdit() self.search.textChanged.connect(self.update_filter) self.table = QTableView() layout.addWidget(self.search) layout.addWidget(self.table) container.setLayout(layout) self.model = QSqlRelationalTableModel(db=db) self.table.setModel(self.model) self.model.setTable("Track") self.model.setRelation(2, QSqlRelation("Album", "AlbumId", "Title")) self.model.setRelation(3, QSqlRelation("MediaType", "MediaTypeId", "Name")) self.model.setRelation(4, QSqlRelation("Genre", "GenreId", "Name")) delegate = QSqlRelationalDelegate(self.table) self.table.setItemDelegate(delegate) self.model.select() self.setMinimumSize(QSize(1024, 600)) self.setCentralWidget(container) def update_filter(self, s): s = re.sub("[\W]+", "", s) filter_str = 'Name LIKE "%{}%"'.format(s) self.model.setFilter(filter_str)
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)
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())
def init_best_before(self): model = QSqlRelationalTableModel() model.setTable("best_before") model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit) model.setRelation(1, QSqlRelation('nutrition', 'ndbno', 'desc')) model.select() #Nutrition table needs to be populated since otherwise only 256 rows are read #And all inserts with ndbno > 100169 fail since they aren't found in nutrition #table nutri_model = model.relationModel(1) while nutri_model.canFetchMore(): nutri_model.fetchMore() self.bb_model = model nutri_model = self.lv_keys.model() #nutri_model = QSqlTableModel() #nutri_model.setTable("nutrition") ##nutri_model.setRelation(2, QSqlRelation('nutrition', 'ndbno', 'desc')) #nutri_model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit) #nutri_model.setSort(1,Qt.AscendingOrder) #nutri_model.select() self.cb_bb_item.setModel(nutri_model) self.cb_bb_item.setModelColumn(0) self.best_before_model = model self.tv_best_before.setModel(model) self.tv_best_before.setSortingEnabled(True) self.tv_best_before.sortByColumn(2, Qt.AscendingOrder) self.tv_best_before.setItemDelegate(QSqlRelationalDelegate(self.tv_best_before)) self.tv_best_before.show() #From Price self.cb_price_item.setModel(nutri_model) self.cb_price_item.setModelColumn(0) #From Tag self.cb_item_tag.setModel(nutri_model) self.cb_item_tag.setModelColumn(0) """Updates Best before table""" def update_bb(): print ("Updating Best Before") if not self.bb_model.submitAll(): QMessageBox.critical(None, "Error updating Best Before", "Couldn't update model: " + self.bb_model.lastError().text()) """Adds new data to best before table update_bb also needs to be called""" def add_bb(): print("Adding to BB") ndbno = self._get_selected_ndbno(self.cb_bb_item.model() \ .record(self.cb_bb_item.currentIndex())) print ("IDX:", self.cb_bb_item.currentIndex(), ndbno) row = self.bb_model.rowCount() self.bb_model.insertRow(row) #print ("NDBNO INDEX:", self.bb_model.fieldIndex("desc")) r = self.bb_model.record() #for i in range(r.count()): #print ("{} => {}".format(i,r.fieldName(i))) #for i in range(100000,100194): #out_ndbno = self.bb_model.setData(self.bb_model.createIndex(row, #self.bb_model.fieldIndex("desc")), i, #Qt.EditRole) #print ("{}? = {}".format(out_ndbno, i)) out_ndbno = self.bb_model.setData(self.bb_model.createIndex(row, self.bb_model.fieldIndex("desc")), ndbno, Qt.EditRole) out_time = self.bb_model.setData(self.bb_model.createIndex(row, self.bb_model.fieldIndex("time")), self.de_bb.date(), Qt.EditRole) print ("NDBNO:", out_ndbno, "TIME:", out_time) self.update_bb = update_bb self.add_bb = add_bb self.buttonBox_3.button(QDialogButtonBox.SaveAll).clicked.connect(self.update_bb) self.buttonBox_3.button(QDialogButtonBox.Apply).clicked.connect(self.add_bb)
class NormaOdk(QWidget): def __init__(self, parent): super(QWidget, self).__init__(parent) self.skrot = QShortcut(QKeySequence(Qt.Key_Return), self) self.naglowki = { 'iddetale': 'ID', 'nr_detalu': 'Detal', 'maszyna': 'Maszyna', "ilosc_m": 'Ilość maszyn', 'ilosc_szt': 'Ilość sztuk na operację', 'nazwa_op': 'Nazwa operacji', 'nr_op': 'Nr operacji', 'tm': 'Czas Tm [s]', 'tp': 'Czas Tp [s]', 'tj': 'Czas Tj [h]', 'norma': 'Norma [szt.]', 'uwagi': 'Uwagi', 'id_uzytkownika': 'Użytkownik' } self.proxy = QSortFilterProxyModel(self) self.parent = parent self.formularz = QGroupBox("Normy") self.lbl_w = QLabel("Wyszukaj") self.edit_w = QLineEdit(self) self.table = QTableView(self) self.btn_odswiez = QPushButton("Odśwież bazę") sciezka = czy_istnieje() self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName(sciezka) if self.db.open(): print('Otworzono bazę danych') self.model = QSqlRelationalTableModel(self, self.db) self.initUI() def initUI(self): # Zainicjowanie tabeli zawsze przed wszystkim self.tabela() # lista wybieralna with open('./resources/Maszyny i operacje.json', 'r', encoding='utf-8') as file: plik_json = json.load(file) masz = plik_json['Maszyny'] operacje = plik_json['Operacje'] self.table.setItemDelegateForColumn(2, ComboDelegate(self, masz)) self.table.setItemDelegateForColumn(6, ComboDelegate(self, operacje)) for row in range(0, self.model.rowCount()): self.table.openPersistentEditor(self.model.index(row, 2)) self.table.openPersistentEditor(self.model.index(row, 6)) # Zatwierdzenie ok_button = QPushButton("Dodaj") cancel_button = QPushButton("Cofnij") hbox = QHBoxLayout() hbox.addStretch(1) hbox.addWidget(self.btn_odswiez) hbox.addWidget(ok_button) hbox.addWidget(cancel_button) # Layouty layout_v = QVBoxLayout() layout_h = QHBoxLayout() # Tabela self.proxy.setSourceModel(self.model) self.table.setModel(self.proxy) # przyporządkowanie layout_h.addWidget(self.lbl_w) layout_h.addWidget(self.edit_w) layout_v.addLayout(layout_h) layout_v.addWidget(self.table) self.formularz.setLayout(layout_v) main_layout = QVBoxLayout() main_layout.addWidget(self.formularz) main_layout.addLayout(hbox) self.setLayout(main_layout) # export # self.export() # Funkcje cancel_button.clicked.connect(self.anulowanie) ok_button.clicked.connect(self.dodaj) self.edit_w.textChanged.connect(self.wyszukiwanie) self.btn_odswiez.clicked.connect(self.refresh_db) self.skrot.activated.connect(self.refresh_db) # Menu kontekstowe własne self.table.setContextMenuPolicy(Qt.CustomContextMenu) self.table.customContextMenuRequested.connect(self.prawoklik) def prawoklik(self): menu = QMenu(self) if self.model.rowCount(): akcja = QAction('Usuń wiersz', self) akcja.triggered.connect(self.usun_wiersz) menu.addAction(akcja) menu.exec_(QCursor.pos()) def usun_wiersz(self): ok = QMessageBox.question(self, 'Potwierdzenie', 'Czy na pewno chcesz usunąć pozycję?', QMessageBox.Ok, QMessageBox.Cancel) if ok == QMessageBox.Ok: selected = self.table.currentIndex() self.model.removeRow(selected.row()) self.model.submitAll() self.model.select() @pyqtSlot(str) def wyszukiwanie(self, text): search = QRegExp(text, Qt.CaseInsensitive, QRegExp.RegExp) self.proxy.setFilterRegExp(search) # Odpowiedzialne za kolumnę, po której filtruje self.proxy.setFilterKeyColumn(-1) @pyqtSlot() def uzupelniene(self): # Pobranie tp, tm z bazy query = 'SELECT iddetale, tm, tp, ilosc_m, ilosc_szt FROM detale' dane_db = multipolaczenie(query) for i in range(len(dane_db)): # if dane_db[i][1] and dane_db[i][2]: tm = dane_db[i][1] tp1 = dane_db[i][2] ilosc_m = dane_db[i][3] ilosc_szt = dane_db[i][4] if not ilosc_m: ilosc_m = 1 zm = 1 else: zm = 0.95 if not ilosc_szt: ilosc_szt = 1 if isinstance(tm, int) and isinstance(tp1, int): tw = tm + tp1 else: tw = 0 tp2 = tw * 0.05 tj = (tw + tp2) * 1.1 tjh = tj / 3600 if tj != 0: norma = 8 / tj * 3600 * ilosc_m * zm * ilosc_szt else: norma = 0 print(round(norma)) # update bazy query = 'UPDATE "detale" SET "tj" = ' + str(round( tjh, 5)) + ', "norma" = ' + str( round(norma)) + ' WHERE "iddetale" = ' + str(dane_db[i][0]) update_bazy(query) # query = 'UPDATE "detale" SET "norma" = ' + str(round(norma)) + # ' WHERE "iddetale" = ' + str(dane_db[i][0]) update_bazy(query) @pyqtSlot() def refresh_db(self): try: self.uzupelniene() except: pass # Odświeżenie tabeli self.model.select() def tabela(self): self.model.setTable('detale') self.model.setRelation( 12, QSqlRelation('uzytkownicy', 'iduzytkownicy', 'nazwa_uz')) # Za zmianę w bazie odpowiada OnFieldChange self.model.setEditStrategy(QSqlTableModel.OnFieldChange) # Ustawianie nagłówków ilosc_kolumn = self.model.columnCount() for i in range(ilosc_kolumn): nazwa_kolumn = self.model.headerData(i, Qt.Horizontal) self.model.setHeaderData(i, Qt.Horizontal, self.naglowki[nazwa_kolumn]) self.model.select() # Odpowiada za edycję pojednynczym kliknieciem ''' Constant Value Description QAbstractItemView::NoEditTriggers 0 No editing possible. QAbstractItemView::CurrentChanged 1 Editing start whenever current item changes. QAbstractItemView::DoubleClicked 2 Editing starts when an item is double clicked. QAbstractItemView::SelectedClicked 4 Editing starts when clicking on an already selected item. QAbstractItemView::EditKeyPressed 8 Editing starts when the platform edit key has been pressed over an item. QAbstractItemView::AnyKeyPressed 16 Editing starts when any key is pressed over an item. QAbstractItemView::AllEditTriggers 31 Editing starts for all above actions. ''' if self.odczyt(): self.table.setEditTriggers(QAbstractItemView.AllEditTriggers) else: self.table.setEditTriggers(QAbstractItemView.NoEditTriggers) self.table.setSelectionMode(QAbstractItemView.SingleSelection) self.table.verticalHeader().setVisible(False) self.table.setSortingEnabled(True) self.table.resizeColumnsToContents() self.table.setModel(self.model) self.table.setAlternatingRowColors(True) self.table.resizeColumnsToContents() # self.table.doubleClicked.connect(self.klikniecie) def anulowanie(self): self.parent.statusBar().clearMessage() from opcje_qt import Wewnatrz menu_gl = Wewnatrz(self.parent) self.parent.setCentralWidget(menu_gl) def odczyt(self): id = str(self.parent.id_user[0]) query = 'SELECT odczyt FROM uzytkownicy WHERE iduzytkownicy=' + id return polaczenie(query)[0] def dodaj(self): poz, masz, op, tm, tp, ok = MultiDialog().getMultidialog(self) print(poz, masz, op, tm, tp, ok) id = self.parent.id_user[0] if ok and poz: query = "INSERT INTO detale(nr_detalu,maszyna,nazwa_op,tm,tp," \ "id_uzytkownika) VALUES ('" + poz + "','" + masz + "','" + op + "','" + tm + "','" + tp + "','" + str( id) + "');" print(query) polaczenie(query) if tm and tp: try: self.uzupelniene() except: pass self.model.select() self.parent.statusBar().showMessage("Dodano nową pozycję", 10000) else: print("Nie wpisano pozycji")
class Ui_MainWindow(object): def setupUi(self, MainWindow): MainWindow.setObjectName("MainWindow") MainWindow.resize(700, 600) MainWindow.setMinimumSize(QtCore.QSize(700, 600)) MainWindow.setMaximumSize(QtCore.QSize(700, 600)) self.centralwidget = QtWidgets.QWidget(MainWindow) self.centralwidget.setObjectName("centralwidget") self.btn_call = QtWidgets.QPushButton(self.centralwidget) self.btn_call.setGeometry(QtCore.QRect(558, 480, 131, 71)) self.btn_call.setObjectName("btn_call") font = QtGui.QFont() font.setPointSize(11) self.btn_call.setFont(font) self.btn_add = QtWidgets.QPushButton(self.centralwidget) self.btn_add.setGeometry(QtCore.QRect(10, 480, 89, 71)) self.btn_add.setStyleSheet("") self.btn_add.setObjectName("btn_add") font = QtGui.QFont() font.setPointSize(11) self.btn_add.setFont(font) self.btn_delete = QtWidgets.QPushButton(self.centralwidget) self.btn_delete.setGeometry(QtCore.QRect(120, 480, 89, 71)) self.btn_delete.setObjectName("btn_delete") font = QtGui.QFont() font.setPointSize(11) self.btn_delete.setFont(font) self.btn_search = QtWidgets.QPushButton(self.centralwidget) self.btn_search.setGeometry(QtCore.QRect(230, 480, 89, 71)) self.btn_search.setObjectName("btn_search") font = QtGui.QFont() font.setPointSize(11) self.btn_search.setFont(font) self.lineEdit = QtWidgets.QLineEdit(self.centralwidget) self.lineEdit.setGeometry(QtCore.QRect(10, 434, 311, 31)) font = QtGui.QFont() font.setPointSize(14) self.lineEdit.setFont(font) self.lineEdit.setObjectName("lineEdit") self.comboBox = QtWidgets.QComboBox(self.centralwidget) self.comboBox.setGeometry(QtCore.QRect(335, 434, 121, 31)) self.comboBox.setObjectName("comboBox") font = QtGui.QFont() font.setPointSize(11) self.comboBox.setFont(font) self.tableView = QtWidgets.QTableView(self.centralwidget) self.tableView.setGeometry(QtCore.QRect(10, 10, 681, 411)) self.tableView.setObjectName("tableView") font = QtGui.QFont() font.setPointSize(11) self.tableView.setFont(font) MainWindow.setCentralWidget(self.centralwidget) self.menubar = QtWidgets.QMenuBar(MainWindow) self.menubar.setGeometry(QtCore.QRect(0, 0, 700, 22)) self.menubar.setObjectName("menubar") self.menuSetting = QtWidgets.QMenu(self.menubar) self.menuSetting.setObjectName("menuSetting") MainWindow.setMenuBar(self.menubar) self.statusbar = QtWidgets.QStatusBar(MainWindow) self.statusbar.setObjectName("statusbar") MainWindow.setStatusBar(self.statusbar) self.action_Invite_window = QtWidgets.QAction(MainWindow) self.action_Invite_window.setObjectName("action_Invite_window") # self.actionPlace_windows = QtWidgets.QAction(MainWindow) # self.actionPlace_windows.setObjectName("actionPlace_windows") self.actionini_py = QtWidgets.QAction(MainWindow) self.actionini_py.setObjectName("actionini_py") self.actionDestination = QtWidgets.QAction(MainWindow) self.actionDestination.setObjectName("actionDestination") self.actionDrop_tablel = QtWidgets.QAction(MainWindow) self.actionDrop_tablel.setObjectName("actionDrop_tablel") self.menuSetting.addAction(self.action_Invite_window) # self.menuSetting.addAction(self.actionPlace_windows) self.menuSetting.addAction(self.actionDestination) self.menuSetting.addAction(self.actionDrop_tablel) self.menuSetting.addSeparator() self.menuSetting.addAction(self.actionini_py) self.menubar.addAction(self.menuSetting.menuAction()) self.retranslateUi(MainWindow) QtCore.QMetaObject.connectSlotsByName(MainWindow) # เรียกฟังชั่นเพื่อโหลด model ลง table self.loadData() self.btn_add.clicked.connect(self.insertData) self.btn_delete.clicked.connect(self.deleteData) self.btn_call.clicked.connect(self.speak) self.btn_search.clicked.connect(self.searchData) self.actionDestination.triggered.connect(self.show_desDialog) def show_desDialog(self): self.desDialog.show() def retranslateUi(self, MainWindow): _translate = QtCore.QCoreApplication.translate MainWindow.setWindowTitle(_translate("MainWindow", "Simple Queue")) self.btn_call.setWhatsThis( _translate( "MainWindow", "<html><head/><body><p>คลิ๊กเพื่อเรียกคิวที่เลือกจากตารางคิว</p></body></html>" )) self.btn_call.setText(_translate("MainWindow", "เรียกคิว")) self.btn_add.setWhatsThis( _translate( "MainWindow", "<html><head/><body><p>คลิ๊กเพื่อเปิดเมนูการเพิ่มคิวด้วยตัวเอง</p></body></html>" )) self.btn_add.setText(_translate("MainWindow", "เพิ่มคิว")) self.btn_delete.setWhatsThis( _translate( "MainWindow", "<html><head/><body><p>คลิ๊กเพื่อลบคิวที่ป้อนค่าผิดออกจากตารางคิว</p></body></html>" )) self.btn_delete.setText(_translate("MainWindow", "ลบคิว")) self.btn_search.setWhatsThis( _translate( "MainWindow", "<html><head/><body><p>คลิ๊กเพื่อโหลดตารางคิวใหม่</p></body></html>" )) self.btn_search.setText(_translate("MainWindow", "ค้นหาคิว")) self.menuSetting.setTitle(_translate("MainWindow", "ตั้งค่า")) self.action_Invite_window.setText( _translate("MainWindow", "Call sentence setting")) #self.actionPlace_windows.setText(_translate("MainWindow", "Place sentence")) self.actionini_py.setText(_translate("MainWindow", "ini.py")) self.actionDestination.setText( _translate("MainWindow", "Destination setting")) self.actionDrop_tablel.setText(_translate("MainWindow", "Drop tablel")) def loadData(self): self.lineEdit.clear() self.lineEdit.setFocus() # สร้าง model เรียก data จาก database # ใช้ QSqlRelationalTableModel สำหรับตารางที่มีคีย์นอก self.model = QSqlRelationalTableModel() self.model.setTable('queue') self.model.setEditStrategy(QSqlTableModel.OnFieldChange) self.model.setHeaderData(0, Qt.Horizontal, 'ID') self.model.setHeaderData(1, Qt.Horizontal, "รายชื่อ") self.model.setHeaderData(2, Qt.Horizontal, "เวลาเพิ่มคิว") self.model.setHeaderData(3, Qt.Horizontal, "เวลาเรียกคิว") self.model.setHeaderData(4, Qt.Horizontal, "สถาณะคิว") self.model.setHeaderData(5, Qt.Horizontal, "ปลายทาง") self.model.setHeaderData(6, Qt.Horizontal, "Option") # ให้ column#5 เป็นคีย์นอกดึงตารางนอกมาแสดง self.model.setRelation( 5, QSqlRelation("destination", "des_id", "des_name")) # เรียกใช้ model เรียกใช้ได้จากทุกที่ใน class ไม่ต้องเรียก loadData() self.model.select() # ให้ tableView เลือก data จาก model ไปแสดง self.tableView.setModel(self.model) self.tableView.setItemDelegate(QSqlRelationalDelegate(self.tableView)) self.tableView.setColumnHidden(0, True) self.tableView.setCornerButtonEnabled(False) self.tableView.setSortingEnabled(True) self.tableView.setColumnWidth(1, 210) # เรียก fetchData เพื่อ fetch data เข้ามาใน model ให้หมด self.fetchData() # เมื่อ click ให้เลือกทั้งแถว self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows) # สร้าง model เรียกตาราง destination แล้วยัดเข้า combobox self.desModel = QSqlQueryModel() selectQuery = QSqlQuery() selectQuery.prepare('SELECT des_name FROM destination') # print('QUERY = ' + str(selectQuery.lastQuery())) if selectQuery.exec(): self.desModel.setQuery(selectQuery) # print('SELECT des_name COMPLETE') self.comboBox.setModel(self.desModel) else: print('SELECT FALSE = ' + selectQuery.lastError().text()) # สร้าง obj ของ destination setting ไว้รอเรียก self.desDialog = QtWidgets.QDialog() self.ui = destination2.Ui_Dialog() self.ui.setupUi(self.desDialog) self.tableView.selectRow(0) def insertData(self): q_number = self.lineEdit.text() q_localtime = time.localtime() q_enter_time = time.strftime("%H:%M:%S", q_localtime) # q_number ต้องไม่ใช่ค่าเว้นวรรค และ ต้องไม่ใช่ค่าว่าง if not q_number.isspace() and q_number != '': # เรียก getDestination_id เพื่อหาค่า des_id เพื่อใช้ในคำสั่ง sql INSERT des_id = self.getDestination_id() try: insertQuery = QSqlQuery() insertQuery.prepare( "INSERT INTO queue " + "(q_number,q_enter_time,des_id) " + "VALUES " + f"('{q_number}','{q_enter_time}',{des_id})") print('Query = ' + insertQuery.lastQuery()) if insertQuery.exec(): print('INSERT COMPLETE') self.loadData() else: print('INSERT FALSE = ' + insertQuery.lastError().text()) except (Error) as e: print( str(time.strftime("%H:%M:%S : ", time.localtime())) + 'ERROR :' + str(e)) else: self.showDialog('กรุณากรอกหมายเลขที่จะเพิ่มก่อน') def getDestination_id(self): # สร้างมาเพื่อให้ return ค่า des_id ในตาราง destination กลับไป temp = self.comboBox.currentText() desModel = QSqlQueryModel() selectQuery = QSqlQuery() selectQuery.prepare('SELECT des_id,des_name From destination') if selectQuery.exec(): desModel.setQuery(selectQuery) for i in range(desModel.rowCount()): if temp == desModel.index(i, 1).data(): return desModel.index(i, 0).data() else: pass else: print('SELECT FALSE = ' + selectQuery.lastError().text()) def insertDev(self): j = 10 for i in range(50): q_number = random.randint(0, 100000) q_enter_time = f'18:{j}:00' des_id = 4 insertQuery = QSqlQuery() insertQuery.prepare("INSERT INTO queue " + "(q_number,q_enter_time,des_id) " + "VALUES " + f"('{q_number}','{q_enter_time}',{des_id})") insertQuery.exec() self.model.select() j += 1 print('insertDEV complete') def searchData(self): self.fetchData() q_number = self.lineEdit.text() totleRow = self.tableView.model().rowCount() foundStatus = False # q_number ต้องไม่ใช่ค่าเว้นวรรค และ ต้องไม่ใช่ค่าว่าง if not q_number.isspace() and q_number != '': i = 0 for i in range(totleRow): # เก็บค่า q_number จาก tableView s_number = self.tableView.model().data( self.tableView.model().index(i, 1)) # เทียบค่า q_number ของ lineEdit กับ tableView if q_number == s_number: # print('found this number.') foundStatus = True break else: foundStatus = False if foundStatus == True: self.tableView.selectRow(i) else: self.showDialog('ค้นหมายเลข ' + q_number + ' ไม่พบ') else: self.showDialog('กรุณากรอกหมายเลขที่จะค้นก่อน') def deleteData(self): try: # เก็บค่า text จาก column current_row = self.tableView.selectedIndexes() current_itemUse = current_row[0] q_number = self.tableView.model().data( self.tableView.model().index(current_itemUse.row(), 1)) except: self.showDialog('กรุณาเลือกหมายเลขที่จะลบก่อน') return if q_number == None: self.showDialog('เกิดข้อผิดพลาดระหว่างการลบ') self.model.select() return # ยืนยันการลบด้วย code 1024 temp = self.confDelete(q_number) if temp == 1024: current_item = self.tableView.selectedIndexes() for index in current_item: self.model.removeRow(index.row()) self.model.select() print('Record deleted.') self.showDialog('ลบหมายเลขแล้ว') else: print('User cancel delete this record.') def confDelete(self, q_number): # สร้าง dialog เพื่อยืนยันการลบ และ return code ยืนยันการลบด้วย code 1024 confMsg = QtWidgets.QMessageBox() confMsg.setIcon(QtWidgets.QMessageBox.Critical) confMsg.setText('ยืนยันที่จะลบหมายเลข : ' + q_number) confMsg.setWindowTitle('แจ้งเตือนการลบ') confMsg.setStandardButtons(QtWidgets.QMessageBox.Ok | QtWidgets.QMessageBox.Cancel) reval = confMsg.exec_() return (reval) def btn_delClicked(self): button = QtGui.QGuiApplication.focusObject() index = self.tableView.indexAt(button.pos()) if index.isValid(): print(index.row(), index.column()) def showDialog(self, message): msg = QtWidgets.QMessageBox() msg.setWindowTitle('แจ้งเตือน') msg.setIcon(QtWidgets.QMessageBox.Information) msg.setText( # message + str(time.strftime(" @ %H:%M:%S ", time.localtime()))) message) msg.exec_() def speak(self): try: # เก็บค่า text จาก column current_row = self.tableView.selectedIndexes() current_inUse = current_row[0] q_numberText = self.tableView.model().data( self.tableView.model().index(current_inUse.row(), 1)) des_idText = self.tableView.model().data( self.tableView.model().index(current_inUse.row(), 5)) except: self.showDialog('กรุณาเลือกหมายเลขที่จะเรียกก่อน') return # ยืนยันการเรียกด้วย code 1024 temp = self.confSpeak(q_numberText) q_localtime = time.localtime() q_call_time = time.strftime("%H:%M:%S", q_localtime) if temp == 1024: # pyttsx3 engine # engine = pyttsx3.init() # """ RATE""" # rate = engine.getProperty('rate') # #print (rate) # engine.setProperty('rate', 100) # """VOLUME""" # volume = engine.getProperty('volume') # #print (volume) # engine.setProperty('volume',1.0) # """VOICE""" # voices = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Speech\Voices\Tokens\TTS_THAI' # engine.setProperty('voice',voices) # engine.say('ขอเชิญหมายเลข' + q_numberText + 'ที่ช่อง' + des_idText + 'นะครับ') # print('result = :'+ q_numberText) # engine.runAndWait() # engine.stop() # gTTS engine speech_text = 'ขอเชิญคุณ ' + q_numberText + ' ที่ช่อง ' + des_idText + ' ค่ะ' tts = gTTS(speech_text, lang='th') tts.save('temp.mp3') speech_file = pyglet.media.load('temp.mp3', streaming=False) speech_file.play() sleep(speech_file.duration) # prevent from killing os.remove('temp.mp3') # remove temperory file try: insertQuery = QSqlQuery() insertQuery.prepare("UPDATE queue SET q_call_time = " + f"'{q_call_time}'" + " , q_call_status = 1 WHERE q_number = " + f"'{q_numberText}'") print('Query after speek ' + insertQuery.lastQuery()) if insertQuery.exec(): print('UPDATE COMPLETE') self.loadData() else: print('UPDATE FALSE = ' + insertQuery.lastError().text()) except (Error) as e: print('ERROR : ' + str(e)) else: print('User decided cancel call.') def confSpeak(self, q_numberText): # สร้าง dialog เพื่อยืนยันการเรียกและ return code ยืนยันการเรียกด้วย code 1024 confMsg = QtWidgets.QMessageBox() confMsg.setIcon(QtWidgets.QMessageBox.Warning) confMsg.setText('ยืนยันที่จะเรียกหมายเลข : ' + q_numberText) confMsg.setWindowTitle('แจ้งเตือน') confMsg.setStandardButtons(QtWidgets.QMessageBox.Ok | QtWidgets.QMessageBox.Cancel) reval = confMsg.exec_() return (reval) def fetchData(self): self.model.select() # fetch ข้อมูลจากตารางออกมาให้หมด มีผลตอนใช้ search while self.model.canFetchMore() == True: self.model.fetchMore() print('rowCoundt = ' + str(self.model.rowCount())) self.tableView.scrollToBottom()
class MainWindow(QtWidgets.QMainWindow): def __init__(self) -> None: super().__init__() self.db = QSqlDatabase.addDatabase("QSQLITE") self.dbpath = DEFAULT_DATABASE_PATH self.qmainwindow = QtWidgets.QMainWindow() # define buttons vehaviour self.button_actions: Dict[str, QtWidgets.QAction] = {} self.define_buttons() # UI variables self.label = QtWidgets.QLabel() self.combo_box = QtWidgets.QComboBox() self.table_model = QSqlRelationalTableModel() self.table_view = QtWidgets.QTableView() # create window and show it self.draw() self.qmainwindow.show() def draw(self) -> None: self.qmainwindow.setObjectName("MainWindow") self.qmainwindow.resize(800, 600) self.qmainwindow.setWindowTitle("Database manager") widget = QtWidgets.QWidget(self.qmainwindow) widget.setObjectName("widget") self.qmainwindow.setCentralWidget(widget) self.toolbar = self.qmainwindow.addToolBar("ToolBar") for _, action in self.button_actions.items(): self.toolbar.addAction(action) _ = QtWidgets.QLabel(widget) _.setGeometry( QtCore.QRect(QtCore.QPoint(10, 10), QtCore.QSize(105, 16))) _.setText("Database path: ") self.label = QtWidgets.QLabel(widget) self.label.setGeometry( QtCore.QRect(QtCore.QPoint(110, 10), QtCore.QSize(750, 16))) self.label.setText(self.dbpath) _ = QtWidgets.QLabel(widget) _.setGeometry( QtCore.QRect(QtCore.QPoint(10, 30), QtCore.QSize(105, 16))) _.setText("Choosen table: ") self.combo_box = QtWidgets.QComboBox(self.qmainwindow) self.combo_box.setGeometry( QtCore.QRect(QtCore.QPoint(100, 55), QtCore.QSize(200, 16))) self.combo_box.currentIndexChanged.connect(self.update_table) _ = QtWidgets.QLabel(widget) _.setGeometry( QtCore.QRect(QtCore.QPoint(10, 65), QtCore.QSize(105, 16))) _.setText("Table") self.table_view = QtWidgets.QTableView(self.qmainwindow) self.table_view.setObjectName("tableView") self.table_view.setGeometry( QtCore.QRect(QtCore.QPoint(10, 115), QtCore.QSize(780, 475))) self.table_view.setShowGrid(False) def define_buttons(self) -> None: def _exit_action() -> None: QtWidgets.qApp.quit() def _show_dialog() -> None: self.dbpath, _ = QtWidgets.QFileDialog.getOpenFileName( self, "Open file") self.label.setText(self.dbpath) def _load_database() -> None: self.db.setDatabaseName(self.dbpath) self.db.open() # update selectors self.combo_box.clear() for tbl in self.db.tables(): self.combo_box.addItem(tbl) self.update_table() def _save_database() -> None: self.table_model.submitAll() def _insert_row() -> None: self.table_model.insertRows(self.table_model.rowCount(), 1) def _remove_row() -> None: for idx in self.table_view.selectedIndexes(): self.table_model.removeRows(idx.row(), 1) self.button_actions.update( {"exit": QtWidgets.QAction("Exit", self.qmainwindow)}) self.button_actions["exit"].triggered.connect(_exit_action) self.button_actions.update({ "dbpath": QtWidgets.QAction("Set database path", self.qmainwindow) }) self.button_actions["dbpath"].triggered.connect(_show_dialog) self.button_actions.update( {"load": QtWidgets.QAction("Load database", self.qmainwindow)}) self.button_actions["load"].triggered.connect(_load_database) self.button_actions.update( {"save": QtWidgets.QAction("Save database", self.qmainwindow)}) self.button_actions["save"].triggered.connect(_save_database) self.button_actions.update( {"insert": QtWidgets.QAction("Insert row", self.qmainwindow)}) self.button_actions["insert"].triggered.connect(_insert_row) self.button_actions.update( {"remove": QtWidgets.QAction("Remove row", self.qmainwindow)}) self.button_actions["remove"].triggered.connect(_remove_row) def update_table(self) -> None: self.table_model = QSqlRelationalTableModel() tablename = self.combo_box.currentText() self.table_model.setTable(tablename) # foreign key logic if tablename == "goods": self.table_model.setRelation(2, QSqlRelation("units", "id", "unit")) self.table_model.setRelation( 3, QSqlRelation("categories", "id", "name")) elif tablename == "employees": self.table_model.setRelation( 2, QSqlRelation("positions", "id", "position")) elif tablename == "vendors": self.table_model.setRelation( 2, QSqlRelation("ownerships", "id", "ownership")) self.table_model.select() self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit) self.table_view.setModel(self.table_model) self.table_view.setItemDelegate(QSqlRelationalDelegate( self.table_view)) # define header width self.table_view.horizontalHeader().setStretchLastSection(False) self.table_view.resizeColumnsToContents() self.table_view.horizontalHeader().setMinimumSectionSize(50) self.table_view.horizontalHeader().setStretchLastSection(True)
def init_price(self): currencies = {"EUR": u"€", "HRK": u"kn"} model = QSqlTableModel() model.setTable("shop") model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit) model.select() self.shop_model = model price_model = QSqlRelationalTableModel() price_model.setTable("price") price_model.setEditStrategy(QSqlRelationalTableModel.OnManualSubmit) price_model.setRelation(1, QSqlRelation('nutrition', 'ndbno', 'desc')) price_model.setRelation(2, QSqlRelation('shop', 'id', 'name')) #Nutrition table needs to be populated since otherwise only 256 rows are read #And all inserts with ndbno > 100169 fail since they aren't found in nutrition #table nutri_model = price_model.relationModel(1) while nutri_model.canFetchMore(): nutri_model.fetchMore() self.price_model = price_model self.price_model.select() self.tv_price.setModel(price_model) self.tv_price.setSortingEnabled(True) self.tv_price.sortByColumn(1, Qt.AscendingOrder) self.tv_price.setItemDelegate(QSqlRelationalDelegate(self.tv_price)) price_sort_model = QSqlQueryModel() price_sort_model.setQuery( "SELECT nutrition.ndbno, nutrition.desc, shop.name, " + "last_updated, price, price/nutrition.package_weight*1000 as " + " price_kg, nutrition.kcal/price as kcal_price_100, " + "nutrition.protein/price as protein_price_100," + " (nutrition.kcal*nutrition.package_weight/100)/price as " + " kcal_price_package, " + " (nutrition.protein*nutrition.package_weight/100)/price " + " as protein_price_package, " + "lowered_price, lowered_untill, " + "currency,comment, temporary FROM price " + " JOIN shop ON shop.id == price.shop_id " + " JOIN nutrition ON price.ndbno = nutrition.ndbno " + " WHERE currency = 'EUR' " " ORDER BY nutrition.desc, price") proxy_model = QSortFilterProxyModel(self) proxy_model.setSourceModel(price_sort_model) proxy_model.setFilterKeyColumn(0) self.price_proxy = proxy_model #print (price_sort_model.lastError().text()) #print (price_sort_model.query().executedQuery()) self.tv_price_view.setModel(proxy_model) self.tv_price_view.setSortingEnabled(True) self.tv_price_view.horizontalHeader().setToolTip("protein/kcal_price " "higher is better") self.cb_shop.setModel(model) self.cb_shop.setModelColumn(1) #self.cb_shop.lineEdit().editingFinished.connect(self.add_shop) model_currency = QStringListModel() model_currency.setStringList(["EUR", "HRK"]) self.cb_currency.setModel(model_currency) self.de_last_updated.setMaximumDate(QDate.currentDate()) self.de_last_updated.setDateTime(QDateTime.currentDateTime()) def update_price(): print("Updating price info") if not price_model.submitAll(): QMessageBox.critical( None, "Error updating price:", "Couldn't update model: " + price_model.lastError().text()) def reset_price(): print("Resetting price info") price_model.revertAll() """Sets suffix symbols for prices based on currency""" def currency_changed(currency): symbol = currencies[currency] self.dsp_price.setSuffix(symbol) self.dsp_low_price.setSuffix(symbol) """Adds new Shop to DB""" def add_shop(): print(self.cb_shop.currentText()) row = self.shop_model.rowCount() self.shop_model.insertRow(row) self.shop_model.setData(self.shop_model.createIndex(row, 1), self.cb_shop.currentText(), Qt.EditRole) self.shop_model.submitAll() def add_price(): shop_id = self.shop_model.record(self.cb_shop.currentIndex()) \ .field("id").value() print("Shop ID:", shop_id, self.cb_shop.currentText()) record = self.cb_price_item.model() \ .record(self.cb_price_item.currentIndex()) ndbno = self._get_selected_ndbno(record) package_weight = record.field("package_weight").value() slices = record.field("num_of_slices").value() last_updated = self.de_last_updated.date() price = self.dsp_price.value() lowered_price = self.dsp_low_price.value() if \ self.dsp_low_price.value() > 0 else None lowered_untill = self.de_low_untill.date() if \ self.de_low_untill.date() > QDate.currentDate() else None currency = self.cb_currency.currentText() comment = self.le_comment.text() if \ len(self.le_comment.text()) > 3 else None temporary = self.cb_temporary.isChecked() print("ITEM:", self.cb_price_item.currentText(), ndbno) print("Weight:", package_weight, " Slices: ", slices) print("LU:", last_updated) print("PRICE:", price, " Low Price:", lowered_price) print("LOWU:", lowered_untill) print("CU:", currency) print("COMMENT:", comment) print("Temp:", temporary) print("NDBNO:", ndbno, type(ndbno)) print("SHOP ID:", shop_id, type(shop_id)) row = self.price_model.rowCount() #print ("ROW:", row, self.price_model.relationModel(1).rowCount()) self.price_model.insertRow(row) def add_data(idx, data): return self.price_model.setData( self.price_model.createIndex(row, idx), data, Qt.EditRole) #for i in range(100000,100194): #for i in self.session.query(LocalNutrition.ndbno).filter(LocalNutrition.ndbno #< 100000).order_by(LocalNutrition.ndbno): #out_ndbno = add_data(1, QVariant(i[0])) #print ("{}? = {}".format(out_ndbno, i[0])) #for i in range(100000,100194): ##for i in self.session.query(LocalNutrition.ndbno).filter(LocalNutrition.ndbno ##< 100000).order_by(LocalNutrition.ndbno): #out_ndbno = add_data(1, QVariant(i)) #print ("{}? = {}".format(out_ndbno, i)) #FIXME: Why is ndbno found in nutrtition only if it lower then 100169 #relation = self.price_model.relation(1) #print(relation, relation.displayColumn(), relation.indexColumn()) #print (relation.dictionary.contains(ndbno)) #return add_data(1, ndbno) add_data(2, shop_id) add_data(3, last_updated) add_data(4, price) if lowered_price is not None and lowered_untill is not None: add_data(5, lowered_price) add_data(6, lowered_untill) add_data(7, currency) if comment is not None: add_data(8, comment) add_data(9, temporary) self.price_model.submitAll() self.update_price = update_price self.reset_price = reset_price self.currency_changed = currency_changed self.buttonBox_4.button(QDialogButtonBox.SaveAll).clicked.connect( self.update_price) self.buttonBox_4.button(QDialogButtonBox.Reset).clicked.connect( self.reset_price) self.cb_currency.currentTextChanged.connect(self.currency_changed) self.pb_add_shop.pressed.connect(add_shop) self.pb_add_price.pressed.connect(add_price)
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_()
def __init__(self, parent=None): super().__init__(parent) horizontalLayout = QHBoxLayout() self.dayView = QTableView() self.dayView.setFrameShape(QFrame.Box) self.dayView.horizontalHeader().setStretchLastSection(True) self.dayView.verticalHeader().setVisible(False) horizontalLayout.addWidget(self.dayView) verticalLayout = QVBoxLayout() self.calendarWidget = QCalendarWidget() self.calendarWidget.setMinimumSize(QSize(250, 200)) self.calendarWidget.setMaximumSize(QSize(250, 200)) self.calendarWidget.setMinimumDate(QDate(2017, 1, 1)) self.calendarWidget.setMaximumDate(QDate(2030, 1, 1)) self.calendarWidget.selectionChanged.connect(self.dataChange) self.calendarWidget.setSelectedDate(QDate.currentDate()) verticalLayout.addWidget(self.calendarWidget) titleFV = QLabel('Food View') verticalLayout.addWidget(titleFV) self.filterLine = QLineEdit() self.filterLine.setMaximumSize(QSize(200, 25)) self.filterLine.textChanged.connect(self.filterChange) buttonAdd = QPushButton(QIcon("images/add.png"), '', None) buttonAdd.setMaximumSize(QSize(20, 30)) buttonAdd.clicked.connect(self.addFood) buttonDell = QPushButton(QIcon("images/del.png"), '', None) buttonDell.setMaximumSize(QSize(20, 30)) buttonDell.clicked.connect(self.delFood) lineEditLayout = QHBoxLayout() lineEditLayout.addWidget(self.filterLine) lineEditLayout.addWidget(buttonAdd) lineEditLayout.addWidget(buttonDell) verticalLayout.addLayout(lineEditLayout) self.foodView = QTableView() self.foodView.setMinimumSize(QSize(0, 0)) self.foodView.setMaximumSize(QSize(250, 1000)) self.foodView.verticalHeader().setVisible(False) self.foodView.horizontalHeader().setStretchLastSection(True) verticalLayout.addWidget(self.foodView) horizontalLayout.addLayout(verticalLayout) self.setLayout(horizontalLayout) model_in = QSqlRelationalTableModel() model_in.setEditStrategy(QSqlTableModel.OnFieldChange) model_in.setTable("intake_food") id_food = model_in.fieldIndex("id_food") date = model_in.fieldIndex("food_date") mass = model_in.fieldIndex("mass") # Set model, hide ID column model_in.setRelation(id_food, QSqlRelation("food", "id", "name")) model_in.setHeaderData(id_food, Qt.Horizontal, "Food") model_in.setHeaderData(date, Qt.Horizontal, "Date") model_in.setHeaderData(mass, Qt.Horizontal, "Mass") if not model_in.select(): self.showError(model_in.lastError()) return self.proxyModel_in = QSortFilterProxyModel() self.proxyModel_in.setSourceModel(model_in) self.proxyModel_in.setFilterKeyColumn(2) self.dayView.setItemDelegate(FlipProxyDelegate()) self.dayView.setModel(self.proxyModel_in) self.dayView.setColumnHidden(0, True) self.dayView.setColumnHidden(2, True) self.dayView.setSelectionMode(QAbstractItemView.SingleSelection) self.dayView.setContextMenuPolicy(Qt.CustomContextMenu) self.dayView.customContextMenuRequested.connect(self.ShowContextMenu) # filter day food by calendar widget self.dataChange() self.model_f = QSqlRelationalTableModel() self.model_f.setEditStrategy(QSqlTableModel.OnFieldChange) self.model_f.setTable("food") self.model_f.setHeaderData(1, Qt.Horizontal, "Food") self.model_f.setHeaderData(2, Qt.Horizontal, "Rate") if not self.model_f.select(): self.showError(self.model_f.lastError()) return self.proxyModel_f = QSortFilterProxyModel() self.proxyModel_f.setSourceModel(self.model_f) self.proxyModel_f.setFilterKeyColumn(1) self.foodView.setModel(self.proxyModel_f) self.foodView.setColumnHidden(0, True) self.foodView.setSelectionMode(QAbstractItemView.SingleSelection) self.foodView.setColumnWidth(1, 150) self.foodView.setColumnWidth(2, 90)
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)) TV_OBJ.hideColumn(0) TV_OBJ.setColumnWidth(1, 150) TV_OBJ.setColumnWidth(2, 150)
class Components(object): def __init__(self, connection): self.connection = connection self.model = QSqlRelationalTableModel() self.model.setTable('customnetwork_component') self.model.setEditStrategy(QSqlTableModel.OnManualSubmit) self.model.setRelation(0, QSqlRelation('component', 'id', 'ticket')) self.model.setRelation(1, QSqlRelation('customnetwork', 'id', 'name')) self.model.select() self.model.setHeaderData(0, Qt.Horizontal, "Ticket") def getModel(self): return self.model def create(self, customnetwork_id, tickets): # TODO: capturar excepciones de error que se pueden dar log.debug(tickets) query = QSqlQuery() last = self.model.rowCount() id = last + 1 self.connection.open() for ticket in tickets: comp_id = self._insert_component_table(query, ticket) ok = self.\ _insert_customernetwork_component_table(query, customnetwork_id, comp_id) self.model.select() self.connection.close() return id def _insert_customernetwork_component_table(self, query, customnetwork_id, comp_id): query.prepare('insert into customnetwork_component values (?,?);') query.addBindValue(comp_id) query.addBindValue(customnetwork_id) return query.exec_() def _insert_component_table(self, query, ticket): ok = query.exec_("select id from component;") id = 0 if ok: query.last() last_id = int(query.value(0)) id = last_id + 1 query.prepare('insert into component values (?,?,?,?);') query.addBindValue(id) query.addBindValue(ticket) query.addBindValue('_generic_description_') query.addBindValue(GENERIC_SECTOR_CODE_ID) if query.exec_(): return id else: return -1 def update_components(self, id, tickets): # TODO: capturar excepciones de error que se pueden dar query = QSqlQuery() self.connection.open() sql = """DELETE FROM customnetwork_component WHERE customnetwork_id = ?; """ query.prepare(sql) query.addBindValue(id) ok = query.exec_() if ok: for ticket in tickets: sql = "SELECT id, ticket FROM component WHERE ticket = '{}';"\ .format(ticket) if query.exec_() and query.next(): component_id = int(query.value(0)) query.clear() sql = """INSERT into customnetwork_component VALUES (?, ?); """ query.prepare(sql) query.addBindValue(component_id) query.exec_() else: comp_id = self._insert_component_table(query, ticket) ok = self.\ _insert_customernetwork_component_table(query, id, comp_id) self.model.select() self.connection.close() return ok def delete_cnc(self, customnetwork_id): query = QSqlQuery() self.connection.open() query_str = 'delete from customnetwork_component where ' ' customnetwork_id = ?;' query.prepare(query) query.addBindValue(customnetwork_id) ok = query.exec_() self.model.select() self.connection.close() return ok
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())
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 MyWindow(QMainWindow): def __init__(self, parent=None): super().__init__(parent) # Собираем окно self.ui = Ui_MainWindow() self.ui.setupUi(self) self.ui.retranslateUi(self) self.db_path = r'database/database.sqlite3' self.ui.path_db.setText(self.db_path) # Собираем кнопки self.save_db = QAction(QIcon('icon/save.png'), 'Save', self) self.open_db_file = QAction(QIcon('icon/open-db.png'), 'Open', self) self.add_row = QAction(QIcon('icon/add.png'), 'Add row', self) self.del_row = QAction(QIcon('icon/del.png'), ' Del row', self) self.ui.toolBar.addAction(self.save_db) self.ui.toolBar.addAction(self.open_db_file) self.ui.toolBar.addAction(self.add_row) self.ui.toolBar.addAction(self.del_row) self.db = None self.table_model = None # Подключаемся к БД self.open_db() # действуем по триггерам self.add_row.triggered.connect(self.add_row_action) self.del_row.triggered.connect(self.del_row_action) self.save_db.triggered.connect(self.save_change_db) self.open_db_file.triggered.connect(self.open_db_file_action) self.ui.comboBox.currentIndexChanged.connect(self.show_table) def open_db(self): # Подключение к БД self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName(self.db_path) self.db.open() self.get_tables_name() self.show_table() def get_tables_name(self): self.ui.comboBox.clear() for table_name in self.db.tables(): self.ui.comboBox.addItem(table_name) def show_table(self): self.table_model = QSqlRelationalTableModel() table = self.ui.comboBox.currentText() if table == 'goods': self.create_goods_table_model() elif table == 'employees': self.create_employees_table_model() else: self.table_model.setTable(table) self.table_model.select() self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit) view = self.ui.tableView view.setModel(self.table_model) view.setItemDelegate(QSqlRelationalDelegate(view)) def create_goods_table_model(self): self.table_model.setTable('goods') self.table_model.setRelation(2, QSqlRelation('units', 'unit_id', 'unit')) self.table_model.setRelation(3, QSqlRelation('categories', 'category_id', 'category_name')) self.table_model.select() def create_employees_table_model(self): self.table_model.setTable('employees') self.table_model.setRelation(2, QSqlRelation('positions', 'position_id', 'position')) self.table_model.select() def add_row_action(self): self.table_model.insertRows(self.table_model.rowCount(), 1) def del_row_action(self): rs = list(map(lambda x: x.row(), self.ui.tableView.selectedIndexes())) print(rs) for i in rs: self.table_model.removeRows(i, 1) def open_db_file_action(self): self.db_path = QFileDialog.getOpenFileName(self, "Open file")[0] self.ui.path_db.setText(self.db_path) self.db.close() self.open_db() def save_change_db(self): if self.table_model.submitAll(): self.ui.statusbar.showMessage('Изменения сохранены') else: self.ui.statusbar.showMessage(f'{self.table_model.lastError().text()}')
class MyWindow(QMainWindow): def __init__(self, parent=None): super().__init__(parent) self.ui = Ui_MainWindow() self.ui.setupUi(self) self.ui.retranslateUi(self) self.db_path = r'database\sklad_db.sqlite3' self.ui.path_db.setText(self.db_path) self.save_db = QAction(QIcon('icon/save.png'), 'Сохранить', self) self.find_db_file = QAction(QIcon('icon/open_db.png'), 'Открыть БД', self) self.add_rec = QAction(QIcon('icon/add.png'), 'Добавить запись', self) self.del_rec = QAction(QIcon('icon/del.png'), 'Удалить запись', self) self.ui.toolBar.addAction(self.save_db) self.ui.toolBar.addAction(self.find_db_file) self.ui.toolBar.addAction(self.add_rec) self.ui.toolBar.addAction(self.del_rec) self.db = None self.table_model = None self.open_db() self.add_rec.triggered.connect(self.add_record_action) self.del_rec.triggered.connect(self.del_record_action) self.save_db.triggered.connect(self.save_change_db) self.find_db_file.triggered.connect(self.find_db_file_action) self.ui.comboBox.currentIndexChanged.connect(self.show_table) def open_db(self): self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName(self.db_path) self.db.open() self.get_table_name() self.show_table() def get_table_name(self): self.ui.comboBox.clear() table_name_ru = None for table_name in self.db.tables(): if table_name == 'goods': table_name_ru = 'Товары' elif table_name == 'categories': table_name_ru = 'Категории' elif table_name == 'units': table_name_ru = 'Единица измерения' elif table_name == 'employees': table_name_ru = 'Персонал' elif table_name == 'positions': table_name_ru = 'Должности' elif table_name == 'vendors': table_name_ru = 'Поставщики' self.ui.comboBox.addItem(table_name_ru) def show_table(self): self.table_model = QSqlRelationalTableModel() table = self.ui.comboBox.currentText() if table == 'Товары': self.create_goods_table_model() elif table == 'Персонал': self.create_employees_table_model() else: if table == 'Единица измерения': table = 'units' elif table == 'Категории': table = 'categories' elif table == 'Должности': table = 'positions' elif table == 'Поставщики': table = 'vendors' self.table_model.setTable(table) self.table_model.select() self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit) view = self.ui.tableView view.setModel(self.table_model) view.setItemDelegate(QSqlRelationalDelegate(view)) def create_goods_table_model(self): self.table_model.setTable('goods') self.table_model.setRelation(2, QSqlRelation('units', 'unit_id', 'unit')) self.table_model.setRelation( 3, QSqlRelation('categories', 'category_id', 'category_name')) self.table_model.select() def create_employees_table_model(self): self.table_model.setTable('employees') self.table_model.setRelation( 2, QSqlRelation('positions', 'position_id', 'position')) self.table_model.select() def add_record_action(self): self.ui.statusbar.clearMessage() self.table_model.insertRows(self.table_model.rowCount(), 1) def del_record_action(self): self.ui.statusbar.clearMessage() rs = list(map(lambda x: x.row(), self.ui.tableView.selectedIndexes())) #print(rs) for i in rs: self.table_model.removeRows(i, 1) def find_db_file_action(self): self.db_path = QFileDialog.getOpenFileName(self, "Open file")[0] self.ui.path_db.setText(self.db_path) self.db.close() self.open_db() def save_change_db(self): if self.table_model.submitAll(): self.ui.statusbar.showMessage('Изменения сохранены') else: self.ui.statusbar.showMessage( f'{self.table_model.lastError().text()}')
class Shtat(QMainWindow): """ Класс, реализующий окно для работы со штатным расписанием в виде таблицы """ con = QSqlDatabase.addDatabase('QSQLITE') con.setDatabaseName(settings.db_name) def __init__(self, parent: any = None) -> None: """ Функция инициализации окна штатного расписания :param parent: Родительский виджет (окно) """ super().__init__(parent) self.filters = ['Номер', 'Подразделение', 'Должность', 'Количество', 'Тариф', 'Оклад', 'ФИО', 'Декрет', 'История', 'Оклад замещающего работника', 'Вид позиции', ] self.setObjectName("ShtatWindow") self.resize(1380, 886) self.centralwidget = QtWidgets.QWidget(self) self.centralwidget.setObjectName("centralwidget") self.view = QTableView(self.centralwidget) self.view.setGeometry(QRect(0, 0, 1381, 821)) self.view.setObjectName("shtat_table_view") self.view.setSortingEnabled(True) self.filter_combo_box = QtWidgets.QComboBox(self.centralwidget) self.filter_combo_box.setGeometry(QRect(250, 840, 231, 31)) self.filter_combo_box.setObjectName("filter_combo_box") self.filter_input = QtWidgets.QLineEdit(self.centralwidget) self.filter_input.setGeometry(QRect(500, 840, 281, 31)) self.filter_input.setObjectName("filter_input") self.filter_label = QtWidgets.QLabel(self.centralwidget) self.filter_label.setGeometry(QRect(10, 840, 221, 31)) font = QFont() font.setFamily("Times New Roman") font.setPointSize(12) self.filter_label.setFont(font) self.filter_label.setObjectName("filter_label") self.filter_button = QtWidgets.QPushButton(self.centralwidget) self.filter_button.setGeometry(QRect(800, 840, 171, 31)) self.save_shtat_button = QtWidgets.QPushButton(self.centralwidget) self.save_shtat_button.setGeometry(QRect(1000, 840, 225, 31)) self.filter_button.setFont(font) self.save_shtat_button.setFont(font) self.filter_button.setObjectName("filter_button") self.setCentralWidget(self.centralwidget) _translate = QCoreApplication.translate self.setWindowTitle(_translate("ShtatWindow", "Работа со штатным расписанием")) self.filter_label.setText(_translate("ShtatWindow", "Фильтровать по столбцу:")) self.filter_button.setText(_translate("ShtatWindow", "Поиск")) self.save_shtat_button.setText(_translate("ShtatWindow", "Выгрузить штатное расписание")) self.model = QSqlRelationalTableModel(self.view) self.model.setTable('salaries') self.model.setEditStrategy(QSqlRelationalTableModel.OnFieldChange) self.model.setRelation(1, QSqlRelation("departments", "code", "department")) self.model.setRelation(10, QSqlRelation("positions", "position_code", "position_name")) for i in range(0, len(self.filters)): self.model.setHeaderData(i, Qt.Horizontal, self.filters[i]) self.model.select() self.proxyModelContact = QSortFilterProxyModel(self) self.proxyModelContact.setSourceModel(self.model) self.view.setModel(self.proxyModelContact) self.view.resizeColumnsToContents() self.view.setItemDelegate(QSqlRelationalDelegate(self.view)) self.filter_combo_box.addItems(self.filters) self.filter_button.clicked.connect(self.use_filter_button) self.save_shtat_button.clicked.connect(self.use_save_shtat_button) self.view.setItemDelegate(QSqlRelationalDelegate(self.view)) def use_filter_button(self) -> None: """ Функция кнопки "Поиск". Считывает паттерн из поля фильтра и применяет его к столбцу, выбранного в комбо-боксе фильтра :return: None """ self.proxyModelContact.setFilterKeyColumn(self.filters.index(self.filter_combo_box.currentText())) self.proxyModelContact.setFilterRegExp(self.filter_input.text()) @staticmethod def use_save_shtat_button() -> None: """ Функция кнопки "Выгрузить штатное расписание". Выгружает все штатное расписание в файл формата Excel :return: None """ file = QtWidgets.QFileDialog.getSaveFileName()[0] saver = FullShtatToExcel(file if file.endswith(".xlsx") else f"{file}.xlsx") saver.full_shtat_to_excel()
class Window(QMainWindow): def __init__(self): super().__init__() self.ui = mainform.Ui_MainWindow() self.ui.setupUi(self) self.ui.retranslateUi(self) self.db_path = r'db/db_test.sqlite3' self.ui.lbl_full_path_db.setText(self.db_path) self.ui.btn_db_open.clicked.connect(self.db_file_open) self.ui.btn_add.clicked.connect(self.db_add) self.ui.btn_add.setEnabled(False) self.ui.btn_del.clicked.connect(self.db_del) self.ui.btn_del.setEnabled(False) self.ui.save_changes.clicked.connect(self.save_change_db) self.ui.save_changes.setEnabled(False) self.ui.mnu_exit.triggered.connect(self.close) self.ui.tablelist.setEnabled(False) self.db = None self.db_model = None self.open_db() self.ui.tablelist.currentIndexChanged.connect(self.show_table) def open_db(self): self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName(self.db_path) self.db.open() self.get_tables_name() # self.show_table() def get_tables_name(self): self.ui.tablelist.clear() for table_name in self.db.tables(): self.ui.tablelist.addItem(table_name) def db_file_open(self): self.db_path = QFileDialog.getOpenFileName(self, "Open file")[0] self.ui.lbl_full_path_db.setText(self.db_path) self.db.close() self.open_db() self.ui.tablelist.setEnabled(True) self.ui.btn_add.setEnabled(True) self.ui.btn_del.setEnabled(True) self.ui.save_changes.setEnabled(True) self.ui.btn_db_open.setEnabled(False) def show_table(self): self.table_model = QSqlRelationalTableModel() table = self.ui.tablelist.currentText() if table == 'goods': self.create_goods_table_model() elif table == 'employees': self.create_employees_table_model() elif table == 'units': self.create_units_table_model() elif table == 'vendors': self.create_vendors_table_model() elif table == 'positions': self.create_positions_table_model() elif table == 'categories': self.create_categories_table_model() else: self.table_model.setTable(table) self.table_model.select() self.table_model.setEditStrategy(QSqlTableModel.OnManualSubmit) view = self.ui.tableView view.setModel(self.table_model) view.hideColumn(0) view.setItemDelegate(QSqlRelationalDelegate(view)) def db_add(self): self.table_model.insertRows(self.table_model.rowCount(), 1) def db_del(self): rs = list(map(lambda x: x.row(), self.ui.tableView.selectedIndexes())) print(rs) for i in rs: self.table_model.removeRows(i, 1) def create_goods_table_model(self): self.table_model.setTable('goods') self.table_model.setRelation( 2, QSqlRelation('units', 'unit_id', 'unit_name')) self.table_model.setRelation( 3, QSqlRelation('categories', 'category_id', 'category_name')) self.table_model.setRelation( 4, QSqlRelation('vendors', 'vendor_id', 'vendor_name')) self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Название продукта') self.table_model.setHeaderData(2, QtCore.Qt.Horizontal, 'Единица измерения') self.table_model.setHeaderData(3, QtCore.Qt.Horizontal, 'Категория продукта') self.table_model.setHeaderData(4, QtCore.Qt.Horizontal, 'Поставщик') self.table_model.select() def create_units_table_model(self): self.table_model.setTable('units') self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Единица измерения') self.table_model.select() def create_employees_table_model(self): self.table_model.setTable('employees') self.table_model.setRelation( 2, QSqlRelation('positions', 'position_id', 'position_name')) self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'ФИО сотрудника') self.table_model.setHeaderData(2, QtCore.Qt.Horizontal, 'Должность') self.table_model.select() def create_positions_table_model(self): self.table_model.setTable('positions') self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Должность') self.table_model.select() def create_vendors_table_model(self): self.table_model.setTable('vendors') self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Наименование') self.table_model.setHeaderData(2, QtCore.Qt.Horizontal, 'Форма собственности') self.table_model.setHeaderData(3, QtCore.Qt.Horizontal, 'Адрес') self.table_model.setHeaderData(4, QtCore.Qt.Horizontal, 'Телефон') self.table_model.setHeaderData(5, QtCore.Qt.Horizontal, 'Электронная почта') self.table_model.select() def create_categories_table_model(self): self.table_model.setTable('categories') self.table_model.setHeaderData(1, QtCore.Qt.Horizontal, 'Название категории') self.table_model.setHeaderData(2, QtCore.Qt.Horizontal, 'Описание категории') self.table_model.select() def save_change_db(self): if self.table_model.submitAll(): self.ui.statusbar.showMessage('Изменения сохранены') else: self.ui.statusbar.showMessage( f'{self.table_model.lastError().text()}')