def get_temp_and_humidity_history_from_BQ(device_uuid):
humidity_array = []
temp_array = []
result_json = {'RH': humidity_array, 'temp': temp_array}
if device_uuid is None or device_uuid is 'None':
return result_json
job_config = bigquery.QueryJobConfig()
job_config.use_legacy_sql = False
query_str = queries.formatQuery(queries.fetch_temp_results_history,
device_uuid)
query_job = bigquery_client.query(query_str, job_config=job_config)
query_result = query_job.result()
for row in list(query_result):
rvalues = row[2] # can't use row.values
values_json = (ast.literal_eval(rvalues))
if 'air_temperature_celcius' == row.var and 'values' in values_json:
values = values_json["values"]
result_json["temp"].append({
'value': values[0]['value'],
'time': row.eastern_time
})
if 'air_humidity_percent' == row.var and 'values' in values_json:
values = values_json["values"]
result_json["RH"].append({
'value': values[0]['value'],
'time': row.eastern_time
})
return result_json
def get_current_float_value_from_BQ(query, device_uuid):
if device_uuid is None or device_uuid is 'None':
return None
# Use a NEW QueryJobConfig for each query, or you will get this error:
# google.api_core.exceptions.BadRequest:
# 400 Cannot explicitly modify anonymous table
job_config = bigquery.QueryJobConfig()
job_config.use_legacy_sql = False
query_str = queries.formatQuery(query, device_uuid)
query_job = bigquery_client.query(query_str, job_config=job_config)
query_result = query_job.result()
for row in list(query_result):
values_json = (ast.literal_eval(row[1]))
if "values" in values_json:
values = values_json["values"]
return "{0:.2f}".format(float(values[0]['value']))
return None # no data
def get_led_panel_history_from_BQ(device_uuid):
if device_uuid is None or device_uuid is 'None':
return []
job_config = bigquery.QueryJobConfig()
job_config.use_legacy_sql = False
query_str = queries.formatQuery(queries.fetch_led_panel_history,
device_uuid)
query_job = bigquery_client.query(query_str, job_config=job_config)
query_result = query_job.result()
result_json = []
for row in list(query_result):
values_json = ast.literal_eval(row[1])
if "values" in values_json:
values = values_json["values"]
if len(values) > 0:
led_json = values[0]['value']
result_json.append(led_json)
return result_json
def get_co2_history_from_BQ(device_uuid):
if device_uuid is None or device_uuid is 'None':
return []
job_config = bigquery.QueryJobConfig()
job_config.use_legacy_sql = False
query_str = queries.formatQuery(queries.fetch_co2_results_history,
device_uuid)
query_job = bigquery_client.query(query_str, job_config=job_config)
query_result = query_job.result()
results = []
for row in list(query_result):
values_json = (ast.literal_eval(row[1]))
if "values" in values_json:
values = values_json["values"]
results.append({
'value': values[0]['value'],
'time': row.eastern_time
})
return results