def delete_failure(self, talk_id): """Removes a Presentation from the database""" QtSql.QSqlQuery('''DELETE FROM failures WHERE Id="%s"''' % talk_id) log.info("Failure %s deleted." % talk_id)
def Insertar(self): print("entro a insertar") # db = self.db.CreateConnection(self.usr, self.passw) # Obtengo un cursor de DB abierto print("Nombre de la conexión " + self.db.connectionName()) #Obtengo listado de campos de la tabla campos = "SHOW COLUMNS from alumnos WHERE Field <> 'IdAlumnos' AND Field <> 'Apellido' AND Field <> 'cohorte' AND Field <> 'Grupo_Sanguineo' AND Field <> 'Ántitetanica' AND Field <> 'Presion_Arterial' AND Field <> 'Enfermedades' AND Field <> 'Tratamiento' AND Field <> 'alergias' AND Field <> 'foto'" print("Nombre de la conexión " + self.db.connectionName()) if self.db.database('inscripciones').isOpen(): q = QtSql.QSqlQuery(self.db.database('inscripciones')) q.prepare(campos) q.exec_() cadena = "" print("entro al while") while q.next(): cadena = cadena + q.value(0) + ", " total = len(cadena.rstrip()) cadena1 = cadena[0:total -1] print("Cadena " + cadena1) #Preparo la cadena sql values = ":nombre, :dni, :lugar, :fenac, :edad, :calle, :numero, :piso, :depto, :civil, :local, :partido, :cp, :tel, :mov, :ecurs, :otros, :trab, :activ, :emer, :os, :sexo, :carrera, :anio, :lunac, :hijos, :flia, :mail, :egreso, :insti, :escuela, :distri, :doc_dni, :doc_Tit, :doc_reg, :doc_fot, :doc_cert" #sql = "INSERT INTO alumnos (" + cadena1 + ") VALUES (:nombre, :dni, :lugar, :fenac, :edad, :calle, :numero, :piso, :depto, :civil, :local, :partido, :cp, :tel, :mov, :ecurs, :otros, :trab, :activ, :emer, :os, :sexo, :carrera, :anio, :lunac, :hijos, :flia, :mail, :egreso, :insti1, :insti2, :escuela, :distri, :doc_dni, :doc_Tit, :doc_reg, :doc_fot, :doc_cert)" sql = "INSERT INTO alumnos(" + cadena1 + ") VALUES (" + values + ")" print(" Este sql " + sql) # sql = "INSERT INTO alumnos(Nombre, DNI, Lugar_Nacimiento, Fecha_Nacimiento, Edad, Domicilio, numero, piso, depto, Estado_Civil, Localidad, Partido, CP, Telefono, Celular, Estudios_Cursados, Otros, Trabaja, Ocupacion, emergencias, osocial, Sexo, Carrera, ciclo, Nacionalidad, hijos, acargo, mail, egreso, insti_otros, escuela, distrito, doc_dni, doc_Tit, doc_Reg, doc_fot, doc_cert) VALUES (:nombre, :dni, :lugar, :fenac, :edad, :calle, :numero, :piso, :depto, :civil, :local, :partido, :cp, :tel, :mov, :ecurs, :otros, :trab, :activ, :emer, :os, :sexo, :carrera, :anio, :lunac, :hijos, :flia, :mail, :egreso, :insti, :escuela, :distri, :doc_dni, :doc_Tit, :doc_reg, :doc_fot, :doc_cert)" # , Fecha_Nacimiento, Edad, Domicilio, numero, piso, depto, Estado_Civil, Localidad, Partido, CP, Telefono, Celular, Estudios_Cursados, Otros, Trabaja, Ocupacion, emergencias, osocial, Sexo, Carrera, ciclo, Nacionalidad, hijos, acargo, mail, egreso, insti_otros, escuela, distrito, doc_dni, doc_Tit, doc_Reg, doc_fot, doc_cert) VALUES (:nombre,:dni,:lugar,:fenac,:edad,:calle,:numero,:piso,:depto,:civil,:local,:partido,:cp,:tel,:mov,:ecurs,:otros,:trab,:activ,:emer,:os,:sexo,:carrera,:anio,:lunac,:hijos,:flia,:mail,:egreso,:insti,:escuela :distri :doc_dni :doc_Tit :doc_reg :doc_fot)"''' #Creo el objeto para hacer la consulta print("Nombre de la conexión " + self.db.connectionName()) q = QtSql.QSqlQuery(self.db.database('inscripciones')) q.prepare(sql) q.bindValue(":nombre", self.Nombre) q.bindValue(":dni", self.dni) q.bindValue(":lugar", self.lnNa) q.bindValue(":fenac", self.fenac) q.bindValue(":edad", self.edad) q.bindValue(":calle", self.calle) q.bindValue(":numero", self.numero) q.bindValue(":piso", self.piso) q.bindValue(":depto", self.depto) q.bindValue(":civil", self.civil) q.bindValue(":local", self.local) q.bindValue(":partido", self.partido) q.bindValue(":cp", self.cp) q.bindValue(":tel", self.tel) # Validador q.bindValue(":mov", self.movil) # validador q.bindValue(":ecurs", self.titulo) q.bindValue(":otros", self.otros) q.bindValue(":trab", self.trab) q.bindValue(":activ", self.activ) q.bindValue(":emer", self.emergencia) q.bindValue(":os", self.os) q.bindValue(":sexo", self.sexo) q.bindValue(":carrera", self.carrera) q.bindValue(":anio", self.anio) q.bindValue(":lunac", self.lunac) q.bindValue(":hijos", self.hijos) q.bindValue(":flia", self.familia) q.bindValue(":mail", self.mail) q.bindValue(":egreso", self.egreso) institutos = self.insti1 + ", " + self.insti2 q.bindValue(":insti", institutos) q.bindValue(":escuela", self.escuela) q.bindValue(":distri", self.distrito) print(self.fotoDni) q.bindValue(":doc_dni", self.fotoDni) q.bindValue(":doc_Tit", self.fotoTit) q.bindValue(":doc_reg", self.numreg) q.bindValue(":doc_fot", self.fotos) q.bindValue(":doc_cert", self.certif) '''q.bindValue(":hora", self.horario)''' if self.db.database('inscripciones').isOpen(): print("Damned world") else: print(self.db.connectionName() + " esta abierta") q.prepare(sql) estado = q.exec_() pipi = q.executedQuery() if estado is True: print("tudobom") self.close() else: print("DNI " + self.dni) print(pipi) print((self.db.database('inscripciones').lastError()))
def _slotPushButtonInitDBClicked(self): self.firstDBConnection() DBName = self.ui.comboBoxDB.currentText() s="SELECT count(*) from pg_database\ WHERE datname = 'tempusaccess_"+DBName+"'"; q=QtSql.QSqlQuery(unicode(s), self.caller.db) q.next() if (int(q.value(0))>0): ret = QMessageBox.question(self, "TempusAccess", u"La base de données 'tempusaccess_"+DBName+u"' existe déjà et va être réinitialisée : toutes les données présentes seront écrasées. \n Confirmez-vous cette opération ?", QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Cancel) else: ret = QMessageBox.Ok if (ret == QMessageBox.Ok): self.updateDBConnection() self.prog = QProgressDialog(self) self.prog.setCancelButton(None) self.prog.setMinimum(0) self.prog.setMaximum(100) self.prog.setAutoClose(True) self.prog.setWindowTitle(u"En cours...") self.prog.show() self.prog.setValue(5) # Restart database server to be sure deleting "TempusAccess" database will be allowed (avoids still connected applications) cmd = [ "python", "-m", "pglite", "stop" ] r = subprocess.call( cmd, shell=True ) cmd = [ "python", "-m", "pglite", "start" ] r = subprocess.call( cmd, shell=True ) self.prog.setValue(15) # Delete database (if exists) and (re)create cmd = [ "dropdb", "-h", self.caller.host, "-p", self.caller.port, self.caller.base ] r = subprocess.call( cmd, shell=True ) cmd = [ "createdb", "-h", self.caller.host, "-p", self.caller.port, self.caller.base ] r = subprocess.call( cmd, shell=True ) self.firstDBConnection() self.refreshDBList() self.ui.comboBoxDB.setCurrentIndex(self.ui.comboBoxDB.findText(DBName)) self.updateDBConnection() self.prog.setValue(25) if (self.caller.db.open() == False): # This should never be the case self.ui.pushButtonImportDB.setEnabled(False) self.ui.pushButtonBackupDB.setEnabled(False) box = QMessageBox() box.setText(u"La connexion à la base de données a échoué :" + unicode(self.caller.db.lastError().text())) box.exec_() else: # Create data schema "tempus" and "tempus_gtfs" dbstring = "host="+self.caller.host+" dbname="+self.caller.base+" port="+self.caller.port cmd = ["python", "C:\\OSGeo4W64\\apps\\Python27\\lib\\site-packages\\tempusloader-1.2.2-py2.7.egg\\tempusloader\\load_tempus.py", "-t", "osm", "-d", dbstring, "-R"] r = subprocess.call( cmd, shell=True ) self.prog.setValue(50) # Add to data schema "tempus" et "tempus_gtfs" application specific elements, mainly in tempus_access schema cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/init_bdd.sql"] r = subprocess.call( cmd, shell=True ) cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_pt_stop_indicator_layer.sql"] r = subprocess.call( cmd, shell=True ) cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_pt_section_indicator_layer.sql"] r = subprocess.call( cmd, shell=True ) cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_pt_trip_indicator_layer.sql"] r = subprocess.call( cmd, shell=True ) cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_pt_route_indicator_layer.sql"] r = subprocess.call( cmd, shell=True ) cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_pt_agency_indicator_layer.sql"] r = subprocess.call( cmd, shell=True ) cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_path_indicator_layer.sql"] r = subprocess.call( cmd, shell=True ) cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_path_details_indicator_layer.sql"] r = subprocess.call( cmd, shell=True ) cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_paths_tree_indicator_layer.sql"] r = subprocess.call( cmd, shell=True ) cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_comb_paths_trees_indicator_layer.sql"] r = subprocess.call( cmd, shell=True ) cmd = ["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-f", self.caller.sql_dir + "/function_create_isosurfaces_indicator_layer.sql"] r = subprocess.call( cmd, shell=True ) self.prog.setValue(80) # Import holidays definition file cmd=["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-c", "\copy tempus_access.holidays FROM "+self.caller.data_dir + "/others/holidays.csv CSV HEADER DELIMITER ';'"] r = subprocess.call( cmd, shell=True ) # Import modalities definition file cmd=["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-c", "\copy tempus_access.modalities FROM "+self.caller.data_dir + "/system/modalities.csv CSV HEADER DELIMITER ';'"] r = subprocess.call( cmd, shell=True ) # Import agregates definition file cmd=["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-c", "\copy tempus_access.agregates FROM "+self.caller.data_dir + "/system/agregates.csv CSV HEADER DELIMITER ';'"] r = subprocess.call( cmd, shell=True ) # Import areas definition file cmd=["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-c", "\copy tempus_access.areas_param FROM "+self.caller.data_dir + "/areas/areas_param.csv CSV HEADER DELIMITER ';'"] r = subprocess.call( cmd, shell=True ) # Import object types file cmd=["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-c", "\copy tempus_access.obj_type FROM "+self.caller.data_dir + "/system/obj_type.csv CSV HEADER DELIMITER ';'"] r = subprocess.call( cmd, shell=True ) # Import indicators file cmd=["psql", "-h", self.caller.host, "-p", self.caller.port, "-d", self.caller.base, "-c", "\copy tempus_access.indicators FROM "+self.caller.data_dir + "/system/indicators.csv CSV HEADER DELIMITER ';'"] r = subprocess.call( cmd, shell=True ) self.prog.setValue(90) s="SELECT lib, code, file_name, id_field, name_field, from_srid FROM tempus_access.areas_param\ ORDER BY 2" q=QtSql.QSqlQuery(self.caller.db) q.exec_(unicode(s)) while q.next(): cmd=["ogr2ogr.exe", "-f", "PostgreSQL", "PG:dbname="+self.caller.base+" host="+self.caller.host+" port="+self.caller.port, self.caller.data_dir + "/areas/" + q.value(2), "-overwrite", "-lco", "GEOMETRY_NAME=geom", "-s_srs", "EPSG:"+str(q.value(5)), "-t_srs", "EPSG:4326", "-nln", "tempus_access.area_type"+str(q.value(1)), "-nlt", "PROMOTE_TO_MULTI"] r = subprocess.call( cmd, shell=True ) # Uniformisation of field names and index creation t="ALTER TABLE tempus_access.area_type"+str(q.value(1))+" RENAME COLUMN "+str(q.value(4))+" TO lib; \ ALTER TABLE tempus_access.area_type"+str(q.value(1))+" RENAME COLUMN "+str(q.value(3))+" TO char_id; \ CREATE INDEX IF NOT EXISTS area_type"+str(q.value(1))+"_lib_idx ON tempus_access.area_type"+str(q.value(1))+" USING gist (lib gist_trgm_ops); \ CREATE INDEX IF NOT EXISTS area_type"+str(q.value(1))+"_char_id_idx ON tempus_access.area_type"+str(q.value(1))+" USING btree (char_id);" r=QtSql.QSqlQuery(self.caller.db) r.exec_(unicode(t)) self.caller.modelAreaType.setQuery(unicode(s), self.caller.db) self.prog.setValue(100) box = QMessageBox() box.setText(u"La base a été initialisée. Vous pouvez maintenant y importer des données. " ) box.exec_()
def sauverOpe(self): # Fonction à lancer quans les boutons "OK" ou "Dernier - Editer CR" sont cliqués # Entre en base les infos sélectionnées dans QGIS, et saisies dans le formulaire par l'utilisateur # Gère les erreurs "pas assez de points sélectionnés pour construire une ligne ou un polygone" # Gère également la transformation géométrique, via le module convert_geoms # Récupération de la géométrie finale. On en déduit la table où sera stockée l'information, et on gère les erreurs # "pas assez de points pour faire la transformation" geom_cbbx = self.ui.trsf_geom.itemText( self.ui.trsf_geom.currentIndex()) if geom_cbbx == 'Points': geom_output = QGis.Point self.nom_table = 'operation_pts' elif geom_cbbx == 'Lignes': geom_output = QGis.Line self.nom_table = 'operation_lgn' if self.iface.activeLayer().geometryType() == 0: if self.iface.activeLayer().selectedFeatureCount() < 2: mess2pts = QtGui.QMessageBox() mess2pts.setText(u'Pas assez de points sélectionnés') mess2pts.setInformativeText( u'Il faut au moins 2 points pour faire une ligne. Merci d\'en sélectionner plus' ) mess2pts.setIcon(QtGui.QMessageBox.Warning) mess2pts.setStandardButtons(QtGui.QMessageBox.Ok) ret = mess2pts.exec_() return elif geom_cbbx == 'Surfaces': geom_output = QGis.Polygon self.nom_table = 'operation_poly' if self.iface.activeLayer().geometryType() == 0: if self.iface.activeLayer().selectedFeatureCount() < 3: mess3pts = QtGui.QMessageBox() mess3pts.setText(u'Pas assez de points sélectionnés') mess3pts.setInformativeText( u'Il faut au moins 3 points pour faire un polygone. Merci d\'en sélectionner plus' ) mess3pts.setIcon(QtGui.QMessageBox.Warning) mess3pts.setStandardButtons(QtGui.QMessageBox.Ok) ret = mess3pts.exec_() return #copie des entités sélectionnées dans une couche "memory". Evite les problèmes avec les types de couches "non éditables" (comme les GPX). coucheactive = self.iface.activeLayer() entselect = [ QgsGeometry(feature.geometry()) for feature in coucheactive.selectedFeatures() ] if entselect[0].type() == QGis.Line: typegeom = 'LineString' elif entselect[0].type() == QGis.Point: typegeom = 'Point' elif entselect[0].type() == QGis.Polygon: typegeom = 'Polygon' else: print "ce ne sont pas des points, des lignes ou des polygones" self.iface.actionCopyFeatures().trigger() if self.iface.activeLayer().crs().authid() == u'EPSG:4326': memlayer = QgsVectorLayer( "{zr_typegeom}?crs=epsg:4326".format(zr_typegeom=typegeom), "memlayer", "memory") if self.iface.activeLayer().crs().authid() == u'EPSG:2154': memlayer = QgsVectorLayer( "{zr_typegeom}?crs=epsg:2154".format(zr_typegeom=typegeom), "memlayer", "memory") QgsMapLayerRegistry.instance().addMapLayer(memlayer, False) root = QgsProject.instance().layerTreeRoot() memlayerNode = QgsLayerTreeLayer(memlayer) root.insertChildNode(0, memlayerNode) self.iface.setActiveLayer(memlayer) memlayer.startEditing() self.iface.actionPasteFeatures().trigger() memlayer.commitChanges() #lancement de convert_geoms.py pour transformer les entités sélectionnées dans le type d'entités choisi. #compréhension de liste : [fonction for x in liste] geom2 = convert_geometries([ QgsGeometry(feature.geometry()) for feature in memlayer.selectedFeatures() ], geom_output) #export de la géométrie en WKT et transformation de la projection si les données ne sont pas saisies en Lambert 93 if memlayer.crs().authid() == u'EPSG:2154': thegeom = 'st_setsrid(st_geometryfromtext (\'{zr_geom2}\'), 2154)'.format( zr_geom2=geom2.exportToWkt()) elif memlayer.crs().authid() == u'EPSG:4326': thegeom = 'st_transform(st_setsrid(st_geometryfromtext (\'{zr_geom2}\'),4326), 2154)'.format( zr_geom2=geom2.exportToWkt()) else: print u'La projection de la couche active n\'est pas supportée' #lancement de la fonction qui vérifie si l'opération fait partie d'un chantier de volontaires. self.recupIdChantvol() #lancement de la requête SQL qui introduit les données géographiques et du formulaire dans la base de données. querysauvope = QtSql.QSqlQuery(self.db) query = u"""insert into bdtravaux.{zr_nomtable} (sortie, plangestion, code_gh, descriptio, chantfini, the_geom, ope_chvol) values ({zr_sortie}, '{zr_plangestion}', '{zr_code_gh}', '{zr_libelle}', '{zr_chantfini}', {zr_the_geom}, '{zr_opechvol}')""".format (zr_nomtable=self.nom_table,\ zr_sortie = self.ui.sortie.itemData(self.ui.sortie.currentIndex()),\ zr_plangestion = self.ui.opprev.currentItem().text().split("/")[-1],\ zr_code_gh = self.ui.opprev.currentItem().text().split("/")[1],\ # zr_ope_typ = self.ui.opreal.currentItem().text().replace("\'","\'\'"),\ zr_libelle = self.ui.descriptio.toPlainText().replace("\'","\'\'"),\ zr_chantfini = str(self.ui.chantfini.isChecked()).lower(),\ zr_the_geom = thegeom,\ #geom2.exportToWkt(),\ #st_transform(st_setsrid(st_geometryfromtext ('{zr_the_geom}'),4326), 2154) si besoin de transformer la projection zr_opechvol = self.id_opechvol) ok = querysauvope.exec_(query) if not ok: QtGui.QMessageBox.warning(self, 'Alerte', u'Requête sauver Ope ratée') # print unicode(query) self.rempliJoinOpe() self.iface.setActiveLayer(coucheactive) QgsMapLayerRegistry.instance().removeMapLayer(memlayer.id()) self.ui.buttonBox.button(QtGui.QDialogButtonBox.Ok).setEnabled(0) self.ui.compoButton.setEnabled(0) self.close
def Composer(self, idsortie): print 'dans composeur, id_sortie='+str(idsortie) #Affichage des contours du site #Récupération des données de la table "sortie" pour affichage du site et utilisation dans les étiquettes du composeur self.recupDonnSortie(idsortie) reqwheresit="""codesite='"""+str(self.codedusite)+"""'""" self.uri.setDataSource("sites_cen", "t_sitescen", "the_geom", reqwheresit) self.contours_site=QgsVectorLayer(self.uri.uri(), "contours_site", "postgres") # Import de la couche contenant les contours du site root = QgsProject.instance().layerTreeRoot() if self.contours_site.featureCount()>0: QgsMapLayerRegistry.instance().addMapLayer(self.contours_site, False) root.insertLayer(0, self.contours_site) # Symbologie du contour de site # create a new single symbol renderer symbol = QgsSymbolV2.defaultSymbol(self.contours_site.geometryType()) renderer = QgsSingleSymbolRendererV2(symbol) # create a new simple marker symbol layer, a white circle with a black border properties = {'color': 'green', 'color_border': 'red'} symbol_layer = QgsSimpleFillSymbolLayerV2.create(properties) symbol_layer.setBrushStyle(0) #0 = Qt.NoBrush. Cf doc de QBrush # assign the symbol layer to the symbol renderer renderer.symbols()[0].changeSymbolLayer(0, symbol_layer) # assign the renderer to the layer self.contours_site.setRendererV2(renderer) #Appel à la fonction "affiche", qui importe les couches non vides de gestion réalisée, parmi operation_poly, operation_lgn et operation_pts self.affiche(idsortie) # Affichage des couches contenant les contours du site et les opérations de gestion saisies, et masquage des autres self.rendreVisible=[] layers=iface.legendInterface().layers() for layer in layers: if layer.type()==QgsMapLayer.VectorLayer: if layer.name()=='gestrealpolys' or layer.name()=='gestreallgn' or layer.name()=='gestrealpts' or layer.name()=='contours_site': iface.legendInterface().setLayerVisible(layer, True) else: if iface.legendInterface().isLayerVisible(layer): self.rendreVisible.append(layer) iface.legendInterface().setLayerVisible(layer, False) #Récupération des données de la table "ch_volont" pour utilisation dans les étiquettes du composeur self.recupDonnChVolont(idsortie) #COMPOSEUR : Production d'un composeur beforeList = self.iface.activeComposers() self.iface.actionPrintComposer().trigger() afterList = self.iface.activeComposers() diffList = [] for item in afterList: if not item in beforeList: diffList.append(item) #Intégration du composeur dans le QgsComposerView et création du QgsComposition self.composerView = diffList[0] self.composition = self.composerView.composition() # Adaptation de la composition : 2 pages A3 self.composition.setPaperSize(420, 297) self.composition.setNumPages(2) #TEMPLATE : Récupération du template. Intégration des ses éléments dans la carte. if sys.platform.startswith('linux'): file1=QtCore.QFile('/home/vincent/.qgis2/python/plugins/bdtravaux/BDT_20130705_T_CART_ComposerTemplate.qpt') if sys.platform.startswith('win32'): file1=QtCore.QFile('C:\qgistemplate\BDT_20130705_T_CART_ComposerTemplate.qpt') doc=QtXml.QDomDocument() doc.setContent(file1, False) elem=doc.firstChildElement() self.composition.loadFromTemplate(doc, substitutionMap=None, addUndoCommands =False) #CARTE : Récupération de la carte. maplist=[] for item in self.composition.composerMapItems(): maplist.append(item) self.composerMap=maplist[0] #Taille définie pour la carte x, y, w, h, mode, frame, page = 5, 15, 408, 270, QgsComposerItem.UpperLeft, False, 1 self.composerMap.setItemPosition(x, y, w, h, mode, frame, page) # print self.composerMap.page() #Crée la bbox autour du site pour la carte en cours (fonction mapItemSetBBox l 293) #self.contours_sites est défini dans la fonction affiche() self.margin=10 self.composerMapSetBBox(self.contours_site, self.margin) #(Dé)zoome sur l'ensemble des deux pages du composeur #self.composition.mActionZoomFullExtent().trigger() #ETIQUETTES : Modifier les étiquettes du composeur. # Trouver les étiquettes dans le composeur labels = [item for item in self.composition.items()\ if item.type() == QgsComposerItem.ComposerLabel] #trouver les opérations effectuées lors de la sortie et leurs commentaires dans la table postgresql, selon l'id de la sortie sélectionnée dans le module "opération" # une boucle permet de récupérer et afficher à la suite dans une seule zone de texte toutes les opérations et leurs descriptions querycomope = QtSql.QSqlQuery(self.db) qcomope=u"""select operation_id, (select distinct array_to_string(array(select distinct typoperation from bdtravaux.join_typoperation where id_jointyp=id_oper order by typoperation),'; ')) as typope, descriptio, code_gh, round(st_area(the_geom)::numeric,2) as surface, round(st_length(the_geom)::numeric,2) as longueur, ST_NumGeometries(the_geom) as compte, (select distinct array_to_string(array(select distinct operateurs from bdtravaux.join_operateurs where id_joinop=id_oper order by operateurs),'; ')) as operateurs from (select * from bdtravaux.operation_poly UNION select * from bdtravaux.operation_lgn UNION select * from bdtravaux.operation_pts) tables where sortie={zr_sortie} order by typ_operat""".format \ (zr_sortie = idsortie) #self.ui.sortie.itemData(self.ui.sortie.currentIndex()) #print unicode(qcomope) ok3 = querycomope.exec_(qcomope) if not ok3: QtGui.QMessageBox.warning(self, 'Alerte', u'Requête operations ratée') querycomope.first() texteope="" #Requête : Données à récupérer pour chaque opération de la sortie for i in xrange(0 , querycomope.size()): #Récupération des autres valeurs de chaque opération ope=unicode(querycomope.value(1)) descrope=unicode(querycomope.value(2)).replace('\n','<br/>') ghope=unicode(querycomope.value(3)) surfope=unicode(querycomope.value(4)) longope=unicode(querycomope.value(5)) countope=unicode(querycomope.value(6)) operatope=unicode(querycomope.value(7)) texteope=unicode(texteope+u'<br/>'+u'<b>'+ope+u'</b>'+u'<h style="margin-left:1cm;">('+ ghope+u')<h style="margin-left:0.5cm;">'+u'/'+u'<h style="margin-left:0.5cm;">'+surfope+u' m²'+'<h style="margin-left:0.5cm;">'+u'/'+u'<h style="margin-left:0.5cm;">'+longope+u' ml<h style="margin-left:0.5cm;">'+u'/'+u'<h style="margin-left:0.5cm;">'+operatope+u'<br/>'+descrope+u'<br/>') querycomope.next() # Pour chaque étiquette qui contient le mot-clé (comme "$codesite"), remplacer le texte par le code du site concerné # La methode find() permet de chercher une chaîne dans une autre. # Elle renvoie le rang du début de la chaîne cherchée. Si = -1, c'est que la chaîne cherchée n'est pas trouvée for label in labels: if label.displayText().find("$codesite")>-1: plac_codesite=label.displayText().find("$codesite") texte=unicode(label.displayText()) label.setText(texte[0:plac_codesite]+self.codedusite+texte[plac_codesite+9:]) #pr python equiv à VB6 left, mid and right : https://mail.python.org/pipermail/tutor/2004-November/033445.html if label.displayText().find("$redac")>-1: plac_redac=label.displayText().find("$redac") texte=unicode(label.displayText()) label.setText(texte[0:plac_redac]+self.salaries+texte[plac_redac+6:]) if label.displayText().find("$date")>-1: plac_date=label.displayText().find("$date") texte=unicode(label.displayText()) label.setText(texte[0:plac_date]+self.datesortie+texte[plac_date+5:]) if label.displayText().find("$datefin")>-1: plac_date=label.displayText().find("$datefin") texte=unicode(label.displayText()) label.setText(texte[0:plac_date]+self.datefin+texte[plac_date+8:]) if label.displayText().find("$jourschan")>-1: plac_date=label.displayText().find("$jourschan") texte=unicode(label.displayText()) label.setText(texte[0:plac_date]+self.jourschan+texte[plac_date+10:]) if label.displayText().find("$commsortie")>-1: plac_commsortie=label.displayText().find("$commsortie") texte=unicode(label.displayText()) label.setText(texte[0:plac_commsortie]+self.sortcom+texte[plac_commsortie+11:]) if label.displayText().find("$nomsite")>-1: plac_nomsite=label.displayText().find("$nomsite") texte=unicode(label.displayText()) label.setText(texte[0:plac_nomsite]+self.nomdusite+texte[plac_nomsite+8:]) if label.displayText().find("$commope")>-1: label.setText(texteope) if label.displayText().find("$objet")>-1: plac_objet=label.displayText().find("$objet") texte=unicode(label.displayText()) label.setText(texte[0:plac_objet]+self.objvisite+texte[plac_objet+6:]) if label.displayText().find("$objvi_autre")>-1: plac_objautre=label.displayText().find("$objvi_autre") texte=unicode(label.displayText()) if self.objautre: label.setText(texte[0:plac_objautre]+self.objautre+texte[plac_objautre+12:]) else: label.setText(texte[0:plac_objautre]+''+texte[plac_objautre+12:]) if label.displayText().find("$natfaune")>-1: label.setText(self.natfaune) if label.displayText().find("$natflore")>-1: label.setText(self.natflore) if self.cv_partenaire is not None: if label.displayText().find("$nbjours")>-1: plac_nbjours=label.displayText().find("$nbjours") texte=unicode(label.displayText()) label.setText(texte[0:plac_nbjours]+str(self.cv_nb_jours)+texte[plac_nbjours+8:]) if label.displayText().find("$nbheurch")>-1: plac_nbheurch=label.displayText().find("$nbheurch") texte=unicode(label.displayText()) label.setText(texte[0:plac_nbheurch]+str(self.cv_nb_heur_ch)+texte[plac_nbheurch+9:]) if label.displayText().find("$nbheurdec")>-1: plac_nbheurdec=label.displayText().find("$nbheurdec") texte=unicode(label.displayText()) label.setText(texte[0:plac_nbheurdec]+str(self.cv_nb_heur_de)+texte[plac_nbheurdec+10:]) if label.displayText().find("$partenair")>-1: plac_partenair=label.displayText().find("$partenair") texte=unicode(label.displayText()) label.setText(texte[0:plac_partenair]+self.cv_partenaire+texte[plac_partenair+10:]) # print unicode(texte) if label.displayText().find("$heberg")>-1: plac_heberg=label.displayText().find("$heberg") texte=unicode(label.displayText()) label.setText(texte[0:plac_heberg]+self.cv_heberg+texte[plac_heberg+7:]) if label.displayText().find("$jr1enc_am")>-1: label.setText(str(self.cv_j1_enc_am)) if label.displayText().find("$jr1enc_pm")>-1: label.setText(str(self.cv_j1_enc_pm)) if label.displayText().find("$jr1tot_am")>-1: label.setText(str(self.cv_j1_tot_am)) if label.displayText().find("$jr1tot_pm")>-1: label.setText(str(self.cv_j1_tot_pm)) if label.displayText().find("$jr1cen_am")>-1: label.setText(str(self.cv_j1adcen_am)) if label.displayText().find("$jr1cen_pm")>-1: label.setText(str(self.cv_j1adcen_pm)) if label.displayText().find("$jr1blo_am")>-1: label.setText(str(self.cv_j1_blon_am)) if label.displayText().find("$jr1blo_pm")>-1: label.setText(str(self.cv_j1_blon_pm)) if label.displayText().find("$jr2enc_am")>-1: label.setText(str(self.cv_j2_enc_am)) if label.displayText().find("$jr2enc_pm")>-1: label.setText(str(self.cv_j2_enc_pm)) if label.displayText().find("$jr2tot_am")>-1: label.setText(str(self.cv_j2_tot_am)) if label.displayText().find("$jr2tot_pm")>-1: label.setText(str(self.cv_j2_tot_pm)) if label.displayText().find("$jr2cen_am")>-1: label.setText(str(self.cv_j2adcen_am)) if label.displayText().find("$jr2cen_pm")>-1: label.setText(str(self.cv_j2adcen_pm)) if label.displayText().find("$jr2blo_am")>-1: label.setText(str(self.cv_j2_blon_am)) if label.displayText().find("$jr2blo_pm")>-1: label.setText(str(self.cv_j2_blon_pm)) if label.displayText().find("$sem_enc")>-1: label.setText(str(self.cv_sem_enc)) if label.displayText().find("$sem_ben")>-1: label.setText(str(self.cv_sem_ben)) else: if re.match("^\$jr",label.displayText()) or re.search("\$sem",label.displayText()) or re.search("\$nb",label.displayText()): label.setText('0') if re.search("\$partenair",label.displayText()) or re.search("\$heberg",label.displayText()): label.setText(' ') # le module re permet de chercher des chaînes dans du texte avec des expression régulières # match => le début du texte doit correspondre #search : on cherche la chaîne quelque-part dans le texte. #le \ permet d'échapper le $ (qui correspond normalement à une fin de ligne dans une regexp). #LEGENDE : mettre à jour la légende. for i in self.composition.items(): if isinstance(i,QgsComposerLegend): print "mise a jour legende" legend = i legend.setAutoUpdateModel(True) for j in xrange(legend.modelV2().rowCount()): modelindex=legend.modelV2().index(j, 0) layertreenode=legend.modelV2().index2node(modelindex) # print modelindex.data() # print modelindex.__class__.__name__ # print layertreenode.__class__.__name__ if isinstance(layertreenode, QgsLayerTreeGroup): layertreenode.setVisible(False) # print modelindex.data() # layertreenode.setName("") # print modelindex.data() else: print 'Layer' legend.setAutoUpdateModel(False) legend.setLegendFilterByMapEnabled(True)
def nestedTransaction(self): QtSql.QSqlQuery(self.db).exec_('SAVEPOINT LEVEL_%d' % (self._openTransactionsCount + 1)) if self.db.lastError().isValid(): raise CDatabaseException(CDatabase.errTransactionError, self.db.lastError())
# author: Jan Bodnar # website: zetcode.com # last edited: October 2009 from PyQt4 import QtSql, QtCore app = QtCore.QCoreApplication([]) db = QtSql.QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("friends.db") if not db.open(): print "cannot establish a database connection" else: query = QtSql.QSqlQuery("SELECT name, age FROM friends") query.first() name = query.value(0).toString() age = query.value(1).toString() print name, age query.next() name = query.value(0).toString() age = query.value(1).toString() print name, age query.last() name = query.value(0).toString() age = query.value(1).toString() print name, age
def query(cn): return QtSql.QSqlQuery(cn)
def generator(csomazon, genfolder, keszito): # postgresql connection try: # csomag adatok lekérdezése prow = [] queryGenerate = QtSql.QSqlQuery() siker = queryGenerate.exec_( """SELECT csomag_azon,csomag_nev,mintater,projminter,felmero,hatarido,sorok,oszlopok,letrehozas, formverzio,oscsomag FROM csomag WHERE csomag_azon = %s""" % (csomazon)) if siker: if queryGenerate.size() == 1: queryGenerate.next() for i in range(11): prow.append(queryGenerate.value(i)) else: QtGui.QMessageBox.warning(None, u"File Generálás", u"Hiányzó csomag rekord.") return else: if queryGenerate.lastError().type() != 0: QtGui.QMessageBox.warning( None, u"Hiba:", u"Hiba típus: %s" % str(queryGenerate.lastError().text())) return print prow forward = True pontx = 0 sql_list = [] queryCreates = QtSql.QSqlQuery() crQuery = """SELECT table_sql FROM csomag_sql WHERE %s= True""" % ( prow[9].strip()) siker = queryCreates.exec_(crQuery) if siker: while queryCreates.next(): sql_list.append(queryCreates.value(0)) else: if queryCreates.lastError().type() != 0: QtGui.QMessageBox.warning( None, u"Hiba:", u"Hiba típus: %s" % str(queryCreates.lastError().text())) return if not len(sql_list): return try: # felhasználó könyvtárának létrehozása a megadott elérési út alatt - ha még nincs if not os.path.exists(genfolder + '/' + prow[4]): print 'Create package folder:', os.path.exists(genfolder + '/' + prow[4]) os.makedirs(genfolder + '/' + prow[4]) # az SQLite adatbázis létrehozása, és kapcsolódás hozzá sdb = sqlite3.connect(genfolder + '/' + prow[4] + '/' + prow[1] + '.db') # sqlite táblák létrehozásának ciklusa for sql_rec in sql_list: scur = sdb.cursor() scur.execute(sql_rec) sdb.commit() # csomag rekord beszúrása prowselect = (prow[0], prow[1], prow[3], prow[4], prow[5].toString('yyyy-MM-dd hh:mm:ss'), prow[6], prow[7], prow[9].strip()) print prowselect csrec = sdb.cursor() csrec.execute( 'INSERT INTO csomag(csomag_id,csom_nev,mintater,felmero,hatarido,sor,oszlop,formverzio) VALUES(?,?,?,?,?,?,?,?)', prowselect) sdb.commit() # gpx változó létrehozása gpx = gpxpy.gpx.GPX() # ppont rekordok lekérdezése pponts = [] queryPRec = QtSql.QSqlQuery() siker = queryPRec.exec_( """SELECT ppont_azon,csomag_azon,epont_azon,bpont_azon,sorszam,sor,oszlop,wgs84_lat,wgs84_lon,statusz FROM ppont WHERE csomag_azon=%s""" % (prow[0])) if siker: while queryPRec.next(): prec = [] for i in range(10): prec.append(queryPRec.value(i)) pponts.append(prec) pontx = len(pponts) for ppont in pponts: # insert ppont records print ppont scur = sdb.cursor() if ppont[9] == 0: # ha uj csomagról van szó scur.execute( '''INSERT INTO ppont(ppont_id,csomag_id,epont_id,bpont_id,sorszam,sor,oszlop, wgs84_lat,wgs84_lon,statusz) VALUES(?,?,?,?,?,?,?,?,?,1)''', (ppont[0], ppont[1], ppont[2], ppont[3], ppont[4], ppont[5], ppont[6], ppont[7], ppont[8])) else: # ha hibajavító csomagról van szó scur.execute( '''INSERT INTO ppont(ppont_id,csomag_id,epont_id,bpont_id,sorszam,sor,oszlop, wgs84_lat,wgs84_lon,statusz) VALUES(?,?,?,?,?,?,?,?,?,?)''', ppont) sdb.commit() # GPX waypoint beszúrások a gpx változóba gpx_wpt = gpxpy.gpx.GPXWaypoint(ppont[7], ppont[8], name=str(ppont[4]), symbol='Navaid, Orange') gpx.waypoints.append(gpx_wpt) # gpx fájl létrehozása és lezárása gpxfilename = genfolder + '/' + prow[4] + '/' + prow[1] + '.gpx' gpxfile = open(gpxfilename, 'a') gpxfile.write(gpx.to_xml()) gpxfile.close() # a Garmin gpi fájl létrehozása GPSBabel program segítségével gpifilename = genfolder + '/' + prow[4] + '/' + prow[1] + '.gpi' pluginfolder = os.path.dirname(os.path.abspath(__file__)) babelparancs = '' if platform == "linux" or platform == "linux2" or platform == "darwin": # linux and OS X babelparancs = 'gpsbabel -w -i gpx -f ' + gpxfilename + ' -o garmin_gpi,category=' + prow[1][:6] \ + prow[1][8:] + ',bitmap="' + pluginfolder + '/pont.bmp",unique=1 -F ' + gpifilename elif platform == "win32": # Windows... babelparancs = 'gpsbabel -w -i gpx -f ' + gpxfilename + ' -o garmin_gpi,category=' + prow[1][:6] \ + prow[1][8:] + ',bitmap="' + pluginfolder.replace('\\','/') + '/pont.bmp",unique=1 -F ' + gpifilename # + prow[1][8:] + ",bitmap=d:/dev/pycharm_projects/scriptek/pont.bmp,unique=1 -F " + gpifilename else: QtGui.QMessageBox.warning( None, u"Hiba:", u"Operációs rendszer függési hiba: %s" % platform) if babelparancs: print babelparancs babelproc = os.system(babelparancs) print babelproc sleep(2) except sqlite3.Error as e: QtGui.QMessageBox.warning(None, u"Hiba:", u"Adatbázis hiba: %s" % e) forward = False except Exception as e: QtGui.QMessageBox.warning(None, u"Hiba:", u"Adatbázis lekérdezés hiba: %s" % e) forward = False finally: if sdb: sdb.close() if forward: # ha az eddigi műveletek sikeresen lezajlottak fname = genfolder + '/' + prow[4] + '/' + prow[1] + '.db' if os.path.isfile(fname): # adatbázis ZIP zömörítés zip_ok = csomag_zip(prow[1], prow[4], genfolder) md5_returned = None if zip_ok: # MD5 value generálás md5_returned = csomag_md5(genfolder + '/' + prow[4] + '/' + prow[1] + '.zip') if md5_returned is not None: tom_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S') # csomag XML element létrehozása, letárolása # csomag_azon,csomag_nev,mintater,projminter,felmero,hatarido,sorok,oszlopok,letrehozas,formverzio,oscsomag # (cs_felm, cs_azon, cs_nev, cs_pontsz, cs_gen, cs_hat, cs_tom, cs_MD5) logazon, logrend = csomag_XML_elem( prow[4], prow[0], prow[1], pontx, prow[8].toString('yyyy-MM-dd hh:mm:ss'), prow[5].toString('yyyy-MM-dd hh:mm:ss'), tom_time, md5_returned, genfolder) if logazon: # ha első alkalommal törénik a generálás if logrend == 1: # a csomag rekord állapotának megváltoztatása a Postgresql-ben queryCsomStatus = QtSql.QSqlQuery() siker = queryCsomStatus.exec_( """UPDATE csomag SET allapot = True WHERE csomag_azon = %s""" % (prow[0])) if siker: print 'Package state = True' else: if queryCsomStatus.lastError().type() != 0: QtGui.QMessageBox.warning( None, u"Hiba:", u"Hiba típus: %s" % str(queryCsomStatus.lastError().text()) ) return # a ppont rekordok státuszának megváltoztatása a Postgresql-ben queryPontStatus = QtSql.QSqlQuery() siker = queryPontStatus.exec_( """UPDATE ppont SET statusz = 1 WHERE csomag_azon = %s""" % (prow[0])) if siker: print 'Pponts state: 1' else: if queryPontStatus.lastError().type() != 0: QtGui.QMessageBox.warning( None, u"Hiba:", u"Hiba típus: %s" % str(queryPontStatus.lastError().text()) ) return # a fájlgenerálás bejegyzése a csomag_gen táblába queryGenLog = QtSql.QSqlQuery() siker = queryGenLog.exec_( '''INSERT INTO csomag_gen(package_id,csomag_nev,file_generalas,gen_mod,gen_rend,kezdemenyezo) VALUES(%s,'%s','%s','manual',%s,'%s') RETURNING gen_id ;''' % (logazon, prow[1], tom_time, logrend, keszito)) if siker: if queryGenLog.size() == 1: queryGenLog.next() print 'gen_id:', queryGenLog.value( 0), 'package_id:', logazon else: QtGui.QMessageBox.warning( None, u"Csomag-gen", u"Csomag generálás rekord rögzítési hiba.") return else: if queryGenLog.lastError().type() != 0: QtGui.QMessageBox.warning( None, u"Hiba:", u"Hiba típus: %s" % str(queryGenLog.lastError().text())) return else: # a fájlok törlése építhető be ide, ha sikertelen volt a csomag_log rekord kezelése pass else: QtGui.QMessageBox.warning(None, u"Hiba:", u"MD5 generálás hiba") return QtGui.QMessageBox.warning( None, u"Csomag generálás", u"Létrehozásra kerültek a csomag fájljai") return QtGui.QMessageBox.warning(None, u"Csomag generálás hiba", u"A fájlok generálása elakadt.") return except Exception as e: QtGui.QMessageBox.warning(None, u"Hiba:", u"Generálás hiba: %s" % e)
def loadLWR(self): self.LWRTreeView.clear() if self.selectionGroup.checkedButton() == self.optionFamilyTree: searchingBySpecies = False else: searchingBySpecies = True self.queryFamilies = QtSql.QSqlQuery( 'Select distinct family from LWR order by family') self.queryFamilies.exec_() while self.queryFamilies.next(): if not searchingBySpecies: familyText = self.queryFamilies.value(0).toString() whereClauseText = ("where family='%s'" % familyText) newFamilyItem = QtGui.QTreeWidgetItem(self.LWRTreeView) newFamilyItem.setText(0, familyText) else: whereClauseText = '' self.querySpecies = QtSql.QSqlQuery(("""Select distinct species from LWR %s order by species""" % whereClauseText)) self.querySpecies.exec_() while self.querySpecies.next(): speciesText = self.querySpecies.value(0).toString() if not searchingBySpecies: treeRoot = newFamilyItem whereClauseText2 = whereClauseText = ( """where family='%s' and species='%s' and LWR.dimref=DIMS.dimsid and LWR.biblioref=BIBLIO.biblioid""" % (familyText, speciesText)) else: treeRoot = self.LWRTreeView whereClauseText2 = whereClauseText = ( """where species='%s' and LWR.dimref=DIMS.dimsid and LWR.biblioref=BIBLIO.biblioid""" % speciesText) newSpeciesItem = QtGui.QTreeWidgetItem(treeRoot) newSpeciesItem.setText(0, speciesText) self.queryRegions = QtSql.QSqlQuery(("""Select distinct species,region,dimfull,minlength,maxlength,n,a,b,r2,authors,title from LWR,DIMS,BIBLIO %s order by species""" % (whereClauseText2))) self.queryRegions.exec_() while self.queryRegions.next(): newRegionsItem = QtGui.QTreeWidgetItem(newSpeciesItem) newRegionsItem.setText( 0, self.queryRegions.value(1).toString()) newRegionsItem.setText( 1, self.queryRegions.value(2).toString()) newRegionsItem.setText( 3, self.queryRegions.value(3).toString()) newRegionsItem.setText( 4, self.queryRegions.value(4).toString()) newRegionsItem.setText( 5, self.queryRegions.value(5).toString()) newRegionsItem.setText( 6, self.queryRegions.value(6).toString()) newRegionsItem.setText( 7, self.queryRegions.value(7).toString()) newRegionsItem.setText( 8, self.queryRegions.value(8).toString()) newRegionsItem.setText( 9, self.queryRegions.value(9).toString()) newRegionsItem.setText( 10, self.queryRegions.value(10).toString()) if searchingBySpecies: break
def log_unmarcked_assignment(self): sqlquery = QtSql.QSqlQuery(""" SELECT assignment_submissions.timemodified, assignment_submissions.timemarked, assignment.name, course.fullname, student.lastname, teacher.lastname FROM assignment_submissions JOIN assignment ON assignment_submissions.assignment = assignment.id JOIN course ON course.id = assignment.course JOIN user as student ON student.id = assignment_submissions.userid LEFT OUTER JOIN user as teacher ON teacher.id = assignment_submissions.teacher WHERE (assignment_submissions.timemarked =0 OR (assignment_submissions.timemarked !=0 AND assignment_submissions.timemodified > assignment_submissions.timemarked) ) ORDER BY assignment_submissions.timemodified DESC; """) self.unmarcked_assignment_model = CustomSqlModel() self.unmarcked_assignment_model.setQuery(sqlquery) proxyModel = QtGui.QSortFilterProxyModel() proxyModel.setSourceModel(self.unmarcked_assignment_model) self.ui.tableView_2.setModel(proxyModel) self.unmarcked_assignment_model.setHeaderData(0, 1, u"Прислано") self.unmarcked_assignment_model.setHeaderData(1, 1, u"Перевірено") self.unmarcked_assignment_model.setHeaderData(2, 1, u"Назва") self.unmarcked_assignment_model.setHeaderData(3, 1, u"Дисципліна") self.unmarcked_assignment_model.setHeaderData(4, 1, u"Студент") self.unmarcked_assignment_model.setHeaderData(5, 1, u"Викладач") self.ui.tableView_2.setColumnWidth(0, 200) self.ui.tableView_2.setColumnWidth(1, 200) self.ui.tableView_2.setColumnWidth(3, 200) while sqlquery.next(): utc_send, ok = sqlquery.value(0).toInt() utc_marked, ok = sqlquery.value(1).toInt() dataSend = QtCore.QDateTime() dataSend.setTime_t(utc_send) mess = QtCore.QString(u"<u>[%1]</u>: ") if utc_marked == 0: mess.append(u"Надійшла ") elif utc_marked < utc_send: mess.append(u"Нова версія ") mess.append(u"<a href='ya.ru'>%2</a> з %3 студента <b>%4</b>. ") mess = QtCore.QString(mess).arg( dataSend.toString("dd.MM.yyyy hh:mm")).arg( sqlquery.value(2).toString()).arg( sqlquery.value(3).toString()).arg( sqlquery.value(4).toString()) dateMustMark = dataSend.addDays(daystomark) if dateMustMark < QtCore.QDateTime.currentDateTime(): mess.append( u"<span style='color: red;'>Робота повинна була бути перевірена %1.</span> " ) mess = QtCore.QString(mess).arg( dateMustMark.toString("dd.MM.yyyy hh:mm")) else: mess.append(u"<u>Кінцева дата перевірки %1.</u> ") mess = QtCore.QString(mess).arg( dateMustMark.toString("dd.MM.yyyy hh:mm")) self.log_dict[utc_send] = mess
#!/usr/bin/python # ZetCode Advanced PyQt4 tutorial # # In this example, we work with an SQL query error # # author: Jan Bodnar # website: zetcode.com # last edited: October 2009 from PyQt4 import QtSql, QtCore app = QtCore.QCoreApplication([]) db = QtSql.QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("friends.db") if not db.open(): print "cannot establish a database connection" else: query = QtSql.QSqlQuery("SELECT friend, age FROM Friends") while query.next(): friend = query.value(0).toString() age = query.value(1).toString() print friend, age error = query.lastError() print error.text()
def insert_recentconn(self, chost, cport, cpass): """Insert a failure into the database""" QtSql.QSqlQuery('''INSERT INTO recentconn VALUES("%s", "%d", "%s")''' % (chost, cport, cpass)) log.info("Recent connection added: %s:%d" % (chost, cport))
def clear_recentconn_table(self): """Drops the recentconn (Controller) table from the database""" QtSql.QSqlQuery('''DROP TABLE IF EXISTS recentconn''')
def run(self): self.buildGraph() for d in self.days: if (self.time_point != "NULL"): # Simple time constraint if (self.path_tree == False): s = "SELECT tempus_access.shortest_path2((" + str( self.road_node_from ) + "), (" + str(self.road_node_to) + "), ARRAY" + str( self.tran_modes) + ", '" + d + " " + self.time_point[ 1:len(self.time_point) - 1] + "'::timestamp, " + str( self.constraint_date_after) + ");\n" print s self.file.write(s) else: for node in self.road_nodes: # For each source node s = "SELECT tempus_access.shortest_paths_tree(("+str(node)+"), ARRAY"+str(self.tran_modes)+", "+str(self.max_cost)+", "+str(self.walking_speed)+", "+str(self.cycling_speed)+", '"+d \ + " " +self.time_point[1:len(self.time_point)-1]+"'::timestamp, "+str(self.constraint_date_after)+");\n" print s self.file.write(s) else: # Time period constraint if ( self.all_services == True ): # All possible services of the period - only fo simple paths current_timestamp = "" bound_timestamp = "" bound_time = "" if (self.constraint_date_after == True): current_timestamp = d + " " + self.time_start[ 1:len(self.time_start) - 1] bound_timestamp = d + " " + self.time_end[ 1:len(self.time_end) - 1] bound_time = self.time_end elif (self.constraint_date_after == False): current_timestamp = d + " " + self.time_end[ 1:len(self.time_end) - 1] bound_timestamp = d + " " + self.time_start[ 1:len(self.time_start) - 1] bound_time = self.time_start while (current_timestamp != bound_timestamp): s = "SELECT tempus_access.shortest_path2((" + str( self.road_node_from ) + "), (" + str(self.road_node_to) + "), ARRAY" + str( self.tran_modes ) + ", '" + current_timestamp + "'::timestamp, " + str( self.constraint_date_after) + ");\n" print s self.file.write(s) s1 = "SELECT next_pt_timestamp::character varying FROM tempus_access.next_pt_timestamp(" + bound_time + "::time, '" + str( d) + "'::date, " + str( self.constraint_date_after) + ")" print s1 q1 = QtSql.QSqlQuery(self.db) q1.exec_(unicode(s1)) while q1.next(): if (current_timestamp == str(q1.value(0))): current_timestamp = bound_timestamp else: current_timestamp = str(q1.value(0)) elif (self.time_interval != "NULL"): # Search at a regular time interval current_timestamp = "" bound_timestamp = "" bound_time = "" if (self.constraint_date_after == True): current_timestamp = d + " " + self.time_start[ 1:len(self.time_start) - 1] bound_timestamp = d + " " + self.time_end[ 1:len(self.time_end) - 1] bound_time = self.time_end elif (self.constraint_date_after == False): current_timestamp = d + " " + self.time_end[ 1:len(self.time_end) - 1] bound_timestamp = d + " " + self.time_start[ 1:len(self.time_start) - 1] bound_time = self.time_start while (current_timestamp != bound_timestamp): if (self.path_tree == False): s = "SELECT tempus_access.shortest_path((" + str( self.road_node_from ) + "), (" + str( self.road_node_to ) + "), ARRAY" + str( self.tran_modes ) + ", '" + current_timestamp + "'::timestamp, " + str( self.constraint_date_after) + ");\n" print s self.file.write(s) elif (self.path_tree == True): for node in self.road_nodes: # For each source/target node s = "SELECT tempus_access.shortest_paths_tree(" + str( node ) + ", ARRAY" + str( self.tran_modes ) + ", " + str(self.max_cost) + ", " + str( self.walking_speed ) + ", " + str( self.cycling_speed ) + ", '" + current_timestamp + "'::timestamp, " + str( self.constraint_date_after) + ");\n" print s self.file.write(s) s1 = "SELECT next_timestamp::character varying FROM tempus_access.next_timestamp('" + current_timestamp + "'::timestamp, " + str( self.time_interval ) + ", '" + bound_timestamp + "'::timestamp, " + str( self.constraint_date_after) + ")" print s1 q1 = QtSql.QSqlQuery(self.db) q1.exec_(unicode(s1)) while q1.next(): current_timestamp = str(q1.value(0)) print(self.query_str) self.file.write(self.query_str) self.file.close() cmd = [ PSQL, "-h", self.db.hostName(), "-p", str(self.db.port()), "-d", self.db.databaseName(), "-U", self.db.userName(), "-f", self.plugin_dir + "\\scripts\\temp.sql" ] done = execute_external_cmd(cmd) res = False if (done == 0): res = True return res
def csomag_XML_elem(cs_felm, cs_azon, cs_nev, cs_pontsz, cs_gen, cs_hat, cs_tom, cs_MD5, genfolder): """ XML minidom-document létrehozása a csomag adataival és a csomag_log rekord kezelése""" doc = Document() # csomag elem létrehozása csomag = doc.createElement('csomag') csomag.setAttribute("azonosito", '%s' % str(cs_azon)) csomag.setAttribute("nev", '%s' % cs_nev) csomag.setAttribute("pontszam", '%s' % str(cs_pontsz)) doc.appendChild(csomag) # a csomag gyermek elemeinek létrehozása generalas = doc.createElement('generalas') generalas_tartalom = doc.createTextNode('%s' % cs_gen) generalas.appendChild(generalas_tartalom) csomag.appendChild(generalas) hatarido = doc.createElement('hatarido') hatarido_tartalom = doc.createTextNode('%s' % cs_hat) hatarido.appendChild(hatarido_tartalom) csomag.appendChild(hatarido) muvelet = doc.createElement('muvelet') csomag.appendChild(muvelet) hash = doc.createElement('hash') csomag.appendChild(hash) # a művelet gyermek elemeinek létrehozása tomorites = doc.createElement('tomorites') tomorites_tartalom = doc.createTextNode('%s' % cs_tom) tomorites.appendChild(tomorites_tartalom) muvelet.appendChild(tomorites) # a művelet gyermek elemeinek létrehozása md5szerver = doc.createElement('MD5_szerver') md5szerver_tartalom = doc.createTextNode('%s' % cs_MD5) md5szerver.appendChild(md5szerver_tartalom) hash.appendChild(md5szerver) fname = genfolder + '/' + cs_felm + '/' + cs_nev + '.xml' # XML kiírása doc.writexml(open(fname, 'w'), indent=" ", addindent=" ", newl='\n') doc.unlink() if os.path.isfile(fname): queryLogId = QtSql.QSqlQuery() siker = queryLogId.exec_( '''SELECT package_id FROM csomag_log WHERE csomag_azon = %s;''' % (cs_azon)) if siker: if queryLogId.size() == 1: queryLogId.next() return queryLogId.value(0), 2 elif queryLogId.size() > 1: QtGui.QMessageBox.warning( None, u"Csomag-log", u"Csomag-log rekord lekérdezési hiba.") return 0, 0 else: if queryLogId.lastError().type() != 0: QtGui.QMessageBox.warning( None, u"Hiba:", u"Hiba típus: %s" % str(queryLogId.lastError().text())) return 0, 0 queryLog = QtSql.QSqlQuery() siker = queryLog.exec_( '''INSERT INTO csomag_log(felmero,csomag_azon,csomag_nev,cs_pontszam,cs_generalas,cs_hatarido,m_tomorites,hash_md5_szerver) VALUES('%s',%s,'%s',%s,'%s','%s','%s','%s') RETURNING package_id ;''' % (cs_felm, cs_azon, cs_nev, cs_pontsz, cs_gen, cs_hat, cs_tom, cs_MD5)) if siker: if queryLog.size() == 1: queryLog.next() return queryLog.value(0), 1 else: QtGui.QMessageBox.warning( None, u"Csomag-log", u"Csomag-log rekord rögzítési hiba.") return 0, 0 else: if queryLog.lastError().type() != 0: QtGui.QMessageBox.warning( None, u"Hiba:", u"Hiba típus: %s" % str(queryLog.lastError().text())) return 0, 0 else: print "No XML - No PackMan!" return 0, 0
def setValue(self, id, field, value): query = QtSql.QSqlQuery(self.db) query.prepare('update users set ' + field + ' = ? where id = ?') query.addBindValue(value) query.addBindValue(id) return query.exec_()
def _slotPushButtonDerivedRepGenerateClicked(self): self.caller.isosurfaces = True self.caller.buildQuery() r = QtSql.QSqlQuery(self.caller.db) done = r.exec_(self.caller.query) self.caller.resultAvailable(done)
def nestedCommit(self): QtSql.QSqlQuery(self.db).exec_('RELEASE SAVEPOINT LEVEL_%d' % self._openTransactionsCount) if self.db.lastError().isValid(): raise CDatabaseException(CDatabase.errNestedCommitTransactionError, self.db.lastError())
def _slotpushButtonReqDisplayClicked(self): size_indic_name = self.caller.modelSizeIndic.record( self.ui.comboBoxSizeIndic.currentIndex()).value("col_name") color_indic_name = self.caller.modelColorIndic.record( self.ui.comboBoxColorIndic.currentIndex()).value("col_name") self.caller.dlg.ui.labelElapsedTime.setText("") if (size_indic_name != ""): s = "SELECT tempus_access.map_indicator('" + self.ui.comboBoxReq.currentText( ) + "', '" + size_indic_name + "', 'size', " + str( self.ui.spinBoxSizeIndicMinValue.value()) + ", " + str( self.ui.spinBoxSizeIndicMaxValue.value()) + ", " + str( self.ui.doubleSpinBoxSize.value()) + ")" q = QtSql.QSqlQuery(self.db) q.exec_(unicode(s)) elif (self.obj_def_name != "paths_tree") and (self.obj_def_name != "comb_paths_trees"): s = "UPDATE indic." + self.ui.comboBoxReq.currentText( ) + " SET symbol_size = 1" q = QtSql.QSqlQuery(self.db) q.exec_(unicode(s)) if (color_indic_name != ""): s = "SELECT tempus_access.map_indicator('" + self.ui.comboBoxReq.currentText( ) + "', '" + color_indic_name + "', 'color', " + str( self.ui.spinBoxColorIndicMinValue.value()) + ", " + str( self.ui.spinBoxColorIndicMaxValue.value()) + ", 1)" q = QtSql.QSqlQuery(self.db) q.exec_(unicode(s)) elif (self.obj_def_name != "paths_tree") and (self.obj_def_name != "comb_paths_trees"): s = "UPDATE indic." + self.ui.comboBoxReq.currentText( ) + " SET symbol_color = 1" q = QtSql.QSqlQuery(self.db) q.exec_(unicode(s)) for layer in self.caller.node_indicators.findLayers(): self.iface.legendInterface().setLayerVisible(layer.layer(), False) # Stop areas or stops if (((self.obj_def_name == "stops") or (self.obj_def_name == "stop_areas")) and self.ui.radioButtonScreenUnit.isChecked()): self.indicDisplay( self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText(), self.plugin_dir + '/styles/pt_stop_by_mode_prop_size_prop_color_screen_unit.qml', 'gid', "geom", '') elif (((self.obj_def_name == "stops") or (self.obj_def_name == "stop_areas")) and self.ui.radioButtonMapUnit.isChecked()): self.indicDisplay( self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText(), self.plugin_dir + '/styles/pt_stop_by_mode_prop_size_prop_color_map_unit.qml', 'gid', "geom", '') # Sections elif ((self.obj_def_name == "sections") and self.ui.radioButtonScreenUnit.isChecked()): self.indicDisplay( self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText(), self.plugin_dir + '/styles/pt_section_by_mode_prop_size_prop_color_screen_unit.qml', 'gid', "geom", '') elif ((self.obj_def_name == "sections") and self.ui.radioButtonMapUnit.isChecked()): self.indicDisplay( self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText(), self.plugin_dir + '/styles/pt_section_by_mode_prop_size_prop_color_map_unit.qml', 'gid', "geom", '') # Trips elif ((self.obj_def_name == "trips") and self.ui.radioButtonScreenUnit.isChecked()): self.indicDisplay( self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText(), self.plugin_dir + '/styles/pt_trip_by_mode_prop_size_prop_color_screen_unit.qml', 'gid', "geom", '') elif ((self.obj_def_name == "trips") and self.ui.radioButtonMapUnit.isChecked()): self.indicDisplay( self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText(), self.plugin_dir + '/styles/pt_trip_by_mode_prop_size_prop_color_map_unit.qml', 'gid', "geom", '') # Stops routes elif ((self.obj_def_name == "stops_routes") and self.ui.radioButtonScreenUnit.isChecked()): self.indicDisplay( self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText(), self.plugin_dir + '/styles/pt_stop_routes_by_mode_prop_size_prop_color_screen_unit.qml', 'gid', "geom", '') elif ((self.obj_def_name == "stops_routes") and self.ui.radioButtonMapUnit.isChecked()): self.indicDisplay( self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText(), self.plugin_dir + '/styles/pt_stop_routes_by_mode_prop_size_prop_color_map_unit.qml', 'gid', "geom", '') # Routes elif (self.obj_def_name == "routes"): self.indicDisplay(self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText(), self.plugin_dir + '/styles/pt_route_by_mode.qml', 'gid', None, '') # Agencies elif (self.obj_def_name == "agencies"): self.indicDisplay( self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText(), self.plugin_dir + '/styles/pt_agency_by_mode.qml', 'gid', None, '') # Paths elif (self.obj_def_name == "paths"): self.indicDisplay(self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText(), self.plugin_dir + "/styles/mm_path.qml", "gid", "geom", "") elif (self.obj_def_name == "paths_details"): self.indicDisplay(self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText(), self.plugin_dir + "/styles/mm_path_by_mode.qml", "gid", "geom", "") # Paths tree described by nodes elif (self.obj_def_name == "paths_tree") and (self.ui.toolBoxDisplay.currentIndex() == 0): self.indicDisplay(self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText() + "_edges", self.plugin_dir + "/styles/mm_isochron_edge.qml", "d_node", "geom_section", "") self.indicDisplay(self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText() + "_nodes", self.plugin_dir + "/styles/mm_isochron_node.qml", "d_node", "geom_point", "") elif ((self.obj_def_name == "paths_tree") or (self.obj_def_name == "comb_paths_trees")) and ( self.ui.toolBoxDisplay.currentIndex() == 1): self.indicDisplay( self.ui.comboBoxDerivedRep.currentText(), self.ui.comboBoxDerivedRep.currentText(), self.plugin_dir + "/styles/mm_isochron_surface.qml", "id", "geom", "") elif (self.obj_def_name == "comb_paths_trees") and ( self.ui.toolBoxDisplay.currentIndex() == 0): self.indicDisplay(self.ui.comboBoxReq.currentText(), self.ui.comboBoxReq.currentText(), self.plugin_dir + "/styles/mm_isochron_node.qml", "id", "geom", "") if (self.obj_def_name == "paths" or self.obj_def_name == "paths_details" or self.obj_def_name == "paths_tree" or self.obj_def_name == "comb_paths_trees"): for layer in self.caller.node_indicators.findLayers(): if (layer.name() == "Destinations" or layer.name() == "Origines"): self.iface.legendInterface().setLayerVisible( layer.layer(), True)
model.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange) model.select() model.setHeaderData(0, QtCore.Qt.Horizontal, "ID") model.setHeaderData(1, QtCore.Qt.Horizontal, "First name") model.setHeaderData(2, QtCore.Qt.Horizontal, "Last name") def createView(title, model): view = QtGui.QTableView() view.setModel(model) view.setWindowTitle(title) return view if __name__ == '__main__': query = QtSql.QSqlQuery() app = QtGui.QApplication(sys.argv) db = QtSql.QSqlDatabase.addDatabase('QSQLITE') db.setDatabaseName('fichadas.db') model = QtSql.QSqlTableModel() delrow = -1 initializeModel(model) projectModel = QSqlQueryModel() projectModel.setQuery("select * from empleados",db) projectView = QTableView() projectView.setModel(projectModel) form = Form() form.show() sys.exit(app.exec_())
def _slotComboBoxReqIndexChanged(self, indexChosenLine): self.caller.parent_layer = "" if (indexChosenLine == 0): # General interface self.caller.dlg.ui.pushButtonIndicCalculate.setEnabled(True) self.caller.dlg.ui.pushButtonReinitCalc.setEnabled(True) self.comments = "" # 1st tab self.caller.dlg.ui.groupBoxObjType.setEnabled(True) self.caller.dlg.ui.groupBoxIndicators.setEnabled(True) self.caller.dlg.ui.toolBoxPaths.setEnabled(False) self.caller.dlg.ui.groupBoxPerimetre.setEnabled(True) self.caller.updateSelectedNodes() # 2nd tab self.caller.dlg.ui.groupBoxPTNetworks.setEnabled(True) self.caller.dlg.ui.groupBoxAgencies.setEnabled(True) self.caller.dlg.ui.groupBoxTransportModes.setEnabled(True) self.caller.dlg.ui.groupBoxForcStop.setEnabled(True) self.caller.dlg.ui.groupBoxForcRoute.setEnabled(True) # 3rd tab self.caller.dlg.ui.toolBoxDays.setEnabled(True) self.caller.dlg.ui.toolBoxTime.setEnabled(True) self.caller.dlg.ui.radioButtonTimePoint.setEnabled(False) # 4th tab self.ui.groupBoxSize.setEnabled(False) self.ui.groupBoxColor.setEnabled(False) self.ui.toolBoxDisplay.setItemEnabled(1, False) self.ui.pushButtonReqDisplay.setEnabled(False) self.ui.pushButtonReqRename.setEnabled(False) self.ui.pushButtonReqDelete.setEnabled(False) self.ui.pushButtonSaveComments.setEnabled(False) self.ui.comboBoxPathID.setEnabled(False) self.ui.textEditComments.setPlainText("") elif (indexChosenLine > 0): # 1st tab ##### # "obj_type" field self.caller.dlg.ui.comboBoxObjType.setCurrentIndex( self.caller.modelObjType.match( self.caller.modelObjType.index(0, 1), 0, self.caller.modelReq.record(indexChosenLine).value( "obj_type"))[0].row()) self.obj_def_name = self.caller.modelObjType.record( self.caller.dlg.ui.comboBoxObjType.currentIndex()).value( "def_name") # Disables the groupBox since they should not be modified when an already calculated indicator is displayed self.caller.dlg.ui.groupBoxObjType.setEnabled(False) self.caller.dlg.ui.groupBoxPaths.setEnabled(False) self.caller.dlg.ui.groupBoxIndicators.setEnabled(False) self.caller.dlg.ui.groupBoxPerimetre.setEnabled(False) self.caller.dlg.ui.pushButtonIndicCalculate.setEnabled(False) # "area_type" and "areas" fields if (self.caller.modelReq.record(indexChosenLine).isNull( "zoning_filter") == True): self.caller.dlg.ui.comboBoxZoningFilter.setCurrentIndex(0) else: self.caller.dlg.ui.comboBoxZoningFilter.setCurrentIndex( self.caller.modelZoningSource.match( self.caller.modelZoningSource.index(0, 2), 0, self.caller.modelReq.record(indexChosenLine).value( "zoning_filter"))[0].row()) self.caller.dlg.ui.comboBoxZone.setCurrentIndex(0) if (self.caller.modelReq.record(indexChosenLine).isNull("zones") == False): for id in ((str( self.caller.modelReq.record(indexChosenLine).value( "zones"))).translate(None, "{}").split(",")): self.caller.dlg.ui.comboBoxZone.setCurrentIndex( self.caller.modelZone.match( self.caller.modelZone.index(0, 1), 0, id)[0].row()) # "indics" field for indic in ((str( self.caller.modelReq.record(indexChosenLine).value( "indics"))).translate(None, "{}").split(",")): row = self.caller.modelIndic.match( self.caller.modelIndic.index(0, 1), 0, indic, 1)[0].row() self.caller.dlg.ui.listViewIndic.selectionModel().select( self.caller.modelIndic.index(row, 0), QItemSelectionModel.Select) # origin and destination nodes if (self.caller.modelReq.record(indexChosenLine).isNull( "node_type") == False): self.caller.node_type = self.caller.modelReq.record( indexChosenLine).value("node_type") self.caller.dlg.ui.comboBoxNodeType.setCurrentIndex( self.caller.modelNodeType.match( self.caller.modelNodeType.index(0, 1), 0, self.caller.node_type)[0].row()) i = 0 if (self.caller.node_type == 0): # Stop areas i = 4 if (self.obj_def_name == "paths" or self.obj_def_name == "paths_details"): if (self.caller.modelReq.record(indexChosenLine).isNull( "o_node") == False) and (self.caller.modelReq.record( indexChosenLine).isNull("d_node") == False): self.caller.dlg.ui.comboBoxOrig.setCurrentIndex( self.caller.modelNode.match( self.caller.modelNode.index(0, i), 0, self.caller.modelReq.record(indexChosenLine).value( "o_node"), 1)[0].row()) self.caller.dlg.ui.comboBoxDest.setCurrentIndex( self.caller.modelNode.match( self.caller.modelNode.index(0, i), 0, self.caller.modelReq.record(indexChosenLine).value( "d_node"), 1)[0].row()) if (self.obj_def_name == "paths_tree"): if (self.caller.modelReq.record(indexChosenLine).isNull( "o_node") == False): self.caller.dlg.ui.comboBoxPathsTreeRootNode.setCurrentIndex( self.caller.modelNode.match( self.caller.modelNode.index(0, i), 0, self.caller.modelReq.record(indexChosenLine).value( "o_node"), 1)[0].row()) self.caller.dlg.ui.comboBoxPathsTreeOD.setCurrentIndex(0) elif (self.caller.modelReq.record(indexChosenLine).isNull( "d_node") == False): self.caller.dlg.ui.comboBoxPathsTreeRootNode.setCurrentIndex( self.caller.modelNode.match( self.caller.modelNode.index(0, i), 0, self.caller.modelReq.record(indexChosenLine).value( "d_node"), 1)[0].row()) self.caller.dlg.ui.comboBoxPathsTreeOD.setCurrentIndex(1) if (self.obj_def_name == "comb_paths_trees"): if (self.caller.modelReq.record(indexChosenLine).isNull( "nodes_ag") == False): self.nodes_ag = self.caller.modelReq.record( indexChosenLine).value("nodes_ag") self.caller.dlg.ui.comboBoxNodeAg.setCurrentIndex( self.caller.modelAgreg.match( self.caller.modelAgreg.index(0, 1), 0, self.nodes_ag)[0].row()) else: self.caller.dlg.ui.comboBoxNodeAg.setCurrentIndex(0) if (self.caller.modelReq.record(indexChosenLine).isNull( "o_nodes") == False): nodes = self.caller.modelReq.record(indexChosenLine).value( "o_nodes") elif (self.caller.modelReq.record(indexChosenLine).isNull( "d_nodes") == False): nodes = self.caller.modelReq.record(indexChosenLine).value( "d_nodes") self.root_nodes = [] for node in ((str(nodes)).translate(None, "{}").split(",")): self.root_nodes.append(node) self.caller.updateSelectedNodes() # 2nd tab ##### self.caller.dlg.ui.groupBoxPTNetworks.setEnabled(False) self.caller.dlg.ui.groupBoxAgencies.setEnabled(False) self.caller.dlg.ui.groupBoxTransportModes.setEnabled(False) self.caller.dlg.ui.groupBoxForcStop.setEnabled(False) self.caller.dlg.ui.groupBoxForcRoute.setEnabled(False) # "stop" field if (self.caller.modelReq.record(indexChosenLine).isNull("stop") == False): self.caller.dlg.ui.comboBoxForcStop.setCurrentIndex( self.caller.modelStop.match( self.caller.modelStop.index(0, 1), 0, self.caller.modelReq.record(indexChosenLine).value( "stop"))[0].row()) # "route" field if (self.caller.modelReq.record(indexChosenLine).isNull("route") == False): self.caller.dlg.ui.comboBoxForcRoute.setCurrentIndex( self.caller.modelRoute.match( self.caller.modelRoute.index(0, 1), 0, self.caller.modelReq.record(indexChosenLine).value( "route"))[0].row()) # "agencies" field if (self.caller.modelReq.record(indexChosenLine).isNull("agencies") == False): for i in ((str( self.caller.modelReq.record(indexChosenLine).value( "agencies"))).translate(None, "{}").split(",")): row = self.caller.modelAgencies.match( self.caller.modelAgencies.index(0, 3), 0, i, 1)[0].row() self.caller.dlg.ui.tableViewAgencies.selectionModel( ).select(self.caller.modelAgencies.index(row, 0), QItemSelectionModel.Select) else: self.caller.dlg.ui.tableViewAgencies.clearSelection() # "gtfs_feeds" field if (self.caller.modelReq.record(indexChosenLine).isNull( "gtfs_feeds") == False): for feed in ((str( self.caller.modelReq.record(indexChosenLine).value( "gtfs_feeds"))).translate(None, "{}").split(",")): row = self.modelPTSources.match( self.caller.modelIndic.index(0, 1), 0, feed, 1)[0].row() self.caller.dlg.ui.listViewPTNetworks.selectionModel( ).select(self.modelPTSources.index(row, 0), QItemSelectionModel.Select) # "pt_modes" field if (self.caller.modelReq.record(indexChosenLine).isNull("pt_modes") == False and self.caller.modelReq.record( indexChosenLine).value("pt_modes") != "{}"): for i in ((str( self.caller.modelReq.record(indexChosenLine).value( "pt_modes"))).translate(None, "{}").split(",")): if (i != "NULL"): row = self.caller.modelPTModes.match( self.caller.modelPTModes.index(0, 1), 0, i, 1)[0].row() self.caller.dlg.ui.listViewPTModes.selectionModel( ).select(self.caller.modelPTModes.index(row, 0), QItemSelectionModel.Select) # "i_modes" field for i in ((str( self.caller.modelReq.record(indexChosenLine).value( "i_modes"))).translate(None, "{}").split(",")): if (i != "NULL"): row = self.caller.modelIModes.match( self.caller.modelIModes.index(0, 1), 0, i, 1)[0].row() self.caller.dlg.ui.listViewIModes.selectionModel().select( self.caller.modelIModes.index(row, 0), QItemSelectionModel.Select) # 3rd tab ##### self.caller.dlg.ui.toolBoxDays.setEnabled(False) self.caller.dlg.ui.toolBoxTime.setEnabled(False) self.caller.dlg.ui.radioButtonTimePoint.setEnabled(False) # "day" / "day_type", "per_start", "per_end", "day_ag" fields if (self.caller.modelReq.record(indexChosenLine).isNull("day_type") == True): for day in ((str( self.caller.modelReq.record(indexChosenLine).value( "days"))).translate(None, "{}").split(",")): self.caller.dlg.ui.calendarWidget.setSelectedDate( QDate.fromString(day, "yyyy-MM-dd")) self.caller.dlg.ui.radioButtonPreciseDate.setChecked(True) else: self.caller.dlg.ui.comboBoxDayType.setCurrentIndex( self.caller.modelDayType.match( self.caller.modelDayType.index(0, 1), 0, self.caller.modelReq.record(indexChosenLine).value( "day_type"))[0].row()) self.caller.dlg.ui.radioButtonDayType.setChecked(True) self.caller.dlg.ui.dateEditPerStart.setDate( self.caller.modelReq.record(indexChosenLine).value( "per_start")) self.caller.dlg.ui.dateEditPerEnd.setDate( self.caller.modelReq.record(indexChosenLine).value( "per_end")) self.caller.dlg.ui.comboBoxPerType.setCurrentIndex( self.caller.modelPerType.match( self.caller.modelPerType.index(0, 1), 0, self.caller.modelReq.record(indexChosenLine).value( "per_type"))[0].row()) self.caller.dlg.ui.comboBoxDayAg.setCurrentIndex( self.caller.modelAgreg.match( self.caller.modelAgreg.index(0, 1), 0, self.caller.modelReq.record(indexChosenLine).value( "day_ag"))[0].row()) # "time_start", "time_end", "time_ag", "time_point" fields if (self.caller.modelReq.record(indexChosenLine).isNull( "time_start") == False): self.caller.dlg.ui.timeEditTimeStart.setTime( self.caller.modelReq.record(indexChosenLine).value( "time_start")) self.caller.dlg.ui.radioButtonTimePeriod.setChecked(True) self.caller.dlg.ui.timeEditTimeEnd.setTime( self.caller.modelReq.record(indexChosenLine).value( "time_end")) elif (self.caller.modelReq.record(indexChosenLine).isNull( "constraint_date_after") == False): self.radioButtonTimePoint.setChecked(True) self.timeEditTimePoint.setTime( self.caller.modelReq.record(indexChosenLine).value( "time_point")) self.caller.dlg.ui.comboBoxTimePointConstraint.setCurrentIndex( self.caller.modelTimeConst.match( self.caller.modelTimeConst.index(0, 1), 0, self.caller.modelReq.record(indexChosenLine).value( "time_const"))[0].row()) # This dialog if ((self.obj_def_name == "stop_areas") or (self.obj_def_name == "stops") or (self.obj_def_name == "sections") or (self.obj_def_name == "trips") or (self.obj_def_name == "stops_routes")): self.ui.groupBoxGeoQuery.setEnabled(True) self.ui.groupBoxColor.setEnabled(True) self.ui.groupBoxSize.setEnabled(True) self.ui.comboBoxPathID.setEnabled(False) self.ui.toolBoxDisplay.setItemEnabled(1, False) elif ((self.obj_def_name == "paths") or (self.obj_def_name == "paths_details")): self.ui.groupBoxGeoQuery.setEnabled(False) self.ui.comboBoxPathID.setEnabled(True) if (self.obj_def_name == "paths"): s = "(SELECT 'Tous' as gid, -1 as gid_order) UNION (SELECT gid::character varying, gid FROM indic." + self.caller.modelReq.record( self.ui.comboBoxReq.currentIndex()).value( "layer_name") + ") ORDER BY gid_order" elif (self.obj_def_name == "paths_details"): s = "(SELECT 'Tous' as path_id, -1 as path_id_order) UNION (SELECT distinct path_id::character varying, path_id FROM indic." + self.caller.modelReq.record( self.ui.comboBoxReq.currentIndex()).value( "layer_name") + ") ORDER BY path_id_order" self.caller.modelPathID.setQuery(unicode(s), self.db) self.ui.toolBoxDisplay.setItemEnabled(1, False) elif ((self.obj_def_name == "routes") or (self.obj_def_name == "agencies")): self.ui.groupBoxGeoQuery.setEnabled(False) self.ui.comboBoxPathID.setEnabled(False) self.ui.toolBoxDisplay.setItemEnabled(1, False) elif ((self.obj_def_name == "paths_tree") or (self.obj_def_name == "comb_paths_trees")): self.ui.groupBoxGeoQuery.setEnabled(True) self.ui.groupBoxColor.setEnabled(True) self.ui.groupBoxSize.setEnabled(True) self.ui.comboBoxPathID.setEnabled(False) # Update available derived surface representations self.ui.toolBoxDisplay.setItemEnabled(1, True) self.caller.parent_layer = self.ui.comboBoxReq.currentText() self.refreshDerivedRep() self._slotComboBoxDerivedRepIndexChanged(0) self.ui.pushButtonReqDisplay.setEnabled(True) self.ui.pushButtonReqRename.setEnabled(True) self.ui.pushButtonReqDelete.setEnabled(True) self.ui.pushButtonSaveComments.setEnabled(True) # Update color and size indicators self.updateReqIndicators() # Display comments of the current layer s = "SELECT coalesce(pg_catalog.obj_description((SELECT 'indic." + self.caller.modelReq.record( self.ui.comboBoxReq.currentIndex()).value( "layer_name") + "'::regclass::oid)), '');" q = QtSql.QSqlQuery(unicode(s), self.db) q.next() self.ui.textEditComments.setPlainText(q.value(0))
def affiche(self, idsortie): # Fonction affichant dans QGIS les entités de la sortie en cours, présentes en base. # Pour l'accès à la base de données postgresql/postigs, voir l.52 # Référencer l'arborescence de la TOC (layer tree). Cela permettra de placer la (les) couche(s) où l'on veut à l'intérieur (i.e. en haut, et en dehors d'un groupe) root = QgsProject.instance().layerTreeRoot() # Requête qui sera intégrée dans uri.setDataSource() (cf. paragraphe ci-dessous) reqwhere="""sortie_id="""+str(idsortie)+""" and the_geom IS NOT NULL""" #self.ui.sortie.itemData(self.ui.sortie.currentIndex()) print reqwhere self.querypoly = QtSql.QSqlQuery(self.db) qpoly=u"""select operation_id from bdtravaux.operation_poly where sortie={zr_sortie} order by operation_id limit 1""".format (zr_sortie = idsortie) #self.ui.sortie.itemData(self.ui.sortie.currentIndex()) okpoly = self.querypoly.exec_(qpoly) if not okpoly: QtGui.QMessageBox.warning(self, 'Alerte', u'Requête existence polygones ratée') if self.querypoly.size()>0: print 'taille requete'+str(self.querypoly.size) # SURFACES : Import de la couche de polygoness si des surfaces sont saisies pour cette sortie # Configure le schéma, le nom de la table, la colonne géométrique, et un sous-jeu de données (clause WHERE facultative) self.uri.setDataSource("bdtravaux", "v_bdtravaux_surfaces", "the_geom", reqwhere, "operation_id") # Instanciation de la couche dans qgis self.gestrealpolys=QgsVectorLayer(self.uri.uri(), "gestrealpolys", "postgres") # if self.gestrealpolys.featureCount()>0: #si la couche importée n'est pas vide... # Intégration dans le Map Layer Registry pour pouvoir l'utiliser, MAIS sans l'importer dans l'arborescence (d'où le False) QgsMapLayerRegistry.instance().addMapLayer(self.gestrealpolys, False) # Intégration de la couche dans l'arboresecnce, à l'index 0 (c'est à dire en haut de l'arborescence) root.insertLayer(0, self.gestrealpolys) ## Attribution de COULEURS différentes aux opérations # Récupération des valeurs uniques du champ qui servira de base à la symbologie layer=self.gestrealpolys field_index = layer.dataProvider().fieldNameIndex('typ_operat') unique_values = layer.uniqueValues(field_index) # Définit une correspondance: valeur -> (couleur) au moyen d'un dictionnaire et de la fonction clr_hasard # Création du dictionnaire au moyen d'une compréhension de dictionnaire operations={valeurunique : self.clr_hasard() for valeurunique in unique_values} # Crée une catégorie pour chaque item dans operations, puis les groupe en une liste (operations) categories = [] for nom_opera, couleur in operations.items(): symbol = QgsSymbolV2.defaultSymbol(layer.geometryType()) symbol.setColor(QtGui.QColor(couleur)) #création de la catégorie. 1er param : l'attribut / 2ème : le symbole à appliquer / 3ème : l'étiquet ds tble matières category = QgsRendererCategoryV2(nom_opera, symbol,nom_opera) categories.append(category) # Crée le renderer et l'assigne à la couche expression = 'typ_operat' # nom du champ renderer = QgsCategorizedSymbolRendererV2(expression, categories) layer.setRendererV2(renderer) layer.setLayerTransparency(50) else: print 'couche de surfaces vide' # LIGNES : Import de la couche de lignes si des linéaires sont saisis pour cette sortie self.querylgn = QtSql.QSqlQuery(self.db) qlgn=u"""select operation_id from bdtravaux.operation_lgn where sortie={zr_sortie} order by operation_id limit 1""".format (zr_sortie = idsortie ) #self.ui.sortie.itemData(self.ui.sortie.currentIndex()) oklgn = self.querylgn.exec_(qlgn) if not oklgn: QtGui.QMessageBox.warning(self, 'Alerte', u'Requête existence lignes ratée') if self.querylgn.size()>0: self.uri.setDataSource("bdtravaux", "v_bdtravaux_lignes", "the_geom", reqwhere, "operation_id") self.gestreallgn=QgsVectorLayer(self.uri.uri(), "gestreallgn", "postgres") # if self.gestreallgn.featureCount()>0: QgsMapLayerRegistry.instance().addMapLayer(self.gestreallgn, False) root.insertLayer(0, self.gestreallgn) layer=self.gestreallgn field_index = layer.dataProvider().fieldNameIndex('typ_operat') unique_values = layer.uniqueValues(field_index) operations={valeurunique : self.clr_hasard() for valeurunique in unique_values} categories = [] for nom_opera, couleur in operations.items(): symbol = QgsSymbolV2.defaultSymbol(layer.geometryType()) symbol.setColor(QtGui.QColor(couleur)) category = QgsRendererCategoryV2(nom_opera, symbol,nom_opera) categories.append(category) expression = 'typ_operat' renderer = QgsCategorizedSymbolRendererV2(expression, categories) layer.setRendererV2(renderer) else : print 'couche de linéaires vide' # POINTS : Import de la couche de points si des ponctuels sont saisis pour cette sortie self.querypts = QtSql.QSqlQuery(self.db) qpts=u"""select operation_id from bdtravaux.operation_pts where sortie={zr_sortie} order by operation_id limit 1""".format (zr_sortie = idsortie) #self.ui.sortie.itemData(self.ui.sortie.currentIndex()) okpts = self.querypts.exec_(qpts) if not okpts: QtGui.QMessageBox.warning(self, 'Alerte', u'Requête existence points ratée') if self.querypts.size()>0: self.uri.setDataSource("bdtravaux", "v_bdtravaux_points", "the_geom", reqwhere, "operation_id") self.gestrealpts=QgsVectorLayer(self.uri.uri(), "gestrealpts", "postgres") #if self.gestrealpts.featureCount()>0: QgsMapLayerRegistry.instance().addMapLayer(self.gestrealpts, False) root.insertLayer(0, self.gestrealpts) layer=self.gestrealpts field_index = layer.dataProvider().fieldNameIndex('typ_operat') unique_values = layer.uniqueValues(field_index) operations={valeurunique : self.clr_hasard() for valeurunique in unique_values} categories = [] for nom_opera, couleur in operations.items(): symbol = QgsSymbolV2.defaultSymbol(layer.geometryType()) symbol.setColor(QtGui.QColor(couleur)) category = QgsRendererCategoryV2(nom_opera, symbol,nom_opera) categories.append(category) expression = 'typ_operat' renderer = QgsCategorizedSymbolRendererV2(expression, categories) layer.setRendererV2(renderer) else : print 'couche de ponctuels vide'
def populateDemo(self, omitted_modules=[]): ''' checks connection is to openmolar_demo, and if so, adds demo data to the tables. ''' if not self.isOpen(): return (False, _("no connection")) LOGGER.info("POPULATING DATABASE WITH DEMO DATA") ## iterate over the ORM modules ## order is important (foreign keys etc) for module in ADMIN_MODULES: if module in omitted_modules: LOGGER.info("Ommitting module %s" % module.__name__) continue try: if not self.isOpen(): return (False, _("no connection")) builder = module.DemoGenerator(self) logged = False total_number_of_queries = builder.length number_of_queries_executed = 0 for query, values in builder.demo_queries(): if not logged: LOGGER.info(u"%s..." % query[:77]) logged = True q_query = QtSql.QSqlQuery(self) q_query.prepare(query) for value in values: q_query.addBindValue(value) q_query.exec_() if q_query.lastError().isValid(): error = q_query.lastError().text() self.emit_(QtCore.SIGNAL("Query Error"), error) LOGGER.error(error) break number_of_queries_executed += 1 progress = int(number_of_queries_executed / total_number_of_queries * 100) if progress % 10 == 0: self.emit_(QtCore.SIGNAL("demo progress"), module, progress) else: #some modules have no queries self.emit_(QtCore.SIGNAL("demo progress"), module, 100) except Exception as e: self.emit_( QtCore.SIGNAL("Query Error"), '''Error installing demo data from module %s<hr />%s''' % (module.__name__, e)) LOGGER.error('ERROR INSTALLING DATA from module %s' % module.__name__) LOGGER.exception('CRITICAL ERROR') self.emit_(QtCore.SIGNAL("demo install complete")) LOGGER.info("DEMO INSTALL COMPLETE") return True
def add_table_names(self, connection): result = QtSql.QSqlQuery('SELECT tablename FROM pg_tables', connection) while result.next(): self.add(result.value(0).toString()) self.prepare()
def sauvSaisie(self): if self.ui.cbx_habref.itemData(self.ui.cbx_habref.currentIndex())=='0': return self.erreurSaisieBase = '0' #copie des entités sélectionnées dans une couche "memory". Evite les problèmes avec les types de couches "non éditables" (comme les GPX). coucheactive=self.iface.activeLayer() # On vérifie que les entités sélectionnées dans la couche active sont bien des polygones (et non des lignes ou des points) entselect=[QgsGeometry(feature.geometry()) for feature in coucheactive.selectedFeatures()] if entselect[0].type() == QGis.Polygon: typegeom='Polygon' # geom = self.transfoPoly(entselect) # print "geom=" + str(geom.exportToWkt()) else: QtGui.QMessageBox.warning(self, 'Alerte', u'Les entités sélectionnées ne sont pas des polygones') self.close return # Création de la couche en mémoire "memlayer" et début de la session d'édition if coucheactive.crs().authid() == u'EPSG:4326': memlayer=QgsVectorLayer("{zr_typegeom}?crs=epsg:4326".format(zr_typegeom = typegeom), "memlayer", "memory") if coucheactive.crs().authid() == u'EPSG:2154': memlayer=QgsVectorLayer("{zr_typegeom}?crs=epsg:2154".format(zr_typegeom = typegeom), "memlayer", "memory") else : QtGui.QMessageBox.warning(self, 'Alerte', u'Les seules projections supportées sont le WGS84 et le Lambert93. Merci de vérifier la projection de votre couche SIG') return QgsMapLayerRegistry.instance().addMapLayer(memlayer, False) root = QgsProject.instance().layerTreeRoot() memlayerNode = QgsLayerTreeLayer(memlayer) root.insertChildNode(0, memlayerNode) self.iface.setActiveLayer(memlayer) memlayer.startEditing() # Pour chaque entité sélectionnée, si elle est multipartie, on ajoute chacune de ses parties individuellement à la couche memlayer. # Sinon, on l'ajoute directement à "memlayer". Puis, on clot la session d'édition. for feature in coucheactive.selectedFeatures() : geom = feature.geometry() temp_feature = QgsFeature(feature) # check if feature geometry is multipart if geom.isMultipart(): # if feature is multipart creates a new feature using the geometry of each part for part in geom.asGeometryCollection (): temp_feature.setGeometry(part) memlayer.dataProvider().addFeatures([temp_feature]) memlayer.updateExtents() # if feature is singlepart, simply adds it to the layer memory else : temp_feature.setGeometry(geom) memlayer.dataProvider().addFeatures([temp_feature]) memlayer.updateExtents() memlayer.commitChanges() print "memlayercount="+str(memlayer.featureCount()) #on sélectionne toutes les entités de memlayer pour en faire une liste de géométries, qui sera saisie en base. memlayer.selectAll() geomlis = [QgsGeometry(feature.geometry()) for feature in memlayer.selectedFeatures()] #geomlist = QgsGeometry.fromMultiPolygon([poly.asPolygon() for poly in geomlis]) --- transformation en multipolygone for entite in geomlis : #export de la géométrie en WKT et transformation de la projection si les données ne sont pas saisies en Lambert 93 if memlayer.crs().authid() == u'EPSG:2154': thegeom='st_setsrid(st_geometryfromtext (\'{zr_geom}\'), 2154)'.format(zr_geom=entite.exportToWkt()) elif memlayer.crs().authid() == u'EPSG:4326': thegeom='st_transform(st_setsrid(st_geometryfromtext (\'{zr_geom}\'),4326), 2154)'.format(zr_geom=entite.exportToWkt()) else : print u'La projection de la couche active n\'est pas supportée' #gestion de l'identifiant id_mosaik, servant à regrouper les enregistrements appartenant à une même mosaïque d'habitats pourcent = int(self.ui.cbx_pourcent.itemText(self.ui.cbx_pourcent.currentIndex())) if pourcent > 99 : print ">99" id_mosaik = 0 else : querymosaik = QtSql.QSqlQuery(self.db) qmosaik = u"""SELECT id_hab_ce, annee, pourcent,the_geom, plantation, id_mosaik FROM bd_habnat.t_ce_saisie WHERE the_geom = {zr_thegeom} AND annee = '{zr_annee}' AND plantation = 'f' """.format (\ zr_thegeom = thegeom,\ zr_annee = self.ui.cbx_annee.itemText(self.ui.cbx_annee.currentIndex())) ok = querymosaik.exec_(qmosaik) if not ok: QtGui.QMessageBox.warning(self, 'Alerte', u'Requête Mosaik ratée') if querymosaik.size() > 0 : print "on est deja dans la mosaique" sumprct = 0 while querymosaik.next() : sumprct += int(querymosaik.value(2)) sumprct += pourcent if sumprct > 100 : QtGui.QMessageBox.warning(self, 'Alerte', u'La somme des pourcentages des habitats dépasse 100 % dans la mosaïque') return querymosaik.first() id_mosaik = querymosaik.value(5) else : querybiggestid = QtSql.QSqlQuery(self.db) qbiggestid = u"""SELECT id_mosaik FROM bd_habnat.t_ce_saisie ORDER BY id_mosaik DESC LIMIT 1""" ok = querybiggestid.exec_(qbiggestid) if not ok: QtGui.QMessageBox.warning(self, 'Alerte', u'Requête PlusGrandIdMosaik ratée') querybiggestid.next() print "debut de la mosaique" if self.ui.chx_plantation.isChecked == True : id_mosaik = 0 else : id_mosaik = int(querybiggestid.value(0))+1 self.habref = self.ui.cbx_habref.itemData(self.ui.cbx_habref.currentIndex()) # si ref = cbnbl : recup rareté, menace et intérêt patri en fonction du taxon sélectionné par l'utilisateur if self.habref == 'cbnbl': queryrarmen = QtSql.QSqlQuery(self.db) qrarmen = u"""SELECT rarete, menace, interetpatr FROM bd_habnat.t_liste_ref_cbnbl_v12 WHERE hab_lat = '{zr_hablat}'""".format (\ zr_hablat= self.ui.cbx_hablat.itemText(self.ui.cbx_hablat.currentIndex()).replace("\'","\'\'")) okrarmen=queryrarmen.exec_(qrarmen) if not okrarmen : QtGui.QMessageBox.warning(self, 'Alerte', u'Requête RarMen ratée') queryrarmen.next() self.rarete = queryrarmen.value(0) self.menace = queryrarmen.value(1) self.interetpatr = queryrarmen.value(2) # construction de pat_cen en fonction de intérêt patri querypatcen = QtSql.QSqlQuery(self.db) qpatcen = u""" SELECT CASE WHEN cbnbl.interetpatr IN ('Oui','#') THEN True ELSE False END FROM bd_habnat.t_liste_ref_cbnbl_v12 cbnbl WHERE hab_lat = '{zr_hablat}'""".format (\ zr_hablat= self.ui.cbx_hablat.itemText(self.ui.cbx_hablat.currentIndex()).replace("\'","\'\'")) okpatcen = querypatcen.exec_(qpatcen) if not okpatcen : QtGui.QMessageBox.warning(self, 'Alerte', u'Requête PatCen ratée') querypatcen.next() self.pat_cen = querypatcen.value(0) # si ref != cbnbl : rareté, menace, intérêt patri = '/' et pat_cen = False else : self.rarete = '/' self.menace = '/' self.interetpatr = '/' self.pat_cen = 'false' # aller chercher hab_cod dans la cbx "lat" si self.habref = cbnbl, sinon dans cbx "fr". # aller chercher code_syntax, code_cahab, code_eunis et code_corine dans itemData cbx_hablat ou cbx_habfr, selon le référentiel choisi if self.habref == 'cbnbl': self.habcod = self.ui.cbx_hablat.itemData(self.ui.cbx_hablat.currentIndex()) v_syntax = self.habcod v_cahab = '' v_eunis ='' v_corine ='' else : self.habcod = self.ui.cbx_habfr.itemData(self.ui.cbx_habfr.currentIndex()) if self.habref == 'eur27' : v_syntax = '' v_cahab = self.habcod v_eunis = '' v_corine = '' elif self.habref == 'eunis' : v_syntax = '' v_cahab = '' v_eunis = self.habcod v_corine = '' elif self.habref == 'corine' : v_syntax = '' v_cahab = '' v_eunis = '' v_corine = self.habcod # récupération du code N2000 par txt_codeeur27 s'il est renseigné, sinon cbx_eur27_mep if self.ui.txt_codeeur27.text() != '': print 'txt_codeeur27 renseigne' self.codeeur27 = self.ui.txt_codeeur27.text() else : if self.ui.cbx_eur27_mep.currentIndex() == 0 : self.codeeur27 = '' else : self.codeeur27 = self.ui.cbx_eur27_mep.itemText(self.ui.cbx_eur27_mep.currentIndex()) #lancement de la requête SQL qui introduit les données géographiques et du formulaire dans la base de données. querysauvhab = QtSql.QSqlQuery(self.db) query = u"""INSERT INTO bd_habnat.t_ce_saisie(codesite, auteur, annee, hab_ref, hab_cod, hab_lat, hab_fr, hab_comment, code_syntax, code_cahab, code_eur27, code_eunis, code_corine, pourcent, rarete, menace, patrimoine, pat_cen, surf_tot, the_geom, plantation, id_mosaik, hab_etat, faciesa, date_crea_poly) VALUES ('{zr_codesite}', '{zr_auteur}', '{zr_annee}', '{zr_habref}','{zr_habcod}', '{zr_hablat}', '{zr_habfr}', '{zr_habcomment}', '{zr_codesyntax}', '{zr_codecahab}', '{zr_codeeur27}', '{zr_codeeunis}', '{zr_codecorine}', '{zr_pourcent}', '{zr_rarete}', '{zr_menace}', '{zr_interetpatr}', {zr_pat_cen}, st_area({zr_thegeom}), {zr_thegeom}, {zr_plantation}, {zr_idmosaik}, '{zr_habetat}', '{zr_faciesa}', current_date)""".format (\ zr_codesite = self.ui.cbx_codesite.itemData(self.ui.cbx_codesite.currentIndex()),\ zr_auteur = self.ui.cbx_auteur.itemData(self.ui.cbx_auteur.currentIndex()),\ zr_annee = self.ui.cbx_annee.itemText(self.ui.cbx_annee.currentIndex()),\ zr_habref = self.ui.cbx_habref.itemData(self.ui.cbx_habref.currentIndex()),\ zr_habcod = self.habcod,\ zr_hablat = self.ui.cbx_hablat.itemText(self.ui.cbx_hablat.currentIndex()).replace("\'","\'\'"),\ zr_habfr = self.ui.cbx_habfr.itemText(self.ui.cbx_habfr.currentIndex()).replace("\'","\'\'"),\ zr_habcomment = self.ui.txt_comment.toPlainText().replace("\'","\'\'"),\ zr_codesyntax = v_syntax,\ zr_codecahab = v_cahab,\ zr_codeeur27 = self.codeeur27,\ zr_codeeunis = v_eunis,\ zr_codecorine = v_corine,\ zr_pourcent = self.ui.cbx_pourcent.itemText(self.ui.cbx_pourcent.currentIndex()),\ zr_rarete = self.rarete,\ zr_menace = self.menace,\ zr_interetpatr = self.interetpatr,\ zr_pat_cen = self.pat_cen,\ zr_thegeom = thegeom,\ zr_plantation = str(self.ui.chx_plantation.isChecked()).lower(),\ zr_idmosaik = id_mosaik,\ zr_habetat = ";".join([unicode(x.text()) for x in self.ui.lst_evol.selectedItems()]),\ zr_faciesa = self.ui.txt_faciesa.text().replace("\'","\'\'")) ok = querysauvhab.exec_(query) if not ok: QtGui.QMessageBox.warning(self, 'Alerte', u'Requête sauver Ope ratée') self.erreurSaisieBase = '1' self.iface.setActiveLayer(coucheactive) QgsMapLayerRegistry.instance().removeMapLayer(memlayer.id()) # Réinitialiser certains contrôles self.ui.lst_evol.clearSelection() self.ui.txt_faciesa.setText('') self.ui.txt_comment.setText('') self.ui.cbx_eur27_mep.setCurrentIndex(0) if self.erreurSaisieBase == '0': QtGui.QMessageBox.information(self, 'Information', u'Données correctement saisies dans la base') else : QtGui.QMessageBox.warning(self, 'Alerte', u'Il y a eu une erreur lors de la saisie. Données non saisies en base.') self.close()
def save_(self): """ This function is huge... This saves all the data associated with the current part. It starts by saving the basic info to Doyle's database. This is the slowest part because of the connection problems. After that, if all the info is filled out, it saves the machining specific data to Riverview's database. To do this it has to look up the machine and material to get the right id numbers. If the user enters an invalid machine, 'N/A' is substituted. For material, the new material is added to the system and the new id is retrieved and used. """ QtGui.QApplication.setOverrideCursor(QtGui.QCursor(QtCore.Qt.WaitCursor)) # Open a connection to Doyle's database. self.dbw, ok = write_connection(self) save_qry = QtSql.QSqlQuery() while ok: descp = prepare_string(self.description.text()) dest = prepare_string(self.destination.text()) notes = prepare_string(self.notes.toPlainText()) # User must at least have a description and destination to save. if descp != "" and dest != "": dest_qry = "Select Dest_ID from inventorysystem.Destination_tbl where Dest_Desc = '{0}'".format(dest) if save_qry.exec_(dest_qry): # Get the destination id. if save_qry.first(): dest = save_qry.value(0).toString() else: dest = '7' else: db_err(save_qry) dest = '7' qry = ("update inventorysystem.Parts_tbl set Part_Desc='{0}', Part_Notes='{1}',destination={2} where " "Part_ID = {3}").format(descp, notes, dest, self.partId.text()) write_qry = QtSql.QSqlQuery(self.dbw) if not write_qry.exec_(qry): db_err(write_qry) break del self.dbw mach = self.machine.text() cycle = self.cycleTime.text() load = self.loadTime.text() setup = self.setupTime.text() mat = self.material.text() clamp = self.clamp.text() blank_length = self.blankLength.text() bar = self.barPull.text() square = self.squareSet.text() pos_stop = self.posStop.text() proc = self.routing.text() prog_file = self.program.text() if mach != "" and mat != "" and proc != "": mach_qry = "Select id from machines where name = '{0}'".format(mach) if save_qry.exec_(mach_qry): if save_qry.first(): mach = save_qry.value(0).toString() else: mach = "14" else: db_err(save_qry) mach = "14" mat_qry = "Select id from material where material = '{0}'".format(mat) if save_qry.exec_(mat_qry): if save_qry.first(): mat = save_qry.value(0).toString() else: if save_qry.exec_("insert into material set material='{0}'".format(mat)): mat = save_qry.lastInsertId().toString() else: db_err(save_qry) mat = "17" else: db_err(save_qry) mat = "17" qry2 = ("machine={0}, cycleTime=time_to_Sec('{1}'), loadTime=time_to_Sec('{2}'), " "setupTime=time_to_Sec('{3}'), material={4}, clampPSI={5}, blankLength={6}, barPull={7}, " "squareSet={8}, posStop={9}, Process='{10}', fileName='{11}'" ).format(mach, cycle, load, setup, mat, clamp, blank_length, bar, square, pos_stop, proc, prog_file) if self.index.text() == '0': part_id = self.partId.text() qry2 = ("Insert into setupInfo set {0}, partId={1}".format(qry2, part_id)) else: qry2 = ("Update setupInfo set {0} where id={1}".format(qry2, self.index.text())) local_qry = QtSql.QSqlQuery() if not local_qry.exec_(qry2): db_err(local_qry) self.load_data(self.partId.text()) QtGui.QApplication.restoreOverrideCursor()
def __init__(self, iface): """Constructor.""" QtGui.QDialog.__init__(self) self.ui = Ui_bdhabnat_dialog() self.ui.setupUi(self) # Référencement de iface dans l'interface (iface = interface de QGIS) self.iface = iface self.canvas = self.iface.mapCanvas() # Connexion à la base de données. DB type, host, user, password... self.db = QtSql.QSqlDatabase.addDatabase("QPSQL") # QPSQL = nom du pilote postgreSQL self.db.setHostName("192.168.0.10") self.db.setDatabaseName("sitescsn") self.db.setUserName("postgres") self.db.setPassword("postgres") ok = self.db.open() if not ok: QtGui.QMessageBox.warning(self, 'Alerte', u'La connexion est échouée'+self.db.hostName()) # Remplir la combobox "cbx_codesite" avec les codes et noms de sites issus de la table "t_sitescen" query_codesite = QtSql.QSqlQuery(self.db) if query_codesite.exec_('select idchamp, codesite, nomsite from sites_cen.t_sitescen order by codesite'): while query_codesite.next(): self.ui.cbx_codesite.addItem(query_codesite.value(1) + " " + query_codesite.value(2), query_codesite.value(1) ) # Remplir la combobox "cbx_auteurs" avec les noms et prénoms issus de la table "t_liste_obsv" query_obsv = QtSql.QSqlQuery(self.db) if query_obsv.exec_('select id_obsv, nom_obsv, prenom_obsv, initiale_obsv from bd_habnat.t_liste_obsv order by nom_obsv, prenom_obsv'): while query_obsv.next(): self.ui.cbx_auteur.addItem(query_obsv.value(1) + " " + query_obsv.value(2), query_obsv.value(3) ) # Combobox cbx_annee : Affecter l'année en cours par défaut en fonction de la date (changement d'année au 28/2/N) if datetime.now().strftime('%m/%d') < '04/02' : self.ui.cbx_annee.setCurrentIndex(self.ui.cbx_annee.findText((str(int(datetime.now().strftime('%Y'))-1)), QtCore.Qt.MatchStartsWith)) else: self.ui.cbx_annee.setCurrentIndex(self.ui.cbx_annee.findText((datetime.now().strftime('%Y')), QtCore.Qt.MatchStartsWith)) # Remplir la combobox "cbx_habref" avec les noms de référentiels issus de la table "t_liste_ref" query_ref = QtSql.QSqlQuery(self.db) if query_ref.exec_('select id_ref, nom_ref, code_ref from bd_habnat.t_liste_ref order by id_ref'): while query_ref.next(): self.ui.cbx_habref.addItem(query_ref.value(1), query_ref.value(2) ) self.ui.cbx_habref.model().item(0).setEnabled(False) # Remplir la combobox "cbx_eur27_mep" avec le champs hab_cod de la table "t_liste_ref_eur27" query_eur27 = QtSql.QSqlQuery(self.db) if query_eur27.exec_('select hab_cod from bd_habnat.t_liste_ref_eur27 order by hab_cod'): while query_eur27.next(): self.ui.cbx_eur27_mep.addItem(query_eur27.value(0), query_eur27.value(0) ) # Désactiver le bouton "OK" tant qu'on n'a pas choisi au moins un référentiel. self.ui.buttonBox.setEnabled(False) # Connexions signaux - slots self.ui.cbx_habref.currentIndexChanged.connect(self.listesref) self.ui.cbx_habfr.currentIndexChanged.connect(self.coreur27) self.ui.buttonBox.accepted.connect(self.sauvSaisie) self.ui.buttonBox.rejected.connect(self.close) self.ui.chx_plantation.stateChanged.connect(self.plantation) self.ui.cbx_hablat.currentIndexChanged.connect(self.listefran) self.ui.cbx_habref.currentIndexChanged.connect(self.activButton)
def setFirstName(self, personId, firstName): query = QtSql.QSqlQuery() query.prepare('update person set firstname = ? where id = ?') query.addBindValue(firstName) query.addBindValue(personId) return query.exec_()
def insert_failure(self, failure): """Inserts a failure into the database""" QtSql.QSqlQuery( '''INSERT INTO failures VALUES ("%d", "%s", "%s", %d)''' % (int(failure.talkId), failure.comment, failure.indicator, failure.release)) log.info("Failure added: %s - %s" % (failure.talkId, failure.comment))