def insert_random_data_in_BI_COMPTAGE_DONNEE_table():
insert_base = insert_query_construction(compo=COMPTAGE_COMPO,
name=HandleDataFromDB.table_name)
def get_comptage(comptage):
if comptage > 100:
return comptage - comptage / 10
elif comptage > 20:
return comptage + comptage - comptage / 2
else:
return 20 + comptage % 3
def __generate_values():
start_day = datetime.today() - timedelta(days=10)
meters_id = [1, 2, 3]
values = list()
comptage = 20
while len(values) < 10:
comptage = get_comptage(comptage)
start_day += timedelta(days=1)
values.append((comptage, random.choice(meters_id), start_day))
return values
values = __generate_values()
for value in values:
my_sql.execute_and_close(query=insert_base, params=value)
def __drop_all_tables():
# NOT BIND
query = __get_query()
if not query:
return
try:
my_sql.execute_and_close(query=query)
except:
print("exception found")
query = __get_query()
my_sql.execute_and_close(query=query)
def insert_data_to_test_in_BI_COMPTAGE_DONNEE_table():
insert_base = insert_query_construction(compo=COMPTAGE_COMPO,
name=HandleDataFromDB.table_name)
meter_id = 4
values = list()
comptage_list = [3, 2, 5, 1, 4]
start_day = datetime.today() - timedelta(days=1)
for comtage in comptage_list:
start_day += timedelta(hours=1)
values.append((comtage, meter_id, start_day))
for value in values:
my_sql.execute_and_close(query=insert_base, params=value)
def insert_in_alert_definition_notification_time(
notification_id, alert_definition_id, notification_datetime: datetime):
query = insert_query_construction(
compo=ALERT_DEFINITION_NOTIFICATION_TIME_COMPO,
name=ALERT_DEFINITION_NOTIFICATION_TIME)
params = [notification_id, alert_definition_id, notification_datetime]
return my_sql.execute_and_close(query=query, params=params, return_id=True)
def insert_in_alert_definition(name, category, description, level, status,
notification_id, calculator_id):
query = insert_query_construction(compo=DEFINITION_COMPO,
name=DEFINITION_TABLE_NAME)
params = [
name, category, description, level, status, notification_id,
calculator_id
]
return my_sql.execute_and_close(query=query, params=params, return_id=True)
def insert_in_calculator(operator, comparator, data_period_type,
data_period_quantity, data_period_unit, value_type,
value_number, value_period_type, hour_start, hour_end,
acceptable_diff):
query = insert_query_construction(compo=CALCULATOR_COMPO,
name=CALCULATOR_NAME)
params = [
operator, comparator, data_period_type, data_period_quantity,
data_period_unit, value_type, value_number, value_period_type,
hour_start, hour_end, acceptable_diff
]
return my_sql.execute_and_close(query=query, params=params, return_id=True)
def insert_in_notification(period_unit: str, period_quantity: int, email: str,
days_flags: int, hours_flags: int):
query = insert_query_construction(compo=NOTIFICATION_COMPO,
name=NOTIFICATION_NAME)
params = [
period_unit,
period_quantity,
email,
days_flags,
hours_flags,
]
return my_sql.execute_and_close(query=query, params=params, return_id=True)
def insert_in_bi_comptage_donnees(donnee_comptage_value, meter_id, time):
query = insert_query_construction(compo=COMPTAGE_COMPO,
name=HandleDataFromDB.table_name)
params = [donnee_comptage_value, meter_id, time]
my_sql.execute_and_close(query=query, params=params)
def insert_in_alert_manager(launch_datetime: datetime):
query = insert_query_construction(compo=ALERT_MANAGER_TABLE_COMPO,
name=ALERT_MANAGER_TABLE_NAME)
params = [launch_datetime]
return my_sql.execute_and_close(query=query, params=params, return_id=True)
def insert_in_alert_definition_meter(meter_id: int, alert_definition_id: int):
query = insert_query_construction_without_id(
compo=METER_DEFINITION_COMPO, name=METER_DEFINITIONS_ALERT_TABLE_NAME)
params = [meter_id, alert_definition_id]
return my_sql.execute_and_close(query=query, params=params, return_id=True)
def insert_in_bi_objectifs(objectif_value: float, time_unit: str, meter_id):
query = insert_query_construction(compo=OBJECTIF_TABLE_COMPO,
name=OBJECTIF_TABLE_NAME)
params = [objectif_value, time_unit, meter_id]
return my_sql.execute_and_close(query=query, params=params, return_id=True)
def insert_in_bi_compteurs(name, is_index: bool):
query = insert_query_construction(compo=METER_TABLE_COMPO,
name=METER_TABLE_NAME)
params = [name, is_index]
print("params", params)
return my_sql.execute_and_close(query=query, params=params, return_id=True)
def erase_data_in_alert_manager_table():
query = "DELETE FROM {}".format(ALERT_MANAGER_TABLE_NAME)
print(query)
my_sql.execute_and_close(query=query)
def erase_meter_id_data(table_name:str, meter_id:int):
query = "DELETE FROM {} WHERE r_compteur=%s".format(table_name)
print(query)
params = [meter_id]
my_sql.execute_and_close(query=query, params=params)
def update_meter_is_index(meter_id, is_index):
query = "UPDATE {} SET is_index={} WHERE id=%s".format(METER_TABLE_NAME, is_index)
params = [meter_id]
print(query)
print(params)
my_sql.execute_and_close(query=query, params=params)
def erase_data_in_table(table_name:str):
query = "DELETE FROM {}".format(table_name)
print(query)
my_sql.execute_and_close(query=query)