def getGeomColumnDict(self): ''' Dict in the form 'geomName':[-list of table names-] ''' self.checkAndOpenDb() sql = self.gen.getGeomColumnDict() query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception( self.tr("Problem getting geom column dict: ") + query.lastError().text()) geomDict = dict() while query.next(): geomColumn = query.value(0) tableName = query.value(1) lyrName = '_'.join(tableName.split('_')[1::]) if geomColumn not in geomDict.keys(): geomDict[geomColumn] = [] geomDict[geomColumn].append(lyrName) return geomDict
def obtainLinkColumn(self, complexClass, aggregatedClass): ''' Obtains the link column between complex and aggregated class complexClass: complex class name aggregatedClass: aggregated class name ''' self.checkAndOpenDb() #query to obtain the link column between the complex and the feature layer sql = self.gen.getLinkColumn(complexClass.replace('complexos_', ''), aggregatedClass) query = QSqlQuery(sql, self.db) if not query.isActive(): self.db.close() raise Exception( self.tr("Problem obtaining link column: ") + query.lastError().text()) column_name = "" while query.next(): column_name = query.value(0) return column_name
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))+'.'+str(query.value(1)) fieldName = str(query.value(2)) if str(query.value(0)) == 'complexos' or className.split('_')[-1] in ['p','l','a']: if className not in classDict.keys(): classDict[className]=dict() classDict[className][fieldName]=fieldName if 'geom' in classDict[className].keys(): classDict[className]['geom'] = 'GEOMETRY' if str(query.value(0)) <> 'complexos' and 'id' in classDict[className].keys(): classDict[className]['id'] = 'OGC_FID' return classDict
def listGeomClassesFromDatabase(self, primitiveFilter=[]): ''' Gets a list with geometry classes from database ''' self.checkAndOpenDb() classList = [] sql = self.gen.getTablesFromDatabase() query = QSqlQuery(sql, self.db) if not query.isActive(): self.db.close() raise Exception( self.tr("Problem listing geom classes: ") + query.lastError().text()) while query.next(): tableName = str(query.value(0)) layerName = tableName if tableName.split("_")[-1] == "p" or tableName.split("_")[-1] == "l" \ or tableName.split("_")[-1] == "a": classList.append(layerName) return classList
def countElements(self, layers): ''' Counts the number of elements in each layer present in layers ''' self.checkAndOpenDb() listaQuantidades = [] for layer in layers: (table, schema) = self.getTableSchema(layer) if layer.split('_')[-1] in ['p', 'l', 'a' ] or schema == 'complexos': sql = self.gen.getElementCountFromLayer(layer) query = QSqlQuery(sql, self.db) query.next() number = query.value(0) if not query.exec_(sql): raise Exception( self.tr("Problem counting elements: ") + query.lastError().text()) listaQuantidades.append([layer, number]) return listaQuantidades
def close_current_record(self, usrn, closure_date, state): """ Close the street record :param usrn: current USRN :param closure_date: date string (yyyymmdd) :param state: index of state combo """ sql = """UPDATE tblSTREET SET currency_flag=1, closure_date={closure_date}, closed_by='{username}', street_state={state}, state_date={closure_date} WHERE usrn={usrn} AND currency_flag=0; """.format(closure_date=closure_date, usrn=usrn, username=self.params['UserName'], state=state) query = QSqlQuery(sql, self.db)
def listComplexClassesFromDatabase(self): ''' Gets a list with complex classes from database ''' self.checkAndOpenDb() classList = [] sql = self.gen.getTablesFromDatabase() query = QSqlQuery(sql, self.db) if not query.isActive(): self.db.close() raise Exception( self.tr("Problem listing complex classes: ") + query.lastError().text()) while query.next(): tableName = str(query.value(0)) layerName = tableName tableSchema = layerName.split('_')[0] if tableSchema == 'complexos': classList.append(layerName) return classList
def getLayersWithElementsV2(self, layerList, useInheritance=False): self.checkAndOpenDb() lyrWithElemList = [] for layer in layerList: if isinstance(layer, dict): schema = layer['tableSchema'] lyr = layer['tableName'] else: if '.' in layer: schema, lyr = layer.replace('"', '').split('.') else: lyr = layer schema = self.getTableSchemaFromDb(lyr) sql = self.gen.getElementCountFromLayerV2(schema, lyr, useInheritance) query = QSqlQuery(sql, self.db) query.next() if query.value(0) > 0: lyrWithElemList.append(lyr) return lyrWithElemList
def reg_producto(self): db = QSqlDatabase.addDatabase('QMYSQL') db.setHostName("localhost") db.setDatabaseName("TiendaVrt") db.setUserName("root") db.setPassword("") if not db.open(): print("Could not open testdb database") print(db.lastError().driverText()) print(db.lastError().databaseText()) else: query = QSqlQuery() query.exec_("CALL TiendaVrt.InsertarProducto(" + self.txt_cod.text() + ",'" + self.txt_prod.text() + "', " + self.txt_precio.text() + ", " + self.txt_cant.text() + ", " + self.txt_cod_cat.text() + ");") print("Database is OK ") db.close()
def load(self, query): self.nesting = 1 self.root = BranchNode("") try: query = QSqlQuery(query) if not query.exec_(): raise Exception("No se pudieron recuperar las categorias") self.columns = query.record().count() print query.size() if query.size() > 0: while query.next(): fields = [] for i in range(self.columns): fields.append(str(query.value(i))) self.addRecord(fields, False) except Exception as inst: print unicode(inst) return False
def makeValueRelationDict(self, table, codes): """ Makes the value relation dictionary. It is necessary for multi valued attributes """ ret = dict() in_clause = '(%s)' % ",".join(map(str, codes)) if self.db.driverName() == 'QPSQL': sql = 'select code, code_name from dominios.%s where code in %s' % ( table, in_clause) elif self.db.driverName() == 'QSQLITE': sql = 'select code, code_name from dominios_%s where code in %s' % ( table, in_clause) query = QSqlQuery(sql, self.db) while query.next(): code = str(query.value(0)) code_name = query.value(1) ret[code_name] = code return ret
def get_mcl_ref_from_rd_pol_id(self, rd_pol_id): """ Query database to get mcl_ref associated with given polygon :param rd_pol_id: str, id number :return mcl_ref: str, id number """ sql = """ SELECT mcl_cref FROM rdpoly WHERE rd_pol_id = {}""".format(rd_pol_id) query = QSqlQuery(sql, self.db) if not query.first(): msg = "No MCLs are linked to polygon {}".format(rd_pol_id) raise rn_except.RampNoLinkedPolyPopupError(msg) mcl_ref = query.record().value('mcl_cref') if isinstance(mcl_ref, QPyNullVariant): msg = "No MCLs are linked to polygon {}".format(rd_pol_id) raise rn_except.RampNoLinkedPolyPopupError(msg) return str(mcl_ref)
def street_ref_types(self, esu_id): """ Get the street type from the db :param esu_id: esu ID :return: List of all types associated with esu """ sql = """ SELECT tblSTREET.street_ref_type FROM lnkESU_STREET INNER JOIN tblSTREET ON lnkESU_STREET.usrn_version_no = tblSTREET.version_no AND lnkESU_STREET.usrn = tblSTREET.usrn WHERE lnkESU_STREET.esu_id = {esu_id} AND lnkESU_STREET.currency_flag = 0 AND tblSTREET.currency_flag = 0 ;""".format(esu_id=esu_id) query = QSqlQuery(sql, self.db) all_type_links = [] while query.next(): type_ = int(query.value(0)) all_type_links.append(type_) return all_type_links
def getDefinition(uuid, oc): remote_sql = QSqlQuery(get_remote_connection()) if oc: remote_sql.prepare(get_definition_query_OC) else: remote_sql.prepare(get_definition_query) remote_sql.bindValue(":uuid", uuid.__str__()) if remote_sql.exec_(): if remote_sql.next(): definition = remote_sql.value(0).__str__() definition = definition.replace(' ', ' ') definition = definition.replace(' </A>', '</A> ') fs = re.findall(r'<[Aa][^>]*> ', definition) for f in fs: new_f = f.strip() definition = definition.replace(f, new_f) return definition else: return None else: return None
def dropDatabase(self, db): """ Drops the created database case a problem occurs during database creation db: QSqlDatabase to be dropped """ host = db.hostName() port = db.port() user = db.userName() password = db.password() database = 'postgres' pgDB = QSqlDatabase('QPSQL') pgDB.setHostName(host) pgDB.setPort(port) pgDB.setUserName(user) pgDB.setPassword(password) pgDB.setDatabaseName(database) if not pgDB.open(): return False sql = self.gen.dropDatabase(db.databaseName()) query = QSqlQuery(pgDB) return query.exec_(sql)
def setQuery(self, str, db=None): if db == None: self.query = QSqlQuery(str) else: self.query = str QSqlQueryModel.setQuery(self, str) del self.data self.data = [] self.rCount = QSqlQueryModel.rowCount(self) if self.rCount > 10000: self.rCount = 10000 self.cCount = QSqlQueryModel.columnCount(self) for i in range(self.rCount): row = [] for j in range(self.cCount): row.append( QSqlQueryModel.data(self, QSqlQueryModel.index(self, i, j))) self.data.append(row) self.clear() print self.rowCount(), self.columnCount()
def _run_test_query(db_file_path): """ Runs SQL (sqlite) query to test database file is valid. Returns True for success, False otherwise. :param db: Open QSqlDatabase object :return bool: True if query runs succesfully. """ if config.DEBUG_MODE: print('DEBUG_MODE: testing database connection') db = connect_and_open(db_file_path, 'test_connection') query = QSqlQuery(db=db) result = query.exec_("""SELECT * FROM sqlite_master""") del (query) connection_name = db.connectionName() db.close() del (db) QSqlDatabase.removeDatabase(connection_name) if config.DEBUG_MODE: print('DEBUG_MODE: closing QSqlDatabase {}'.format(connection_name)) return result
def makeValueRelationDict(self, table, codes): """ Makes a query to obtain a dictionary with code names and related codes """ ret = dict() in_clause = '(%s)' % ",".join(map(str, codes)) if self.abstractDb.db.driverName() == 'QPSQL': sql = 'select code, code_name from dominios.%s where code in %s' % ( table, in_clause) elif self.abstractDb.db.driverName() == 'QSQLITE': sql = 'select code, code_name from dominios_%s where code in %s' % ( table, in_clause) query = QSqlQuery(sql, self.abstractDb.db) while query.next(): code = query.value(0) code_name = query.value(1) ret[code_name] = code return ret
def getGeomDict(self, getCentroids=False): ''' returns a dict like this: {'tablePerspective' : { 'layerName' : ''' self.checkAndOpenDb() edgvVersion = self.getDatabaseVersion() sql = self.gen.getGeomTablesFromGeometryColumns(edgvVersion) query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception( self.tr("Problem getting geom tables from db: ") + query.lastError().text()) geomDict = dict() geomDict['primitivePerspective'] = self.getGeomTypeDict() geomDict['tablePerspective'] = dict() while query.next(): isCentroid = False srid = query.value(0) if edgvVersion in ('2.1.3', 'FTer_2a_Ed'): geometryType = query.value(2) else: geometryType = self.getResolvedGeomType(query.value(2)) tableName = query.value(3) tableSchema = tableName.split('_')[0] geometryColumn = query.value(1) layerName = '_'.join(tableName.split('_')[1::]) if layerName not in geomDict['tablePerspective'].keys(): geomDict['tablePerspective'][layerName] = dict() geomDict['tablePerspective'][layerName]['schema'] = tableSchema geomDict['tablePerspective'][layerName]['srid'] = str(srid) geomDict['tablePerspective'][layerName][ 'geometryColumn'] = geometryColumn geomDict['tablePerspective'][layerName][ 'geometryType'] = geometryType geomDict['tablePerspective'][layerName][ 'tableName'] = tableName return geomDict
def getGeomTypeDict(self, loadCentroids=False): self.checkAndOpenDb() edgvVersion = self.getDatabaseVersion() sql = self.gen.getGeomByPrimitive(edgvVersion) query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception( self.tr("Problem getting geom types from db: ") + query.lastError().text()) geomDict = dict() while query.next(): if edgvVersion in ('2.1.3', 'FTer_2a_Ed'): type = query.value(0) else: type = self.getResolvedGeomType(query.value(0)) tableName = query.value(1) layerName = '_'.join(tableName.split('_')[1::]) if type not in geomDict.keys(): geomDict[type] = [] if layerName not in geomDict[type]: geomDict[type].append(layerName) return geomDict
def insertFrame(self, scale, mi, inom, frame, paramDict=dict()): self.checkAndOpenDb() srid = self.findEPSG() geoSrid = QgsCoordinateReferenceSystem( int(srid)).geographicCRSAuthId().split(':')[-1] sql = self.gen.insertFrame(scale, mi, inom, frame, srid, geoSrid, paramDict=paramDict) self.db.transaction() query = QSqlQuery(self.db) if not query.exec_(sql): self.db.rollback() self.db.close() raise Exception( self.tr('Problem inserting frame: ') + query.lastError().text()) self.db.commit() self.db.close()
def getUserRelatedRoles(self, username): try: self.checkAndOpenDb() except: return [], [] installed = [] assigned = [] sql = self.gen.getUserRelatedRoles(username) query = QSqlQuery(sql, self.db) while query.next(): rolname = query.value(0) usename = query.value(1) if not usename: installed.append(rolname) else: assigned.append(rolname) installed.sort() assigned.sort() return installed, assigned
def createResolvedDomainViews(self, createViewClause, fromClause): try: self.checkAndOpenDb() except: return if self.checkSuperUser(): filename = self.getSqlViewFile() if filename <> None: file = codecs.open(filename, encoding='utf-8', mode="r") sql = file.read() sql = sql.replace('[VIEW]', createViewClause).replace('[FROM]', fromClause) file.close() commands = sql.split('#') self.db.transaction() query = QSqlQuery(self.db) for command in commands: if not query.exec_(command): self.db.rollback() self.db.close() raise Exception(self.tr('Problem creating views: ') + str(query.lastError().text())) self.db.commit() self.db.close()
def get_mcl_cref(self, rd_pol_id): """ Get the MCL ref attached to given polygon :param rd_pol_id: :return: str, mcl_cref """ sql = """ SELECT mcl_cref FROM rdpoly WHERE rd_pol_id = '{}' ;""".format(rd_pol_id) query = QSqlQuery(sql, self.db) if not query.isActive(): msg = "Invalid rd_pol_id:" msg += "\n\nSQL command:\n\n{}".format(sql) msg += "\n\nDatabase reply:\n\n{}".format(query.lastError().text()) raise rn_except.RampRdPolyUpdateFailedPopupError(msg) query.first() mcl_ref = str(query.record().value('mcl_cref')) return mcl_ref
def fetchall(self, db, sql): rows = [] qry = QSqlQuery(db) if qry.exec_(sql): rec = qry.record() while qry.next(): row = {} for i in range(0, rec.count()): v = "%s" % qry.value(i) if v == "NULL": v = '' row[rec.fieldName(i)] = v.strip() rows.append(row) else: qDebug("Exec failed: " + qry.lastError().text()) return rows
def fill_fields(self): query = QSqlQuery(get_remote_connection()) if self.onlyClassified: query.prepare(get_name_and_short_def_query_OC) else: query.prepare(get_name_and_short_def_query) query.bindValue(':uuid', self.uuid) if query.exec_(): if query.next(): self.mainWordLabel.setText('Ссылка на термин: <b>' + query.value(0) + "</b>") short_def = remove_all_tags(query.value(1)) if len(query.value(1)) < 299: self.definitionLabel.setText("Определение: " + short_def) else: self.definitionLabel.setText("Определение: " + short_def + '...') else: self.mainWordLabel.setText('Термин не найден') self.definitionLabel.setText("Возможно термин, на который ведет данная ссылка был удален или" " Вы подключились к другой БД. Пожалуйста, обновите список терминов.") else: print(query.lastError().text()) print(query.lastQuery())
def get_spatialite_version_as_int(db): """ Query the database to find spatialite version :param db: open QSqlDatabase object :return: Integer form of version number e.g. 411 """ # Query the database sql = "SELECT spatialite_version() AS version;" query = QSqlQuery(sql, db) query_success = query.first() if query_success is False: msg = "Cannot get spatialite version. Database replied: {}".format( query.lastError().text()) raise RuntimeError(msg) # Get the version number and convert to int record = query.record() version = record.value('version') version_as_int = int(re.sub('\D', '', version)) return version_as_int
def set_values(self): """ set all required values to show in the form widgets from db :return: string[] """ i = 0 meta_values = [] qry_md_vals = QSqlQuery(self.db) qry_md_vals.exec_(self.get_meta_info) rec = qry_md_vals.record() field_count = rec.count() meta_vals = [ rec.indexOf("name"), rec.indexOf("scope"), rec.indexOf("territory"), rec.indexOf("owner"), rec.indexOf("custodian"), rec.indexOf("coord_sys"), rec.indexOf("coord_units"), rec.indexOf("metadata_date"), rec.indexOf("class_scheme"), rec.indexOf("code_scheme"), rec.indexOf("custodian_code"), rec.indexOf("gaz_language"), rec.indexOf("charset") ] while qry_md_vals.next(): while i <= field_count - 1: if i == 7: # handles date formatting date_obj = datetime.strptime( str(qry_md_vals.value(meta_vals[i])), "%Y%m%d") meta_date_clean = str(date_obj.strftime("%d/%m/%Y")) meta_values.append(meta_date_clean) i += 1 continue meta_values.append(str(qry_md_vals.value(meta_vals[i]))) i += 1 return meta_values
def save(self, iddocumento, linea): """ Este metodo guarda la linea en la base de datos @param iddocumento: el id del documento al que esta enlazada la linea """ if not self.valid: raise Exception("Se intento guardar una linea no valida") query = QSqlQuery() if not query.prepare(""" INSERT INTO productosxdocumento (iddocumento, idprecioproducto,cantidadcajas,linea) VALUES( :iddocumento, :idarticulo, :unidades,:linea) """): raise Exception("no esta preparada") query.bindValue(":iddocumento", iddocumento) query.bindValue(":idarticulo", self.itemId) query.bindValue(":unidades", self.quantity * -1) query.bindValue(":linea", linea) if not query.exec_(): print(query.lastError().text()) raise Exception("line %d" % self.itemId)
def CadastrarVeiculo(veiculo): conn = ConexaoSQL db = conn.getConexao() db.open() query = QSqlQuery() query.prepare( "INSERT INTO Veiculo(Modelo, Marca, AnoModelo, Placa, " "Alugado, Batido, KmAtual, ValorDiaria, Descricao, TipoVeiculo) " "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)") query.addBindValue(veiculo.Modelo) query.addBindValue(veiculo.Marca) query.addBindValue(veiculo.AnoModelo) query.addBindValue(veiculo.Placa) query.addBindValue(veiculo.Alugado) query.addBindValue(veiculo.Batido) query.addBindValue(veiculo.KmAtual) query.addBindValue(veiculo.ValorDiaria) query.addBindValue(veiculo.Descricao) query.addBindValue(veiculo.TipoVeiculo) query.exec_() db.commit()