def test_tablenameschema_exists(self): """ This test controls if the table exist in the specified schema. The query returns True or False. True=Tablename exists in the schema False=Tablename does'nt exist in the schema """ for layer in layersData: """ Subtest only available on python 3.4 """ with self.subTest(tablename=layersData[layer]['tablename'],schema_hectare=layersData[layer]['schema_hectare']): sql_query = get_exists_table_query(tbname=layersData[layer]['tablename'], schema=layersData[layer]['schema_hectare']) query = query_geographic_database(sql_query).fetchone() #print("schema_hectare:"+layersData[layer]['schema_hectare'] + ", tablename:" + layersData[layer]['tablename'] + " => result:" + str(query[0])) self.assertTrue(bool(query[0]) == True) if layersData[layer]['data_aggregated'] == True: with self.subTest(tablename=layersData[layer]['tablename']+"_nuts",schema_scalelvl=layersData[layer]['schema_scalelvl']): sql_query = get_exists_table_query(tbname=layersData[layer]['tablename']+"_nuts", schema=layersData[layer]['schema_scalelvl']) query = query_geographic_database(sql_query).fetchone() #print("schema_scalelvl:"+layersData[layer]['schema_scalelvl'] + ", tablename:" + layersData[layer]['tablename']+"_nuts => result:" + str(query[0])) self.assertTrue(bool(query[0]) == True) with self.subTest(tablename=layersData[layer]['tablename']+"_lau",schema_scalelvl=layersData[layer]['schema_scalelvl']): sql_query = get_exists_table_query(tbname=layersData[layer]['tablename']+"_lau", schema=layersData[layer]['schema_scalelvl']) query = query_geographic_database(sql_query).fetchone() #print("schema_scalelvl:"+layersData[layer]['schema_scalelvl'] + ", tablename:" + layersData[layer]['tablename']+"_lau => result:" + str(query[0])) self.assertTrue(bool(query[0]) == True)
def getEnergyMixNutsLau(nuts): sql_query = "WITH energy_total as (SELECT sum(electricity_generation) as value FROM " + ELECRICITY_MIX + " WHERE nuts0_code IN ("+nuts+") )" + \ "SELECT DISTINCT energy_carrier, SUM(electricity_generation * 100 /energy_total.value) FROM " + ELECRICITY_MIX + " ,energy_total WHERE nuts0_code IN ("+nuts+") GROUP BY energy_carrier ORDER BY energy_carrier ASC" ; query = model.query_geographic_database(sql_query) labels = [] data = [] backgroundColor = [] for c, l in enumerate(query): labels.append(l[0]) data.append(helper.roundValue(l[1])) backgroundColor.append(helper.getGenerationMixColor(l[0])) datasets = { 'data' : data, 'label': '%', 'backgroundColor': backgroundColor } result = { 'labels':labels, 'datasets':datasets } return result
def test_aggregateddata_statlvl_nutslau(self): """ This function test if aggregated data are in the database. The test runs over all indicators and check data in with schema_scalelvl (the schema) and the tablename (tablename of the dataset). For the tablename, "_lau" and "_nuts" are added are added at the end of the tablename. At nuts level, every stat level are tested (column name: stat_levl_), from 0 to 3. 0=NUTS 0 1=NUTS 1 2=NUTS 2 3=NUTS 3 The query return the count of rows available in the table. Result: Query returns 0 => No data avalaible for the specific scale Query return >= 1 => Data are available in the specific scale """ for layer in layersData: if layersData[layer]['data_aggregated'] == True: sql_querie = "select count(*) from " + layersData[layer][ 'schema_scalelvl'] + "." + layersData[layer]['tablename'] sql_querie_lau = sql_querie + "_lau limit 100" with self.subTest( stat_levl_='lau', tablename=layersData[layer]['schema_scalelvl'] + "." + layersData[layer]['tablename'] + "_lau"): try: query = query_geographic_database( sql_querie_lau).fetchone() self.assertTrue(int(query[0]) >= 1) except Exception as e: self.assertTrue(False) for i in range(0, 4): sql_querie_nuts = sql_querie + "_nuts where stat_levl_ = " + str( i) with self.subTest( stat_levl_=i, tablename=layersData[layer]['schema_scalelvl'] + "." + layersData[layer]['tablename'] + "_nuts"): try: query = query_geographic_database( sql_querie_nuts).fetchone() self.assertTrue(int(query[0]) >= 1) except Exception as e: self.assertTrue(False)
def test_aggregateddata_statlvl_nutslau(self): """ This function test if aggregated data are in the database. The test runs over all indicators and check data in with schema_scalelvl (the schema) and the tablename (tablename of the dataset). For the tablename, "_lau" and "_nuts" are added are added at the end of the tablename. At nuts level, every stat level are tested (column name: stat_levl_), from 0 to 3. 0=NUTS 0 1=NUTS 1 2=NUTS 2 3=NUTS 3 The query return the count of rows available in the table. Result: Query returns 0 => No data avalaible for the specific scale Query return >= 1 => Data are available in the specific scale """ for layer in layersData: if layersData[layer]['data_aggregated'] == True: sql_querie = "select count(*) from "+layersData[layer]['schema_scalelvl']+"."+layersData[layer]['tablename'] sql_querie_lau = sql_querie + "_lau limit 100" #print(sql_querie_lau) with self.subTest(stat_levl_='lau',tablename=layersData[layer]['schema_scalelvl']+"."+layersData[layer]['tablename']+"_lau"): try: query = query_geographic_database(sql_querie_lau).fetchone() self.assertTrue(int(query[0]) >= 1) except Exception as e: #print(e) self.assertTrue(False) for i in range(0,4): sql_querie_nuts = sql_querie + "_nuts where stat_levl_ = "+str(i) #print(sql_querie_nuts) with self.subTest(stat_levl_=i,tablename=layersData[layer]['schema_scalelvl']+"."+layersData[layer]['tablename']+"_nuts"): try: query = query_geographic_database(sql_querie_nuts).fetchone() self.assertTrue(int(query[0]) >= 1) except Exception as e: #print(e) self.assertTrue(False)
def test_tablenameschema_exists(self): """ This test controls if the table exist in the specified schema. The query returns True or False. True=Tablename exists in the schema False=Tablename does'nt exist in the schema """ for layer in layersData: """ Subtest only available on python 3.4 """ with self.subTest( tablename=layersData[layer]['tablename'], schema_hectare=layersData[layer]['schema_hectare']): sql_query = get_exists_table_query( tbname=layersData[layer]['tablename'], schema=layersData[layer]['schema_hectare']) query = query_geographic_database(sql_query).fetchone() self.assertTrue(bool(query[0]) == True) if layersData[layer]['data_aggregated'] == True: with self.subTest( tablename=layersData[layer]['tablename'] + "_nuts", schema_scalelvl=layersData[layer]['schema_scalelvl']): sql_query = get_exists_table_query( tbname=layersData[layer]['tablename'] + "_nuts", schema=layersData[layer]['schema_scalelvl']) query = query_geographic_database(sql_query).fetchone() self.assertTrue(bool(query[0]) == True) with self.subTest( tablename=layersData[layer]['tablename'] + "_lau", schema_scalelvl=layersData[layer]['schema_scalelvl']): sql_query = get_exists_table_query( tbname=layersData[layer]['tablename'] + "_lau", schema=layersData[layer]['schema_scalelvl']) query = query_geographic_database(sql_query).fetchone() self.assertTrue(bool(query[0]) == True)
def duration_curve_hectares( year, geometry): #/heat-load-profile/duration-curve/hectares # Get the query sql_query = createQueryDataDCHectares(year=year, geometry=geometry) query = model.query_geographic_database(sql_query) listAllValues = [] for n, q in enumerate(query): listAllValues.append({'X': n + 1, 'Y': q[0]}) listAllValues = listAllValues[0:len(listAllValues) - 1:10] return listAllValues
def heatloadprofile_nuts_lau(year, month, day, nuts, nuts_level): #/heat-load-profile/nuts-lau request_type='' # Check the type of the query if month != 0 and day != 0: request_type = 'day' elif month != 0: request_type = 'month' else: request_type = 'year' # Get the data query = createQueryDataLPNutsLau(year=year, month=month, day=day, nuts=nuts,request_type=request_type, nuts_level=nuts_level) # Construction of the query # Execution of the query #query = db.session.execute(sql_query) res = model.query_geographic_database(query) # Storing the results only if there is data output = [] for c, q in enumerate(res): if q[0]: data={} if request_type == 'year': data = { 'year': year,'month': q[4],'granularity': 'month','unit': 'kW','min': round(q[0], constants.NUMBER_DECIMAL_DATA), 'max': round(q[1], constants.NUMBER_DECIMAL_DATA),'average': round(q[2], constants.NUMBER_DECIMAL_DATA) } elif request_type == 'month': data = { 'year': year,'month': month,'day': q[4],'granularity': 'day','unit': 'kW','min': round(q[0], constants.NUMBER_DECIMAL_DATA), 'max': round(q[1], constants.NUMBER_DECIMAL_DATA), 'average': round(q[2], constants.NUMBER_DECIMAL_DATA) } elif request_type == 'day': data = { 'year': year,'month': month,'day': day,'hour_of_day': q[4],'granularity': 'hour', 'unit': 'kW','value': round(q[3], constants.NUMBER_DECIMAL_DATA) } output.append(data) else: output = [] return { "values": output }
def duration_curve_hectares(year, geometry): #/heat-load-profile/duration-curve/hectares # Get the query sql_query = createQueryDataDCHectares(year=year, geometry=geometry) query = model.query_geographic_database(sql_query) listAllValues = [] for n, q in enumerate(query): listAllValues.append({ 'X':n+1, 'Y':q[0] }) listAllValues = listAllValues[0:len(listAllValues)-1:10] return listAllValues
def duration_curve_nuts_lau( year, nuts, nuts_level): #/heat-load-profile/duration-curve/nuts-lau # Get the query sql_query = createQueryDataDCNutsLau(year=year, nuts=nuts, nuts_level=nuts_level) # Execution of the query #query = db.session.execute(sql_query) query = model.query_geographic_database(sql_query) # Store query results in a list listAllValues = [] points = [] for n, q in enumerate(query): #listAllValues.append(q[0]) points.append({'X': n + 1, 'Y': float(q[0])}) newList = points[0:len(points) - 1:10] return newList
def duration_curve_nuts_lau(year, nuts, nuts_level): #/heat-load-profile/duration-curve/nuts-lau # Get the query sql_query = createQueryDataDCNutsLau(year=year, nuts=nuts, nuts_level=nuts_level) # Execution of the query #query = db.session.execute(sql_query) query = model.query_geographic_database(sql_query) # Store query results in a list listAllValues = [] points = [] for n, q in enumerate(query): #listAllValues.append(q[0]) points.append({ 'X':n+1, 'Y':float(q[0]) }) newList = points[0:len(points)-1:10] return newList
def getEnergyMixNutsLau(nuts): sql_query = "WITH energy_total as (SELECT sum(electricity_generation) as value FROM " + ELECRICITY_MIX + " WHERE nuts0_code IN ("+nuts+") )" + \ "SELECT DISTINCT energy_carrier, SUM(electricity_generation * 100 /energy_total.value) FROM " + ELECRICITY_MIX + " ,energy_total WHERE nuts0_code IN ("+nuts+") GROUP BY energy_carrier ORDER BY energy_carrier ASC" query = model.query_geographic_database(sql_query) labels = [] data = [] backgroundColor = [] for c, l in enumerate(query): labels.append(l[0]) data.append(helper.roundValue(l[1])) backgroundColor.append(helper.getGenerationMixColor(l[0])) datasets = { 'data': data, 'label': '%', 'backgroundColor': backgroundColor } result = {'labels': labels, 'datasets': datasets} return result
def heatloadprofile_hectares(year, month, day, geometry): #/heat-load-profile/hectares # Check the type of the query if month != 0 and day != 0: by = 'byDay' elif month != 0: by = 'byMonth' else: by = 'byYear' # Get the data queryData = createQueryDataLPHectares(year=year, month=month, day=day, geometry=geometry) # Construction of the query sql_query = queryData[by]['with'] + queryData[by]['select'] # Execution of the query #query = db.session.execute(sql_query) query = model.query_geographic_database(sql_query) # Storing the results only if there is data output = [] if by == 'byYear': for c, q in enumerate(query): if q[0]: output.append({ 'year': year, 'month': q[3], 'granularity': 'month', 'unit': 'kW', 'min': round(q[0], constants.NUMBER_DECIMAL_DATA), 'max': round(q[1], constants.NUMBER_DECIMAL_DATA), 'average': round(q[2], constants.NUMBER_DECIMAL_DATA) }) else: output = [] elif by == 'byMonth': for c, q in enumerate(query): if q[0]: output.append({ 'year': year, 'month': month, 'day': q[3], 'granularity': 'day', 'unit': 'kW', 'min': round(q[0], constants.NUMBER_DECIMAL_DATA), 'max': round(q[1], constants.NUMBER_DECIMAL_DATA), 'average': round(q[2], constants.NUMBER_DECIMAL_DATA) }) else: output = [] else: for c, q in enumerate(query): if q[0]: output.append({ 'year': year, 'month': month, 'day': day, 'hour_of_day': q[1], 'granularity': 'hour', 'unit': 'kW', 'value': round(q[0], constants.NUMBER_DECIMAL_DATA) }) else: output = [] return {"values": output}
def heatloadprofile_nuts_lau(year, month, day, nuts, nuts_level): #/heat-load-profile/nuts-lau request_type = '' # Check the type of the query if month != 0 and day != 0: request_type = 'day' elif month != 0: request_type = 'month' else: request_type = 'year' # Get the data query = createQueryDataLPNutsLau(year=year, month=month, day=day, nuts=nuts, request_type=request_type, nuts_level=nuts_level) # Construction of the query # Execution of the query #query = db.session.execute(sql_query) res = model.query_geographic_database(query) # Storing the results only if there is data output = [] for c, q in enumerate(res): if q[0]: data = {} if request_type == 'year': data = { 'year': year, 'month': q[4], 'granularity': 'month', 'unit': 'kW', 'min': str(round(q[0], constants.NUMBER_DECIMAL_DATA)), 'max': str(round(q[1], constants.NUMBER_DECIMAL_DATA)), 'average': str(round(q[2], constants.NUMBER_DECIMAL_DATA)) } elif request_type == 'month': data = { 'year': year, 'month': month, 'day': q[4], 'granularity': 'day', 'unit': 'kW', 'min': str(round(q[0], constants.NUMBER_DECIMAL_DATA)), 'max': str(round(q[1], constants.NUMBER_DECIMAL_DATA)), 'average': str(round(q[2], constants.NUMBER_DECIMAL_DATA)) } elif request_type == 'day': data = { 'year': year, 'month': month, 'day': day, 'hour_of_day': q[4], 'granularity': 'hour', 'unit': 'kW', 'value': str(round(q[3], constants.NUMBER_DECIMAL_DATA)) } output.append(data) else: output = [] return {"values": output}
def heatloadprofile_hectares(year, month, day, geometry): #/heat-load-profile/hectares # Check the type of the query if month != 0 and day != 0: by = 'byDay' elif month != 0: by = 'byMonth' else: by = 'byYear' # Get the data queryData = createQueryDataLPHectares(year=year, month=month, day=day, geometry=geometry) # Construction of the query sql_query = queryData[by]['with'] + queryData[by]['select'] # Execution of the query #query = db.session.execute(sql_query) query = model.query_geographic_database(sql_query) # Storing the results only if there is data output = [] if by == 'byYear': for c, q in enumerate(query): if q[0]: output.append({ 'year': year, 'month': q[3], 'granularity': 'month', 'unit': 'kW', 'min': round(q[0], constants.NUMBER_DECIMAL_DATA), 'max': round(q[1], constants.NUMBER_DECIMAL_DATA), 'average': round(q[2], constants.NUMBER_DECIMAL_DATA) }) else: output = [] elif by == 'byMonth': for c, q in enumerate(query): if q[0]: output.append({ 'year': year, 'month': month, 'day': q[3], 'granularity': 'day', 'unit': 'kW', 'min': round(q[0], constants.NUMBER_DECIMAL_DATA), 'max': round(q[1], constants.NUMBER_DECIMAL_DATA), 'average': round(q[2], constants.NUMBER_DECIMAL_DATA) }) else: output = [] else: for c, q in enumerate(query): if q[0]: output.append({ 'year': year, 'month': month, 'day': day, 'hour_of_day': q[1], 'granularity': 'hour', 'unit': 'kW', 'value': round(q[0], constants.NUMBER_DECIMAL_DATA) }) else: output = [] return { "values": output }