def remove_rule(self, rule): query = QSqlQueryModel() query.setQuery("delete from rules "\ "where id={}"\ .format(rule.idx)) if query.lastError().isValid(): print(query.lastError().text(), file=sys.stderr)
def update_dsc(self, thing, dsc): query = QSqlQueryModel() query.setQuery("UPDATE things "\ "SET dsc = '{}' "\ "WHERE id={}" .format(dsc, thing.idx)) if query.lastError().isValid(): print(query.lastError().text(), file=sys.stderr)
def insert_rule(self, name, dsc, things): query = QSqlQueryModel() t_ids = sorted([x.idx for x in things]) query.setQuery("insert into rules(name, things, dsc) "\ "values ('{}', {}, '{}')"\ .format(name, _array_string(t_ids), dsc)) if query.lastError().isValid(): print(query.lastError().text(), file=sys.stderr)
def remove_thing(self, thing): rules = self.rules_with_thing(thing) for rule in rules: self.remove_rule(rule) query = QSqlQueryModel() query.setQuery("delete from things "\ "where id={}"\ .format(thing.idx)) if query.lastError().isValid(): print(query.lastError().text(), file=sys.stderr)
def rules(self): query = QSqlQueryModel() query.setQuery("select * from rules") if query.lastError().isValid(): print(query.lastError().text(), file=sys.stderr) ret = [] for i in range(query.rowCount()): rule = self.rule_by_sqlrecord(query.record(i)) ret.append(rule) return ret
def rules_with_thing(self, thing): query = QSqlQueryModel() query.setQuery("select * from rules "\ "where {} = ANY(things)"\ .format(thing.idx)) if query.lastError().isValid(): print(query.lastError().text(), file=sys.stderr) ret = [] for i in range(query.rowCount()): rule = self.rule_by_sqlrecord(query.record(i)) ret.append(rule) return ret
def things(self, types): query = QSqlQueryModel() if types is not None: query.setQuery("select * from things "\ "where type=ANY({})"\ .format(_array_string(types))) else: query.setQuery("select * from things") if query.lastError().isValid(): print(query.lastError().text(), file=sys.stderr) ret = [] for i in range(query.rowCount()): thing = self.thing_by_sqlrecord(query.record(i)) ret.append(thing) return ret
def rules_by_tids(self, t_ids, exact): query = QSqlQueryModel() if exact: query.setQuery("select * from rules "\ "where things={}"\ .format(_array_string(t_ids)), db=self.db) else: query.setQuery("select * from rules "\ "where things @>{}"\ .format(_array_string(t_ids)), db=self.db) if query.lastError().isValid(): print(query.lastError().text(), file=sys.stderr) ret = [] for i in range(query.rowCount()): rule = self.rule_by_sqlrecord(query.record(i)) ret.append(rule) return ret
def executeQuery(self): """ Public slot to execute the entered query. """ model = QSqlQueryModel(self.table) model.setQuery(QSqlQuery(self.sqlEdit.toPlainText(), self.connections.currentDatabase())) self.table.setModel(model) if model.lastError().type() != QSqlError.NoError: self.statusMessage.emit(model.lastError().text()) elif model.query().isSelect(): self.statusMessage.emit(self.tr("Query OK.")) else: self.statusMessage.emit( self.tr("Query OK, number of affected rows: {0}").format(model.query().numRowsAffected()) ) self.table.resizeColumnsToContents() self.updateActions()
def executeQuery(self): """ Public slot to execute the entered query. """ model = QSqlQueryModel(self.table) model.setQuery( QSqlQuery(self.sqlEdit.toPlainText(), self.connections.currentDatabase())) self.table.setModel(model) if model.lastError().type() != QSqlError.NoError: self.statusMessage.emit(model.lastError().text()) elif model.query().isSelect(): self.statusMessage.emit(self.tr("Query OK.")) else: self.statusMessage.emit( self.tr("Query OK, number of affected rows: {0}").format( model.query().numRowsAffected())) self.table.resizeColumnsToContents() self.updateActions()
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 WarehouseController(QObject): def __init__(self, list, table, addButton, editButton, deleteButton, dbase): super().__init__() self.list = list self.table = table self.addButton = addButton self.editButton = editButton self.deleteButton = deleteButton self.dbase = dbase self.shopModel = QSqlTableModel(db = dbase) self.shopModel.setTable("shop") self.shopModel.select() self.list.setModel(ComplexListModel(self.shopModel, "{name}")) self.list.selectionModel().currentChanged.connect(self.listSelectionChanged) self._checkPrivileges() if self.only_select: self.addButton.setEnabled(False) self.deleteButton.setEnabled(False) self.addButton.clicked.connect(self.addButtonClicked) self.editButton.clicked.connect(self.editButtonClicked) self.deleteButton.clicked.connect(self.deleteButtonClicked) def _checkPrivileges(self): query = QSqlQuery("SHOW GRANTS") only_select = None table_pattern = "`{}`".format("shop_detail").lower() while query.next(): s = query.value(0).lower() if table_pattern in s: if "select" in s and only_select is None: only_select = True else: only_select = False self.only_select = bool(only_select) def listSelectionChanged(self, cur, prev): if not cur.isValid(): return self.table.setModel(None) else: self.setShopIndex(cur.row()) def setShopIndex(self, row): record = self.shopModel.record(row) self.detailModel = QSqlQueryModel() query = "SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as dtl, shop_detail.quantity as qnt \ FROM shop_detail INNER JOIN detail \ ON shop_detail.detail_id = detail.id \ WHERE shop_detail.shop_id={} ORDER BY dtl".format(record.value("id")) self.detailModel.setQuery(query) self.detailModel.setHeaderData(1, Qt.Horizontal, "Наименование") self.detailModel.setHeaderData(2, Qt.Horizontal, "Количество") self.table.setModel(self.detailModel) self.table.hideColumn(0) self.table.resizeColumnsToContents() self.table.selectionModel().currentChanged.connect(self.tableSelectionChanged) if not self.detailModel.query().isActive(): print(self.detailModel.lastError().text()) self.deleteButton.setEnabled(False) self.editButton.setEnabled(False) def tableSelectionChanged(self, cur, prev): if self.only_select: return if cur.isValid(): self.deleteButton.setEnabled(True) self.editButton.setEnabled(True) else: self.deleteButton.setEnabled(False) self.editButton.setEnabled(False) self.addButton.setEnabled(True) def addButtonClicked(self): shop = self.shopModel.record(self.list.currentIndex().row()) query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \ FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM shop_detail \ WHERE shop_id={}))".format(shop.value("id"))) details = {} while query.next(): details[query.value("name")] = query.value("id") if not details: return QMessageBox.warning(None, "Ошибка добавления", "Не удалось добавить новый товар на склад: все возможные товары уже добавлены.") choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:", list(details.keys()), 0, False) if not ok: return qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", 1, 1) if not ok: return detail_id = details[choice] shop_id = shop.value("id") query = QSqlQuery("INSERT INTO shop_detail (shop_id, detail_id, quantity) \ VALUES ({}, {}, {})".format(shop_id, detail_id, qnt)) if not query.isActive(): print(query.lastError().text()) self.setShopIndex(self.list.currentIndex().row()) self.table.selectionModel().clearSelection() def editButtonClicked(self): detail = self.detailModel.record(self.table.currentIndex().row()) qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", detail.value("qnt"), 0) if not ok: return shop = self.shopModel.record(self.list.currentIndex().row()) if qnt > 0: query = QSqlQuery("UPDATE shop_detail SET quantity={} \ WHERE shop_id={} AND detail_id={}".format(qnt, shop.value("id"), detail.value("id"))) else: query = QSqlQuery("DELETE FROM shop_detail WHERE \ shop_id={} AND detail_id={} LIMIT 1".format( shop.value("id"), detail.value("id"))) if not query.isActive(): print(query.lastError().text()) self.setShopIndex(self.list.currentIndex().row()) def deleteButtonClicked(self): if not self.table.currentIndex().isValid(): return detail = self.detailModel.record(self.table.currentIndex().row()) shop = self.shopModel.record(self.list.currentIndex().row()) query = QSqlQuery("DELETE FROM shop_detail WHERE \ shop_id={} AND detail_id={} LIMIT 1".format( shop.value("id"), detail.value("id"))) if not query.isActive(): print(query.lastError().text()) self.setShopIndex(self.list.currentIndex().row()) def update(self): cur = self.list.currentIndex() if cur.isValid(): row = cur.row() else: row = 0 self.shopModel.select() self.list.reset() self.selectRow(row) def selectRow(self, row): self.list.selectionModel().clearSelection() self.list.selectionModel().setCurrentIndex( self.shopModel.index(row, 0), QItemSelectionModel.Select)
class Filtrage_bope_dialog(QDialog, Ui_dlgBopeRechercheForm): ''' Class de la fenêtre permettant le filtrage attributaire des baux de pêche :param QDialog: Permet d'afficher l'interface graphique comme une fenêtre indépendante :type QDialog: QDialog :param Ui_dlgBopeRechercheForm: Class du script de l'interface graphique du formulaire, apporte les éléments de l'interface :type Ui_dlgBopeRechercheForm: class ''' def __init__(self, db, dbType, dbSchema, modelBauxPe, parent=None): ''' Constructeur, récupération de variable, connection des événements et remplissage des combobox :param db: définie dans le setupModel(), représente la connexion avec la base de données :type db: QSqlDatabase :param dbType: type de la base de données (postgre) :type dbType: str :param dbSchema: nom du schéma sous PostgreSQL contenant les données (data) :type dbSchema: unicode :param modelBauxPe: modèle droit de pêche qui contient les données de la base de données :type modelBauxPe: QSqlRelationalTableModel :param parent: défini que cette fenêtre n'hérite pas d'autres widgets :type parent: NoneType ''' super(Filtrage_bope_dialog, self).__init__(parent) self.db = db self.dbType = dbType self.dbSchema = dbSchema self.modelBauxPe = modelBauxPe self.setupUi(self) self.btnAnnuler.clicked.connect(self.reject) self.btnExec.clicked.connect(self.execution) self.btnRaz.clicked.connect(self.raz) self.btnEt.clicked.connect(self.et) self.btnOu.clicked.connect(self.ou) self.btnPrevisualiser.clicked.connect(self.previSql) self.btnId.clicked.connect(self.ajoutId) self.btnRiviere.clicked.connect(self.ajoutRiviere) self.btnAappma.clicked.connect(self.ajoutAappma) self.btnPossession.clicked.connect(self.ajoutPossession) self.btnSign.clicked.connect(self.ajoutDateSign) self.btnFin.clicked.connect(self.ajoutDateFin) self.btnC.clicked.connect(self.ajoutCommune) self.btnCS.clicked.connect(self.ajoutComSection) self.btnCSP.clicked.connect(self.ajoutComSecParcelle) self.btnProprio.clicked.connect(self.ajoutProprio) self.btnAdresse.clicked.connect(self.ajoutAdresse) self.btnEt.setEnabled(False) self.btnOu.setEnabled(False) self.leTel.setInputMask("#9999999999999") self.possessionBool = False self.aappmaBool = False self.anneeSignBool = False self.anneeFinBool = False self.CBool = False self.CSBool = False self.CSPBool = False self.wwhere = "" self.wwherePossession = "" self.wwhereProprio = "" self.modelAappma = QSqlTableModel(self, self.db) wrelation = "aappma" if self.dbType == "postgres": wrelation = self.dbSchema + "." + wrelation self.modelAappma.setTable(wrelation) self.modelAappma.setSort(1, Qt.AscendingOrder) if (not self.modelAappma.select()): QMessageBox.critical(self, u"Remplissage du modèle AAPPMA", self.modelAappma.lastError().text(), QMessageBox.Ok) self.cmbAappma.setModel(self.modelAappma) self.cmbAappma.setModelColumn(self.modelAappma.fieldIndex("apma_nom")) self.modelRiviere = QSqlTableModel(self, self.db) wrelation = "cours_eau" if self.dbType == "postgres": wrelation = self.dbSchema + "." + wrelation self.modelRiviere.setTable(wrelation) self.modelRiviere.setFilter("ceau_nom <> 'NR'") self.modelRiviere.setSort(2, Qt.AscendingOrder) if (not self.modelRiviere.select()): QMessageBox.critical(self, u"Remplissage du modèle Rivière", self.modelRiviere.lastError().text(), QMessageBox.Ok) self.cmbRiviere.setModel(self.modelRiviere) self.cmbRiviere.setModelColumn( self.modelRiviere.fieldIndex("ceau_nom")) self.modelCommune = QSqlTableModel(self, self.db) wrelation = "commune" if self.dbType == "postgres": wrelation = self.dbSchema + "." + wrelation self.modelCommune.setTable(wrelation) self.modelCommune.setSort(2, Qt.AscendingOrder) if (not self.modelCommune.select()): QMessageBox.critical(self, u"Remplissage du modèle Commune", self.modelCommune.lastError().text(), QMessageBox.Ok) self.cmbCommune.setModel(self.modelCommune) self.cmbCommune.setModelColumn(self.modelCommune.fieldIndex("com_nom")) self.cmbCommune.setCurrentIndex(1) self.modelSection = QSqlQueryModel(self) self.modelParcelle = QSqlQueryModel(self) self.cmbSection.setModel(self.modelSection) self.cmbSection.setModelColumn(2) self.cmbParcelle.setModel(self.modelParcelle) self.cmbParcelle.setModelColumn(1) self.cmbCommune.currentIndexChanged.connect(self.changeCmbCommune) self.cmbSection.currentIndexChanged.connect(self.changeCmbSection) self.cmbCommune.setCurrentIndex(0) def reject(self): '''Ferme la fenêtre si clic sur le bouton annuler''' QDialog.reject(self) def changeCmbCommune(self, newInd): ''' Filtre la combobox section en fonction de la commune affichée dans celle des communes :param newInd: index courant dans la combobox :type newInd: int ''' self.modelParcelle.clear() self.cmbParcelle.clear() self.cmbParcelle.setModel(self.modelParcelle) self.cmbParcelle.setModelColumn(1) record = self.modelCommune.record(newInd) wcom_insee = record.value(self.modelCommune.fieldIndex("com_insee")) self.modelSection.clear() wrelation = "section" if self.dbType == "postgres": wrelation = self.dbSchema + "." + wrelation self.modelSection.setQuery( "select sec_id, sec_nom || ' ; ' || sec_com_abs from " + wrelation + " where sec_com_insee = '%s' order by sec_nom;" % str(wcom_insee), self.db) if self.modelSection.lastError().isValid(): QMessageBox.critical(self, u"Remplissage du modèle Section", self.modelSection.lastError().text(), QMessageBox.Ok) self.cmbSection.setModel(self.modelSection) self.cmbSection.setModelColumn(1) self.cmbSection.setCurrentIndex(0) def changeCmbSection(self, newInd): ''' Filtre la combobox parcelle en fonction de la section affichée dans celle des sections :param newInd: index courant dans la combobox :type newInd: int ''' record = self.modelSection.record(newInd) wsec_id = record.value(0) self.cmbParcelle.clear() self.modelParcelle.clear() wrelation = "parcelle" if self.dbType == "postgres": wrelation = self.dbSchema + "." + wrelation self.modelParcelle.setQuery( "select par_id, par_numero from " + wrelation + " where par_sec_id = '%s' order by par_numero;" % str(wsec_id), self.db) if self.modelParcelle.lastError().isValid(): QMessageBox.critical(self, u"Remplissage du modèle Parcelle", self.modelParcelle.lastError().text(), QMessageBox.Ok) self.cmbParcelle.setModel(self.modelParcelle) self.cmbParcelle.setModelColumn(1) def raz(self): '''Réinitialise toutes les variables de la fenêtre afin de recommencer une nouvelle requête''' self.possessionBool = False self.aappmaBool = False self.anneeSignBool = False self.anneeFinBool = False self.CBool = False self.CSBool = False self.CSPBool = False self.spnId.setValue(0) self.wrq = "" self.txtSql.setText("") self.wwhere = "" self.wwherePossession = "" self.wwhereProprio = "" self.chkPossession.setChecked(False) self.dateSign.setDate(QDate(2000, 1, 1)) self.dateFin.setDate(QDate(2000, 1, 1)) self.btnEt.setEnabled(False) self.btnOu.setEnabled(False) self.btnId.setEnabled(True) self.btnSign.setEnabled(True) self.btnFin.setEnabled(True) self.btnRiviere.setEnabled(True) self.btnAappma.setEnabled(True) self.btnPossession.setEnabled(True) self.btnC.setEnabled(True) self.btnCS.setEnabled(True) self.btnCSP.setEnabled(True) self.btnProprio.setEnabled(True) self.btnAdresse.setEnabled(True) def et(self): '''Change l'état des boutons et ajoute "and" à la requête''' self.btnEt.setEnabled(False) self.btnOu.setEnabled(False) self.btnId.setEnabled(True) self.btnRiviere.setEnabled(True) self.btnProprio.setEnabled(True) self.btnAdresse.setEnabled(True) if self.possessionBool == False: self.btnPossession.setEnabled(True) if self.aappmaBool == False: self.btnAappma.setEnabled(True) if self.anneeSignBool == False: self.btnSign.setEnabled(True) if self.anneeFinBool == False: self.btnFin.setEnabled(True) if self.CBool == False: self.btnC.setEnabled(True) if self.CSBool == False: self.btnCS.setEnabled(True) if self.CSPBool == False: self.btnCSP.setEnabled(True) self.wwhere += " AND " def ou(self): '''Change l'état des boutons et ajoute "or" à la requête''' self.btnEt.setEnabled(False) self.btnOu.setEnabled(False) self.btnId.setEnabled(True) self.btnSign.setEnabled(True) self.btnFin.setEnabled(True) self.btnRiviere.setEnabled(True) self.btnAappma.setEnabled(True) self.btnC.setEnabled(True) self.btnCS.setEnabled(True) self.btnCSP.setEnabled(True) self.btnProprio.setEnabled(True) self.btnAdresse.setEnabled(True) self.btnPossession.setEnabled(True) self.possessionBool = False self.aappmaBool = False self.anneeSignBool = False self.anneeFinBool = False self.CBool = False self.CSBool = False self.CSPBool = False self.wwhere += " OR " def ajoutId(self): '''Change l'état des boutons et ajoute un critère d'id à la requête''' self.btnOu.setEnabled(True) self.btnId.setEnabled(False) self.btnSign.setEnabled(False) self.btnFin.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnPossession.setEnabled(False) self.btnC.setEnabled(False) self.btnCS.setEnabled(False) self.btnCSP.setEnabled(False) self.btnProprio.setEnabled(False) self.btnAdresse.setEnabled(False) self.wid = self.spnId.value() if self.spnId.value() != "": if self.wid != "": self.wwhere += "bope_id = '" + str(self.wid) + "'" self.spnId.setValue(0) self.spnId.setFocus() def ajoutRiviere(self): '''Change l'état des boutons et ajoute un critère de cours d'eau à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnId.setEnabled(False) self.btnSign.setEnabled(False) self.btnFin.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnPossession.setEnabled(False) self.btnC.setEnabled(False) self.btnCS.setEnabled(False) self.btnCSP.setEnabled(False) self.btnProprio.setEnabled(False) self.btnAdresse.setEnabled(False) wfrombce = "bail_cours_eau" if self.dbType == "postgres": self.wfromCeau = self.dbSchema + "." + wfrombce wrecord = self.cmbRiviere.model().record( self.cmbRiviere.currentIndex()) self.wCeau = wrecord.value(0) if self.cmbRiviere.currentText() != "": if self.wCeau != "": self.wwhere += "bope_id in (select distinct bce_bope_id from " + self.wfromCeau + " where bce_ceau_id = '" + str( self.wCeau) + "')" def ajoutAappma(self): '''Change l'état des boutons et ajoute un critère d'aappma à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnId.setEnabled(False) self.btnSign.setEnabled(False) self.btnFin.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnPossession.setEnabled(False) self.btnC.setEnabled(False) self.btnCS.setEnabled(False) self.btnCSP.setEnabled(False) self.btnProprio.setEnabled(False) self.btnAdresse.setEnabled(False) self.aappmaBool = True wrecord = self.cmbAappma.model().record(self.cmbAappma.currentIndex()) self.wbope_aappma = wrecord.value(0) if self.cmbAappma.currentText() != "": if self.wbope_aappma != "": self.wwhere += "bope_apma_id = '" + str( self.wbope_aappma) + "'" def ajoutPossession(self): '''Change l'état des boutons et ajoute un critère de possession à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnId.setEnabled(False) self.btnSign.setEnabled(False) self.btnFin.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnSign.setEnabled(False) self.btnPossession.setEnabled(False) self.btnC.setEnabled(False) self.btnCS.setEnabled(False) self.btnCSP.setEnabled(False) self.btnProprio.setEnabled(False) self.btnAdresse.setEnabled(False) self.possessionBool = True if self.chkPossession.isChecked() == True: self.wwherePossession = "bope_existe = True" else: self.wwherePossession = "bope_existe = False" self.wwhere += self.wwherePossession def ajoutDateSign(self): '''Change l'état des boutons et ajoute un critère de date de signature à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnId.setEnabled(False) self.btnSign.setEnabled(False) self.btnFin.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnSign.setEnabled(False) self.btnPossession.setEnabled(False) self.btnC.setEnabled(False) self.btnCS.setEnabled(False) self.btnCSP.setEnabled(False) self.btnProprio.setEnabled(False) self.btnAdresse.setEnabled(False) self.anneeSignBool = True self.wbope_date_sign = self.dateSign.date().toString("yyyy") if self.wbope_date_sign != "": self.wwhere += "date_part('year', bope_date_sign) = '" + str( self.wbope_date_sign) + "'" def ajoutDateFin(self): '''Change l'état des boutons et ajoute un critère de date d'expiration à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnId.setEnabled(False) self.btnSign.setEnabled(False) self.btnFin.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnPossession.setEnabled(False) self.btnC.setEnabled(False) self.btnCS.setEnabled(False) self.btnCSP.setEnabled(False) self.btnProprio.setEnabled(False) self.btnAdresse.setEnabled(False) self.anneeFinBool = True self.wbope_date_fin = self.dateFin.date().toString("yyyy") if self.wbope_date_fin != "": self.wwhere += "date_part('year', bope_date_fin) = '" + str( self.wbope_date_fin) + "'" def ajoutCommune(self): '''Change l'état des boutons et ajoute un critère de commune à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnId.setEnabled(False) self.btnSign.setEnabled(False) self.btnFin.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnPossession.setEnabled(False) self.btnC.setEnabled(False) self.btnCS.setEnabled(False) self.btnCSP.setEnabled(False) self.btnProprio.setEnabled(False) self.btnAdresse.setEnabled(False) self.CBool = True wrecord = self.cmbCommune.model().record( self.cmbCommune.currentIndex()) self.wcommune = wrecord.value(0) if self.cmbCommune.currentText() != "": if self.wcommune != "": self.wwhere += "bope_id in (select bope_id from data.droit_peche, data.parcelle, data.section where bope_id = par_bope_id and par_sec_id = sec_id and sec_com_insee = '" + str( self.wcommune) + "')" def ajoutComSection(self): '''Change l'état des boutons et ajoute un critère de commune et section à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnId.setEnabled(False) self.btnSign.setEnabled(False) self.btnFin.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnPossession.setEnabled(False) self.btnC.setEnabled(False) self.btnCS.setEnabled(False) self.btnCSP.setEnabled(False) self.btnProprio.setEnabled(False) self.btnAdresse.setEnabled(False) self.CSBool = True wrecord = self.cmbSection.model().record( self.cmbSection.currentIndex()) self.wsection = wrecord.value(0) if self.cmbSection.currentText() != "": if self.wsection != "": self.wwhere += "bope_id in (select bope_id from data.droit_peche, data.parcelle where par_bope_id = bope_id and par_sec_id = '" + str( self.wsection) + "')" def ajoutComSecParcelle(self): '''Change l'état des boutons et ajoute un critère de commune, section et parcelle à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnId.setEnabled(False) self.btnSign.setEnabled(False) self.btnFin.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnPossession.setEnabled(False) self.btnC.setEnabled(False) self.btnCS.setEnabled(False) self.btnCSP.setEnabled(False) self.btnProprio.setEnabled(False) self.btnAdresse.setEnabled(False) self.CSPBool = True wrecord = self.cmbParcelle.model().record( self.cmbParcelle.currentIndex()) self.wparcelle = wrecord.value(0) if self.cmbParcelle.currentText() != "": if self.wparcelle != "": self.wwhere += "bope_id in (select par_bope_id from data.parcelle where par_id = '" + str( self.wparcelle) + "')" def ajoutProprio(self): ''' Change l'état des boutons et ajoute un critère de nom de propriétaire et / ou de mail et / ou de téléphone à la requête ''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnId.setEnabled(False) self.btnSign.setEnabled(False) self.btnFin.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnPossession.setEnabled(False) self.btnC.setEnabled(False) self.btnCS.setEnabled(False) self.btnCSP.setEnabled(False) self.btnProprio.setEnabled(False) self.btnAdresse.setEnabled(False) self.wwhereProprio = "" self.wnom = self.leNom.text() if "'" in self.wnom and "''" not in self.wnom: self.wnom = self.wnom.replace("'", "''") self.wmail = self.leMail.text() self.wtel = self.leTel.text() if self.leNom.text() != "": if self.wnom != "": self.wwhereProprio += " bope_id in (select bope_id from data.droit_peche, data.proprietaire where bope_pro_id = pro_id and pro_nom ilike '%" + self.wnom + "%')" if self.leMail.text() != "": if self.wmail != "": if self.wnom != "": self.wwhereProprio += " and " self.wwhereProprio += " bope_id in (select bope_id from data.droit_peche, data.proprietaire where bope_pro_id = pro_id and pro_mail = '" + self.wmail + "')" if self.leTel.text() != "": if self.wtel != "": if self.wnom != "" or self.wmail != "": self.wwhereProprio += " and " self.wwhereProprio += " bope_id in (select bope_id from data.droit_peche, data.proprietaire where bope_pro_id = pro_id and pro_telephone = '" + str( self.wtel) + "')" if self.wwhereProprio != "": self.wwhere += self.wwhereProprio self.leNom.setText("") self.leMail.setText("") self.leTel.setText("") self.leNom.setFocus() def ajoutAdresse(self): '''Change l'état des boutons et ajoute un critère d'adresse à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnId.setEnabled(False) self.btnSign.setEnabled(False) self.btnFin.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnPossession.setEnabled(False) self.btnC.setEnabled(False) self.btnCS.setEnabled(False) self.btnCSP.setEnabled(False) self.btnProprio.setEnabled(False) self.btnAdresse.setEnabled(False) self.wadresse = self.leAdresse.text() if "'" in self.wadresse and "''" not in self.wadresse: self.wadresse = self.wadresse.replace("'", "''") if self.leAdresse.text() != "": if self.wadresse != "": self.wwhere += " bope_id in (select distinct bope_id from data.droit_peche, data.proprietaire where (bope_pro_id = pro_id ) and (pro_adresse ilike '%" + self.wadresse + "%'))" else: if self.leAdresse.text() == "": if self.wadresse == "": self.wwhere += " bope_id in (select distinct bope_id from data.droit_peche, data.proprietaire where (bope_pro_id = pro_id )" self.leAdresse.setText("") self.leAdresse.setFocus() def creaRequete(self): # def previSql(self): '''Regroupe les différentes variables contenant les clauses de la requête SQL et les concatène pour en faire une requête exécutable''' self.wrq = "" # Construit la clause FROM de la requête cfrom = "droit_peche" if self.dbType == "postgres": cfrom = self.dbSchema + "." + cfrom # Construit la clause SELECT et ajoute la clause FROM à la requête self.wrq = "SELECT DISTINCT bope_id FROM " + cfrom # Construit la clause WHERE et ORDER BY et l'ajoute à la requête if self.wwhere != "": #Supprime l'opérateur "and" ou "or" si celui-ci n'est pas suivi d'un critère operateurs = ["AND", "OR"] fin_where = self.wwhere[-5:] for ext in operateurs: if ext in fin_where: self.wwhere = self.wwhere[:-4] self.wrq += " WHERE " + self.wwhere + " ORDER BY bope_id" else: self.wrq += " ORDER BY bope_id" def previSql(self): '''Permet de prévisualiser la requête avant de l'éxecuter''' self.txtSql.setText("") self.creaRequete() # Affiche la requête self.txtSql.setText(self.wrq) def execution(self): '''Permet d'éxecuter la requête''' # Vérifie la non présence de mot pouvant endommager la base de données erreur = False interdit = [ "update", "delete", "insert", "intersect", "duplicate", "merge", "truncate", "create", "drop", "alter" ] if self.txtSql.toPlainText() != "": self.requete = self.txtSql.toPlainText() else: self.creaRequete() self.requete = self.wrq testRequete = self.requete.lower() for mot in interdit: if mot in testRequete: erreur = True if erreur == True: QMessageBox.critical( self, u"Erreur SQL", u"Vous essayez d'exécuter une requête qui peut endommager la base de données !", QMessageBox.Ok) # Après récupération du contenu de la zone de texte, exécute la requête else: query = QSqlQuery(self.db) query.prepare(self.requete) if query.exec_(): wparam = "" while query.next(): wparam += str(query.value(0)) + "," if (wparam != ""): wparam = "(" + wparam[0:len(wparam) - 1] + ")" if self.modelBauxPe: # Filtre le modèle des droits de pêche et ferme la fenêtre self.modelBauxPe.setFilter("bope_id in %s" % wparam) self.modelBauxPe.select() QDialog.accept(self) else: QMessageBox.information( self, "Filtrage", u"Aucun bail de pêche ne correspond aux critères ...", QMessageBox.Ok) else: QMessageBox.critical(self, u"Erreur SQL", query.lastError().text(), QMessageBox.Ok)
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)
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.setAlternatingRowColors(True) self.ui.tableView.verticalHeader().setDefaultSectionSize(22) self.ui.tableView.horizontalHeader().setDefaultSectionSize(60) 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.__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.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) self.ui.actRecInsert.setEnabled(True) self.ui.actRecDelete.setEnabled(True) self.ui.actRecEdit.setEnabled(True) self.ui.actScan.setEnabled(True) self.ui.actTestSQL.setEnabled(True) def __updateRecord(self, recNo): curRec = self.qryModel.record(recNo) empNo = curRec.value("EmpNo") query = QSqlQuery(self.DB) query.prepare("SELECT * FROM employee WHERE EmpNo = :ID") query.bindValue(":ID", empNo) query.exec() query.first() if (not query.isValid()): return curRec = query.record() dlgData = QmyDialogData(self) dlgData.setUpdateRecord(curRec) ret = dlgData.exec() if (ret != QDialog.Accepted): return recData = dlgData.getRecordData() query.prepare('''UPDATE employee SET Name=:Name, Gender=:Gender, Birthday=:Birthday, Province=:Province, Department=:Department, Salary=:Salary, Memo=:Memo, Photo=:Photo WHERE EmpNo = :ID''') query.bindValue(":Name", recData.value("Name")) query.bindValue(":Gender", recData.value("Gender")) query.bindValue(":Birthday", recData.value("Birthday")) query.bindValue(":Province", recData.value("Province")) query.bindValue(":Department", recData.value("Department")) query.bindValue(":Salary", recData.value("Salary")) query.bindValue(":Memo", recData.value("Memo")) query.bindValue(":Photo", recData.value("Photo")) query.bindValue(":ID", empNo) if (not query.exec()): QMessageBox.critical(self, "错误", "记录更新错误\n" + query.lastError().text()) else: self.qryModel.query().exec() @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_actRecInsert_triggered(self): query = QSqlQuery(self.DB) query.exec("select * from employee where EmpNo = -1") curRec = query.record() curRec.setValue("EmpNo", self.qryModel.rowCount() + 3000) dlgData = QmyDialogData(self) dlgData.setInsertRecord(curRec) ret = dlgData.exec() if (ret != QDialog.Accepted): return recData = dlgData.getRecordData() query.prepare('''INSERT INTO employee (EmpNo,Name,Gender,Birthday, Province,Department,Salary,Memo,Photo) VALUES(:EmpNo,:Name, :Gender,:Birthday,:Province, :Department,:Salary,:Memo,:Photo)''') query.bindValue(":EmpNo", recData.value("EmpNo")) query.bindValue(":Name", recData.value("Name")) query.bindValue(":Gender", recData.value("Gender")) query.bindValue(":Birthday", recData.value("Birthday")) query.bindValue(":Province", recData.value("Province")) query.bindValue(":Department", recData.value("Department")) query.bindValue(":Salary", recData.value("Salary")) query.bindValue(":Memo", recData.value("Memo")) query.bindValue(":Photo", recData.value("Photo")) res = query.exec() if (res == False): QMessageBox.critical(self, "错误", "插入记录错误\n" + query.lastError().text()) else: sqlStr = self.qryModel.query().executedQuery() self.qryModel.setQuery(sqlStr) @pyqtSlot() def on_actRecDelete_triggered(self): curRecNo = self.selModel.currentIndex().row() curRec = self.qryModel.record(curRecNo) if (curRec.isEmpty()): return empNo = curRec.value("EmpNo") query = QSqlQuery(self.DB) query.prepare("DELETE FROM employee WHERE EmpNo = :ID") query.bindValue(":ID", empNo) if (query.exec() == False): QMessageBox.critical(self, "错误", "删除记录出现错误\n" + query.lastError().text()) else: sqlStr = self.qryModel.query().executedQuery() self.qryModel.setQuery(sqlStr) @pyqtSlot() def on_actRecEdit_triggered(self): curRecNo = self.selModel.currentIndex().row() self.__updateRecord(curRecNo) def on_tableView_doubleClicked(self, index): curRecNo = index.row() self.__updateRecord(curRecNo) @pyqtSlot() def on_actScan_triggered(self): qryEmpList = QSqlQuery(self.DB) qryEmpList.exec("SELECT empNo,Salary FROM employee ORDER BY empNo") qryUpdate = QSqlQuery(self.DB) qryUpdate.prepare( '''UPDATE employee SET Salary=:Salary WHERE EmpNo = :ID''') qryEmpList.first() while (qryEmpList.isValid()): empID = qryEmpList.value("empNo") salary = qryEmpList.value("Salary") salary = salary + 500 qryUpdate.bindValue(":ID", empID) qryUpdate.bindValue(":Salary", salary) qryUpdate.exec() if not qryEmpList.next(): break self.qryModel.query().exec() QMessageBox.information(self, "提示", "涨工资计算完毕") @pyqtSlot() def on_actTestSQL_triggered(self): query = QSqlQuery(self.DB) query.exec('''UPDATE employee SET Salary=500+Salary''') sqlStr = self.qryModel.query().executedQuery() self.qryModel.setQuery(sqlStr) print("SQL OK") def do_currentRowChanged(self, current, previous): if (current.isValid() == False): return curRec = self.qryModel.record(current.row()) empNo = curRec.value("EmpNo") self.ui.statusBar.showMessage("当前记录:工号 = %d" % empNo)
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.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.__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.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) self.ui.actRecInsert.setEnabled(True) self.ui.actRecDelete.setEnabled(True) self.ui.actRecEdit.setEnabled(True) self.ui.actScan.setEnabled(True) self.ui.actTestSQL.setEnabled(True) def __updateRecord(self, recNo): ##更新一条记录 curRec = self.qryModel.record(recNo) #获取当前记录 empNo = curRec.value("EmpNo") #获取EmpNo query = QSqlQuery(self.DB) #查询出当前记录的所有字段 query.prepare("SELECT * FROM employee WHERE EmpNo = :ID") query.bindValue(":ID", empNo) query.exec() # query.first() if (not query.isValid()): #是否为有效记录 return curRec = query.record() #获取当前记录的数据,QSqlRecord类型 dlgData = QmyDialogData(self) #创建对话框 dlgData.setUpdateRecord(curRec) #调用对话框函数更新数据和界面 ret = dlgData.exec() # 以模态方式显示对话框 if (ret != QDialog.Accepted): return recData = dlgData.getRecordData() #获得对话框返回的记录 query.prepare('''UPDATE employee SET Name=:Name, Gender=:Gender, Birthday=:Birthday, Province=:Province, Department=:Department, Salary=:Salary, Memo=:Memo, Photo=:Photo WHERE EmpNo = :ID''') query.bindValue(":Name", recData.value("Name")) query.bindValue(":Gender", recData.value("Gender")) query.bindValue(":Birthday", recData.value("Birthday")) query.bindValue(":Province", recData.value("Province")) query.bindValue(":Department", recData.value("Department")) query.bindValue(":Salary", recData.value("Salary")) query.bindValue(":Memo", recData.value("Memo")) query.bindValue(":Photo", recData.value("Photo")) query.bindValue(":ID", empNo) ## if (not query.exec_()): if (not query.exec()): #PyQt 5.11.2以前应该使用exec_()函数 QMessageBox.critical(self, "错误", "记录更新错误\n" + query.lastError().text()) else: self.qryModel.query().exec() #数据模型重新查询数据,更新tableView显示 ## ==========由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_actRecInsert_triggered(self): query = QSqlQuery(self.DB) query.exec("select * from employee where EmpNo =-1") #实际不查询出记录,只查询字段信息 curRec = query.record() #获取当前记录,实际为空记录,但有字段信息 curRec.setValue("EmpNo", self.qryModel.rowCount() + 3000) dlgData = QmyDialogData(self) dlgData.setInsertRecord(curRec) #插入记录 ret = dlgData.exec() #以模态方式显示对话框 if (ret != QDialog.Accepted): return recData = dlgData.getRecordData() query.prepare('''INSERT INTO employee (EmpNo,Name,Gender,Birthday, Province,Department,Salary,Memo,Photo) VALUES(:EmpNo,:Name, :Gender,:Birthday,:Province, :Department,:Salary,:Memo,:Photo)''') query.bindValue(":EmpNo", recData.value("EmpNo")) query.bindValue(":Name", recData.value("Name")) query.bindValue(":Gender", recData.value("Gender")) query.bindValue(":Birthday", recData.value("Birthday")) query.bindValue(":Province", recData.value("Province")) query.bindValue(":Department", recData.value("Department")) query.bindValue(":Salary", recData.value("Salary")) query.bindValue(":Memo", recData.value("Memo")) query.bindValue(":Photo", recData.value("Photo")) res = query.exec() #执行SQL语句 if (res == False): QMessageBox.critical(self, "错误", "插入记录错误\n" + query.lastError().text()) else: #插入,删除记录后需要重新设置SQL语句查询 sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句 self.qryModel.setQuery(sqlStr) #reset 重新查询数据 @pyqtSlot() ##删除记录 def on_actRecDelete_triggered(self): curRecNo = self.selModel.currentIndex().row() curRec = self.qryModel.record(curRecNo) #获取当前记录 if (curRec.isEmpty()): #当前为空记录 return empNo = curRec.value("EmpNo") #获取员工编号 query = QSqlQuery(self.DB) query.prepare("DELETE FROM employee WHERE EmpNo = :ID") query.bindValue(":ID", empNo) if (query.exec() == False): QMessageBox.critical(self, "错误", "删除记录出现错误\n" + query.lastError().text()) else: #插入,删除记录后需要重新设置SQL语句查询 sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句 self.qryModel.setQuery(sqlStr) #reset 重新查询数据 @pyqtSlot() ##编辑记录 def on_actRecEdit_triggered(self): curRecNo = self.selModel.currentIndex().row() self.__updateRecord(curRecNo) ## @pyqtSlot() ##双击编辑记录 def on_tableView_doubleClicked(self, index): curRecNo = index.row() self.__updateRecord(curRecNo) @pyqtSlot() ##遍历记录,涨工资 def on_actScan_triggered(self): qryEmpList = QSqlQuery(self.DB) #员工工资信息列表 qryEmpList.exec("SELECT empNo,Salary FROM employee ORDER BY empNo") qryUpdate = QSqlQuery(self.DB) #临时 QSqlQuery qryUpdate.prepare( '''UPDATE employee SET Salary=:Salary WHERE EmpNo = :ID''') qryEmpList.first() while (qryEmpList.isValid()): #当前记录有效 empID = qryEmpList.value("empNo") #获取empNo salary = qryEmpList.value("Salary") #获取Salary salary = salary + 500 #涨工资 qryUpdate.bindValue(":ID", empID) qryUpdate.bindValue(":Salary", salary) #设置SQL语句参数 qryUpdate.exec() #执行update语句 if not qryEmpList.next(): #移动到下一条记录,并判断是否到末尾了 break self.qryModel.query().exec() #数据模型重新查询数据,更新tableView的显示 QMessageBox.information(self, "提示", "涨工资计算完毕") @pyqtSlot() ##SQL语句测试 def on_actTestSQL_triggered(self): query = QSqlQuery(self.DB) ## # SQL语句测试1, exec_() 和exec()都可以直接执行不带参数的SQL语句 ## query.exec('''UPDATE employee SET Salary=3000 where Gender="女" ''') ## query.exec_('''UPDATE employee SET Salary=4500 where Gender="女" ''') # SQL语句测试2,执行带参数的SQL语句,只能用 exec_(),不能用exec() ## query.prepare('''UPDATE employee SET Salary=9000 where Gender=:Gender ''') ## query.bindValue(":Gender","男") ## query.exec() query.exec('''UPDATE employee SET Salary=500+Salary ''') ## query.bindValue(":Gender","男") ## query.exec() ## query.prepare("UPDATE employee SET Department=?, Salary=? where Name=?") ## query.bindValue(0, "技术部") ## query.bindValue(1, 5500) ## query.bindValue(2, "张三") ## query.exec_() #只能用exec_(),而不能用exec()函数 ## self.qryModel.query().exec() #不增减记录时更新显示 ## 增减记录后的更新显示 sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句 self.qryModel.setQuery(sqlStr) #reset 重新查询数据 print("SQL OK") ## =============自定义槽函数=============================== def do_currentRowChanged(self, current, previous): ##行切换时触发 if (current.isValid() == False): return curRec = self.qryModel.record(current.row()) #获取当前记录,QSqlRecord类型 empNo = curRec.value("EmpNo") #不需要加 toInt()函数 self.ui.statusBar.showMessage("当前记录:工号=%d" % empNo)
class Filtrage_peche_dialog(QDialog, Ui_dlgPecheRechercheForm): ''' Class de la fenêtre permettant le filtrage attributaire des inventaires de reproduction :param QDialog: Permet d'afficher l'interface graphique comme une fenêtre indépendante :type QDialog: QDialog :param Ui_dlgPecheRechercheForm: Class du script de l'interface graphique du formulaire, apporte les éléments de l'interface :type Ui_dlgPecheRechercheForm: class ''' def __init__(self, db, dbType, dbSchema, modelPeche, parent=None): ''' Constructeur, récupération de variable, connection des événements et remplissage des combobox :param db: définie dans le setupModel(), représente la connexion avec la base de données :type db: QSqlDatabase :param dbType: type de la base de données (postgre) :type dbType: str :param dbSchema: nom du schéma sous PostgreSQL contenant les données (data) :type dbSchema: unicode :param modelPeche: modèle pêche qui contient les données de la base de données :type modelPeche: QSqlRelationalTableModel :param parent: défini que cette fenêtre n'hérite pas d'autres widgets :type parent: NoneType ''' super(Filtrage_peche_dialog, self).__init__(parent) self.db = db self.dbType = dbType self.dbSchema = dbSchema self.modelPeche = modelPeche self.setupUi(self) self.btnAnnuler.clicked.connect(self.reject) self.btnExec.clicked.connect(self.execution) self.btnRaz.clicked.connect(self.raz) self.btnEt.clicked.connect(self.et) self.btnOu.clicked.connect(self.ou) self.btnPrevisualiser.clicked.connect(self.previSql) self.btnCode.clicked.connect(self.ajoutCode) self.btnId.clicked.connect(self.ajoutId) self.btnPdpg.clicked.connect(self.ajoutPdpg) self.btnDate.clicked.connect(self.ajoutDate) self.btnRiviere.clicked.connect(self.ajoutRiviere) self.btnAappma.clicked.connect(self.ajoutAappma) self.btnMeau.clicked.connect(self.ajoutMeau) self.btnMotif.clicked.connect(self.ajoutMotif) self.btnEt.setEnabled(False) self.btnOu.setEnabled(False) self.aappmaBool = False self.motifBool = False self.pdpgBool = False self.ceauBool = False self.meauBool = False self.anneeBool = False self.wwhere = "" self.modelPdpg = QSqlTableModel(self, self.db) wrelation = "contexte_pdpg" if self.dbType == "postgres": wrelation = self.dbSchema + "." + wrelation self.modelPdpg.setTable(wrelation) self.modelPdpg.setSort(2, Qt.AscendingOrder) if (not self.modelPdpg.select()): QMessageBox.critical(self, u"Remplissage du modèle PDPG", self.modelPdpg.lastError().text(), QMessageBox.Ok) self.cmbPdpg.setModel(self.modelPdpg) self.cmbPdpg.setModelColumn(self.modelPdpg.fieldIndex("pdpg_nom")) self.modelAappma = QSqlTableModel(self, self.db) wrelation = "aappma" if self.dbType == "postgres": wrelation = self.dbSchema + "." + wrelation self.modelAappma.setTable(wrelation) self.modelAappma.setSort(1, Qt.AscendingOrder) if (not self.modelAappma.select()): QMessageBox.critical(self, u"Remplissage du modèle AAPPMA", self.modelAappma.lastError().text(), QMessageBox.Ok) self.cmbAappma.setModel(self.modelAappma) self.cmbAappma.setModelColumn(self.modelAappma.fieldIndex("apma_nom")) self.modelRiviere = QSqlTableModel(self, self.db) wrelation = "cours_eau" if self.dbType == "postgres": wrelation = self.dbSchema + "." + wrelation self.modelRiviere.setTable(wrelation) self.modelRiviere.setFilter("ceau_nom <> 'NR'") self.modelRiviere.setSort(2, Qt.AscendingOrder) if (not self.modelRiviere.select()): QMessageBox.critical(self, u"Remplissage du modèle Rivière", self.modelRiviere.lastError().text(), QMessageBox.Ok) self.cmbRiviere.setModel(self.modelRiviere) self.cmbRiviere.setModelColumn( self.modelRiviere.fieldIndex("ceau_nom")) self.modelMeau = QSqlQueryModel(self) wrelation = "masse_eau" if self.dbType == "postgres": wrelation = self.dbSchema + "." + wrelation self.modelMeau.setQuery( "select meau_code, meau_code || ' ; ' || meau_nom from " + wrelation + " order by meau_code;", self.db) if self.modelMeau.lastError().isValid(): QMessageBox.critical(self, u"Remplissage du modèle Masse d'eau", self.modelMeau.lastError().text(), QMessageBox.Ok) self.cmbMeau.setModel(self.modelMeau) self.cmbMeau.setModelColumn(1) self.ModelMotif = QSqlTableModel(self, self.db) wrelation = "motif_peche" if self.dbType == "postgres": wrelation = self.dbSchema + "." + wrelation self.ModelMotif.setTable(wrelation) self.ModelMotif.setSort(1, Qt.AscendingOrder) if (not self.ModelMotif.select()): QMessageBox.critical(self, u"Remplissage du modèle Motif", self.ModelMotif.lastError().text(), QMessageBox.Ok) self.cmbMotif.setModel(self.ModelMotif) self.cmbMotif.setModelColumn(self.ModelMotif.fieldIndex("mope_motif")) def reject(self): '''Ferme la fenêtre si clic sur le bouton annuler''' QDialog.reject(self) def raz(self): '''Réinitialise toutes les variables de la fenêtre afin de recommencer une nouvelle requête''' self.spnId.setValue(0) self.wrq = "" self.txtSql.setText("") self.wwhere = "" self.datePeche.setDate(QDate(2000, 1, 1)) self.btnEt.setEnabled(False) self.btnOu.setEnabled(False) self.btnCode.setEnabled(True) self.btnId.setEnabled(True) self.btnPdpg.setEnabled(True) self.btnDate.setEnabled(True) self.btnRiviere.setEnabled(True) self.btnAappma.setEnabled(True) self.btnMeau.setEnabled(True) self.btnMotif.setEnabled(True) self.aappmaBool = False self.motifBool = False self.pdpgBool = False self.ceauBool = False self.meauBool = False self.anneeBool = False def et(self): '''Change l'état des boutons et ajoute "and" à la requête''' self.btnEt.setEnabled(False) self.btnOu.setEnabled(False) self.btnCode.setEnabled(True) self.btnId.setEnabled(True) if self.aappmaBool == False: self.btnAappma.setEnabled(True) if self.motifBool == False: self.btnMotif.setEnabled(True) if self.pdpgBool == False: self.btnPdpg.setEnabled(True) if self.ceauBool == False: self.btnRiviere.setEnabled(True) if self.meauBool == False: self.btnMeau.setEnabled(True) if self.anneeBool == False: self.btnDate.setEnabled(True) self.wwhere += " AND " def ou(self): '''Change l'état des boutons et ajoute "or" à la requête''' self.btnEt.setEnabled(False) self.btnOu.setEnabled(False) self.btnCode.setEnabled(True) self.btnId.setEnabled(True) self.btnPdpg.setEnabled(True) self.btnDate.setEnabled(True) self.btnRiviere.setEnabled(True) self.btnAappma.setEnabled(True) self.btnMeau.setEnabled(True) self.btnMotif.setEnabled(True) self.aappmaBool = False self.motifBool = False self.pdpgBool = False self.ceauBool = False self.meauBool = False self.anneeBool = False self.wwhere += " OR " def ajoutCode(self): '''Change l'état des boutons et ajoute un critère de code opération à la requête''' self.btnOu.setEnabled(True) self.btnCode.setEnabled(False) self.btnId.setEnabled(False) self.btnPdpg.setEnabled(False) self.btnDate.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnMeau.setEnabled(False) self.btnMotif.setEnabled(False) self.wcode = self.leCodeOpe.text() if self.leCodeOpe.text() != "": if self.wcode != "": self.wwhere += "opep_ope_code ilike '%" + self.wcode + "%'" self.leCodeOpe.setText("") self.leCodeOpe.setFocus() def ajoutId(self): '''Change l'état des boutons et ajoute un critère d'id à la requête''' self.btnOu.setEnabled(True) self.btnCode.setEnabled(False) self.btnId.setEnabled(False) self.btnPdpg.setEnabled(False) self.btnDate.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnMeau.setEnabled(False) self.btnMotif.setEnabled(False) self.wid = self.spnId.value() if self.spnId.value() != "": if self.wid != "": self.wwhere += "opep_id = '" + str(self.wid) + "'" self.spnId.setValue(0) self.spnId.setFocus() def ajoutDate(self): '''Change l'état des boutons et ajoute un critère de date à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.anneeBool = True self.btnCode.setEnabled(False) self.btnId.setEnabled(False) self.btnPdpg.setEnabled(False) self.btnDate.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnMeau.setEnabled(False) self.btnMotif.setEnabled(False) self.wopep_date = self.datePeche.date().toString("yyyy") if self.wopep_date != "": self.wwhere += "date_part('year', opep_date) = '" + self.wopep_date + "'" def ajoutPdpg(self): '''Change l'état des boutons et ajoute un critère de pdpg à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnCode.setEnabled(False) self.btnId.setEnabled(False) self.btnPdpg.setEnabled(False) self.btnDate.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnMeau.setEnabled(False) self.btnMotif.setEnabled(False) self.pdpgBool = True wfromOperation = "operation" wfromStation = "station" wfromPeche = "ope_peche_elec" if self.dbType == "postgres": self.wfromPdpg = self.dbSchema + "." + wfromPeche + ", " + self.dbSchema + "." + wfromOperation + ", " + self.dbSchema + "." + wfromStation wrecord = self.cmbPdpg.model().record(self.cmbPdpg.currentIndex()) self.wsta_pdpg = wrecord.value(0) if self.cmbPdpg.currentText() != "": if self.wsta_pdpg != "": self.wwhere += " opep_id in (select distinct opep_id from " + self.wfromPdpg + " where (opep_ope_code = ope_code) and (ope_sta_id = sta_id) and sta_pdpg_id = '" + str( self.wsta_pdpg) + "')" def ajoutRiviere(self): '''Change l'état des boutons et ajoute un critère de cours d'eau à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnCode.setEnabled(False) self.btnId.setEnabled(False) self.btnPdpg.setEnabled(False) self.btnDate.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnMeau.setEnabled(False) self.btnMotif.setEnabled(False) self.ceauBool = True wfromOperation = "operation" wfromStation = "station" wfromPeche = "ope_peche_elec" if self.dbType == "postgres": self.wfromCeau = self.dbSchema + "." + wfromPeche + ", " + self.dbSchema + "." + wfromOperation + ", " + self.dbSchema + "." + wfromStation wrecord = self.cmbRiviere.model().record( self.cmbRiviere.currentIndex()) self.wsta_riviere = wrecord.value(0) if self.cmbRiviere.currentText() != "": if self.wsta_riviere != "": self.wwhere += " opep_id in (select distinct opep_id from " + self.wfromCeau + " where (opep_ope_code = ope_code) and (ope_sta_id = sta_id) and sta_ceau_id = '" + str( self.wsta_riviere) + "')" def ajoutAappma(self): '''Change l'état des boutons et ajoute un critère d'AAPPMA à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnCode.setEnabled(False) self.btnId.setEnabled(False) self.btnPdpg.setEnabled(False) self.btnDate.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnMeau.setEnabled(False) self.btnMotif.setEnabled(False) self.aappmaBool = True wfromOperation = "operation" wfromStation = "station" wfromPeche = "ope_peche_elec" if self.dbType == "postgres": self.wfromAappma = self.dbSchema + "." + wfromPeche + ", " + self.dbSchema + "." + wfromOperation + ", " + self.dbSchema + "." + wfromStation wrecord = self.cmbAappma.model().record(self.cmbAappma.currentIndex()) self.wsta_aappma = wrecord.value(0) if self.cmbAappma.currentText() != "": if self.wsta_aappma != "": self.wwhere += " opep_id in (select distinct opep_id from " + self.wfromAappma + " where (opep_ope_code = ope_code) and (ope_sta_id = sta_id) and sta_apma_id = '" + str( self.wsta_aappma) + "')" def ajoutMeau(self): '''Change l'état des boutons et ajoute un critère de Masse d'eau à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnCode.setEnabled(False) self.btnId.setEnabled(False) self.btnPdpg.setEnabled(False) self.btnDate.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnMeau.setEnabled(False) self.btnMotif.setEnabled(False) self.meauBool = True wfromOperation = "operation" wfromStation = "station" wfromPeche = "ope_peche_elec" if self.dbType == "postgres": self.wfromMeau = self.dbSchema + "." + wfromPeche + ", " + self.dbSchema + "." + wfromOperation + ", " + self.dbSchema + "." + wfromStation wrecord = self.cmbMeau.model().record(self.cmbMeau.currentIndex()) self.wsta_meau = wrecord.value(0) if self.cmbMeau.currentText() != "": if self.wsta_meau != "": self.wwhere += " opep_id in (select distinct opep_id from " + self.wfromMeau + " where (opep_ope_code = ope_code) and (ope_sta_id = sta_id) and sta_meau_code = '" + str( self.wsta_meau) + "')" def ajoutMotif(self): '''Change l'état des boutons et ajoute un critère de motif de pêche à la requête''' self.btnEt.setEnabled(True) self.btnOu.setEnabled(True) self.btnCode.setEnabled(False) self.btnId.setEnabled(False) self.btnPdpg.setEnabled(False) self.btnDate.setEnabled(False) self.btnRiviere.setEnabled(False) self.btnAappma.setEnabled(False) self.btnMeau.setEnabled(False) self.btnMotif.setEnabled(False) self.motifBool = True wrecord = self.cmbMotif.model().record(self.cmbMotif.currentIndex()) self.wopep_motif = wrecord.value(0) if self.cmbMotif.currentText() != "": if self.wopep_motif != "": self.wwhere += "opep_mope_id = '" + str(self.wopep_motif) + "'" def creaRequete(self): # def previSql(self): '''Regroupe les différentes variables contenant les clauses de la requête SQL et les concatène pour en faire une requête exécutable''' self.wrq = "" # Construit la clause FROM de la requête cfrom = "ope_peche_elec" if self.dbType == "postgres": cfrom = self.dbSchema + "." + cfrom # Construit la clause SELECT et ajoute la clause FROM à la requête self.wrq = "SELECT DISTINCT opep_id FROM " + cfrom # Construit la clause WHERE et ORDER BY et l'ajoute à la requête if self.wwhere != "": #Supprime l'opérateur "and" ou "or" si celui-ci n'est pas suivi d'un critère operateurs = ["AND", "OR"] fin_where = self.wwhere[-5:] for ext in operateurs: if ext in fin_where: self.wwhere = self.wwhere[:-4] self.wrq += " WHERE " + self.wwhere + " ORDER BY opep_id" else: self.wrq += " ORDER BY opep_id" def previSql(self): '''Permet de prévisualiser la requête avant de l'éxecuter''' self.txtSql.setText("") self.creaRequete() # Affiche la requête self.txtSql.setText(self.wrq) def execution(self): '''Permet d'éxecuter la requête''' # Vérifie la non présence de mot pouvant endommager la base de données erreur = False interdit = [ "update", "delete", "insert", "intersect", "duplicate", "merge", "truncate", "create", "drop", "alter" ] if self.txtSql.toPlainText() != "": self.requete = self.txtSql.toPlainText() else: self.creaRequete() self.requete = self.wrq testRequete = self.requete.lower() for mot in interdit: if mot in testRequete: erreur = True if erreur == True: QMessageBox.critical( self, u"Erreur SQL", u"Vous essayez d'exécuter une requête qui peut endommager la base de données !", QMessageBox.Ok) # Après récupération du contenu de la zone de texte, exécute la requête else: query = QSqlQuery(self.db) query.prepare(self.requete) if query.exec_(): wparam = "" while query.next(): wparam += str(query.value(0)) + "," if (wparam != ""): wparam = "(" + wparam[0:len(wparam) - 1] + ")" if self.modelPeche: # Filtre le modèle des inventaires de reproduction et ferme la fenêtre self.modelPeche.setFilter("opep_id in %s" % wparam) self.modelPeche.select() QDialog.accept(self) else: QMessageBox.information( self, "Filtrage", u"Aucun pêche électrique ne correspond aux critères ...", QMessageBox.Ok) else: QMessageBox.critical(self, u"Erreur SQL", query.lastError().text(), QMessageBox.Ok)
class OrderController(QObject): def __init__(self, form, orderTable, addButton, editButton, deleteButton, dbase, mainwindow): super().__init__() self.form = form self.orderTable = orderTable self.addButton = addButton self.editButton = editButton self.deleteButton = deleteButton self.dbase = dbase self.mainwindow = mainwindow form.currentRecordChanged.connect(self.recordChanged) form.recordInserted.connect(lambda: self.recordChanged(None)) form.recordDeleted.connect(lambda: self.recordChanged(None)) if form.only_select: self.addButton.setEnabled(False) self.editButton.setEnabled(False) self.deleteButton.setEnabled(False) self.addButton.clicked.connect(self.addButtonClicked) self.editButton.clicked.connect(self.editButtonClicked) self.deleteButton.clicked.connect(self.deleteButtonClicked) def recordChanged(self, record): #print("record changed", record) if record is None: for button in (self.addButton, self.editButton, self.deleteButton): button.setEnabled(False) self.orderTable.setModel(None) self._hiddingHack(True) else: if not self.form.only_select: self.addButton.setEnabled(True) self.detailModel = QSqlQueryModel() query = "SELECT detail.id as id, detail.article as article, detail.name as name, order_detail.quantity as qnt, \ detail.price as sole_price, detail.price*order_detail.quantity as total_price\ FROM order_detail INNER JOIN detail \ ON order_detail.detail_id = detail.id \ WHERE order_detail.order_id={} ORDER BY article".format(record.value("id")) self.detailModel.setQuery(query) self.detailModel.setHeaderData(1, Qt.Horizontal, "Артикул") self.detailModel.setHeaderData(2, Qt.Horizontal, "Наименование") self.detailModel.setHeaderData(3, Qt.Horizontal, "Количество") self.detailModel.setHeaderData(4, Qt.Horizontal, "Цена за штуку") self.detailModel.setHeaderData(5, Qt.Horizontal, "Суммарная цена") self.orderTable.setModel(self.detailModel) self.orderTable.hideColumn(0) self.orderTable.resizeColumnsToContents() self.orderTable.selectionModel().currentChanged.connect(self.tableSelectionChanged) if not self.detailModel.query().isActive(): print(self.detailModel.lastError().text()) self.deleteButton.setEnabled(False) self.editButton.setEnabled(False) self._hiddingHack(False) def _hiddingHack(self, val): ui = self.mainwindow.ui if val is True: ui.client_hack.setCurrentWidget(ui.client_hide_page) ui.shop_hack.setCurrentWidget(ui.shop_hide_page) ui.emp_hack.setCurrentWidget(ui.emp_hide_page) else: ui.client_hack.setCurrentWidget(ui.client_ok_page) ui.shop_hack.setCurrentWidget(ui.shop_ok_page) ui.emp_hack.setCurrentWidget(ui.emp_ok_page) def tableSelectionChanged(self, cur, prev): if self.form.only_select: return if cur.isValid(): self.deleteButton.setEnabled(True) self.editButton.setEnabled(True) else: self.deleteButton.setEnabled(False) self.editButton.setEnabled(False) self.addButton.setEnabled(True) def addButtonClicked(self): order = self.form.currentRecord() query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \ FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM order_detail \ WHERE order_id={}))".format(order.value("id"))) details = {} while query.next(): details[query.value("name")] = query.value("id") if not details: return QMessageBox.warning(None, "Ошибка добавления", "Не удалось добавить новый товар к заказу: все возможные товары уже добавлены.") choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:", list(details.keys()), 0, False) if not ok: return qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", 1, 1) if not ok: return detail_id = details[choice] order_id = order.value("id") query = QSqlQuery("INSERT INTO order_detail (order_id, detail_id, quantity) \ VALUES ({}, {}, {})".format(order_id, detail_id, qnt)) if not query.isActive(): print(query.lastError().text()) self.form.update() def editButtonClicked(self): detail = self.detailModel.record(self.orderTable.currentIndex().row()) qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", detail.value("qnt"), 0) if not ok: return order = self.form.currentRecord() if qnt > 0: query = QSqlQuery("UPDATE order_detail SET quantity={} \ WHERE order_id={} AND detail_id={}".format(qnt, order.value("id"), detail.value("id"))) else: query = QSqlQuery("DELETE FROM order_detail WHERE \ order_id={} AND detail_id={} LIMIT 1".format( order.value("id"), detail.value("id"))) query.exec_() if not query.isActive(): print(query.lastError().text()) self.form.update() def deleteButtonClicked(self): if not self.orderTable.currentIndex().isValid(): return detail = self.detailModel.record(self.orderTable.currentIndex().row()) order = self.form.currentRecord() query = QSqlQuery("DELETE FROM order_detail WHERE \ order_id={} AND detail_id={} LIMIT 1".format( order.value("id"), detail.value("id"))) query.exec_() if not query.isActive(): print(query.lastError().text()) self.form.update() def selectRow(self, row): self.form.selectRow(row) def update(self): self.form.update()