def insert_to_db(data):
for values in data.values():
print(values)
sql_values = [
values["timestamp"], values["occupancy"], CAM_LABEL,
values["label"], values["occupant_coordinates"]
]
execute_sql_for_dict(OCCUPANCY_SQL, sql_values)
execute_sql_for_dict(OCCUPANCY_CACHE_SQL, sql_values)
def get_pixel_analytics_for_patch(cam_label, lux_label, patch_label):
ret = {"lux": -1, "h_mean": -1}
from_db = execute_sql_for_dict(PIXEL_ANALYTICS_SQL,
[cam_label, lux_label, patch_label])
if len(from_db) == 1:
res = from_db[0]
ret["lux"] = res["lux"]
ret["h_mean"] = res["h_mean"]
return ret
def get_true_lux(label, pin, start_ts, end_ts):
ret = {}
from_db = execute_sql_for_dict(TRUE_LUX_QUERY,
[label, pin, start_ts, end_ts])
for row in from_db:
for key in row.keys():
if key not in ret.keys():
ret[key] = []
ret[key].append(row[key])
return ret
def get_above_threshold_patches_for_label(cam_label, lux_label):
ret = []
from_db = execute_sql_for_dict(
PATCH_SQL, [cam_label, lux_label, PEARSON_CORR_THRESHOLD])
for row in from_db:
ret.append({
"label": row["patch_label"],
"h_min": row["h_min"],
"h_max": row["h_max"]
})
return ret
def get_psudo_lux(comp_lux_label, cam_label, patch_label, start_ts, end_ts):
ret = {}
from_db = execute_sql_for_dict(
PSUDO_LUX_QUERY,
[comp_lux_label, cam_label, patch_label, start_ts, end_ts])
for row in from_db:
for key in row.keys():
if key not in ret.keys():
ret[key] = []
ret[key].append(row[key])
return ret
def get_dc(start_ts, end_ts):
ret = {}
from_db = execute_sql_for_dict(DC_QUERY, [start_ts, end_ts])
inter_ret = get_plot_ready_data(from_db)
for i in range(len(inter_ret['timestamp'])):
ts = inter_ret['timestamp'][i]
label = inter_ret['label'][i]
dc = inter_ret['dc'][i] / 10000
pin = inter_ret['pin'][i]
new_label = "{}_{}".format(label, pin)
if new_label not in ret.keys():
ret[new_label] = {'ts': [], 'dc': []}
ret[new_label]['ts'].append(ts)
ret[new_label]['dc'].append(dc)
return ret
def get_lux(start_ts, end_ts):
ret = {}
from_db = execute_sql_for_dict(TRUE_LUX_QUERY, [start_ts, end_ts])
inter_ret = get_plot_ready_data(from_db)
for i in range(len(inter_ret['timestamp'])):
ts = inter_ret['timestamp'][i]
label = inter_ret['label'][i]
lux = inter_ret['lux'][i]
pin = inter_ret['pin'][i]
new_label = "{}_{}".format(label, pin)
if new_label not in ret.keys():
ret[new_label] = {'ts': [], 'lux': []}
ret[new_label]['ts'].append(ts)
ret[new_label]['lux'].append(lux)
return ret
def get_latest_occupancy():
ret = {}
from_db = execute_sql_for_dict(SQL, [])
for row in from_db:
ts = row.get("timestamp")
occupancy = row.get("occupancy")
label = row.get("label")
occupant_coordinates = row.get("occupant_coordinates")
occupant_coordinates_json = None
if occupant_coordinates and len(occupant_coordinates) > 0:
# print(occupant_coordinates)
occupant_coordinates_json = json.loads(occupant_coordinates)
ret[label] = {
"timestamp": ts,
"occupancy": occupancy,
"occupants": occupant_coordinates_json
}
return ret
def get_lux_from_db():
ret = {}
lux = execute_sql_for_dict(LUX_QUERY, [])
for row in lux:
label = row['label']
pin = row['pin']
lux = row['lux']
composite_label = "{}_{}".format(label, pin)
if composite_label == 'a_tsl_0':
ret['a'] = lux
if composite_label == 'b_tsl_2':
ret['b'] = lux
if composite_label == 'c_tsl_0':
ret['c'] = lux
if composite_label == 'd_tsl_0':
ret['d'] = lux
if composite_label == 'e_tsl_0':
ret['e'] = lux
if composite_label == 'f_tsl_0':
ret['f'] = lux
return ret
def get_highest_correlation(lux_label):
from_db = execute_sql_for_dict(FP_QUERY, [
lux_label,
])
return from_db[0]