def getAllEdgvDomainsFromTableName(self, table): """ EDGV databases deployed by DSGTools have a set of domain tables. Gets the value map from such DB. It checks for all attributes found. :param table: (str) layer to be checked for its EDGV mapping. :param table: (QgsVectorLayer) overloaded method - layer to be checked for its EDGV mapping. :param field: (str) field to be checked. :return: (dict) value map for all attributes that have one. """ ret = defaultdict(dict) currentLayer = table if isinstance(table, QgsVectorLayer) else self.layerByName(table) if currentLayer.isValid(): try: uri = currentLayer.dataProvider().uri() if uri.host() == '': db = QSqlDatabase('QSQLITE') db.setDatabaseName( uri.uri().split("|")[0].strip() if uri.uri().split("|")[0].strip().endswith(".gpkg") \ else uri.database() ) sql = 'select code, code_name from dominios_{field} order by code' 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()) sql = 'select code, code_name from dominios.{field} order by code' if not db.open(): db.close() return ret for field in currentLayer.fields(): fieldName = field.name() if fieldName in self.specialEdgvAttributes(): # EDGV "special" attributes that are have different domains depending on # which class it belongs to if self.edgvVersion(db) in ("2.1.3 Pro", "3.0 Pro"): cat = table if isinstance(table, str) else table.name() # Pro versions now follow the logic "{attribute}_{CLASS_NAME}" cat = cat.rsplit("_", 1)[0].split("_", 1)[-1] else: cat = (table if isinstance(table, str) else table.name()).split("_")[0] fieldN = "{attribute}_{cat}".format(attribute=fieldName, cat=cat) query = QSqlQuery(sql.format(field=fieldN), db) else: query = QSqlQuery(sql.format(field=fieldName), db) if not query.isActive(): continue while query.next(): code = str(query.value(0)) code_name = query.value(1) ret[fieldName][code_name] = code db.close() except: pass return ret
def testCreateEmptyLayer(self): # cleanup (it seems overwrite option doesn't clean the sdo_geom_metadata table) self.execSQLCommand('DROP TABLE "QGIS"."EMPTY_LAYER"', ignore_errors=True) self.execSQLCommand("DELETE FROM user_sdo_geom_metadata where TABLE_NAME='EMPTY_LAYER'", ignore_errors=True) uri = self.dbconn + "srid=4326 type=POINT table=\"EMPTY_LAYER\" (GEOM)" exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=QgsFields(), geometryType=QgsWkbTypes.Point, crs=QgsCoordinateReferenceSystem(4326), overwrite=True) self.assertEqual(exporter.errorCount(), 0) self.assertEqual(exporter.errorCode(), 0) # check IF there is an empty table (will throw error if the EMPTY_LAYER table does not exist) self.execSQLCommand('SELECT count(*) FROM "QGIS"."EMPTY_LAYER"') # check that metadata table has been correctly populated query = QSqlQuery(self.conn) self.assertTrue(query.exec_("SELECT column_name, srid FROM user_sdo_geom_metadata WHERE table_name = 'EMPTY_LAYER'")) self.assertTrue(query.next()) self.assertEqual(query.value(0), "GEOM") self.assertEqual(query.value(1), 4326) query.finish() # no feature, so we cannot guess the geometry type, so the layer is not valid # but srid is set for provider in case you want to add a feature even if the layer is invalid! # layer sourceCrs is empty because the layer is not considered spatial (not know geometry type) vl = QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."EMPTY_LAYER" (GEOM) sql=', 'test', 'oracle') self.assertFalse(vl.isValid()) self.assertEqual(vl.dataProvider().sourceCrs().authid(), "EPSG:4326") # so we set the geometry type vl = QgsVectorLayer(self.dbconn + ' sslmode=disable type=POINT table="QGIS"."EMPTY_LAYER" (GEOM) sql=', 'test', 'oracle') self.assertTrue(vl.isValid()) self.assertEqual(vl.sourceCrs().authid(), "EPSG:4326") f = QgsFeature(vl.fields()) f.setGeometry(QgsGeometry.fromWkt('POINT (43.5 1.42)')) vl.dataProvider().addFeatures([f]) query = QSqlQuery(self.conn) self.assertTrue(query.exec_('SELECT "l"."GEOM"."SDO_SRID" from "QGIS"."EMPTY_LAYER" "l"')) self.assertTrue(query.next()) self.assertEqual(query.value(0), 4326) query.finish() # now we can autodetect geom type and srid vl = QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."EMPTY_LAYER" (GEOM) sql=', 'test', 'oracle') self.assertTrue(vl.isValid()) self.assertEqual(vl.sourceCrs().authid(), "EPSG:4326")
def get_count_dates(self): query = QSqlQuery(self.db) query_str = ("select start_process_date, end_process_date " "from comptages.count where id = {};".format( self.count_id)) query.exec_(query_str) result = [] while query.next(): start_date = query.value(0) end_date = query.value(1) i = 0 while True: date = start_date.addDays(i) if date <= end_date: result.append( (int(date.toString('yyyy')), int(date.toString('MM')), int(date.toString('dd')))) i += 1 else: break return result
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) if not query.next(): # use may not have permission to read the table from schema QgsMessageLog.logMessage( self.tr("Unable to read table {0}. Error message: '{1}'")\ .format( self.db.databaseName(), self.db.lastError().databaseText() ), "DSGTools Plugin", Qgis.Warning ) continue if query.value(0) > 0: lyrWithElemList.append(lyr) return lyrWithElemList
def getDbsFromServer(self, name): """ Gets server databases name: server name """ gen = self.factory.createSqlGenerator(driver=DsgEnums.DriverPostGIS) (host, port, user, password) = self.getServerConfiguration(name) database = 'postgres' postgisDb = self.dbFactory.createDbFactory(DsgEnums.DriverPostGIS) postgisDb.connectDatabaseWithParameters(host, port, database, user, password) if not postgisDb.db.open(): QgsMessageLog.logMessage(db.lastError().text(), "DSGTools Plugin", Qgis.Critical) QMessageBox.critical( self.iface.mainWindow(), self.tr('Critical'), self.tr('A problem occurred! Check log for details.')) query = QSqlQuery(gen.getDatabasesFromServer(), postgisDb.db) if not query.isActive(): QMessageBox.critical( self.iface.mainWindow(), self.tr('Critical'), self.tr("Problem executing query: ") + query.lastError().text()) dbList = [] while query.next(): dbList.append(query.value(0)) postgisDb.closeDatabase() return self.browseServer(dbList, host, port, user, password)
def get_detail_category_chart_data(self, count_id, status, section_id): self.init_db_connection() query = QSqlQuery(self.db) query_str = ("select cat.code, cat.name, count(det.id_category) from " "comptages.count_detail as det " "join comptages.category as cat " "on det.id_category = cat.id " "join comptages.lane as lan " "on det.id_lane = lan.id " "where det.id_count = {} " "and det.import_status = {} " "and lan.id_section = '{}' " "group by det.id_category, cat.code, cat.name " "order by cat.code;".format(count_id, status, section_id)) query.exec_(query_str) labels = [] values = [] while query.next(): labels.append("{} ({})".format(str(query.value(1)), str(query.value(0)))) values.append(query.value(2)) return labels, values
def write_row_into_db(self, line): row = self.parse_data_line(line) if not row: return query = QSqlQuery(self.db) query_str = ("insert into comptages.count_detail (" "numbering, timestamp, " "distance_front_front, distance_front_back, " "speed, length, height, " "file_name, import_status, " "id_lane, id_count, id_category) values (" "{}, '{}', {}, {}, {}, {}, '{}', '{}', {}, {}, " "{}, {})".format( row['numbering'], row['timestamp'], row['distance_front_front'], row['distance_front_back'], row['speed'], row['length'], row['height'], self.basename, Layers.IMPORT_STATUS_QUARANTINE, self.lanes[int(row['lane'])+1], self.count_id, self.categories[row['category']])) query.exec_(query_str)
def check_geom(self, layer, pk, wkt, wkt_ref=None, check_valid=True): """ Add geom to the layer and check everything is OK """ table = layer.dataProvider().uri().table() # insert geom in layer self.assertTrue(layer.startEditing()) feature = QgsFeature(layer.fields()) geom = QgsGeometry.fromWkt(wkt) feature.setAttributes([pk]) feature.setGeometry(geom) self.assertTrue(layer.addFeature(feature)) self.assertTrue(layer.commitChanges()) if check_valid: self.assertTrue(self.conn) query = QSqlQuery(self.conn) sql = """select p.GEOM.st_isvalid() from QGIS.{} p where "pk" = {}""".format( table, pk) res = query.exec_(sql) self.assertTrue(res, sql + ': ' + query.lastError().text()) query.next() valid = query.value(0) self.assertTrue( valid, "geometry '{}' inserted in database is not valid".format(wkt)) query.finish() expected_wkt = wkt if wkt_ref is None else wkt_ref res_wkt = layer.getFeature(pk).geometry().asWkt() self.assertTrue( compareWkt(res_wkt, expected_wkt, 0.00001), "\nactual = {}\nexpected = {}".format(res_wkt, expected_wkt))
def getStructureDict(self): ''' Gets database structure according to the edgv version ''' self.checkAndOpenDb() classDict = dict() sql = self.gen.getStructure(self.getDatabaseVersion()) query = QSqlQuery(sql, self.db) if not query.isActive(): self.db.close() raise Exception( self.tr("Problem getting database structure: ") + query.lastError().text()) 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 list(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 list(classDict[className].keys()): classDict[className]['GEOMETRY'] = 'geom' if 'geometry' in list(classDict[className].keys()): classDict[className]['geometry'] = 'geom' if 'OGC_FID' in list(classDict[className].keys()): classDict[className]['OGC_FID'] = 'id' return classDict
def get_aggregate_category_chart_data(self, count_id, status, section_id): self.init_db_connection() query = QSqlQuery(self.db) query_str = ("select sum(cls.value), cat.code, cat.name from " "comptages.count_aggregate as agg " "join comptages.count_aggregate_value_cls as cls " "on agg.id = cls.id_count_aggregate " "join comptages.category as cat " "on cls.id_category = cat.id " "join comptages.lane as lan " "on agg.id_lane = lan.id " "where agg.id_count = {} and agg.type = 'CLS' " "and agg.import_status = {} " "and lan.id_section = '{}' " "group by cat.code, cat.name " "order by cat.code;".format(count_id, status, section_id)) query.exec_(query_str) labels = [] values = [] while query.next(): labels.append("{} ({})".format(str(query.value(2)), str(query.value(1)))) values.append(query.value(0)) return labels, values
def test_data_detail(self): self.db.open() query = QSqlQuery(self.db) query.exec_("SELECT id FROM comptages.installation \ WHERE name = '64080011';") query.next() installation_id = query.value(0) query.exec_("SELECT id FROM comptages.model \ WHERE name = 'M660_LT';") query.next() model_id = query.value(0) query.exec_("select id from comptages.sensor_type \ where name = 'Tube'") query.next() sensor_type_id = query.value(0) query.exec_("select id from comptages.class \ where name = 'SWISS10'") query.next() class_id = query.value(0) query_str = ( "INSERT INTO comptages.count(id, " "start_process_date, end_process_date, start_service_date, " "end_service_date, id_sensor_type, id_model, id_class, " "id_installation) " "VALUES (1, '2018-12-18', '2018-12-20', '2018-12-18', " "'2018-12-20', {}, {}, {}, {});".format( sensor_type_id, model_id, class_id, installation_id)) query.exec_(query_str) task = self.comptages.import_file( os.path.join( self.test_data_path, 'data_loader_simple_detail.V01'), 1) task.waitForFinished() # Let the time to the db to finish the writing time.sleep(1) data_loader = DataLoader( 1, '64080011', self.layers.IMPORT_STATUS_QUARANTINE) count_data = data_loader.load() self.assertEqual( [1, 0, 3, 0, 1, 3, 0, 1, 0, 0, 0, 0, 2], count_data.day_data[0].hour_data[15].direction_data[0].speed_data) self.assertEqual( [1, 1, 2, 1, 1, 1, 1, 1, 1, 1], count_data.day_data[0].hour_data[15].direction_data[0].category_data) self.assertEqual(20, count_data.day_data[0].total()) self.assertEqual(16, count_data.day_data[0].light_vehicles()) self.assertEqual(4, count_data.day_data[0].heavy_vehicles()) self.assertEqual(20.0, count_data.day_data[0].percent_heavy_vehicles())
def get_sections_with_data_of_count(self, count_id, status): self.init_db_connection() query = QSqlQuery(self.db) query_str = ( "select distinct(lan.id_section) from comptages.count_aggregate " "as cou " "join comptages.lane as lan " "on lan.id = cou.id_lane " "where cou.id_count = {0} " "and import_status = {1} " "union " "select distinct(lan.id_section) from comptages.count_detail " "as cou " "join comptages.lane as lan " "on lan.id = cou.id_lane " "where cou.id_count = {0} " "and import_status = {1} ".format(count_id, status)) result = [] query.exec_(query_str) while query.next(): result.append(query.value(0)) return result
def get_aggregate_speed_chart_data(self, count_id, status, section_id): self.init_db_connection() query = QSqlQuery(self.db) query_str = ("select sum(spd.value), spd.low, spd.high from " "comptages.count_aggregate as agg " "join comptages.count_aggregate_value_spd as spd " "on agg.id = spd.id_count_aggregate " "join comptages.lane as lan " "on agg.id_lane = lan.id " "where agg.id_count = {} and agg.type = 'SPD' " "and agg.import_status = {} " "and lan.id_section = '{}'" "group by spd.low, spd.high " "order by spd.low;".format(count_id, status, section_id)) query.exec_(query_str) x = [] y = [] while query.next(): x.append("{}-{} km/h".format(str(query.value(1)), str(query.value(2)))) y.append(query.value(0)) return x, y
def delete_count_data(self, count_id, section_id, status): self.init_db_connection() query = QSqlQuery(self.db) query_strs = [] query_strs.append("delete from comptages.count_aggregate as cou " "using comptages.lane as lan " "where cou.id_lane = lan.id " "and cou.id_count = {} " "and cou.import_status = {} " "and lan.id_section = '{}' ".format( count_id, status, section_id)) query_strs.append("delete from comptages.count_detail as cou " "using comptages.lane as lan " "where cou.id_lane = lan.id " "and cou.id_count = {} " "and cou.import_status = {} " "and lan.id_section = '{}' ".format( count_id, status, section_id)) for _ in query_strs: query.exec_(_) push_info("Les données ont été supprimées")
def change_status_of_count_data(self, count_id, section_id, new_status): self.init_db_connection() query = QSqlQuery(self.db) query_strs = [] query_strs.append( "update comptages.count_aggregate as cou set import_status = {} " "from comptages.lane as lan " "where cou.id_lane = lan.id " "and id_count = {} " "and lan.id_section = '{}'".format(new_status, count_id, section_id)) query_strs.append( "update comptages.count_detail as cou set import_status = {} " "from comptages.lane as lan " "where cou.id_lane = lan.id " "and id_count = {} " "and lan.id_section = '{}'".format(new_status, count_id, section_id)) for _ in query_strs: query.exec_(_) push_info("Les données ont été importées")
def get_runs(self): q = QSqlQuery(db=self.db) q.exec_('select run from hsrr.run_info order by run') runs = [] while q.next(): runs.append(q.value(0)) return runs
def __init__(self): self.file = os.path.join(os.path.dirname('__file__'), 'settings.db') #self.file = 'C:/Users/da2/AppData/Roaming/QGIS/QGIS3/profiles/default/python/plugins/CatastroV3/settings.db' print(self.file) self.db = self.get_db() self.query = QSqlQuery(self.db) config = { 'host': 'localhost', 'dbname': '', 'port': 5432, 'user': '', 'pswd': '', 'schema': 'public', 'layers': [ "departamento", "circunscripcion", "seccion", "chacra", "quinta", "fraccion", "manzana", "parcela", "subparcela" ], 'partidos': self.get_partidos() } self.set_config(config) #print(config) config2 = self.get_config() print(config2)
def get_detail_time_chart_data_day_by_direction(self, count_id, day, status, direction, section_id): self.init_db_connection() query = QSqlQuery(self.db) query_str = ("select date_part('hour', timestamp), " "date_part('hour', timestamp) + 1, " "count(date_part('hour', timestamp)) " "from comptages.count_detail as cou " "join comptages.lane as lan on " "cou.id_lane = lan.id " "where id_count = {} and " "date_trunc('day', timestamp) = '{}' " "and import_status = {} " "and lan.direction = {} " "and lan.id_section = '{}' " "group by date_part('hour', timestamp);".format( count_id, day, status, direction, section_id)) query.exec_(query_str) x = [ "00h-01h", "01h-02h", "02h-03h", "03h-04h", "04h-05h", "05h-06h", "06h-07h", "07h-08h", "08h-09h", "09h-10h", "10h-11h", "11h-12h", "12h-13h", "13h-14h", "14h-15h", "15h-16h", "16h-17h", "17h-18h", "18h-19h", "19h-20h", "20h-21h", "21h-22h", "22h-23h", "23h-00h" ] y = [None] * 24 while query.next(): y[int(query.value(0))] = query.value(2) return x, y
def testCreateEmptyLayer(self): # cleanup (it seems overwrite option doesn't clean the sdo_geom_metadata table) self.execSQLCommand('DROP TABLE "QGIS"."EMPTY_LAYER"', ignore_errors=True) self.execSQLCommand("DELETE FROM user_sdo_geom_metadata where TABLE_NAME='EMPTY_LAYER'", ignore_errors=True) uri = self.dbconn + "srid=4326 type=POINT table=\"EMPTY_LAYER\" (GEOM)" exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=QgsFields(), geometryType=QgsWkbTypes.Point, crs=QgsCoordinateReferenceSystem(4326), overwrite=True) self.assertEqual(exporter.errorCount(), 0) self.assertEqual(exporter.errorCode(), 0) # check IF there is an empty table (will throw error if the EMPTY_LAYER table does not exist) self.execSQLCommand('SELECT count(*) FROM "QGIS"."EMPTY_LAYER"') # check that metadata table has been correctly populated query = QSqlQuery(self.conn) self.assertTrue(query.exec_("SELECT column_name, srid FROM user_sdo_geom_metadata WHERE table_name = 'EMPTY_LAYER'")) self.assertTrue(query.next()) self.assertEqual(query.value(0), "GEOM") self.assertEqual(query.value(1), 4326) query.finish() vl = QgsVectorLayer( self.dbconn + ' sslmode=disable table="QGIS"."EMPTY_LAYER" sql=', 'test', 'oracle') self.assertTrue(vl.isValid())
def get_bcgw_column_key(db_table,user_name,user_pass): # estimate a unique id column for an oracle table if OBJECTID does not exist owner,table = db_table.split('.') driver ="QOCISPATIAL" conn_name = "bcgw_conn" if not QSqlDatabase.contains(conn_name): db = QSqlDatabase.addDatabase(driver,conn_name) else: db = QSqlDatabase.database(conn_name) db.setDatabaseName('bcgw.bcgov' + "/" + 'idwprod1.bcgov') db.setUserName(user_name) db.setPassword(user_pass) db.open() if not db.open(): print ("Failed Connection from find_bcgw_the_geom") q = QSqlQuery(db) sql = f"SELECT cols.column_name \ FROM all_tab_cols cols where cols.table_name = '{table}' and cols.COLUMN_NAME like \'OBJECTID\'" q.exec(sql) if q.first(): key_c = q.value(0) else: sql = f"SELECT COLUMN_NAME FROM all_tab_cols where table_name = '{table}' \ order by COLUMN_ID FETCH FIRST 1 ROWS ONLY" q.exec(sql) if q.first(): key_c = q.value(0) db.close() return key_c
def getGeomColumnTupleList(self, showViews=False): """ list in the format [(table_schema, table_name, geometryColumn, geometryType, tableType)] centroids are hidden by default """ self.checkAndOpenDb() edgvVersion = self.getDatabaseVersion() sql = self.gen.getGeomColumnTupleList(edgvVersion) query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception( self.tr("Problem getting geom tuple list: ") + query.lastError().text()) geomList = [] while query.next(): if edgvVersion in ['2.1.3', 'FTer_2a_Ed']: geomList.append((query.value(0).split('_')[0], '_'.join(query.value(0).split('_')[1::]), query.value(1), query.value(2), 'BASE TABLE')) else: geomList.append( (query.value(0).split('_')[0], '_'.join(query.value(0).split('_')[1::]), query.value(1), self.getResolvedGeomType(int(query.value(2))), 'BASE TABLE')) return geomList
def setUp(self): self.db.open() query = QSqlQuery(self.db) query.exec_("DELETE FROM comptages.count;") query.exec_("DELETE FROM comptages.count_detail;") self.db.close()
def execSQLCommand(self, sql, ignore_errors=False): self.assertTrue(self.conn) query = QSqlQuery(self.conn) res = query.exec_(sql) if not ignore_errors: self.assertTrue(res, sql + ': ' + query.lastError().text()) query.finish()
def geocod(self, tipusVia, variant, codi, numIni, lletraIni='', numFi='', lletraFi=''): self.dbLogConnexio() if self.dbLog is None: return None, None if self.queryGeo is None: self.queryGeo = QSqlQuery(self.dbLog) try: self.queryGeo.prepare("CALL QV_GEOCOD(:TIPUSVIA, :VARIANTE, :CODIGO, " + ":NUMINI, :LETRAINI, :NUMFIN, :LETRAFIN, :X, :Y)") self.queryGeo.bindValue(':TIPUSVIA', tipusVia) self.queryGeo.bindValue(':VARIANTE', variant) self.queryGeo.bindValue(':CODIGO', codi) self.queryGeo.bindValue(':NUMINI', numIni) self.queryGeo.bindValue(':LETRAINI', lletraIni) self.queryGeo.bindValue(':NUMFIN', numFi) self.queryGeo.bindValue(':LETRAFIN', lletraFi) self.queryGeo.bindValue(':X', 0.0, QSql.Out) self.queryGeo.bindValue(':Y', 0.0, QSql.Out) ok = self.queryGeo.exec_() if ok: x = self.queryGeo.boundValue(':X') if not isinstance(x, float): x = None y = self.queryGeo.boundValue(':Y') if not isinstance(y, float): y = None return x, y except Exception: return None, None
def execute_query(self, sql): query = QSqlQuery(self.db) res = query.exec_(sql) if res is False: QApplication.restoreOverrideCursor() raise VerisoErrorWithBar(self.iface.messageBar(), "Error " + (query.lastError().text()))
def __init__(self, family='QVISTA', logname='DESKTOP', dbLog=_DB_QVISTA): if hasattr(self, 'db'): # Solo se inicializa una vez return self.family = family.upper() self.logname = logname.upper() self.usuari = getpass.getuser().upper() self.db = self.connexio(dbLog) self.sessio = str(uuid.uuid1()) self.query = QSqlQuery()
def checkAccessTableHasAdmin(self): sql = "SELECT lower(username) FROM postnas_search_access_control WHERE access = 0" self.__openDB() query = QSqlQuery(self.db) query.exec_(sql) if (query.size() > 0): return True else: return False
def _query(self, querystring, **mappings): querystring = querystring.replace(r"\r\n", " ") query = QSqlQuery(self.db) query.prepare(querystring) for key, value in mappings.items(): bindvalue = ":{}".format(key) if re.search(r"{}\b".format(bindvalue), querystring): query.bindValue(bindvalue, value) return query
def getAllEdgvDomainsFromTableName(self, schema, table): """ EDGV databases deployed by DSGTools have a set of domain tables. Gets the value map from such DB. It checks for all attributes found. :param table: (str) layer to be checked for its EDGV mapping. :return: (dict) value map for all attributes that have one. """ self.abstractDb.checkAndOpenDb() ret = defaultdict(dict) db = self.abstractDb.db edgv = self.abstractDb.getDatabaseVersion() domainMap = self.domainMapping(edgv) fullTablaName = schema + "_" + table sql = 'select code, code_name from dominios_{field} order by code' for fieldName in self.tableFields(fullTablaName): if fullTablaName in domainMap: domains = domainMap[fullTablaName] # if domain mapping is not yet available for current version if fieldName in domains: # replace this method over querying db for the table... domainTable = domains[fieldName][0] else: # non-mapped attribute continue query = QSqlQuery(sql.format(field=domainTable), db) elif fieldName in self.specialEdgvAttributes(): # EDGV "special" attributes that are have different domains depending on # which class it belongs to if edgv in ("2.1.3 Pro", "3.0 Pro"): # Pro versions now follow the logic "{attribute}_{CLASS_NAME}" cat = table.rsplit("_", 1)[0].split("_", 1)[-1] else: cat = table.split("_")[0] attrTable = "{attribute}_{cat}".format(attribute=fieldName, cat=cat) query = QSqlQuery(sql.format(field=attrTable), db) else: query = QSqlQuery(sql.format(field=fieldName), db) if not query.isActive(): continue while query.next(): code = str(query.value(0)) code_name = query.value(1) ret[fieldName][code_name] = code return ret
def __checkLoggingTableExists(self): sql = "SELECT table_name FROM information_schema.tables WHERE table_name = 'postnas_search_logging'"; self.__openDB() query = QSqlQuery(self.db) query.exec_(sql) if(query.size() > 0): return True else: return False