def recuperar_datos(self): query = QSqlQuery() sql = "select cliente_id,fecha,hora,notas from ventas where venta_id=%d" % self.id if not query.exec_(sql): print "Error al obtener venta." return g = lambda x:query.value(x).toString() query.next() self.cliente_id = int(g(0)) self.fecha = g(1) self.hora = g(2) self.notas = g(3) self.productos = {} sql = """ select codigo,cantidad,descuento from detalle_ventas where venta_id=%d""" % self.id query.exec_(sql) for i in range(query.size()): query.next() asd = str(g(0)) self.productos[asd] = producto(g(0)) self.productos[asd].cantidad = int(g(1)) self.productos[asd].descuento = int(g(2)) self.productos[asd].actualizar_subtotal() self.actualizar_total()
def execSql( self, strQuery ): strQuery = QString(strQuery) query = QSqlQuery( self.sqlDatabase() ) if query.exec_( strQuery ) != True: print "Failed execute ", strQuery.toLatin1().data(), ' error:' , query.lastError().databaseText().toLatin1().data() return query
def getStructureDict(self): try: self.checkAndOpenDb() except: return dict() classDict = dict() sql = self.gen.getStructure(self.getDatabaseVersion()) query = QSqlQuery(sql, self.db) while query.next(): className = str(query.value(0)) classSql = str(query.value(1)) if className.split('_')[0] == 'complexos' or className.split('_')[-1] in ['p','l','a']: if className not in classDict.keys(): classDict[className]=dict() classSql = classSql.split(className)[1] sqlList = classSql.replace('(','').replace(')','').replace('\"','').replace('\'','').split(',') for s in sqlList: fieldName = str(s.strip().split(' ')[0]) classDict[className][fieldName]=fieldName if 'GEOMETRY' in classDict[className].keys(): classDict[className]['GEOMETRY'] = 'geom' if 'OGC_FID' in classDict[className].keys(): classDict[className]['OGC_FID'] = 'id' return classDict
def init( self ): try: if not self.database.isOpen(): if not self.database.open(): raise UserWarning( u"No se pudo abrir la conexión "\ + "con la base de datos" ) query = QSqlQuery( """ SELECT * FROM cuentascontables c jOIN cuentasxdocumento cd ON c.idcuenta = cd.idcuenta ; """ ) if not query.exec_(): raise UserWarning( "No se pudo consultar el catalogo de cuentas" ) self.inicial = query.size()==0 except UserWarning as inst: logging.error( unicode( inst ) ) self.movimientosDisponibles(False) finally: if self.database.isOpen(): self.database.close() self.btnMovements.setText("Balance \n Inicial" if self.inicial else "Ajustes Contables" ) self.status = True
def __init__( self, parentId, parent = None ): super( AccountsModel, self ).__init__( parent ) query = """ SELECT cc.codigo, cc.descripcion, cc.esdebe, COUNT(ch.idcuenta) nhijos, SUM(IFNULL(monto,0)) monto, cc.padre, cc.idcuenta FROM cuentascontables cc LEFT JOIN cuentascontables ch ON cc.idcuenta = ch.padre LEFT JOIN cuentasxdocumento cxd ON cc.idcuenta = cxd.idcuenta WHERE cc.padre = %d GROUP BY cc.idcuenta """ % parentId query = QSqlQuery( query ) query.exec_() query.first() self.rootItem = Account( QModelIndex(), parentId, query.value( CODIGO ).toString(), query.value( DESCRIPCION ).toString(), Decimal( query.value( MONTO ).toString() ), query.value( ESDEBE ).toInt()[0], query.value( HIJOS ).toInt()[0] )
def findEPSG(self): sql = self.gen.getSrid() query = QSqlQuery(sql, self.db) srids = [] while query.next(): srids.append(query.value(0)) return srids[0]
def isComplexClass(self, className): #getting all complex tables query = QSqlQuery(self.gen.getComplexTablesFromDatabase(), self.db) while query.next(): if query.value(0) == className: return True return False
def accept(self): rows = self.sModel.rowCount() cols = self.sModel.columnCount() model = self.sModel mlqry = dict() qry = None for r in range(rows): record = model.record(r) for c in range(1, cols): if record.value(c).toString(): if not qry: qry = "(" elif c > 1 and qry : qry = "%s AND" % qry qry = "%s %s %s" % (qry, record.fieldName(c), record.value(c).toString()) if qry : mlqry[r] = "%s )" % qry qry = "" for q in mlqry.keys(): if not qry: qry = mlqry[q] continue qry = "%s OR %s" % (qry, mlqry[q]) self.resultFilter = qry query = QSqlQuery() query.exec_("DROP TABLE filtertable") if self.dbi: del self.dbi QDialog.accept(self)
def creaStrutturaDB(self): query = QSqlQuery() if not ("magamaster" in self.db.tables()): if not query.exec_("""CREATE TABLE magamaster ( id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, scaff VARCHAR(10) NOT NULL)"""): QMessageBox.warning(self, "Magazzino", QString("Creazione tabella fallita!")) return False if not ("magaslave" in self.db.tables()): if not query.exec_("""CREATE TABLE magaslave ( id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, datains DATE NOT NULL, abbi VARCHAR(50), angro VARCHAR(50), desc VARCHAR(100), qt INTEGER NOT NULL DEFAULT '1', imp DOUBLE NOT NULL DEFAULT '0.0', equiv VARCHAR(100), mmid INTEGER NOT NULL, fatt VARCHAR(50), note VARCHAR(200), FOREIGN KEY (mmid) REFERENCES magamaster)"""): QMessageBox.warning(self, "Magazzino", QString("Creazione tabella fallita!")) return False QMessageBox.information(self, "Magazzino", QString("Database Creato!")) return True
def insertNewRecord(self, data_dict): query = QSqlQuery(self.handle) if query.prepare(self.insert_query % data_dict): query.bindValue(":photo", data_dict['photo']) return query.exec_() else: return False
def movDeposito( iddoc, deposito, ctabanco ): ''' MOVIMIENTOS CONTABLE PARA UNA ENTRADA COMPRA (-)deposito > sale de Caja genera:id=5, cod=110 001 001 000 (+)deposito > entra a cuenta del banco ctabanco @param iddoc: El id del documento que genera estos movimientos @type iddoc: int @param deposito: TODO @type deposito: Decimal @param ctabanco: TODO @type ctabanco: Decimal ''' iddoc = str( iddoc ) total = deposito.to_eng_string() ctabanco = str( ctabanco ) query = QSqlQuery() query.prepare( "INSERT INTO cuentasxdocumento (idcuenta,iddocumento,monto) values " + "(" + CAJAGENERAL + "," + iddoc + ",-" + total + ")," + "(" + ctabanco + "," + iddoc + "," + total + ")" ) if not query.exec_(): print( iddoc ) print( query.lastError().text() ) raise Exception( "NO SE PUDIERON INSERTAR LAS CUENTAS CONTABLES" )
def createRole(self, role, dict): try: self.checkAndOpenDb() except: return #making this so the instaciated permissions stay with different names uuid = str(uuid4()).replace('-', '_') role += '_'+uuid sql = self.gen.createRole(role, dict) split = sql.split(';') query = QSqlQuery(self.db) #try to revoke the permissions try: self.dropRole(role) except: pass for inner in split: if not query.exec_(inner): if '42710' in query.lastError().text(): #In this case the role is already created (duplicate object error). We just need to proceed executing the grants. continue else: raise Exception(self.tr('Problem assigning profile: ') +role+'\n'+query.lastError().text())
def makeValueRelationDict(self, valueDict): ret = dict() codes = valueDict['FilterExpression'].replace('code in (', '').replace(')','').split(',') keyColumn = valueDict['Key'] valueColumn = valueDict['Value'] table = valueDict['Layer'][:-17]#removing the date-time characters uri = QgsDataSourceURI(self.currLayer.dataProvider().dataSourceUri()) if uri.host() == '': db = QSqlDatabase('QSQLITE') db.setDatabaseName(uri.database()) else: db = QSqlDatabase('QPSQL') db.setHostName(uri.host()) db.setPort(int(uri.port())) db.setDatabaseName(uri.database()) db.setUserName(uri.username()) db.setPassword(uri.password()) if not db.open(): db.close() return ret in_clause = ','.join(map(str, codes)) query = QSqlQuery('select code, code_name from dominios.%s where code in (%s)' % (table, in_clause), db) while query.next(): code = str(query.value(0)) code_name = query.value(1) ret[code_name] = code db.close() return ret
def getDomainDict(self): try: self.checkAndOpenDb() except: return dict() if self.getDatabaseVersion() == '2.1.3': schemaList = ['cb', 'complexos', 'dominios'] elif self.getDatabaseVersion() == 'FTer_2a_Ed': schemaList = ['pe','ge','ct', 'complexos'] else: QgsMessageLog.logMessage(self.tr('Operation not defined for this database version!'), "DSG Tools Plugin", QgsMessageLog.CRITICAL) return sql = self.gen.validateWithDomain(schemaList) query = QSqlQuery(sql, self.db) classDict = dict() domainDict = dict() while query.next(): schemaName = str(query.value(0)) className = str(query.value(1)) attName = str(query.value(2)) domainName = str(query.value(3)) domainTable = str(query.value(4)) domainQuery = str(query.value(5)) cl = schemaName+'.'+className query2 = QSqlQuery(domainQuery,self.db) while query2.next(): value = int(query2.value(0)) classDict = self.utils.buildNestedDict(classDict,[str(cl),str(attName)],[value]) return classDict
def setData( self, column, value ): value = value.toList() if column < 0: return False if column == 0: try: if not QSqlDatabase.database().isOpen(): if not QSqlDatabase.database().open(): raise Exception( "No se pudo conectar con la base de datos" ) query = QSqlQuery() if not query.prepare( """ UPDATE categorias SET nombre = :nombre WHERE idcategoria = %d """ % self.itemData[1] ): raise Exception( "No se pudo preparar la consulta para actualizar la categoria" ) query.bindValue( ":nombre", value[column].toString().strip() ) if not query.exec_(): raise Exception( "No se pudo actualizar la categoria" ) except Exception as inst: logging.error( unicode( inst ) ) return False self.itemData[column] = value[column] return True
def findEPSG(self, db): gen = self.factory.createSqlGenerator(self.isSpatialiteDB(db)) sql = gen.getSrid() query = QSqlQuery(sql, db) srids = [] while query.next(): srids.append(query.value(0)) return srids[0]
def getDatabaseVersion(self, db): gen = self.factory.createSqlGenerator(self.isSpatialiteDB(db)) sqlVersion = gen.getEDGVVersion() queryVersion = QSqlQuery(sqlVersion, db) version = '2.1.3' while queryVersion.next(): version = queryVersion.value(0) return version
def tableHasField(dbhandle, table, field): """does the table contain a column named field?""" query = QSqlQuery(dbhandle) query.exec_('select * from %s' % table) record = query.record() for idx in range(record.count()): if record.fieldName(idx) == field: return True
def _query(self, querystring, **mappings): query = QSqlQuery(self.db) query.prepare(querystring) for key, value in mappings.iteritems(): bindvalue = ":{}".format(key) if bindvalue in querystring: query.bindValue(bindvalue, value) return query
def create_table(self): """Create table Progression(song, pattern, key) if not exist already""" # Tablen nimen saa vaihtaa query = QSqlQuery() query.prepare("CREATE TABLE IF NOT EXISTS Progressions(song TEXT PRIMARY KEY, pattern TEXT, key TEXT)") success = query.exec_() if not success: pass # TODO
def eliminar_producto(self): query=QSqlQuery() sql="delete from productos where codigo='%s'"%self.codigo if query.exec_(sql): return True else: return False
def getTableSchemaFromDb(self, table): self.checkAndOpenDb() sql = self.gen.getFullTablesName(table) query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception(self.tr("Problem getting full table name: ")+query.lastError().text()) while query.next(): return query.value(0).split('_')[0]
def showFeilds_mysql(self): defaultDB = QSqlDatabase.database() query = QSqlQuery("desc {}".format(self.listWidgetTables.currentItem().text())) qe = query.exec_() if qe: self.listWidgetFeilds.clear() while query.next(): self.listWidgetFeilds.addItem(query.value(0).toString())
def obtainLinkColumn(self, complexClass, aggregatedClass): #query to obtain the link column between the complex and the feature layer sql = self.gen.getLinkColumn(complexClass, aggregatedClass) query = QSqlQuery(sql, self.db) column_name = "" while query.next(): column_name = query.value(0) return column_name
def updateArticles( self ): """ Actualizar la lista de articulos """ query = QSqlQuery() try: if not self.database.isOpen(): if not self.database.open(): raise UserWarning( u"No se pudo conectar con la " + "base de datos" ) self.updateArticleList( query ) self.editmodel.updateLines( query ) providers_model = QSqlQueryModel() providers_model.setQuery( """ SELECT idpersona, nombre FROM personas p WHERE tipopersona = 2 AND activo = 1 """ ) if not providers_model.rowCount() > 0: raise UserWarning( "No existen proveedores en el sistema" ) self.cbProvider.setModel( providers_model ) self.cbProvider.setModelColumn( 1 ) warehouse_model = QSqlQueryModel() warehouse_model.setQuery( """ SELECT idbodega, nombrebodega FROM bodegas b ORDER BY idbodega """ ) if not warehouse_model.rowCount() > 0: raise UserWarning( "No existen bodegas en el sistema" ) self.cbWarehouse.setModel( warehouse_model ) self.cbWarehouse.setModelColumn( 1 ) self.cbWarehouse.setCurrentIndex( -1 ) self.cbProvider.setCurrentIndex( -1 ) except UserWarning as inst: QMessageBox.warning( self, qApp.organizationName(), unicode( inst ) ) logging.error( query.lastError().text() ) logging.error( unicode( inst ) ) self.cancel() except Exception as inst: QMessageBox.critical( self, qApp.organizationName(), "Hubo un error fatal al tratar de actualizar la lista " \ + "de articulos, el sistema no puede recuperarse" \ + " y sus cambios se han perdido" ) logging.error( query.lastError().text() ) logging.critical( unicode( inst ) ) self.cancel()
def getDatabaseVersion(self, db): version = '-1' if not db.open(): return version sqlVersion = 'SELECT edgvversion FROM db_metadata LIMIT 1' queryVersion = QSqlQuery(sqlVersion, db) while queryVersion.next(): version = queryVersion.value(0) return version
def prepare(self, query_string): query = QSqlQuery(self.db) query.setForwardOnly(True) # weird; QSqlQuery instances don't appear to be GC'ed by the time the DB is closed - so I capture them in a strong-ref'd # set and finish them up myself manually. self.__refQuery(query) if query.prepare(query_string): return query raise SyntaxError("failed to prepare query, sql was: '{}', db error: {}".format(query_string, query.lastError().text()))
def _query(self, querystring, **mappings): querystring = querystring.replace(r"\r\n", " ") query = QSqlQuery(self.db) query.prepare(querystring) for key, value in mappings.iteritems(): bindvalue = ":{}".format(key) if re.search(r"{}\b".format(bindvalue), querystring): query.bindValue(bindvalue, value) return query
def enviar_comandos(self): query=QSqlQuery() r=True for comando in self.comandos: if not query.exec_(comando): r=False return r
def nuevo_producto(self, datos): query = QSqlQuery() sql = """ insert into productos values(null,"%s","%s","%s",%.2f,%d)""" % datos if not query.exec_(sql): print "Error en algun dato de producto" return self.codigo = datos[0]
def save(self): resultado = False try: if not self.database.isOpen(): if not self.database.open(): raise UserWarning( u"No se pudo abrir la conexión "\ + "con la base de datos" ) if not self.database.transaction(): raise Exception( u"No se pudo comenzar la transacción" ) query = QSqlQuery() if not query.prepare( """ UPDATE preciosproducto SET activo = 0 WHERE idproducto = :id; """ ): raise Exception( "No se pudo preparar la consulta para actualizar" ) query.bindValue( ":id", self.articuloId) if not query.exec_(): raise Exception( "No se pudo desactivar el precio actual" ) if not query.prepare( """ INSERT INTO preciosproducto(idproducto,precio,unidadesxcaja) VALUES (:id,:precio,:cantidad); """ ): raise Exception( "No se pudo preparar la consulta para insertar los nuevos precios" ) query.bindValue( ":id", self.articuloId) query.bindValue( ":precio", self.precio) query.bindValue( ":unidades", self.unidades ) if not query.exec_(): raise Exception( "No se pudo insertar el nuevo precio" ) if not self.database.commit(): raise Exception( "No se pudo hacer commit" ) resultado = True except UserWarning as inst: self.database.rollback() print unicode( inst ) QMessageBox.critical( self, qApp.organizationName(), unicode( inst ) ) resultado = False finally: if self.database.isOpen(): self.database.close() return resultado
def exec_query(self, q_string): query = QSqlQuery(self.db) query.exec_(q_string) return query
query.exec_("insert into person values(102, 'Christine', 'Holand')") query.exec_("insert into person values(103, 'Lars', 'Gordon')") query.exec_("insert into person values(104, 'Roberto', 'Robitaille')") query.exec_("insert into person values(105, 'Maria', 'Papadopoulos')") query.exec_("select id, firstname from person") return query #query = setup_db_sqlite() from pyscada.gui.qt_dbhelp import dburl_to_qsqldb try: # Se lo chantamos así nomás qApp.db_con = dburl_to_qsqldb( 'mysql://*****:*****@localhost:3306/dsem') q = QSqlQuery("SET NAMES 'utf8'", qApp.db_con) print q.exec_() except Exception, e: import traceback QMessageBox.critical( None, "Error", u"<h2>%s</h2></pre>%s</pre>" % (type(e), traceback.format_exc())) sys.exit() query = QSqlQuery(''' SELECT CONCAT(UC.nombre_uc, '(' ,UC.id_UC,')'), UC.id FROM UC WHERE UC.co_id = %(co_id)d ''' % {'co_id': 1}) print "Error?", query.lastError().databaseText()
def updateEditModels(self): """ Este metodo actualiza los modelos usados en el modo edición """ resultado = False try: if not self.database.isOpen(): if not self.database.open(): raise UserWarning( u"No se pudo abrir la conexión "\ + "con la base de datos" ) self.clientesModel.setQuery(""" SELECT idpersona , nombre AS cliente FROM personas WHERE escliente = 1 """) self.cbcliente.setModel(self.clientesModel) self.cbcliente.setModelColumn(1) self.clienteCompleter.setCaseSensitivity(Qt.CaseInsensitive) self.clienteCompleter.setModel(self.clientesModel) self.clienteCompleter.setCompletionColumn(1) self.cbcliente.setCompleter(self.clienteCompleter) self.editmodel = FacturaModel() # Cargar el numero de la factura actual query = QSqlQuery(""" SELECT MAX(CAST( IFNULL(referencia,0) AS SIGNED)) FROM documentos d WHERE idtipodoc =%d; """ % constantes.IDFACTURA) if not query.exec_(): raise Exception("No se pudo obtener el numero de la factura") query.first() if query.size() == 0: n = 1 else: n = str(int(query.value(0)) + 1) self.editmodel.printedDocumentNumber = str( int(query.value(0)) + 1) self.lblnumero.setText(n) # if self.clientesModel.rowCount() == 0: # raise UserWarning( "No existen clientes en la"\ # + " base de datos" ) # return self.clienteCompleter.setModel(self.clientesModel) self.cbcliente.setModel(self.clientesModel) self.cbcliente.setCompleter(self.clienteCompleter) # #Crear el delegado con los articulo y verificar si existen articulos self.existenciaModel.setQuery( QSqlQuery(""" SELECT categoria, descripcion, precio, unidadesxcaja, -- cajas, 100 as cajas, idprecioproducto FROM vw_articulos -- WHERE existencia >0 """)) self.categoriesview.update(""" SELECT categoria, descripcion, precio, unidadesxcaja, -- cajas, 100 as cajas, idprecioproducto FROM vw_articulos WHERE idprecioproducto IS NOT NULL -- WHERE existencia >0 """) self.categoriesview.expandAll() self.categoriesview.setColumnHidden(3, True) self.categoriesview.setColumnHidden(4, True) self.categoriesview.setColumnWidth(0, 150) self.categoriesview.setColumnWidth(1, 60) self.categoriesview.setColumnWidth(2, 20) self.proxyexistenciaModel = SingleSelectionModel() self.proxyexistenciaModel.setSourceModel(self.existenciaModel) # self.proxyexistenciaModel.setFilterKeyColumn( IDBODEGAEX ) if self.proxyexistenciaModel.rowCount() == 0: raise UserWarning("No hay articulos en bodega") delegate = FacturaDelegate(self.proxyexistenciaModel) self.tabledetails.setItemDelegate(delegate) self.tabledetails.setModel(self.editmodel) self.tabledetails.setColumnHidden(0, True) # self.editmodel.insertRow(1) self.editmodel.dataChanged[QModelIndex, QModelIndex].connect(self.updateLabels) self.txtobservaciones.setPlainText("") self.dtPicker.setDate(QDate.currentDate().addDays(1)) self.editmodel.fecha = QDate.currentDate().addDays(1) self.cbcliente.setCurrentIndex(-1) resultado = True except UserWarning as inst: logging.error(unicode(inst)) QMessageBox.critical(self, qApp.organizationName(), unicode(inst)) finally: if self.database.isOpen(): self.database.close() return resultado
def save_modifications(self): """ Insert unmodified copy of the record, then save changes and deal with currency flag. """ unmod_query = QSqlQuery(self.db) unmod_query.exec_(self.record_copy_sql) # Save changes from dropdowns by directly editing model current = self.mapper.currentIndex() for idx, qitem in self.update_dict.iteritems(): combo = qitem[0] try: data = combo.itemData(combo.currentIndex()) except AttributeError: if idx == 5: # Desc text data = qitem[1].toPlainText() elif idx == 2: # Version num data = qitem[1].text() else: # Otherwise its a date data_raw = combo.text() date_obj = datetime.datetime.strptime(data_raw, "%d/%m/%Y") data = str(date_obj.strftime("%Y%m%d")) # Add modified data to the model if not self.model.setData(self.model.createIndex(current, idx), data): msg = "Error updating database model\n" msg += "Row: {}, Column: {}, Data: {}\n".format( current, idx, data) msg += self.model.lastError().text() raise BaseException(msg) # Compare model row before and after edit session map_lookup = MapLookupValues(self.model) updated_model = map_lookup.all_mapped_fields(current, 2, 1) dirty = self.is_coords_dirty(self.cur_model, updated_model) self.cur_model = None coord_dirt = dirty[0] other_dirt = dirty[1] if not coord_dirt and not other_dirt: # No edits so revert self.cancel_modifications() elif coord_dirt and not other_dirt: # Only coords edited, so no need to to duplicate record, ver no, etc self.model.submitAll() self.cancel_modifications() # Some data in the model has changed so submit and increment version number + updatedate else: # Auto set current date (update date) data = str(datetime.datetime.now().strftime("%Y%m%d")) self.model.setData(self.model.createIndex(current, 7), data) # Change updated by self.model.setData(self.model.createIndex(current, 23), self.params['UserName']) # Increment version number cur_ver = int(self.street_browser.ui.versionLineEdit.text()) ver_plus_one = cur_ver + 1 self.model.setData(self.model.createIndex(current, 2), str(ver_plus_one)) # Save esu links self.update_esu_link(cur_ver, ver_plus_one) # Commit to db self.model.submitAll() # Select all rows in the model again (else filtering fails) self.post_model_submit(current)
def read(self, abfrage): sql = abfrage query = QSqlQuery(sql,self.db) datensatz = {} i = 0 while i < query.record().count(): result = [] query.first() result.append(self.__pystring(query.value(i))) lastError = query.lastError().text() if len(lastError) > 1: QMessageBox.information(None, self.tr('DB-Error'), lastError) while query.next(): result.append(self.__pystring(query.value(i))) lastError = query.lastError().text() if len(lastError) > 1: QMessageBox.information(None, self.tr('DB-Error'), lastError) datensatz.update({str(query.record().fieldName(i)).upper(): result}) i = i + 1 return datensatz
def export_lor(self): """ Export list of roads to CSV. """ strstatussql = "" # List of road status codes to be included strstreettypesql = "1,2" # List of street types to be included strincludefieldsql = "" # SQL with additional fields to export strordersql = "tblSTREET.Description" # SQL with additional sorting order if self.any_radio: if self.pub_chx: strstatussql += "1" if self.pro_chx: if strstatussql != "": if strstatussql != "": strstatussql += "," strstatussql += "2" if self.priv_chx: if strstatussql != "": strstatussql += "," strstatussql += "3" if self.trunk_chx: if strstatussql != "": strstatussql += "," strstatussql += "4" else: strstatussql = "1" if self.town_chx: strordersql = "tlkpTOWN.Name," + strordersql if self.inc_t3_chx or self.inc_t4_chx: if self.inc_t3_chx: strstreettypesql += ",3" if self.inc_t4_chx: strstreettypesql += ",4" strincludefieldsql += ", tblStreet.Street_Ref_Type as Type" strordersql = "tblStreet.Street_Ref_Type," + strordersql if self.inc_lor_chx: strincludefieldsql += ", tblMaint.Lor_No as Road_No" if self.usrn_cx: strincludefieldsql += ", tblSTREET.USRN as USRN" sql_lor = "SELECT DISTINCT tblSTREET.Description AS Name, tlkpTOWN.Name AS Town," \ " tblMAINT.Location_Text as Description, tlkpROAD_STATUS.Description as Status" \ " %s FROM (tlkpLOCALITY INNER JOIN (tlkpTOWN INNER JOIN tblSTREET" \ " ON tlkpTOWN.Town_Ref = tblSTREET.Town_Ref) ON tlkpLOCALITY.Loc_Ref = tblSTREET.Loc_Ref)" \ " INNER JOIN (tblMAINT INNER JOIN tlkpROAD_STATUS" \ " ON tblMAINT.Road_status_ref = tlkpROAD_STATUS.Road_status_ref) ON tblSTREET.USRN = tblMAINT.USRN" \ " WHERE ((tblMaint.Road_Status_Ref in ( %s ))" \ " AND (tblSTREET.Street_ref_type In ( %s )) AND (tblMAINT.Currency_flag=0)" \ " AND (tblSTREET.Currency_flag=0)) ORDER BY %s ;" % \ (strincludefieldsql, strstatussql, strstreettypesql, strordersql) query = QSqlQuery(self.db) query.exec_(sql_lor) rec = query.record() try: # create the output file if self.csv_chx: self.csv_filename = os.path.join(self.path, "{}LOR.csv".format(self.code)) output_file = open(self.csv_filename, "wb") self.csv = csv.writer(output_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_NONNUMERIC, lineterminator='\r') else: self.csv_filename = os.path.join(self.path, "{}.txt".format(self.code)) output_file = open(self.csv_filename, "wb") # loop over each result and write it the text or CSV while query.next(): vals = [ query.value(rec.indexOf('Name')), query.value(rec.indexOf('Town')), query.value(rec.indexOf('Description')), query.value(rec.indexOf('Status')) ] if self.usrn_cx: vals.append(query.value(rec.indexOf('USRN'))) if self.inc_lor_chx: vals.append(query.value(rec.indexOf('Road_No'))) if self.csv_chx: self.csv.writerow(list(vals)) else: line = str() for v in vals: if type(v) != unicode: v = unicode(v) line += v + " " line += "\n" output_file.write(line) output_file.close() return None except IOError: return self.csv_filename
def results_to_txt_row(self, vals, sql, mode): query_model = QSqlQueryModel() query_model.setQuery(sql) model_index = QModelIndex() row_count = query_model.rowCount(model_index) if row_count < 1: self.file.write(self.no_content) else: query = QSqlQuery(self.db) query.exec_(sql) rec = query.record() if mode == "streets": avals = [ rec.indexOf(vals[0]), rec.indexOf(vals[1]), rec.indexOf(vals[2]), rec.indexOf(vals[3]), rec.indexOf(vals[4]), rec.indexOf(vals[5]), rec.indexOf(vals[6]), rec.indexOf(vals[7]), rec.indexOf(vals[8]), rec.indexOf(vals[9]), rec.indexOf(vals[10]), rec.indexOf(vals[11]) ] n = 0 # write content headers = self.headers[0] while n <= len(headers) - 1: if n == len(headers) - 1: self.file.write(str(headers[n]) + "\n") else: self.file.write(str(headers[n]) + " ") n += 1 while query.next(): line = [ query.value(avals[0]), query.value(avals[1]), query.value(avals[2]), query.value(avals[3]), query.value(avals[4]), query.value(avals[5]), query.value(avals[6]), query.value(avals[7]), query.value(avals[8]), query.value(avals[9]), query.value(avals[10]), query.value(avals[11]) ] self.file.write( str(line[0]) + " , " + str(line[1]) + " , " + str(line[2]) + " , " + str(line[3]) + " , " + str(line[4]) + " , " + str(line[5]) + " , " + self.format_dates(str(line[6])) + " , " + self.format_dates(str(line[7])) + " , " + self.format_dates(str(line[8])) + " , " + self.format_dates(str(line[9])) + " , " + str(line[10]) + " " + str(line[11]) + "\n") else: avals = [ rec.indexOf(vals[0]), rec.indexOf(vals[1]), rec.indexOf(vals[2]), rec.indexOf(vals[3]), rec.indexOf(vals[4]), rec.indexOf(vals[5]), rec.indexOf(vals[6]), rec.indexOf(vals[7]) ] m = 0 headers = self.headers[1] while m <= len(headers) - 1: if m == len(headers) - 1: self.file.write(str(headers[m]) + "\n") else: self.file.write(str(headers[m]) + " ") m += 1 while query.next(): line = [ query.value(avals[0]), query.value(avals[1]), query.value(avals[2]), query.value(avals[3]), query.value(avals[4]), query.value(avals[5]), query.value(avals[6]), query.value(avals[7]) ] self.file.write( str(line[0]) + " , " + str(line[1]) + " , " + str(line[2]) + " , " + str(line[3]) + " , " + str(line[4]) + " , " + str(line[5]) + " , " + str(line[6]) + " , " + str(line[7]) + "\n")
def results_to_csv_row(self, vals, sql, mode): query = QSqlQuery(self.db) query.exec_(sql) rec = query.record() if mode == "streets": avals = [ rec.indexOf(vals[0]), rec.indexOf(vals[1]), rec.indexOf(vals[2]), rec.indexOf(vals[3]), rec.indexOf(vals[4]), rec.indexOf(vals[5]), rec.indexOf(vals[6]), rec.indexOf(vals[7]), rec.indexOf(vals[8]), rec.indexOf(vals[9]), rec.indexOf(vals[10]), rec.indexOf(vals[11]) ] while query.next(): line = [ query.value(avals[0]), query.value(avals[1]), query.value(avals[2]), query.value(avals[3]), query.value(avals[4]), query.value(avals[5]), self.format_dates(str(query.value(avals[6]))), self.format_dates(str(query.value(avals[7]))), self.format_dates(str(query.value(avals[8]))), self.format_dates(str(query.value(avals[9]))), query.value(avals[10]), query.value(avals[11]) ] self.csv.writerow(line) else: avals = [ rec.indexOf(vals[0]), rec.indexOf(vals[1]), rec.indexOf(vals[2]), rec.indexOf(vals[3]), rec.indexOf(vals[4]), rec.indexOf(vals[5]), rec.indexOf(vals[6]), rec.indexOf(vals[7]) ] while query.next(): line = [ query.value(avals[0]), query.value(avals[1]), query.value(avals[2]), query.value(avals[3]), query.value(avals[4]), query.value(avals[5]), query.value(avals[6]), query.value(avals[7]) ] self.csv.writerow(line)
def updateTextBrowser(self): # check to make sure we have a feature selected in our selectList -- note that there might be more than one feature phoutput = "" moistureoutput = "" if self.phList: # ############ EXAMPLE 1 EDITS GO HERE #################### ''' write code that will output ALL selected feature attributes for a single feature into the Text Browser''' ''' instead of using the dataProvider.select() function get the actual QgsFeature using dataProvider.featureAtId() ''' # get the feature by passing in empty Feature request = QgsFeatureRequest(self.phList[0]) for f in self.pHLayer.getFeatures(request): phoutput = "Soil pH Level: %s \n" % (f['pH']) if self.MoistureList: request = QgsFeatureRequest(self.MoistureList[0]) for f in self.MoistureLayer.getFeatures(request): moistureoutput = "Soil Moisture Level: %s \n" % (f['moisture_level']) output = phoutput + moistureoutput self.dlg.setTextBrowser(output) provider = self.pHLayer.dataProvider() if provider.name() == 'postgres': # get the URI containing the connection parameters uri = QgsDataSourceURI(provider.dataSourceUri()) print uri.uri() # create a PostgreSQL connection using QSqlDatabase db = QSqlDatabase.addDatabase('QPSQL') # check to see if it is valid if db.isValid(): print "QPSQL db is valid" # set the parameters needed for the connection db.setHostName(uri.host()) db.setDatabaseName(uri.database()) db.setPort(int(uri.port())) db.setUserName(uri.username()) db.setPassword(uri.password()) # open (create) the connection if db.open(): print "Opened %s" % uri.uri() # execute a simple query #query = db.exec_("""select genus,species from botanical_name where genus = 'Prunus'""") query = QSqlQuery ("""select genus,species from botanical_name where genus = 'Prunus'""") self.dlg.tblPlants.clear() self.dlg.tblPlants.setRowCount(query.size()) self.dlg.tblPlants.setColumnCount(query.record().count()) self.dlg.tblPlants.setHorizontalHeaderLabels(["Genus", "Species"]) self.dlg.tblPlants.setSelectionMode(QTableWidget.SingleSelection) self.dlg.tblPlants.setSelectionBehavior(QTableWidget.SelectRows) # loop through the result set and print the name index=0 while query.next(): record = query.record() self.dlg.tblPlants.setItem(index, 0, QTableWidgetItem(query.value(0))) self.dlg.tblPlants.setItem(index, 1, QTableWidgetItem(query.value(1))) index = index+1 # print record.field('name').value().toString() self.dlg.tblPlants.resizeColumnsToContents() else: err = db.lastError() print err.driverText()
def run(self, fid = 0): ################################## ###### PEGA A LAYER ATIVA ######## ################################## parametros = self.layer.source().split(" ") # recebe todos os parametros em uma lista ( senha, porta, password etc..) #################################### ###### INICIANDO CONEXÃO DB ######## #################################### # Outra opção para isso, seria usar ex: self.dbname.. self.host.. etc.. direto dentro do laço for. dbname = "" host = "" port = 0 user = "" password = "" for i in parametros: part = i.split("=") # Recebe os parametros guardados na própria Layer if "dbname" in part[0]: dbname = part[1].replace("'", "") elif "host" in part[0]: host = part[1].replace("'", "") elif "port" in part[0]: port = int(part[1].replace("'", "")) elif "user" in part[0]: user = part[1].replace("'", "") elif "password" in part[0]: password = part[1].split("|")[0].replace("'", "") print dbname, host, port, user, password # Testa se os parametros receberam os valores pretendidos, caso não, apresenta a mensagem informando.. if len(dbname) == 0 or len(host) == 0 or port == 0 or len(user) == 0 or len(password) == 0: self.iface.messageBar().pushMessage("Erro", u'Um dos parametros não foram devidamente recebidos!', level=QgsMessageBar.CRITICAL, duration=4) return #################################### #### SETA VALORES DE CONEXÃO DB #### #################################### connection = QSqlDatabase.addDatabase('QPSQL') connection.setHostName(host) connection.setPort(port) connection.setUserName(user) connection.setPassword(password) connection.setDatabaseName(dbname) if not connection.isOpen(): # Testa se a conexão esta recebendo os parametros adequadamente. if not connection.open(): print 'Error connecting to database!' self.iface.messageBar().pushMessage("Erro", u'Error connecting to database!', level=QgsMessageBar.CRITICAL, duration=4) print connection.lastError().text() return #################################### ###### CRIAÇÃO DE MEMORY LAYER ##### #################################### layerCrs = self.layer.crs().authid() # Passa o formato (epsg: numeros) flagsLayerName = self.layer.name() + "_flags" flagsLayerExists = False for l in QgsMapLayerRegistry.instance().mapLayers().values(): # Recebe todas as camadas que estão abertas if l.name() == flagsLayerName: # ao encontrar o nome pretendido.. self.flagsLayer = l # flagslayer vai receber o nome.. self.flagsLayerProvider = l.dataProvider() flagsLayerExists = True # se encontrado os parametros buscados, recebe True. break if flagsLayerExists == False: # se não encontrado os parametros buscados, recebe False. tempString = "Point?crs=" tempString += str(layerCrs) self.flagsLayer = QgsVectorLayer(tempString, flagsLayerName, "memory") self.flagsLayerProvider = self.flagsLayer.dataProvider() self.flagsLayerProvider.addAttributes([QgsField("flagId", QVariant.String), QgsField("geomId", QVariant.String), QgsField("motivo", QVariant.String)]) self.flagsLayer.updateFields() if fid == 0: # Se for 0 então está iniciando e limpa, caso contrário não. self.flagsLayer.startEditing() ids = [feat.id() for feat in self.flagsLayer.getFeatures()] self.flagsLayer.deleteFeatures(ids) self.flagsLayer.commitChanges() lista_fid = [] # Iniciando lista for f in self.layer.getFeatures(): lista_fid.append(str(f.id())) # Guarda na lista. A lista de Feature ids passa tipo "int", foi convertido e guardado como "str". source = self.layer.source().split(" ") self.tableName = "" # Inicia vazio layerExistsInDB = False for i in source: if "table=" in i or "layername=" in i: # Se encontrar os atributos pretendidos dentre todos do for self.tableName = source[source.index(i)].split(".")[1] # Faz split em ponto e pega a segunda parte. self.tableName = self.tableName.replace('"', '') layerExistsInDB = True break if layerExistsInDB == False: self.iface.messageBar().pushMessage("Erro", u"Provedor da camada corrente não provem do banco de dados!", level=QgsMessageBar.CRITICAL, duration=4) return geomType = self.layer.geometryType() if geomType == QGis.Line: # Busca através do SQL sql = """ WITH result AS (SELECT points."{4}", points.anchor, (degrees ( ST_Azimuth(points.anchor, points.pt1) - ST_Azimuth(points.anchor, points.pt2) )::decimal + 360) % 360 as angle FROM (SELECT ST_PointN("{3}", generate_series(1, ST_NPoints("{3}")-2)) as pt1, ST_PointN("{3}", generate_series(2, ST_NPoints("{3}")-1)) as anchor, ST_PointN("{3}", generate_series(3, ST_NPoints("{3}"))) as pt2, linestrings."{4}" as "{4}" FROM (SELECT "{4}" as "{4}", (ST_Dump("{3}")).geom as "{3}" FROM only "{0}"."{1}" ) AS linestrings WHERE ST_NPoints(linestrings."{3}") > 2 ) as points) select distinct "{4}", ST_AsText(anchor), angle from result where (result.angle % 360) < {2} or result.angle > (360.0 - ({2} % 360.0)) and result.{4} in ({5})""".format( self.tableSchema, self.tableName, self.angle, self.geometryColumn, self.keyColumn,",".join(lista_fid)) elif geomType == QGis.Polygon: sql = """ WITH result AS (SELECT points."{4}", points.anchor, (degrees ( ST_Azimuth(points.anchor, points.pt1) - ST_Azimuth(points.anchor, points.pt2) )::decimal + 360) % 360 as angle FROM (SELECT ST_PointN("{3}", generate_series(1, ST_NPoints("{3}")-1)) as pt1, ST_PointN("{3}", generate_series(1, ST_NPoints("{3}")-1) % (ST_NPoints("{3}")-1)+1) as anchor, ST_PointN("{3}", generate_series(2, ST_NPoints("{3}")) % (ST_NPoints("{3}")-1)+1) as pt2, linestrings."{4}" as "{4}" FROM (SELECT "{4}" as "{4}", (ST_Dump(ST_Boundary(ST_ForceRHR((ST_Dump("{3}")).geom)))).geom as "{3}" FROM only "{0}"."{1}" ) AS linestrings WHERE ST_NPoints(linestrings."{3}") > 2 ) as points) select distinct "{4}", ST_AsText(anchor), angle from result where (result.angle % 360) < {2} or result.angle > (360.0 - ({2} % 360.0)) and result.{4} in ({5})""".format( self.tableSchema, self.tableName, self.angle, self.geometryColumn, self.keyColumn,",".join(lista_fid) ) query = QSqlQuery(sql) self.flagsLayer.startEditing() flagCount = fid # iniciando contador que será referência para os IDs da camada de memória. listaFeatures = [] while query.next(): id = query.value(0) local = query.value(1) angulo = query.value(2) flagId = str(flagCount) flagFeat = QgsFeature() flagFeat.setFields(self.flagsLayer.fields()) # passa quais atributos serão usados. flagGeom = QgsGeometry.fromWkt(local) # passa o local onde foi localizado o erro. flagFeat.setGeometry(flagGeom) flagFeat.initAttributes(3) flagFeat.setAttribute(0,flagId) # insere o id definido para a coluna 0 da layer de memória. flagFeat.setAttribute(1, id) # insere o id da geometria para a coluna 1 da layer de memória. flagFeat.setAttribute(2, u"Ângle-ExcededBound") listaFeatures.append(flagFeat) flagCount += 1 # incrementando o contador a cada iteração. self.flagsLayerProvider.addFeatures(listaFeatures) self.flagsLayer.commitChanges() # Aplica as alterações à camada. QgsMapLayerRegistry.instance().addMapLayer(self.flagsLayer) # Adicione a camada no mapa. return flagCount
def CadastrarCliente(cliente): conn = ConexaoSQL db = conn.getConexao() db.open() query = QSqlQuery() query.prepare( "INSERT INTO Cliente(Nome, CPF, Endereco, Email, Telefone) " "VALUES (?, ?, ?, ?, ?)") query.addBindValue(cliente.Nome) query.addBindValue(cliente.CPF) query.addBindValue(cliente.Endereco) query.addBindValue(cliente.Email) query.addBindValue(cliente.Telefone) query.exec_() db.commit()
class GkukanMusiumdbDockWidget(QtGui.QDockWidget, FORM_CLASS): closingPlugin = pyqtSignal() GKukanMusiumMessage = pyqtSignal(unicode, int) def __init__(self, iface, land, parent=None): """Constructor.""" super(GkukanMusiumdbDockWidget, self).__init__(parent) # Set up the user interface from Designer. # After setupUI you can access any designer object by doing # self.<objectname>, and you can use autoconnect slots - see # http://qt-project.org/doc/qt-4.8/designer-using-a-ui-file.html # #widgets-and-dialogs-with-auto-connect self.setupUi(self) self.loadListCount = 0 self.loadmax = self.tblPhotos.columnCount() self.tblPhotos.setEditTriggers(QtGui.QAbstractItemView.NoEditTriggers) self.iface = iface self.land = land #Landmark toolbox self.canvas = self.iface.mapCanvas() self.geoCrs = QgsCoordinateReferenceSystem(4326) self.db = QSqlDatabase.addDatabase('QPSQL') self.photolist = [] self.filtermode = False self.btnReloadphoto.clicked.connect(self.FilterMode) self.btnLoadMorePhoto.clicked.connect(self.AddMorePhotosToList) self.btnSetLandmark.clicked.connect(self.AddSelectiontoLandmark) self.btnClearSelection.clicked.connect(self.ClearSelection) self.btnSetToPoint.clicked.connect(self.SetPointToSelection) self.tblPhotos.cellDoubleClicked.connect(self.clickPhoto) self.btnLoadMorePhoto.setEnabled(False) self.btnSetLandmark.setEnabled(False) self.btnSetToPoint.setEnabled(False) def closeEvent(self, event): self.closingPlugin.emit() event.accept() def clickPhoto(self, r, c): tbl = self.tblPhotos p = tbl.item(1, c) if hasattr(p, 'data') == False: return a = p.data(32) if not a: return if r == 0 or (a.lon == 0 and a.lat == 0): dlg = ViewPhotoDialog(a.filePath) dlg.exec_() return point = self._transformPoint(QgsPoint(a.lon, a.lat)) self.canvas.freeze(True) self.canvas.setCenter(point) self.canvas.freeze(False) self.canvas.refresh() def SetPointToSelection(self): pass if self._checkLoadLayer() == False: return tbl = self.tblPhotos if len(tbl.selectedItems()) == 0: return pl = [] for p in tbl.selectedItems(): a = p.data(32) if a is not None: pl.append(a) if self.AskUpdatePoint(pl) is False: return if len(pl): self.canvas.freeze(True) self.SetPhotoPosition(pl, self.canvas.center()) self.canvas.freeze(False) self.canvas.refresh() i = 0 for p in tbl.selectedItems(): if p.row() == 1: p.setBackground(QBrush(QColor('#FFFFFF'))) def AskUpdatePoint(self, pl): i = False for p in pl: if p.lat: if p.lon: i = True if i: reply = QtGui.QMessageBox.question(self, u'位置情報変更の確認', u"画像に紐づく位置情報を変更してもよろしいですか?", QtGui.QMessageBox.Yes, QtGui.QMessageBox.No) if reply == QtGui.QMessageBox.Yes: return True else: return False else: return True def AddSelectiontoLandmark(self): if self.loadListCount == 0: return tbl = self.tblPhotos if len(tbl.selectedItems()) == 0: return lid = self.land.toolbox.getLandmarkID() if lid is None: return pl = [] for p in tbl.selectedItems(): a = p.data(32) if a is not None: pl.append(a) if self.AskUpdateLandmark(lid, pl) is False: return if len(pl): if self.SetLandMarkID(lid, pl): self.land.toolbox._highlightLandmark() self.land.toolbox.populatePhotos() def AskUpdateLandmark(self, lid, pl): i = False for p in pl: if p.landmark_id: if p.landmark_id <> lid: i = True if i: reply = QtGui.QMessageBox.question(self, u'史料情報変更の確認', u"画像に紐づく史料情報を変更してもよろしいですか?", QtGui.QMessageBox.Yes, QtGui.QMessageBox.No) if reply == QtGui.QMessageBox.Yes: return True else: return False else: return True def FilterMode(self): self.filtermode = self.FilterNoEdit.isChecked() self.ResetTable() self.AddPhotosToList() self.btnLoadMorePhoto.setEnabled(True) self.btnSetLandmark.setEnabled(True) self.btnSetToPoint.setEnabled(True) def ResetTable(self): self.tblPhotos.clear() del self.photolist[:] self.loadListCount = 0 self.tblPhotos.setColumnCount(20) self.tblPhotos.setBackgroundRole(QtGui.QPalette.Dark) self.loadmax = self.tblPhotos.columnCount() self.tblPhotos.setEditTriggers(QtGui.QAbstractItemView.NoEditTriggers) def ScrollTableLeft(self): tbl = self.tblPhotos tbl.setColumnCount(self.loadListCount + 5) self.LoadMorePhotoThread(5) def LoadPhotosToList(self): self.loadListCount = 0 self.AddPhotosToList() def AddMorePhotosToList(self): oldst = self.loadListCount n = 100 QApplication.setOverrideCursor(Qt.WaitCursor) if not self.GetPhotosList(self.loadListCount + 1, self.loadListCount + n): QApplication.restoreOverrideCursor() return tbl = self.tblPhotos tbl.verticalHeader().resizeSection(0, 50) for i in range(oldst, self.loadListCount): fn = os.path.join(self.folderpath, self.photolist[i].filename) self.photolist[i].filePath = fn tp = os.path.join(os.path.join(self.folderpath, 'thumb'), str(self.photolist[i].p_id) + '.png') self.photolist[i].thumbPath = tp item = ImgWidget(fn, tp, self) hd = QTableWidgetItem(str(self.photolist[i].p_id)) tbl.insertColumn(i) tbl.setHorizontalHeaderItem(i, hd) tbl.setCellWidget(0, i, item) b = QtGui.QTableWidgetItem(self.photolist[i].filename) b.setData(32, self.photolist[i]) tbl.setItem(1, i, b) if self.photolist[i].lon == 0 and self.photolist[i].lat == 0: tbl.item(1, i).setBackground(QBrush(QColor('#CCFFFF'))) if self.photolist[i].keywords: tbl.setItem(2, i, QtGui.QTableWidgetItem(self.photolist[i].keywords)) QgsMessageLog.logMessage(u"LoadPhoto..." + str(i), tag="LoadMorePhotoThread", level=QgsMessageLog.INFO) QApplication.restoreOverrideCursor() def AddPhotosToList(self): oldst = self.loadListCount QApplication.setOverrideCursor(Qt.WaitCursor) if not self.GetPhotosList(self.loadListCount + 1, self.loadListCount + self.loadmax): QApplication.restoreOverrideCursor() return tbl = self.tblPhotos tbl.verticalHeader().resizeSection(0, 50) self.ClearTableCells() for i in range(oldst, self.loadListCount): fn = os.path.join(self.folderpath, self.photolist[i].filename) self.photolist[i].filePath = fn tp = os.path.join(os.path.join(self.folderpath, 'thumb'), str(self.photolist[i].p_id) + '.png') self.photolist[i].thumbPath = tp item = ImgWidget(fn, tp, self) pos = i - oldst hd = QTableWidgetItem(str(i + 1)) tbl.setHorizontalHeaderItem(pos, hd) tbl.setCellWidget(0, pos, item) b = QtGui.QTableWidgetItem(self.photolist[i].filename) b.setData(32, self.photolist[i]) tbl.setItem(1, i, b) if self.photolist[i].lon == 0 and self.photolist[i].lat == 0: tbl.item(1, i).setBackground(QBrush(QColor('#CCFFFF'))) if self.photolist[i].keywords: tbl.setItem(2, pos, QtGui.QTableWidgetItem(self.photolist[i].keywords)) QgsMessageLog.logMessage(u"LoadPhoto..." + str(i), tag="LoadPhotoThread", level=QgsMessageLog.INFO) QApplication.restoreOverrideCursor() def ClearSelection(self): tbl = self.tblPhotos if len(tbl.selectedItems()) == 0: return tbl.selectionModel().clearSelection() def ClearTableCells(self): tbl = self.tblPhotos tbl.clear() def openDatabase(self): if self.db.isValid(): settings = QSettings('MatsueGkukan', 'Gkukandb') dbHostName = settings.value('hostname') dbDatabaseName = settings.value('databasename') dbUserName = settings.value('username') dbPassword = settings.value('dbpassword') self.db.setHostName(dbHostName) self.db.setDatabaseName(dbDatabaseName) self.db.setUserName(dbUserName) self.db.setPassword(dbPassword) if not self.db.open(): self.GKukanMusiumMessage.emit( self.tr('Can not open GKukanMusium database'), QgsMessageBar.WARNING) return False self.query = QSqlQuery(self.db) return True else: settings = QSettings('MatsueGkukan', 'Gkukandb') dbHostName = settings.value('hostname') dbDatabaseName = settings.value('databasename') dbUserName = settings.value('username') dbPassword = settings.value('dbpassword') self.db.removeDatabase(dbDatabaseName) del self.db self.db = None self.db = QSqlDatabase.addDatabase('QPSQL') self.db.setHostName(dbHostName) self.db.setDatabaseName(dbDatabaseName) self.db.setUserName(dbUserName) self.db.setPassword(dbPassword) if not self.db.open(): self.GKukanMusiumMessage.emit( self.tr('Can not open GKukanMusium database'), QgsMessageBar.WARNING) return False self.query = QSqlQuery(self.db) return True return False def GetPhotoFolderPath(self): if not self.openDatabase(): return False if self.query.exec_(u'select * from m_folder'): self.query.first() self.folderpath = self.query.value(2) ret = self.folderpath else: ret = '' self.db.close() return ret def GetPhotosList(self, st, ed): folder = self.GetPhotoFolderPath() if not self.openDatabase(): return False l = ed - st + 1 if self.filtermode: sql = u'select * from (select row_number() over() as rid, * from t_photo where t_photo.lat=0 and t_photo.lon=0 order by p_id) as p where (p.rid between %s and %s ) limit %s;' % ( str(st), str(ed), str(l)) else: sql = u'select * from (select row_number() over() as rid, * from t_photo order by p_id) as p where p.rid between %s and %s limit %s;' % ( str(st), str(ed), str(l)) if self.query.exec_(sql): self.query.first() for i in range(self.query.size()): v = self.query.value self.photolist.append( PhotoData(v(1), v(2), v(3), v(4), v(5), v(6), v(7), v(8), v(9), v(10), v(11), v(12), v(13), v(14), v(15), v(16), v(17), v(18), v(19), v(20), v(21), v(22))) self.query.next() self.loadListCount += self.query.size() self.query.clear() self.db.close() return True else: self.db.close() return False def SetLandMarkID(self, lid, pl): if not len(pl): return False if not self.openDatabase(): return False a = [] for p in pl: a.append(p.p_id) sp = ",".join(map(str, a)) sql = u'UPDATE t_photo SET landmark_id=%s WHERE p_id in (%s);' % ( str(lid), str(sp)) print sql if self.query.exec_(sql): self.query.clear() self.db.close() else: self.db.close() return False return True def SetPhotoPosition(self, pl, pos): if not len(pl): return False a = [] for p in pl: a.append(p.p_id) sp = ",".join(map(str, a)) pos = self._transformMapToPoint(pos) geom = QgsGeometry.fromPoint(pos) x = geom.asQPointF().x() y = geom.asQPointF().y() gt = "'" + geom.exportToWkt() + "'" sql = u'UPDATE t_photo SET lon=%s,lat=%s,geom=ST_GeomFromText(%s,4326) WHERE p_id in (%s);' % ( str(x), str(y), gt, str(sp)) v = self.convertGeomFromPostGis(geom) self.updatePhotoDataXY(a, x, y, v) if not self.openDatabase(): return False if self.query.exec_(sql): self.query.clear() self.db.close() else: a = self.query.lastError().text() self.db.close() return False return True def updatePhotoDataXY(self, pl, x, y, v): pass pp = self.photolist for i in range(len(pp)): if pp[i].p_id in pl: self.photolist[i].x = x self.photolist[i].y = y self.photolist[i].geom = v return def decodeBinary(self, wkb): """Decode the binary wkb and return as a hex string""" value = binascii.a2b_hex(wkb) value = value[::-1] value = binascii.b2a_hex(value) return value def _checkLoadLayer(self): pass return True def _transformMapToPoint(self, pnt): crsDest = self.canvas.mapSettings().destinationCrs() xform = QgsCoordinateTransform(crsDest, self.geoCrs) p2 = xform.transform(pnt) return p2 def _transformPoint(self, pnt): crsDest = self.canvas.mapSettings().destinationCrs() xform = QgsCoordinateTransform(self.geoCrs, crsDest) p2 = xform.transform(pnt) return p2 def convertGeomFromPostGis(self, geom): if not self.openDatabase(): return None gt = "'" + geom.exportToWkt() + "'" sql = u'SELECT ST_GeomFromText(%s,4326);' % (gt) if self.query.exec_(sql): self.query.first() v = self.query.value self.db.close() return v else: a = self.query.lastError().text() self.db.close() return False
if __name__ == "__main__": app = QCoreApplication(sys.argv) parser = OptionParser() parser.add_option("-m", "--month", dest="month", help="El mes a descargar") parser.add_option("-y", "--year", dest="year", help=u"El año a descargar") (options, args) = parser.parse_args() db = QSqlDatabase.addDatabase("QMYSQL") db.setDatabaseName("esquipulasdb") db.setUserName("root") db.setHostName("localhost") db.setPassword("root") query = QSqlQuery() try: year = int(options.year if not options.year is None else datetime.date. today().year) month = int(options.month if not options.month is None else datetime. date.today().month) url = "http://www.elpueblopresidente.com/servicios/wsmoneda.php?formato=jsonvalido&ano=%d&mes=%d" % ( year, month) print url file = urllib.urlopen(url) data = json.loads(file.read()) if not db.open(): raise Exception(u"No se pudo abrir la conexión a la base de datos")
def __init__(self, cmdList, args=None, dbHandle=None, silent=False, mayFail=False): """we take a list of sql statements. Only the last one is allowed to be a select statement. Do prepared queries by passing a single query statement in cmdList and the parameters in args. If args is a list of lists, execute the prepared query for every sublist. If dbHandle is passed, use that for db access. Else if the default dbHandle (DBHandle.default) is defined, use it.""" # pylint: disable=R0912 # pylint says too many branches silent |= not Debug.sql self.dbHandle = dbHandle or DBHandle.default preparedQuery = not isinstance(cmdList, list) and bool(args) self.query = QSqlQuery(self.dbHandle) self.msg = None self.records = [] if not isinstance(cmdList, list): cmdList = list([cmdList]) self.cmdList = cmdList for cmd in cmdList: retryCount = 0 while retryCount < 100: self.lastError = None if preparedQuery: self.query.prepare(cmd) if not isinstance(args[0], list): args = list([args]) for dataSet in args: if not silent: _, utf8Args = xToUtf8(u'', dataSet) logDebug("{cmd} [{args}]".format( cmd=cmd, args=", ".join(utf8Args))) for value in dataSet: self.query.addBindValue(QVariant(value)) self.success = self.query.exec_() if not self.success: break else: if not silent: logDebug('%s %s' % (self.dbHandle.name, cmd)) self.success = self.query.exec_(cmd) if self.success or self.query.lastError().number() not in (5, 6): # 5: database locked, 6: table locked. Where can we get symbols for this? break time.sleep(0.1) retryCount += 1 if not self.success: self.lastError = unicode(self.query.lastError().text()) self.msg = 'ERROR in %s: %s' % (self.dbHandle.databaseName(), self.lastError) if mayFail: if not silent: logDebug(self.msg) else: logException(self.msg) return self.records = None self.fields = None if self.query.isSelect(): self.retrieveRecords()
class Query(object): """a more pythonic interface to QSqlQuery. We could instead use the python sqlite3 module but then we would either have to do more programming for the model/view tables, or we would have two connections to the same database. For selecting queries we fill a list with ALL records. Every record is a list of all fields. q.records[0][1] is record 0, field 1. For select, we also convert to python data types - as far as we need them""" localServerName = m18ncE('kajongg name for local game server', 'Local Game') def __init__(self, cmdList, args=None, dbHandle=None, silent=False, mayFail=False): """we take a list of sql statements. Only the last one is allowed to be a select statement. Do prepared queries by passing a single query statement in cmdList and the parameters in args. If args is a list of lists, execute the prepared query for every sublist. If dbHandle is passed, use that for db access. Else if the default dbHandle (DBHandle.default) is defined, use it.""" # pylint: disable=R0912 # pylint says too many branches silent |= not Debug.sql self.dbHandle = dbHandle or DBHandle.default preparedQuery = not isinstance(cmdList, list) and bool(args) self.query = QSqlQuery(self.dbHandle) self.msg = None self.records = [] if not isinstance(cmdList, list): cmdList = list([cmdList]) self.cmdList = cmdList for cmd in cmdList: retryCount = 0 while retryCount < 100: self.lastError = None if preparedQuery: self.query.prepare(cmd) if not isinstance(args[0], list): args = list([args]) for dataSet in args: if not silent: _, utf8Args = xToUtf8(u'', dataSet) logDebug("{cmd} [{args}]".format( cmd=cmd, args=", ".join(utf8Args))) for value in dataSet: self.query.addBindValue(QVariant(value)) self.success = self.query.exec_() if not self.success: break else: if not silent: logDebug('%s %s' % (self.dbHandle.name, cmd)) self.success = self.query.exec_(cmd) if self.success or self.query.lastError().number() not in (5, 6): # 5: database locked, 6: table locked. Where can we get symbols for this? break time.sleep(0.1) retryCount += 1 if not self.success: self.lastError = unicode(self.query.lastError().text()) self.msg = 'ERROR in %s: %s' % (self.dbHandle.databaseName(), self.lastError) if mayFail: if not silent: logDebug(self.msg) else: logException(self.msg) return self.records = None self.fields = None if self.query.isSelect(): self.retrieveRecords() def rowcount(self): """how many rows were affected?""" return self.query.numRowsAffected() def retrieveRecords(self): """get all records from SQL into a python list""" record = self.query.record() self.fields = [record.field(x) for x in range(record.count())] self.records = [] while self.query.next(): self.records.append( [self.__convertField(x) for x in range(record.count())]) def __convertField(self, idx): """convert a QSqlQuery field into a python value""" result = self.query.value(idx).toPyObject() if isinstance(result, QString): result = unicode(result) if isinstance(result, long) and -sys.maxint - 1 <= result <= sys.maxint: result = int(result) return result
def loadDatabaseStructure(self, edgvPath): """ Loads the database structure edgvPath: path to the databse sql """ commands = [] hasTemplate = self.abstractDb.checkTemplate(self.version) if not hasTemplate: file = codecs.open(edgvPath, encoding='utf-8', mode="r") sql = file.read() sql = sql.replace('[epsg]', '4674') file.close() commands = sql.split('#') # Progress bar steps calculated self.signals.rangeCalculated.emit(len(commands)+4, self.getId()) if not hasTemplate: try: self.abstractDb.createTemplateDatabase(self.version) self.signals.stepProcessed.emit(self.getId()) self.connectToTemplate() self.signals.stepProcessed.emit(self.getId()) except Exception as e: return (0, self.messenger.getProblemFeedbackMessage()+'\n'+':'.join(e.args)) self.db.open() self.db.transaction() query = QSqlQuery(self.db) for command in commands: if not self.stopped[0]: if not query.exec_(command): QgsMessageLog.logMessage(self.messenger.getProblemMessage(command, query), "DSG Tools Plugin", QgsMessageLog.CRITICAL) self.db.rollback() self.db.close() self.dropDatabase(self.db) return (0, self.messenger.getProblemFeedbackMessage()) # Updating progress self.signals.stepProcessed.emit(self.getId()) else: self.db.rollback() self.db.close() self.dropDatabase(self.db) QgsMessageLog.logMessage(self.messenger.getUserCanceledFeedbackMessage(), "DSG Tools Plugin", QgsMessageLog.INFO) return (-1, self.messenger.getUserCanceledFeedbackMessage()) self.db.commit() if self.version == '2.1.3': sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'cb\',\'complexos\',\'dominios\';' % self.db.databaseName() elif self.version == 'FTer_2a_Ed': sql = 'ALTER DATABASE %s SET search_path = "$user", public, topology,\'pe\',\'ge\',\'complexos\',\'dominios\';' % self.db.databaseName() if not query.exec_(sql): QgsMessageLog.logMessage(self.messenger.getProblemMessage(command, query), "DSG Tools Plugin", QgsMessageLog.CRITICAL) return (0, self.messenger.getProblemFeedbackMessage()) #this commit was missing, so alter database statement was not commited. self.db.commit() self.db.close() self.abstractDb.setDbAsTemplate(self.version) #creates from template if not self.stopped[0]: templateName = self.abstractDb.getTemplateName(self.version) self.abstractDb.createDbFromTemplate(self.dbName, templateName, parentWidget = self.parent) self.signals.stepProcessed.emit(self.getId()) #5. alter spatial structure createdDb = self.dbFactory.createDbFactory('QPSQL') createdDb.connectDatabaseWithParameters(self.abstractDb.db.hostName(), self.abstractDb.db.port(), self.dbName, self.abstractDb.db.userName(), self.abstractDb.db.password()) errorTuple = createdDb.updateDbSRID(self.epsg, parentWidget = self.parent, threading = True) # if an error occur during the thread we should pass the message to the main thread if errorTuple: QgsMessageLog.logMessage(self.messenger.getProblemMessage(errorTuple[0], errorTuple[1]), "DSG Tools Plugin", QgsMessageLog.CRITICAL) return (0, self.messenger.getProblemFeedbackMessage()) self.signals.stepProcessed.emit(self.getId()) else: QgsMessageLog.logMessage(self.messenger.getUserCanceledFeedbackMessage(), "DSG Tools Plugin", QgsMessageLog.INFO) return (-1, self.messenger.getUserCanceledFeedbackMessage()) QgsMessageLog.logMessage(self.messenger.getSuccessFeedbackMessage(), "DSG Tools Plugin", QgsMessageLog.INFO) return (1, self.messenger.getSuccessFeedbackMessage())
def fill_fields(self): search_word = self.filterEdit.text() if search_word.strip() != '': search_str = stem_str(search_word) else: search_str = '' query = QSqlQuery(self.local_cn) LIMIT = 100 OFFSET = 0 query.prepare(show_termins_in_link_selector_query) query.bindValue(':search_str', search_str + '%') query.bindValue(':linked', 1) query.bindValue(':limit', LIMIT.__str__()) query.bindValue(':offset', OFFSET.__str__()) if query.exec_(): self.root.takeChildren() while query.next(): c = QTreeWidgetItem() c.setText(0, query.value(0)) # Заглавное слово c.setData(1, 0, query.value(1)) # uuid self.root.addChild(c) else: print(query.lastError().text()) print("not exec") self.treeWidget.scrollToTop()
class DataProcessor: def __init__(self, source): self.layer = None self.dataFile = None regex = re.compile("dbname='(.+)'") r = regex.search(source) self.source = r.groups()[0] #QPSQL #self.db = QSqlDatabase.addDatabase('QPSQL') self.db = QSqlDatabase.addDatabase('QSQLITE') encoding = locale.getdefaultlocale()[1] if encoding is None: self.csvEncoding = 'utf-8' else: self.csvEncoding = encoding.lower() def setLayer(self, layer): self.layer = layer def setDataFile(self, fileName): self.dataFile = fileName def exportData(self): landmarks = self._exportLayer() if landmarks is None: return False photos = self._exportPhotos() if photos is None: return False zf = zipfile.ZipFile(self.dataFile, 'w') zf.write(landmarks, 'landmarks.csv') zf.write(photos, 'photos.csv') zf.close() return True def importData(self): if not self._importLayer(): return False if not self._importPhotos(): return False return True def _openDatabase(self): self.db.setDatabaseName(self.source) if not self.db.open(): return False self.query = QSqlQuery() return True def _exportLayer(self): fileName = self._tempFileName() err = QgsVectorFileWriter.writeAsVectorFormat( self.layer, fileName, self.csvEncoding, self.layer.crs(), 'CSV', layerOptions=['GEOMETRY=AS_XY']) if err != QgsVectorFileWriter.NoError: return None return fileName def _importLayer(self): ids = [] titles = [] types = [] angles = [] x = [] y = [] result = False zf = zipfile.ZipFile(self.dataFile, 'r') fh = StringIO.StringIO(zf.read('landmarks.csv')) reader = UnicodeReader(fh, dialect='excel', quotechar='"', encoding=self.csvEncoding) reader.next() for r in reader: x.append(float(r[0]) if r[0] else None) y.append(float(r[1]) if r[1] else None) ids.append(int(r[2]) if r[2] else None) titles.append(r[3]) types.append(int(r[4]) if r[4] else None) angles.append(float(r[5]) if r[5] else None) if not self._openDatabase(): return False self.query.prepare( 'INSERT OR REPLACE INTO landmark("id", "title", "icon_type", "label_angle", "the_geom") VALUES(?, ?, ?, ?, MakePoint(?, ?, 4326));' ) self.query.addBindValue(ids) self.query.addBindValue(titles) self.query.addBindValue(types) self.query.addBindValue(angles) self.query.addBindValue(x) self.query.addBindValue(y) if self.query.execBatch(): result = True self.db.close() return result def _exportPhotos(self): if not self._openDatabase(): return None result = None fileName = self._tempFileName() self.query.prepare( 'SELECT "id", "angle", "date", "title", "comment", "imagepath", "landmark_id" FROM photo;' ) if self.query.exec_(): result = fileName with open(fileName, 'wb') as f: writer = UnicodeWriter(f, dialect='excel', quotechar='"', encoding=self.csvEncoding) writer.writerow(('id', 'angle', 'date', 'title', 'comment', 'imagepath', 'landmark_id')) while self.query.next(): pid = unicode(self.query.value( 0)) if self.query.value(0) != None else '' angle = unicode( self.query.value(1)) if self.query.value(1) else '' date = self.query.value(2) if self.query.value(2) else '' title = self.query.value(3) if self.query.value(3) else '' comment = self.query.value(4) if self.query.value( 4) else '' imgpath = self.query.value(5) if self.query.value( 5) else '' landmarkid = unicode(self.query.value( 6)) if self.query.value(6) != None else '' writer.writerow((pid, angle, date, title, comment, imgpath, landmarkid)) self.db.close() return result def _importPhotos(self): pids = [] angles = [] dates = [] titles = [] comments = [] paths = [] ids = [] result = False zf = zipfile.ZipFile(self.dataFile, 'r') fh = StringIO.StringIO(zf.read('photos.csv')) reader = UnicodeReader(fh, dialect='excel', quotechar='"', encoding=self.csvEncoding) reader.next() for r in reader: pids.append(int(r[0]) if r[0] else None) angles.append(float(r[1]) if r[1] else None) dates.append(r[2]) titles.append(r[3]) comments.append(r[4]) paths.append(r[5]) ids.append(int(r[6]) if r[6] else None) if not self._openDatabase(): return False self.query.prepare( 'INSERT OR REPLACE INTO photo("id", "angle", "date", "title", "comment", "imagepath", "landmark_id") VALUES(?, ?, ?, ?, ?, ?, ?);' ) self.query.addBindValue(pids) self.query.addBindValue(angles) self.query.addBindValue(dates) self.query.addBindValue(titles) self.query.addBindValue(comments) self.query.addBindValue(paths) self.query.addBindValue(ids) if self.query.execBatch(): result = True self.db.close() return result def _tempDirectory(self): tmp = unicode(os.path.join(QDir.tempPath(), 'landmark')) if not os.path.exists(tmp): QDir().mkpath(tmp) return os.path.abspath(tmp) def _tempFileName(self): tmpDir = self._tempDirectory() fName = os.path.join(tmpDir, str(uuid.uuid4()).replace('-', '') + '.csv') return fName
def validateWithOutputDatabaseSchema(self, outputAbstractDb): try: self.checkAndOpenDb() except: return dict() invalidated = self.buildInvalidatedDict() inputdbStructure = self.getStructureDict() outputdbStructure = outputAbstractDb.getStructureDict() domainDict = outputAbstractDb.getDomainDict() classes = self.listClassesWithElementsFromDatabase() notNullDict = outputAbstractDb.getNotNullDict() for inputClass in classes.keys(): outputClass = self.translateAbstractDbLayerNameToOutputFormat( inputClass, outputAbstractDb) (schema, className) = self.getTableSchema(inputClass) if outputClass in outputdbStructure.keys(): outputAttrList = self.reorderTupleList( outputdbStructure[outputClass].keys()) inputAttrList = self.reorderTupleList( inputdbStructure[inputClass].keys()) sql = self.gen.getFeaturesWithSQL(inputClass, inputAttrList) query = QSqlQuery(sql, self.db) while query.next(): id = query.value(0) #detects null lines for i in range(len(inputAttrList)): nullLine = True value = query.value(i) if value <> None: nullLine = False break if nullLine: if cl not in invalidated['nullLine'].keys(): invalidated['nullLine'][inputClass] = 0 invalidated['nullLine'][inputClass] += 1 #validates pks if id == None and (not nullLine): if cl not in invalidated['nullPk'].keys(): invalidated['nullPk'][inputClass] = 0 invalidated['nullPk'][inputClass] += 1 for i in range(len(inputAttrList)): value = query.value(i) #validates domain if outputClass in domainDict.keys(): if inputAttrList[i] in domainDict[ outputClass].keys(): if value not in domainDict[outputClass][ inputAttrList[i]] and (not nullLine): invalidated = self.utils.buildNestedDict( invalidated, [ 'notInDomain', inputClass, id, inputAttrList[i] ], value) #validates not nulls if outputClass in notNullDict.keys(): if outputClass in domainDict.keys(): if inputAttrList[i] in notNullDict[ outputClass] and inputAttrList[ i] not in domainDict[ outputClass].keys(): if (value == None) and (not nullLine) and ( inputAttrList[i] not in domainDict[outputClass].keys()): invalidated = self.utils.buildNestedDict( invalidated, [ 'nullAttribute', inputClass, id, inputAttrList[i] ], value) else: if inputAttrList[i] in notNullDict[ outputClass]: if (value == None) and (not nullLine) and ( inputAttrList[i] not in domainDict[outputClass].keys()): invalidated = self.utils.buildNestedDict( invalidated, [ 'nullAttribute', inputClass, id, inputAttrList[i] ], value) if outputClass in domainDict.keys(): if (inputAttrList[i] not in [ 'geom', 'GEOMETRY', 'id', 'OGC_FID' ] and schema <> 'complexos') or ( schema == 'complexos' and inputAttrList[i] <> 'id'): if inputAttrList[i] not in outputdbStructure[ outputClass].keys(): invalidated = self.utils.buildNestedDict( invalidated, [ 'attributeNotFoundInOutput', inputClass ], [inputAttrList[i]]) else: invalidated['classNotFoundInOutput'].append(inputAttrList) return invalidated
def _open_database(self, dbPath): """Opens a database. Checks if there are geometry_columns and spatial_ref_sys tables in the database, if not it creates and fills those tables. Checks if there are all PU columns in PAR table, if it it creates and fills those columns. Args: dbPath (str): A full path to the database. Raises: dw.puError: When SQLITE database driver is not available or when database connection fails. """ if not QSqlDatabase.isDriverAvailable('QSQLITE'): raise self.dW.puError( self.dW, self, u'SQLITE database driver is not available.', u'Databázový ovladač QSQLITE není dostupný.', u'Databázový ovladač QSQLITE není dostupný.') connectionName = QUuid.createUuid().toString() db = QSqlDatabase.addDatabase('QSQLITE', connectionName) db.setDatabaseName(dbPath) db.open() if not db.open(): raise self.dW.puError(self.dW, self, u'Database connection failed.', u'Nepodařilo se připojit k databázi.', u'Nepodařilo se připojit k databázi.') self.set_text_statusbar.emit(u'Kontroluji tabulky a sloupce...', 0, False) QgsApplication.processEvents() sqlQuery = QSqlQuery(db) sqlDir = QDir(self.pluginDir.path() + u'/data/sql') query = self._read_text_from_file(sqlDir.filePath(u'check_gc_srs.sql')) sqlQuery.exec_(query) QgsApplication.processEvents() checkGcSrsSize = 0 while sqlQuery.next(): checkGcSrsSize += 1 if checkGcSrsSize < 2: queries = self._read_text_from_file( sqlDir.filePath(u'create_fill_gc_srs.sql')).split(';') for query in queries: sqlQuery.exec_(query) QgsApplication.processEvents() query = self._read_text_from_file( sqlDir.filePath(u'check_pu_columns_PAR.sql')) sqlQuery.exec_(query) QgsApplication.processEvents() columns = [] while sqlQuery.next(): record = sqlQuery.record() name = str(record.value('name')) columns.append(name) if not all(column in columns for column in self.dW.allPuColumns): queries = self._read_text_from_file( sqlDir.filePath(u'add_pu_columns_PAR.sql')).split(';') for query in queries: sqlQuery.exec_(query) QgsApplication.processEvents() queries = self._read_text_from_file( sqlDir.filePath(u'create_sobr_spol.sql')).split(';') for query in queries: sqlQuery.exec_(query) db.close()
def createConnection(): db = QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("my.db") if not db.open(): QMessageBox.critical(0, "Cannot open database1", "Unable to establish a database connection.", QMessageBox.Cancel) return False query = QSqlQuery() # 创建student表 query.exec_( QString("create table student (id int primary key, " "name varchar, course int)")) query.exec_(QString("insert into student values(1, '李强', 11)")) query.exec_(QString("insert into student values(2, '马亮', 11)")) query.exec_(QString("insert into student values(3, '孙红', 12)")) # 创建course表 query.exec_( QString("create table course (id int primary key, " "name varchar, teacher varchar)")) query.exec_(QString("insert into course values(10, '数学', '王老师')")) query.exec_(QString("insert into course values(11, '英语', '张老师')")) query.exec_(QString("insert into course values(12, '计算机', '白老师')")) return True
def loadAssociatedFeatures(self, complex): try: self.checkAndOpenDb() except: return dict() associatedDict = dict() #query to get the possible links to the selected complex in the combobox complexName = complex.replace('complexos_', '') sql = self.gen.getComplexLinks(complexName) query = QSqlQuery(sql, self.db) while query.next(): #setting the variables complex_schema = query.value(0) complex = query.value(1) aggregated_schema = query.value(2) aggregated_class = query.value(3) column_name = query.value(4) #query to obtain the created complexes sql = self.gen.getComplexData(complex_schema, complex) complexQuery = QSqlQuery(sql, self.db) while complexQuery.next(): complex_uuid = complexQuery.value(0) name = complexQuery.value(1) if not (complex_uuid and name): continue associatedDict = self.utils.buildNestedDict( associatedDict, [name, complex_uuid, aggregated_class], []) #query to obtain the id of the associated feature sql = self.gen.getAssociatedFeaturesData( aggregated_schema, aggregated_class, column_name, complex_uuid) associatedQuery = QSqlQuery(sql, self.db) while associatedQuery.next(): ogc_fid = associatedQuery.value(0) associatedDict = self.utils.buildNestedDict( associatedDict, [name, complex_uuid, aggregated_class], [ogc_fid]) return associatedDict
def export_polys(self): """ Export ESUs :return: """ canvas = self.iface.mapCanvas() clayer = canvas.currentLayer() # will return 0 if none selected count = clayer.selectedFeatureCount() feature_count = clayer.featureCount() # Get list of selected features selected_poly_ids = list() if count > 0: selectedfeats = clayer.selectedFeatures() for feat in selectedfeats: selected_poly_ids.append(int(feat.attribute('rd_pol_id'))) feature_count = clayer.selectedFeatureCount() self.warn_about_selected_features(feature_count) # Prepare sql query if self.unassigned: polyexportsql = self.sql_queries['export_all'] else: polyexportsql = self.sql_queries['export_assigned_only'] # SQL to filter out unselected and/or public road records if count > 0: polyexportsql += " WHERE rd_pol_id IN ({})".format(', '.join( map(str, selected_poly_ids))) else: polyexportsql += " WHERE rd_pol_id IS NOT NULL" if self.public_only: polyexportsql += " AND is_public = 1" # Setup database temporary tables for table in ['maint_records']: # Drop tables if left behind from last export args = {'table': table} query = self.run_sql('drop_table', args) query = self.run_sql('create_maint_records') # Run the main query if config.DEBUG_MODE: print(polyexportsql) query = QSqlQuery(self.db) query.setForwardOnly(True) query.exec_(polyexportsql) if query.isActive() is False: raise StandardError('Database query problem: {}'.format( query.lastError().text())) # create layer vlayer = QgsVectorLayer("multipolygon?crs=EPSG:27700", "temp", "memory") vlayer.setCrs( QgsCoordinateReferenceSystem( 27700, QgsCoordinateReferenceSystem.EpsgCrsId)) provider = vlayer.dataProvider() # add fields self.fields = [ QgsField("poly_id", QVariant.String), QgsField("RefNo", QVariant.LongLong), QgsField("rec_type", QVariant.Int), QgsField("desctxt", QVariant.String), QgsField("Locality", QVariant.String), QgsField("Town", QVariant.String), QgsField("LocTxt", QVariant.String), QgsField("RdStatus", QVariant.LongLong), QgsField("Swa_org", QVariant.String), QgsField("Adopt_Date", QVariant.Date), QgsField("Entry_Date", QVariant.Date), QgsField("lor_no", QVariant.Int), QgsField("route", QVariant.String), QgsField("symbol", QVariant.Int), QgsField("element", QVariant.String), QgsField("hierarchy", QVariant.String) ] provider.addAttributes(self.fields) vlayer.updateFields() # Exit if output file path is invalid if len(str(self.export_path)) < 0: return False if self.check_if_export_file_in_use(): return False # Run through SQL results creating features from records self.progresswin.show() i = 0 while query.next(): if self.progresswin.wasCanceled(): self.kill_export() break record = query.record() new_feature = self.create_feature_from_record(record) provider.addFeatures([new_feature]) # Update progress bar i += 1 diff = feature_count + ( i - feature_count) if i > feature_count else feature_count self.percent_complete = (i / float(diff)) * 100 self.progresswin.setValue(self.percent_complete) if self.killed: # Show message and exit if killed export_error_msg_box = QMessageBox( QMessageBox.Warning, " ", "An error occurred while exporting the" "shapefile", QMessageBox.Ok, None) export_error_msg_box.setWindowFlags(Qt.CustomizeWindowHint | Qt.WindowTitleHint) export_error_msg_box.exec_() return False vlayer.updateExtents() result = QgsVectorFileWriter.writeAsVectorFormat( vlayer, self.export_path, "utf-8", None, "ESRI Shapefile") # checks for completed export if result == 0: self.progresswin.close() if config.DEBUG_MODE: print('DEBUG_MODE: {} features exported'.format( vlayer.featureCount())) return True
def save(self, save_not_accepted=False): text = self.textEdit.toInitialHtmlFormat() links = re.findall(r'<[Aa][^>]*>.*?</[Aa]>', text) for l in links: print("LINK - " + l) if "termin##" in l: href = re.findall(r'href=([^>]*?)>', l)[0] if "status##1##" in l: if save_not_accepted: print("HREF - " + href) new_href = '"termin;' + href.split( "##")[1].__str__() + '"' print("NEW HREF - " + new_href) new_l = l.replace(href, new_href) print("NEW LINK - " + new_l) # заменить и сохранить ссылку text = text.replace(l, new_l) else: new_l = re.findall(r'<[Aa][^>]*>(.*?)</[Aa]>', l)[0] print("RES TEXT - " + new_l) text = text.replace(l, new_l) elif "status##2##" in l: new_l = re.findall(r'<[Aa][^>]*>(.*?)</[Aa]>', l)[0] print("RES TEXT - " + new_l) # удалить ссылку, потому что не однозначная text = text.replace(l, new_l) elif "status##" not in l: print("HREF - " + href) new_href = '"termin;' + href.split("##")[1].__str__() + '"' print("NEW HREF - " + new_href) new_l = l.replace(href, new_href) print("NEW LINK - " + new_l) text = text.replace(l, new_l) # заменить и сохранить ссылку, т.к. она принята print('\n') print(text) local_update = QSqlQuery(self.local_cn) local_update.prepare(update_local_termin_query) local_update.bindValue(":linked", 1) local_update.bindValue(":uuid", self.current_uuid) remote_update = QSqlQuery(self.remote_cn) if self.onlyClassified: remote_update.prepare(update_remote_termin_query_OC) else: remote_update.prepare(update_remote_termin_query) remote_update.bindValue(":definition", text) remote_update.bindValue(":uuid", self.current_uuid) QtSql.QSqlDatabase.database('SQLiteConnection').transaction() QtSql.QSqlDatabase.database('PGconnection').transaction() local_status = False remote_status = False if remote_update.exec_(): remote_status = True else: print(remote_update.lastQuery()) print(remote_update.lastError().text()) if local_update.exec_(): local_status = True else: print(local_update.lastQuery()) print(local_update.lastError().text()) if local_status and remote_status: QtSql.QSqlDatabase.database('SQLiteConnection').commit() QtSql.QSqlDatabase.database('PGconnection').commit() return True else: QtSql.QSqlDatabase.database('SQLiteConnection').rollback() QtSql.QSqlDatabase.database('PGconnection').rollback() return False
def run(self): ################################## ###### PEGA A LAYER ATIVA ######## ################################## layer = self.iface.activeLayer() if not layer: self.iface.messageBar().pushMessage("Erro", u"Esperando uma Active Layer!", level=QgsMessageBar.CRITICAL, duration=4) return if layer.featureCount() == 0: self.iface.messageBar().pushMessage("Erro", u"a camada não possui feições!", level=QgsMessageBar.CRITICAL, duration=4) return parametros = layer.source().split(" ") # recebe todos os parametros em uma lista ( senha, porta, password etc..) #################################### ###### INICIANDO CONEXÃO DB ######## #################################### # Outra opção para isso, seria usar ex: self.dbname.. self.host.. etc.. direto dentro do laço for. dbname = "" host = "" port = 0 user = "" password = "" for i in parametros: part = i.split("=") # Recebe os parametros guardados na própria Layer if "dbname" in part[0]: dbname = part[1].replace("'", "") elif "host" in part[0]: host = part[1].replace("'", "") elif "port" in part[0]: port = int(part[1].replace("'", "")) elif "user" in part[0]: user = part[1].replace("'", "") elif "password" in part[0]: password = part[1].split("|")[0].replace("'", "") #print dbname, host, port, user, password # Testa se os parametros receberam os valores pretendidos, caso não, apresenta a mensagem informando.. if len(dbname) == 0 or len(host) == 0 or port == 0 or len(user) == 0 or len(password) == 0: self.iface.messageBar().pushMessage("Erro", u'Um dos parametros não foram devidamente recebidos!', level=QgsMessageBar.CRITICAL, duration=4) return #################################### #### SETA VALORES DE CONEXÃO DB #### #################################### connection = QSqlDatabase.addDatabase('QPSQL') connection.setHostName(host) connection.setPort(port) connection.setUserName(user) connection.setPassword(password) connection.setDatabaseName(dbname) if not connection.isOpen(): # Testa se a conexão esta recebendo os parametros adequadamente. if not connection.open(): print 'Error connecting to database!' self.iface.messageBar().pushMessage("Erro", u'Error connecting to database!', level=QgsMessageBar.CRITICAL, duration=4) print connection.lastError().text() return #################################### ###### CRIAÇÃO DE MEMORY LAYER ##### #################################### layerCrs = layer.crs().authid() # Passa o formato (epsg: numeros) flagsLayerName = layer.name() + "_flags" flagsLayerExists = False for l in QgsMapLayerRegistry.instance().mapLayers().values(): # Recebe todas as camadas que estão abertas if l.name() == flagsLayerName: # ao encontrar o nome pretendido.. self.flagsLayer = l # flagslayer vai receber o nome.. self.flagsLayerProvider = l.dataProvider() flagsLayerExists = True # se encontrado os parametros buscados, recebe True. break if flagsLayerExists == False: # se não encontrado os parametros buscados, recebe False. tempString = "Point?crs=" tempString += str(layerCrs) self.flagsLayer = QgsVectorLayer(tempString, flagsLayerName, "memory") self.flagsLayerProvider = self.flagsLayer.dataProvider() self.flagsLayerProvider.addAttributes([QgsField("id", QVariant.Int), QgsField("motivo", QVariant.String)]) self.flagsLayer.updateFields() self.flagsLayer.startEditing() ids = [feat.id() for feat in self.flagsLayer.getFeatures()] self.flagsLayer.deleteFeatures(ids) self.flagsLayer.commitChanges() lista_fid = [] # Iniciando lista for f in layer.getFeatures(): lista_fid.append(str(f.id())) # Guarda na lista. A lista de Feature ids passa tipo "int", foi convertido e guardado como "str". source = layer.source().split(" ") self.tableName = "" # Inicia vazio layerExistsInDB = False for i in source: if "table=" in i or "layername=" in i: # Se encontrar os atributos pretendidos dentre todos do for self.tableName = source[source.index(i)].split(".")[1] # Faz split em ponto e pega a segunda parte. self.tableName = self.tableName.replace('"', '') layerExistsInDB = True break if layerExistsInDB == False: self.iface.messageBar().pushMessage("Erro", u"Provedor da camada corrente não provem do banco de dados!", level=QgsMessageBar.CRITICAL, duration=4) return # Busca através do SQL query_string = '''select distinct (reason(ST_IsValidDetail(f."{2}",0))) AS motivo, ''' query_string += '''ST_AsText(ST_Multi(ST_SetSrid(location(ST_IsValidDetail(f."{2}",0)), ST_Srid(f.{2})))) as local from ''' query_string += '''(select "{3}", "{2}" from only "{0}"."{1}" where ST_IsValid("{2}") = 'f' and {3} in ({4})) as f''' query_string = query_string.format(self.tableSchema, self.tableName, self.geometryColumn, self.keyColumn, ",".join(lista_fid)) query = QSqlQuery(query_string) self.flagsLayer.startEditing() flagCount = 0 # iniciando contador que será referência para os IDs da camada de memória. listaFeatures = [] while query.next(): motivo = query.value(0) local = query.value(1) flagId = flagCount flagFeat = QgsFeature() flagGeom = QgsGeometry.fromWkt(local) # passa o local onde foi localizado o erro. flagFeat.setGeometry(flagGeom) flagFeat.initAttributes(2) flagFeat.setAttribute(0,flagId) # insere o id definido para a coluna 0 da layer de memória. flagFeat.setAttribute(1, motivo) # insere o motivo/razão pré-definida para a coluna 1 da layer de memória. listaFeatures.append(flagFeat) flagCount += 1 # incrementando o contador a cada iteração self.flagsLayerProvider.addFeatures(listaFeatures) self.flagsLayer.commitChanges() # Aplica as alterações à camada. QgsMapLayerRegistry.instance().addMapLayer(self.flagsLayer) # Adicione a camada no mapa ################################## ##### TROCA MARCADOR DA LAYER #### ################################## # # AQUI PRETENDO PERSONALIZAR O MARCADOR DA MEMORY LAYER, POREM A PRINCIPIO SÓ ESTA ALTERANDO A LAYER ORIGINAL. # for lay in QgsMapLayerRegistry.instance().mapLayers().values(): # if lay == flagsLayerName and not layer.name(): # # Estilo base. # line = QgsLineSymbolV2() # # Crie uma linha de marcadores. # marker_line = QgsMarkerLineSymbolLayerV2() # marker_line.setInterval(10) # # Configure o marcador. # simple_marker = QgsSimpleMarkerSymbolLayerV2() # simple_marker.setShape(QgsSimpleMarkerSymbolLayerBase.HalfSquare) # Formato # simple_marker.setSize(3) # simple_marker.setAngle(180) # simple_marker.setColor(QColor('red')) # cor # simple_marker.setOutlineColor(QColor('red')) # cor # # O marcador tem sua própria camada de símbolo. # marker = QgsMarkerSymbolV2() # marker.changeSymbolLayer(0, simple_marker) # # Adicione a camada à camada do marcador. # marker_line.setSubSymbol(marker) # # Finalmente, substitua a camada de símbolo no estilo base. # line.changeSymbolLayer(0, marker_line) # # Adicione o estilo à camada de linha. # renderer = QgsSingleSymbolRendererV2(line) # layer.setRendererV2(renderer) if flagCount == 0: QgsMapLayerRegistry.instance().removeMapLayer(self.flagsLayer.id()) self.iface.messageBar().pushMessage("Aviso", u"Não foi encontrado Flags em \"" + layer.name() + "\" !", level=QgsMessageBar.CRITICAL, duration=4) return if len(query.lastError().text()) == 1: self.iface.messageBar().pushMessage("Aviso", "foram geradas " + str(flagCount) + " flags para a camada \"" + layer.name() + "\" !", level=QgsMessageBar.INFO, duration=4) else: self.iface.messageBar().pushMessage("Erro", u"a geração de flags falhou!", level=QgsMessageBar.CRITICAL, duration=4) print query.lastError().text()
def setup_db_sqlite(): db = QSqlDatabase.addDatabase('QSQLITE') db.setDatabaseName(":memory:") if not db.open(): QMessageBox.critical(None, "No se puede acceder", "No se puede acceder a la DB") sys.exit(2) query = QSqlQuery() # Toma la DB definida... query.exec_("create table person(id int primary key, " "firstname varchar(20), lastname varchar(20))") query.exec_("insert into person values(102, 'Christine', 'Holand')") query.exec_("insert into person values(103, 'Lars', 'Gordon')") query.exec_("insert into person values(104, 'Roberto', 'Robitaille')") query.exec_("insert into person values(105, 'Maria', 'Papadopoulos')") query.exec_("select id, firstname from person") return query