def collect_data(ui, db): #! Please add cute Icons back, sir num_model = QSqlQueryModel() cust_model = QSqlQueryModel() ui.treeView = QtWidgets.QTreeView(ui.comboBox) db.open() num_model.setQuery('SELECT Number FROM Room', db) cust_model.setQuery('SELECT Name, ID, Sex FROM Customer', db) db.close() while cust_model.canFetchMore(): cust_model.fetchMore() ui.comboBox_2.setModel(num_model) ui.comboBox.setModel(cust_model) ui.comboBox.setView(ui.treeView) ui.treeView.setColumnHidden(2, True) icon4 = QtGui.QIcon() icon4.addPixmap(QtGui.QPixmap(":/ctmr/48px-Emblem-person-blue.svg.png")) icon5 = QtGui.QIcon() icon5.addPixmap(QtGui.QPixmap(":/ctmr/48px-User_icon_3.svg.png")) i = 0 while i < ui.comboBox.count(): #print(cust_model.index(i, 1).data()) if cust_model.index(i, 2).data() == 'Male': ui.comboBox.setItemIcon(i, icon4) else: ui.comboBox.setItemIcon(i, icon5) i += 1
def setData(self, index, value, role): if index.column() != 2: return False primaryKeyIndex = QSqlQueryModel.index(self, index.row(), 1) id = self.data(primaryKeyIndex) self.clear() ok = self.setState(id, str(value)) self.refresh() return ok
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 write_csv(model: QtSql.QSqlQueryModel): filename, _ = QFileDialog \ .getSaveFileName(None, "Export data to CSV", ".", CSV_FILE_FILTER, CSV_FILE_FILTER) data = list() with open(filename, "a") as f: # process headers for i in range(0, model.columnCount()): header = str( model.headerData(i, QtCore.Qt.Horizontal, QtCore.Qt.DisplayRole)) data.append('"' + header + '"') # write headers f.write(";".join(data)) f.write("\n") # process rows for i in range(model.rowCount()): data.clear() for j in range(model.columnCount()): cell = str(model.data(model.index(i, j))) data.append('"' + cell + '"') f.write(";".join(data)) f.write("\n")
class DatabaseSearchWidget(QWidget, Ui_DatabaseSearchWidget): # Signal to be emitted when user wants item to be added signal_add_item = QtCore.pyqtSignal(int) # Signal to be emitted when user selection changes signal_current_selection = QtCore.pyqtSignal(int) def __init__(self, queries, headers, parent=None): super(DatabaseSearchWidget, self).__init__(parent) self.setupUi(self) # Setup database connection self.database = pyqt_database_connection() self.database.open() self.databaseModel = QSqlQueryModel(self) self.dataView_search_results.setModel(self.databaseModel) # Store bound queries for usage self.queries = queries self.combo_search_options.addItems(sorted(queries.keys())) # Connect signals self.dataView_search_results.selectionModel().selectionChanged.connect( self.slot_emit_selection_changed) self.dataView_search_results.doubleClicked.connect( self.slot_emit_add_item) self.pushButton_search.clicked.connect(self.update_query) self.combo_search_options.currentIndexChanged.connect( self.update_query) # Setup header for element in headers: self.databaseModel.setHeaderData(0, QtCore.Qt.Horizontal, element) self.installEventFilter(self) @QtCore.pyqtSlot() def update_query(self): """ Update the query with the search term Returns ------- None """ LOGGER.debug("Updating search query..") query = self.queries[self.combo_search_options.currentText()] # Todo: Slow query setting freezes ui self.databaseModel.setQuery( query.format(input=self.lineEdit_search_input.text().strip())) LOGGER.debug(str(self.databaseModel.query().executedQuery())) LOGGER.debug("Search complete.") def selected_id(self): """ Return the id of the currently selected item Returns ------- int """ selection = self.dataView_search_results.get_selected_rows() if len(selection) != 1: return -1 else: return int( self.databaseModel.data( self.databaseModel.index(selection[0], 0))) def eventFilter(self, source, event): if event.type() == QtCore.QEvent.KeyPress and event.key() in ( QtCore.Qt.Key_Return, QtCore.Qt.Key_Enter): self.update_query() return True return False @QtCore.pyqtSlot() def slot_emit_selection_changed(self): """ Emit database id of the currently selected item Returns ------- None """ self.signal_current_selection.emit(self.selected_id()) @QtCore.pyqtSlot() def slot_emit_add_item(self): """ Emit database id of the currently selected item Returns ------- None """ self.signal_add_item.emit(self.selected_id())
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.splitter) # tableView显示属性设置 self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows) self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection) self.ui.tableView.setAlternatingRowColors(True) self.ui.tableView.verticalHeader().setDefaultSectionSize(22) self.ui.tableView.horizontalHeader().setDefaultSectionSize(60) ## self.ui.tableView.resizeColumnsToContents() ## ==============自定义功能函数============ def __getFieldNames(self): ##获取所有字段名称 emptyRec = self.qryModel.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.qryModel = QSqlQueryModel(self) self.qryModel.setQuery( '''SELECT empNo, Name, Gender, Birthday, Province, Department, Salary FROM employee ORDER BY empNo''' ) if self.qryModel.lastError().isValid(): QMessageBox.critical( self, "错误", "数据表查询错误,错误信息\n" + self.qryModel.lastError().text()) return self.ui.statusBar.showMessage("记录条数:%d" % self.qryModel.rowCount()) self.__getFieldNames() #获取字段名和序号 ##设置字段显示名,直接使用序号 self.qryModel.setHeaderData(0, Qt.Horizontal, "工号") self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名") self.qryModel.setHeaderData(2, Qt.Horizontal, "性别") self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期") self.qryModel.setHeaderData(4, Qt.Horizontal, "省份") self.qryModel.setHeaderData(5, Qt.Horizontal, "部门") self.qryModel.setHeaderData(6, Qt.Horizontal, "工资") ## self.qryModel.setHeaderData(self.fldNum["empNo"], Qt.Horizontal, "工号") ## self.qryModel.setHeaderData(self.fldNum["Name"], Qt.Horizontal, "姓名") ## self.qryModel.setHeaderData(self.fldNum["Gender"], Qt.Horizontal, "性别") ## self.qryModel.setHeaderData(self.fldNum["Birthday"], Qt.Horizontal, "出生日期") ## self.qryModel.setHeaderData(self.fldNum["Province"], Qt.Horizontal, "省份") ## self.qryModel.setHeaderData(self.fldNum["Department"], Qt.Horizontal, "部门") ## self.qryModel.setHeaderData(self.fldNum["Salary"], Qt.Horizontal, "工资") ##创建界面组件与数据模型的字段之间的数据映射 self.mapper = QDataWidgetMapper() self.mapper.setModel(self.qryModel) #设置数据模型 ## self.mapper.setSubmitPolicy(QDataWidgetMapper.AutoSubmit) ##界面组件与qryModel的具体字段之间的联系 ## self.mapper.addMapping(self.ui.dbSpinEmpNo, self.fldNum["empNo"]) ## self.mapper.addMapping(self.ui.dbEditName, self.fldNum["Name"]) ## self.mapper.addMapping(self.ui.dbComboSex, self.fldNum["Gender"]) ## self.mapper.addMapping(self.ui.dbEditBirth, self.fldNum["Birthday"]) ## self.mapper.addMapping(self.ui.dbComboProvince, self.fldNum["Province"] ) ## self.mapper.addMapping(self.ui.dbComboDep, self.fldNum["Department"] ) ## self.mapper.addMapping(self.ui.dbSpinSalary,self.fldNum["Salary"] ) self.mapper.addMapping(self.ui.dbSpinEmpNo, 0) self.mapper.addMapping(self.ui.dbEditName, 1) self.mapper.addMapping(self.ui.dbComboSex, 2) self.mapper.addMapping(self.ui.dbEditBirth, 3) self.mapper.addMapping(self.ui.dbComboProvince, 4) self.mapper.addMapping(self.ui.dbComboDep, 5) self.mapper.addMapping(self.ui.dbSpinSalary, 6) self.mapper.toFirst() #移动到首记录 self.selModel = QItemSelectionModel(self.qryModel) #关联选择模型 self.selModel.currentRowChanged.connect( self.do_currentRowChanged) #选择行变化时 self.ui.tableView.setModel(self.qryModel) #设置数据模型 self.ui.tableView.setSelectionModel(self.selModel) #设置选择模型 self.ui.actOpenDB.setEnabled(False) def __refreshTableView(self): ##刷新tableView显示 index = self.mapper.currentIndex() curIndex = self.qryModel.index(index, 1) #QModelIndex self.selModel.clearSelection() #清空选择项 self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select) ## ==========由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_actRecFirst_triggered(self): self.mapper.toFirst() self.__refreshTableView() @pyqtSlot() ##前一记录 def on_actRecPrevious_triggered(self): self.mapper.toPrevious() self.__refreshTableView() @pyqtSlot() ##后一条记录 def on_actRecNext_triggered(self): self.mapper.toNext() self.__refreshTableView() @pyqtSlot() ##最后一条记录 def on_actRecLast_triggered(self): self.mapper.toLast() self.__refreshTableView() ## =============自定义槽函数=============================== def do_currentRowChanged(self, current, previous): ##记录移动时触发 if (current.isValid() == False): self.ui.dbLabPhoto.clear() #清除图片显示 return self.mapper.setCurrentIndex(current.row()) #更新数据映射的行号 first = (current.row() == 0) #是否首记录 last = (current.row() == self.qryModel.rowCount() - 1) #是否尾记录 self.ui.actRecFirst.setEnabled(not first) #更新使能状态 self.ui.actRecPrevious.setEnabled(not first) self.ui.actRecNext.setEnabled(not last) self.ui.actRecLast.setEnabled(not last) curRec = self.qryModel.record(current.row()) #获取当前记录,QSqlRecord类型 empNo = curRec.value("EmpNo") #不需要加 toInt()函数 query = QSqlQuery(self.DB) query.prepare( '''SELECT EmpNo, Memo, Photo FROM employee WHERE EmpNo = :ID''') query.bindValue(":ID", empNo) ## if not query.exec_(): #注意,在PyQt5.11.2之前的版本里只能使用exec_()函数 if not query.exec( ): #注意,在PyQt5.11.2添加了遗漏的overload型exec()函数,在PyQt5.11.2里没问题了 QMessageBox.critical(self, "错误", "执行SQL语句错误\n" + query.lastError().text()) return else: query.first() picData = query.value("Photo") if (picData == None): #图片字段内容为空 self.ui.dbLabPhoto.clear() else: #显示照片 pic = QPixmap() pic.loadFromData(picData) W = self.ui.dbLabPhoto.size().width() self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(W)) memoData = query.value("Memo") #显示备注 self.ui.dbEditMemo.setPlainText(memoData)
class RecordsViewer(QDialog): def __init__(self): super(RecordsViewer, self).__init__() self.resize(800, 500) self.setWindowTitle('myOCR——我的识别历史') self.setWindowIcon(QIcon('./images/icon.png')) self.queryModel = None self.tableView = None self.currentPage = 0 self.totalPage = 0 self.totalRecord = 0 self.pageRecord = 10 self.initUI() def initUI(self): self.vbox = QVBoxLayout() self.h1box = QHBoxLayout() self.h2box = QHBoxLayout() self.searchEdit = QLineEdit() self.searchEdit.setFixedHeight(32) self.searchEdit.setFont(QFont("苏新诗柳楷繁", 15)) self.searchButton = QPushButton("查询") self.searchButton.setFixedHeight(32) self.searchButton.setFont(QFont("苏新诗柳楷繁", 15)) self.condisionComboBox = QComboBox() searchCondision = ['按识别编号查询', '按识别时间查询', '按识别类型查询', '按识别文字查询'] self.condisionComboBox.setFixedHeight(32) self.condisionComboBox.setFont(QFont("苏新诗柳楷繁", 15)) self.condisionComboBox.addItems(searchCondision) self.h1box.addWidget(self.searchEdit) self.h1box.addWidget(self.condisionComboBox) self.h1box.addWidget(self.searchButton) self.jumpToLabel = QLabel(self) self.jumpToLabel.setText("跳转到第") self.jumpToLabel.setFont(QFont("苏新诗柳楷繁", 12)) self.jumpToLabel.setFixedWidth(90) self.pageEdit = QLineEdit() self.pageEdit.setFixedWidth(30) self.pageEdit.setFont(QFont("苏新诗柳楷繁", 12)) s = "/" + str(self.totalPage) + "页" self.pageLabel = QLabel(s) self.pageLabel.setFont(QFont("苏新诗柳楷繁", 12)) self.pageLabel.setFixedWidth(40) self.jumpToButton = QPushButton(self) self.jumpToButton.setText("跳转") self.jumpToButton.setFont(QFont("苏新诗柳楷繁", 12)) self.jumpToButton.setFixedHeight(30) self.jumpToButton.setFixedWidth(60) self.prevButton = QPushButton("前一页") self.prevButton.setFont(QFont("苏新诗柳楷繁", 12)) self.prevButton.setFixedHeight(30) self.prevButton.setFixedWidth(80) self.backButton = QPushButton("后一页") self.backButton.setFont(QFont("苏新诗柳楷繁", 12)) self.backButton.setFixedHeight(30) self.backButton.setFixedWidth(80) self.detailbutton = QPushButton(self) self.detailbutton.setText("详细信息") self.detailbutton.setFixedWidth(90) self.detailbutton.setFont(QFont("苏新诗柳楷繁", 12)) self.detailbutton.clicked.connect(self.detailInfo) self.hbox = QHBoxLayout() self.hbox.addWidget(self.jumpToLabel) self.hbox.addWidget(self.pageEdit) self.hbox.addWidget(self.pageLabel) self.hbox.addWidget(self.jumpToButton) self.hbox.addStretch(1) self.hbox.addWidget(self.detailbutton) self.hbox.addStretch(1) self.hbox.addWidget(self.prevButton) self.hbox.addWidget(self.backButton) widget = QWidget() widget.setLayout(self.hbox) widget.setFixedWidth(600) self.h2box.addWidget(widget) self.db = QSqlDatabase.addDatabase("QSQLITE") self.db.setDatabaseName('./db/myOCR.db') self.db.open() self.tableView = QTableView() self.tableView.horizontalHeader().setStretchLastSection(True) self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers) self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows) self.tableView.setFont(QFont("苏新诗柳楷繁", 12)) self.tableView.horizontalHeader().setFont(QFont("苏新诗柳楷繁", 12)) self.queryModel = QSqlQueryModel() self.searchButtonClicked() self.tableView.setModel(self.queryModel) self.queryModel.setHeaderData(0, Qt.Horizontal, "识别编号") self.queryModel.setHeaderData(1, Qt.Horizontal, "识别时间") self.queryModel.setHeaderData(2, Qt.Horizontal, "识别类型") self.queryModel.setHeaderData(3, Qt.Horizontal, "识别文字") self.vbox.addLayout(self.h1box) self.vbox.addWidget(self.tableView) self.vbox.addLayout(self.h2box) self.setLayout(self.vbox) self.searchButton.clicked.connect(self.searchButtonClicked) self.prevButton.clicked.connect(self.prevButtonClicked) self.backButton.clicked.connect(self.backButtonClicked) self.jumpToButton.clicked.connect(self.jumpToButtonClicked) self.searchEdit.returnPressed.connect(self.searchButtonClicked) def setButtonStatus(self): if (self.currentPage == self.totalPage): self.prevButton.setEnabled(True) self.backButton.setEnabled(False) if (self.currentPage == 1): self.backButton.setEnabled(True) self.prevButton.setEnabled(False) if (self.currentPage < self.totalPage and self.currentPage > 1): self.prevButton.setEnabled(True) self.backButton.setEnabled(True) def getTotalRecordCount(self): self.queryModel.setQuery( "select RecordId,RecordTime,RecordType,RecordText from records") self.totalRecord = self.queryModel.rowCount() return def getPageCount(self): self.getTotalRecordCount() self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) return def recordQuery(self, index): conditionChoice = self.condisionComboBox.currentText() if (conditionChoice == "按识别编号查询"): conditionChoice = 'RecordId' elif (conditionChoice == "按识别时间查询"): conditionChoice = 'RecordTime' elif (conditionChoice == "按识别类型查询"): conditionChoice = 'RecordType' else: conditionChoice = 'RecordText' if (self.searchEdit.text() == ""): queryCondition = "select RecordId,RecordTime,RecordType,RecordText from records order by RecordTime DESC" self.queryModel.setQuery(queryCondition) self.totalRecord = self.queryModel.rowCount() self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) queryCondition = ( "select RecordId,RecordTime,RecordType,RecordText from records order by %s desc limit %d,%d " % (conditionChoice, index, self.pageRecord)) self.queryModel.setQuery(queryCondition) self.setButtonStatus() return temp = self.searchEdit.text() s = '%' for i in range(0, len(temp)): s = s + temp[i] + "%" queryCondition = ( "select RecordId,RecordTime,RecordType,RecordText from records where %s like '%s' order by %s desc" % (conditionChoice, s, conditionChoice)) self.queryModel.setQuery(queryCondition) self.totalRecord = self.queryModel.rowCount() if (self.totalRecord == 0): print( QMessageBox.information(self, "提醒", "查询无记录", QMessageBox.Yes, QMessageBox.Yes)) queryCondition = "select RecordId,RecordTime,RecordType,RecordText from records order by RecordTime DESC" self.queryModel.setQuery(queryCondition) self.totalRecord = self.queryModel.rowCount() self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) queryCondition = ( "select RecordId,RecordTime,RecordType,RecordText from records order by %s desc limit %d,%d " % (conditionChoice, index, self.pageRecord)) self.queryModel.setQuery(queryCondition) self.setButtonStatus() return self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) queryCondition = ( "select RecordId,RecordTime,RecordType,RecordText from records where %s like '%s' order by %s desc limit %d,%d " % (conditionChoice, s, conditionChoice, index, self.pageRecord)) self.queryModel.setQuery(queryCondition) self.setButtonStatus() return def searchButtonClicked(self): self.currentPage = 1 self.pageEdit.setText(str(self.currentPage)) self.getPageCount() s = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(s) index = (self.currentPage - 1) * self.pageRecord self.recordQuery(index) return def prevButtonClicked(self): self.currentPage -= 1 if (self.currentPage <= 1): self.currentPage = 1 self.pageEdit.setText(str(self.currentPage)) index = (self.currentPage - 1) * self.pageRecord self.recordQuery(index) return def backButtonClicked(self): self.currentPage += 1 if (self.currentPage >= int(self.totalPage)): self.currentPage = int(self.totalPage) self.pageEdit.setText(str(self.currentPage)) index = (self.currentPage - 1) * self.pageRecord self.recordQuery(index) return def jumpToButtonClicked(self): if (self.pageEdit.text().isdigit()): self.currentPage = int(self.pageEdit.text()) if (self.currentPage > self.totalPage): self.currentPage = self.totalPage if (self.currentPage <= 1): self.currentPage = 1 else: self.currentPage = 1 index = (self.currentPage - 1) * self.pageRecord self.pageEdit.setText(str(self.currentPage)) self.recordQuery(index) return def detailInfo(self): index_ = self.tableView.currentIndex().row() if (index_ == -1): print( QMessageBox.warning(self, "警告", "您没有选中任何记录", QMessageBox.Yes, QMessageBox.Yes)) return else: str = self.queryModel.data(self.queryModel.index(index_, 0)) type = self.queryModel.data(self.queryModel.index(index_, 2)) if (type == '身份证识别'): self.recordDetail_id(str) else: self.recordDetail(str) def recordDetail(self, RecordId): recorddetaildialog = RecordDetailDialog(RecordId) recorddetaildialog.show() recorddetaildialog.exec_() def recordDetail_id(self, RecordId): recorddetail_id = RecordDetailDialog_id(RecordId) recorddetail_id.show() recorddetail_id.exec_()
class Window(QMainWindow, Ui_MainWindow): def __init__(self): super().__init__() self.setupUi(self) self.model = QSqlQueryModel() self.connectToSql() # Обработка действий по меню # нажата кнопка меню "Справочники -> Подразделения" self.unit_action.triggered.connect(self.unit_dialog) # нажата кнопка меню "Справочники -> Сотрудники" self.user_action.triggered.connect(self.user_dialog) # нажата кнопка меню "Справочники -> Сервисные службы" self.services_action.triggered.connect(self.services_dialog) # Работа с основной формой # Нажата кнопка "Добавить строку" на форме self.btProblemAdd.clicked.connect(self.bt_add) # Нажата кнопка "Обновить строку" на форме self.btProblemUpdate.clicked.connect(self.bt_upd) # Нажата кнопка "Удалить строку" на форме self.btProblemDel.clicked.connect(self.bt_del) # Найти заявку self.btProblemFind.clicked.connect(self.findText) # Сбросить фильтр self.btProblemReset.clicked.connect(self.reset) def connectToSql(self): """ Открываем базу и создаем запрос и соединяем модель с таблицей """ self.db = QtSql.QSqlDatabase.addDatabase( "QSQLITE") # создаём подключение self.db.setDatabaseName("ProblemDB.db") if self.db.open(): self.qry = QSqlQuery(self.db) self.qry.prepare( """SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d Left Join services s on d.idServices = s.id left join User u on d.idUser = u.id""") self.qry.exec() self.model.setQuery(self.qry) self.tableView.setModel(self.model) self.tableView.horizontalHeader().setSectionResizeMode( QHeaderView.ResizeToContents) self.model.setHeaderData(0, Qt.Horizontal, '№ заявки') self.model.setHeaderData(1, Qt.Horizontal, 'Дата') self.model.setHeaderData(2, Qt.Horizontal, 'Служба') self.model.setHeaderData(3, Qt.Horizontal, 'Текст заявки') self.model.setHeaderData(4, Qt.Horizontal, 'ФИО заявителя') else: QMessageBox.critical(self, 'error', self.model.lastError().text()) def bt_add(self): """ Вызывается по нажатию кнопки "Добавить строку" """ dialog_add = Ed_win(self) # Устанавливаем пустые значения в поля на форме dialog_add.edit_text.setText = '' dialog_add.cb_ser_name.setCurrentIndex(-1) dialog_add.cb_fio.setCurrentIndex(-1) # открываем окно добавления dialog_add.show() # Если нажата кнопка ОК на форме добавления if dialog_add.exec() == QDialog.Accepted: ser = dialog_add.cb_ser_name.currentText() user = dialog_add.cb_fio.currentText() self.db = QtSql.QSqlDatabase.addDatabase( "QSQLITE") # создаём подключение self.db.setDatabaseName("ProblemDB.db") if self.db.open(): qry1 = QSqlQuery(self.db) qry1.prepare("""SELECT s.id FROM services s where s.name =:ser""") qry1.bindValue(":ser", ser) qry1.exec() qry1.next() servId = qry1.value(0) qry1.prepare("""SELECT u.id FROM User u where u.FIO =:user""") qry1.bindValue(":user", user) qry1.exec() qry1.next() userId = qry1.value(0) qry1.prepare( """INSERT INTO def_message(idServices, text,idUser) VALUES(:idService, :text,:idUser)""") qry1.bindValue(":idService", servId) qry1.bindValue(":text", dialog_add.edit_text.text()) qry1.bindValue(":idUser", userId) qry1.exec() self.qry = QSqlQuery(self.db) self.qry.prepare("""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d Left Join services s on d.idServices = s.id left join User u on d.idUser = u.id""") self.qry.exec() self.model.setQuery(self.qry) self.tableView.setModel(self.model) else: QMessageBox.critical(self, 'error', self.model.lastError().text()) def bt_upd(self): """ Вызывается по нажатию кнопки "Изменить строку" """ dialog_add = Ed_win(self) # определяем индекс строки в таблице index = self.tableView.currentIndex().row() print(index) if index == -1: QMessageBox.information(self, 'Ошибка', 'Строка не выбрана') else: # определяем id строки в таблице в базе данных nom = str( self.model.data(self.model.index(index, 0), Qt.DisplayRole)) # устанавливаем текст заявки в поле edit_text dialog_add.edit_text.setText( str(self.model.data(self.model.index(index, 3), Qt.DisplayRole))) # Данные по сервисной службе index_serv = dialog_add.cb_ser_name.findText( str(self.model.data(self.model.index(index, 2), Qt.DisplayRole))) if index_serv > -1: dialog_add.cb_ser_name.setCurrentIndex(index_serv) else: dialog_add.cb_ser_name.setCurrentIndex(-1) # Данные по сотруднику, который подал заявку index_fio = dialog_add.cb_fio.findText( str(self.model.data(self.model.index(index, 4), Qt.DisplayRole))) if index_fio > -1: dialog_add.cb_fio.setCurrentIndex(index_fio) else: dialog_add.cb_fio.setCurrentIndex(-1) # ОТкрываем окно с данными для редактирования dialog_add.show() # если нажата кнопка ОК в окне для редактирования строки if dialog_add.exec() == QDialog.Accepted: ser = dialog_add.cb_ser_name.currentText() user = dialog_add.cb_fio.currentText() self.db = QtSql.QSqlDatabase.addDatabase( "QSQLITE") # создаём подключение self.db.setDatabaseName("ProblemDB.db") if self.db.open(): qry1 = QSqlQuery(self.db) qry1.prepare("""SELECT s.id FROM services s where s.name =:ser""") qry1.bindValue(":ser", ser) qry1.exec() qry1.next() servId = qry1.value(0) qry1.prepare("""SELECT u.id FROM User u where u.FIO =:user""") qry1.bindValue(":user", user) qry1.exec() qry1.next() userId = qry1.value(0) qry1.prepare( """UPDATE def_message SET idServices=:servId, text=:txt,idUser=:userId WHERE id=:id""" ) qry1.bindValue(":servId", servId) qry1.bindValue(":txt", dialog_add.edit_text.text()) qry1.bindValue(":userId", userId) qry1.bindValue(":id", nom) qry1.exec() # снова делаем выборку данных после проведения обновления данных self.qry = QSqlQuery(self.db) self.qry.prepare( """SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d Left Join services s on d.idServices = s.id left join User u on d.idUser = u.id""") self.qry.exec() self.model.setQuery(self.qry) self.tableView.setModel(self.model) else: QMessageBox.critical(self, 'error', self.model.lastError().text()) def bt_del(self): """ Вызывается по нажатию кнопки "Удалить строку" """ quit_msg = "Подтвердите удаление записи" reply = QMessageBox.question(self, 'Confirm', quit_msg, QMessageBox.Yes, QMessageBox.No) if reply == QMessageBox.Yes: index = self.tableView.currentIndex().row() nom = str( self.model.data(self.model.index(index, 0), Qt.DisplayRole)) self.db = QtSql.QSqlDatabase.addDatabase( "QSQLITE") # создаём подключение self.db.setDatabaseName("ProblemDB.db") if self.db.open(): qry1 = QSqlQuery(self.db) qry1.prepare("""DELETE FROM def_message where id =:id""") qry1.bindValue(":id", nom) qry1.exec() self.qry = QSqlQuery(self.db) self.qry.prepare( """SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d Left Join services s on d.idServices = s.id left join User u on d.idUser = u.id""" ) self.qry.exec() self.model.setQuery(self.qry) self.tableView.setModel(self.model) else: QMessageBox.critical(self, 'error', self.model.lastError().text()) def findText(self): """ Вызывается по кнопке "Найти заявку" """ self.db = QtSql.QSqlDatabase.addDatabase( "QSQLITE") # создаём подключение self.db.setDatabaseName("ProblemDB.db") if self.db.open(): self.qry = QSqlQuery(self.db) self.qry.prepare( """SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d Left Join services s on d.idServices = s.id left join User u on d.idUser = u.id where d.text Like :txt """) self.qry.bindValue(":txt", '%' + self.EditFind.text() + '%') self.qry.exec() self.model.setQuery(self.qry) self.tableView.setModel(self.model) else: QMessageBox.critical(self, 'error', self.model.lastError().text()) def reset(self): """ Вызывается по кнопке "Сбросить фильтр" """ self.db = QtSql.QSqlDatabase.addDatabase( "QSQLITE") # создаём подключение self.db.setDatabaseName("ProblemDB.db") if self.db.open(): self.qry = QSqlQuery(self.db) self.qry.prepare( """SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d Left Join services s on d.idServices = s.id left join User u on d.idUser = u.id """) self.qry.exec() self.model.setQuery(self.qry) self.tableView.setModel(self.model) else: QMessageBox.critical(self, 'error', self.model.lastError().text()) def unit_dialog(self): """ Функция выполняется при нажатии кнопки меню "Справочники -> Подразделения" :return: Открывает окно с таблицей информации по подразделениям (Unit_win) """ dialog_unit = Unit_win(self) dialog_unit.show() # Скрываем основное окно программы wnd.hide() def user_dialog(self): """ Функция выполняется при нажатии кнопки меню "Справочники -> Сотрудники" :return: Открывает окно с таблицей информации по отрудникам (User_win) """ dialog_user = User_win(self) dialog_user.show() # Скрываем основное окно программы wnd.hide() def services_dialog(self): """ Функция выполняется при нажатии кнопки меню "Справочники -> Сервисные службы" :return: Открывает окно с таблицей информации по сервисным службам (Services_win) """ dialog = Services_win(self) dialog.show() # Скрываем основное окно программы wnd.hide()
class QmyMainWindow(QtWidgets.QMainWindow): def __init__(self, parent=None): super().__init__(parent) self.ui = Ui_MainWindow() self.ui.setupUi(self) self.setCentralWidget(self.ui.splitter) self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows) self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection) self.ui.tableView.setAlternatingRowColors(True) self.ui.tableView.verticalHeader().setDefaultSectionSize(22) self.ui.tableView.horizontalHeader().setDefaultSectionSize(60) @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, "错误", "打开数据库失败") def __openTable(self): self.qryModel = QSqlQueryModel(self) self.qryModel.setQuery( '''SELECT empNo, Name, Gender, Birthday, Province, Department, Salary FROM employee ORDER BY empNo''' ) if self.qryModel.lastError().isValid(): QMessageBox.critical( self, "错误", "数据表查询错误,错误信息\n" + self.qryModel.lastError().text()) return self.ui.statusBar.showMessage("记录条数:%d" % self.qryModel.rowCount()) self.__getFieldNames() self.qryModel.setHeaderData(0, Qt.Horizontal, "工号") self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名") self.qryModel.setHeaderData(2, Qt.Horizontal, "性别") self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期") self.qryModel.setHeaderData(4, Qt.Horizontal, "省份") self.qryModel.setHeaderData(5, Qt.Horizontal, "部门") self.qryModel.setHeaderData(6, Qt.Horizontal, "工资") self.mapper = QDataWidgetMapper() self.mapper.setModel(self.qryModel) self.mapper.addMapping(self.ui.dbSpinEmpNo, 0) self.mapper.addMapping(self.ui.dbEditName, 1) self.mapper.addMapping(self.ui.dbComboSex, 2) self.mapper.addMapping(self.ui.dbEditBirth, 3) self.mapper.addMapping(self.ui.dbComboProvince, 4) self.mapper.addMapping(self.ui.dbComboDep, 5) self.mapper.addMapping(self.ui.dbSpinSalary, 6) self.mapper.toFirst() self.selModel = QItemSelectionModel(self.qryModel) self.selModel.currentRowChanged.connect(self.do_currentRowChanged) self.ui.tableView.setModel(self.qryModel) self.ui.tableView.setSelectionModel(self.selModel) self.ui.actOpenDB.setEnabled(False) def __refreshTableView(self): index = self.mapper.currentIndex() curIndex = self.qryModel.index(index, 1) self.selModel.clearSelection() self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select) def __getFieldNames(self): emptyRec = self.qryModel.record() self.fldNum = {} for i in range(emptyRec.count()): fieldName = emptyRec.fieldName(i) self.fldNum.setdefault(fieldName) self.fldNum[fieldName] = i print(self.fldNum) @pyqtSlot() def on_actRecFirst_triggered(self): self.mapper.toFirst() self.__refreshTableView() @pyqtSlot() def on_actRecPrevious_triggered(self): self.mapper.toPrevious() self.__refreshTableView() @pyqtSlot() def on_actRecNext_triggered(self): self.mapper.toNext() self.__refreshTableView() @pyqtSlot() def on_actRecLast_triggered(self): self.mapper.toLast() self.__refreshTableView() def do_currentRowChanged(self, current, previous): if (current.isValid() == False): self.ui.dbLabPhoto.clear() return self.mapper.setCurrentIndex(current.row()) first = (current.row() == 0) last = (current.row() == self.qryModel.rowCount() - 1) self.ui.actRecFirst.setEnabled(not first) self.ui.actRecPrevious.setEnabled(not first) self.ui.actRecNext.setEnabled(not last) self.ui.actRecLast.setEnabled(not last) curRec = self.qryModel.record(current.row()) empNo = curRec.value("EmpNo") query = QSqlQuery(self.DB) query.prepare( '''SELECT EmpNo, Memo, Photo FROM employee WHERE EmpNo = :ID''') query.bindValue(":ID", empNo) if not query.exec(): QMessageBox.critical(self, "错误", "执行SQL语句错误\n" + query.lastError().text()) return else: query.first() picData = query.value("Photo") if (picData == None): self.ui.dbLabPhoto.clear() else: pic = QPixmap() pic.loadFromData(picData) W = self.ui.dbLabPhoto.size().width() self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(W)) memoData = query.value("Memo") self.ui.dbEditMemo.setPlainText(memoData)
def deleteData(self): current_row = self.listView.currentIndex() des_name = self.listView.model().data(current_row,0) # ถ้าไม่ได้เลือก record ให้ return if des_name == None: self.showDialog('กรุณาเลือกปลายทางที่จะลบก่อน') return des_name_inUse = False # เก็บ status ของ des_name # สร้าง sqlmModel ไว้สำหรับ query des_id มาเทียบกันระหว่างสองตาราง tempFromQueue = QSqlQueryModel() selectQueue = QSqlQuery() selectQueue.prepare('SELECT des_id FROM queue') tempFromDes = QSqlQueryModel() selectDes = QSqlQuery() selectDes.prepare('SELECT des_id FROM destination WHERE des_name = ' + f"'{des_name}'") # เก็บค่าของจากทั้งสองตารางมาตรวจสอบการซ้ำของ des_id ว่ามีการใชงานอยู่จริงหรือไม่ # exec query ถ้าผ่านให้ setQuery if selectDes.exec() == True and selectQueue.exec() == True: tempFromDes.setQuery(selectDes) tempFromQueue.setQuery(selectQueue) # fetch more หา rowCount ของจริง while tempFromQueue.canFetchMore(): tempFromQueue.fetchMore() # เทียบค่า des_id for i in range(tempFromQueue.rowCount()): if tempFromQueue.index(i,0).data() == tempFromDes.index(0,0).data(): des_name_inUse = True break else: des_name_inUse = False # ตรวจสอบว่า des_name ไม่มีการใช้งานในตาราง queue # หากมีการใช้งาน จะไม่สามารถลบออกจาก destination ได้ if des_name_inUse == True: self.showDialog(f'ปลายทาง {des_name} มีการใช้งานในตาราง ไม่สามารถลบได้') return # ยืนยันการลบด้วย code 1024 confCode = self.confDelete(des_name) if confCode == 1024: try: deleteQuery = QSqlQuery() deleteQuery.prepare("DELETE FROM destination WHERE des_name = " + f"('{des_name}')") print('Query = ' + deleteQuery.lastQuery()) if deleteQuery.exec(): print('DELETE COMPLETE') self.showDialog('ลบปลายทาง : ' + des_name + ' แล้ว') self.loadData() else: print('DELETE FALSE = ' + deleteQuery.lastError().text()) except(QSqlError) as e: print(str(time.strftime("%H:%M:%S : ", time.localtime())) + 'ERROR :' + str(e)) else: print('User decided cancel delete this record.')
class ProjectsOverview(FilterableTable): """a widget to display a complete overview over all projects """ changed_projects = pyqtSignal(str) change_view = pyqtSignal(int) deleted_project = pyqtSignal() submit_to_ENA = pyqtSignal(str) submit_to_IPD = pyqtSignal(str) open_new_allele_form = pyqtSignal(str) def __init__(self, log, mydb, parent = None): super().__init__(log, mydb) if parent: self.settings = parent.settings else: import GUI_login self.settings = GUI_login.get_settings("admin", self.log) self.enhance_UI() self.show_closed = False self.filter_closed() self.table.resizeColumnsToContents() self.table.horizontalHeader().setSectionResizeMode(6, QHeaderView.Stretch) self.table.horizontalHeader().setSectionResizeMode(7, QHeaderView.Stretch) self.add_headers() self.update_filterbox() def enhance_UI(self): self.toggle_btn = QPushButton(self) self.toggle_btn.setText("Show closed projects!") self.toggle_btn.setCheckable(True) self.show_closed = False self.grid.addWidget(self.toggle_btn, 1, 0) self.toggle_btn.toggled.connect(self.on_toggleBtn_clicked) self.table.customContextMenuRequested.connect(self.open_menu) def add_headers(self): headers = ["Project Name", "Project Status", "Creation Date", "User Name", "Gene", "Pool", "Title", "Description", "Number of Alleles"] for (i, column) in enumerate(headers): self.proxy.setHeaderData(i, Qt.Horizontal, column) def create_model(self): """creates the table model """ self.log.debug("Creating the table model...") self.model = QSqlQueryModel() self.query_open = """ SELECT projects.project_name, project_status, creation_date, username, projects.gene, pool, title, description, count(alleles.local_name) as nr_alleles FROM projects LEFT OUTER JOIN alleles ON projects.project_name = alleles.project_name WHERE project_status = 'Open' GROUP BY projects.project_name, project_status, creation_date, username, projects.gene, pool, title, description ORDER BY projects.project_name desc """ self.query_all = """ SELECT projects.project_name, project_status, creation_date, username, projects.gene, pool, title, description, count(alleles.local_name) as nr_alleles FROM projects LEFT OUTER JOIN alleles ON projects.project_name = alleles.project_name GROUP BY projects.project_name, project_status, creation_date, username, projects.gene, pool, title, description ORDER BY projects.project_name desc """ self.q = QSqlQuery() self.log.debug("\t=> Done!") @pyqtSlot() def filter_closed(self): """filters the table either for only open projects (default) or all projects """ if self.show_closed: self.log.debug("Filtering for all projects...") query = self.query_all else: self.log.debug("Filtering for only open projects...") query = self.query_open self.q.exec_(query) self.check_error(self.q) self.model.setQuery(self.q) def on_toggleBtn_clicked(self, state): """when "ShowAll!" button is toggled, reset query to appropriate filter """ self.show_closed = state self.filter_closed() def open_menu(self, pos): """provides a context menu """ menu = QMenu() new_act = menu.addAction("Add new Target Allele") open_act = menu.addAction("Open Project View") select_act = menu.addAction("Select as current Project") ena_act = menu.addAction("Submit Project to ENA") ipd_act = menu.addAction("Submit Project to IPD") del_act = menu.addAction("Delete project if empty") action = menu.exec_(self.table.mapToGlobal(pos)) if action: row = self.table.indexAt(pos).row() myindex = self.model.index(row, 0) project = self.model.data(myindex) if action == select_act: self.changed_projects.emit(project) self.log.debug("ProjectsOverview emitted changed_projects") elif action == open_act: self.log.debug("ProjectsOverview emitted changed_projects & change_view") self.changed_projects.emit(project) self.change_view.emit(3) elif action == new_act: self.open_new_allele_form.emit(project) elif action == ena_act: self.submit_to_ENA.emit(project) elif action == ipd_act: self.submit_to_IPD.emit(project) elif action == del_act: self.delete_project(project, row) @pyqtSlot(str, int) def delete_project(self, project, row): """delete a project from the database & file system if it's empty """ self.log.debug("Attempting to delete project '{}' from database...".format(project)) q = QSqlQuery() count_query = "select count(*) from alleles where project_name = '{}'".format(project) q.exec_(count_query) check_error(q, self.mydb, self.log) alleles = 0 while q.next(): alleles = q.value(0) if alleles == 0: delete_query = "delete from projects where project_name = '{}'".format(project) q.exec_(delete_query) self.log.debug("\t=> Successfully deleted empty project") self.log.debug("Attempting to delete project '{}' from file system...".format(project)) project_dir = os.path.join(self.settings["projects_dir"], project) try: shutil.rmtree(project_dir) except Exception as E: self.log.debug("=> File deletion did not work:") self.log.error(E) self.log.exception(E) self.log.debug("=> Project '{}' successfully deleted from database and file system".format(project)) self.deleted_project.emit() else: self.log.debug("\t=> Project contains {} alleles => cannot delete!".format(alleles)) def refresh(self): """refreshes the table using setQuery => take care, this might lead to performance issues """ self.model.setQuery(self.model.query().lastQuery())