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]