def get_dataframe_query(query, user, interval, start_date, end_date, switch_id,
country_id_list, second_index):
"""
build sql query return the dataframe
"""
upd_query = sqlquery
upd_query = upd_query.replace("#SECOND_INDEX#", second_index)
upd_query = upd_query.replace("#USER_CONDITION#", condition_user(user))
upd_query = upd_query.replace("#DATEDAY_FORMAT#", "dateday AS dateday")
upd_query = upd_query.replace("#SWITCH_CONDITION#",
condition_switch_id(switch_id))
upd_query = upd_query.replace("#INTERVAL#", interval)
if country_id_list and len(country_id_list) > 0:
select_country = ", ".join(str(int(l)) for l in country_id_list)
upd_query = upd_query.replace(
"#COUNTRY_CONDITION#",
"AND country_id IN (" + select_country + ")")
else:
upd_query = upd_query.replace("#COUNTRY_CONDITION#", "")
params = {
'start_date': start_date,
'end_date': end_date,
}
df = sql.read_sql_query(upd_query, connection, params=params)
return df
def get_dataframe_query_cmp_day(query, user, interval, start_date, end_date, switch_id):
"""
build sql query return the dataframe
"""
upd_query = sqlquery
upd_query = upd_query.replace("#SECOND_INDEX#", "switch_id")
upd_query = upd_query.replace("#USER_CONDITION#", condition_user(user))
upd_query = upd_query.replace("#DATEDAY_FORMAT#", "extract(hour from dateday) as dateday")
upd_query = upd_query.replace("#SWITCH_CONDITION#", condition_switch_id(switch_id))
upd_query = upd_query.replace("#INTERVAL#", interval)
upd_query = upd_query.replace("#COUNTRY_CONDITION#", "")
params = {
'start_date': start_date,
'end_date': end_date,
}
# df = sql.read_sql_query(upd_query, connection, params=params, index_col=["dateday", "switch_id"])
df = sql.read_sql_query(upd_query, connection, params=params)
return df
def get_dataframe_query_cmp_day(query, user, interval, start_date, end_date,
switch_id):
"""
build sql query return the dataframe
"""
upd_query = sqlquery
upd_query = upd_query.replace("#SECOND_INDEX#", "switch_id")
upd_query = upd_query.replace("#USER_CONDITION#", condition_user(user))
upd_query = upd_query.replace("#DATEDAY_FORMAT#",
"extract(hour from dateday) as dateday")
upd_query = upd_query.replace("#SWITCH_CONDITION#",
condition_switch_id(switch_id))
upd_query = upd_query.replace("#INTERVAL#", interval)
upd_query = upd_query.replace("#COUNTRY_CONDITION#", "")
params = {
'start_date': start_date,
'end_date': end_date,
}
# df = sql.read_sql_query(upd_query, connection, params=params, index_col=["dateday", "switch_id"])
df = sql.read_sql_query(upd_query, connection, params=params)
return df
def get_dataframe_query(query, user, interval, start_date, end_date, switch_id, country_id_list, second_index):
"""
build sql query return the dataframe
"""
upd_query = sqlquery
upd_query = upd_query.replace("#SECOND_INDEX#", second_index)
upd_query = upd_query.replace("#USER_CONDITION#", condition_user(user))
upd_query = upd_query.replace("#DATEDAY_FORMAT#", "dateday AS dateday")
upd_query = upd_query.replace("#SWITCH_CONDITION#", condition_switch_id(switch_id))
upd_query = upd_query.replace("#INTERVAL#", interval)
if country_id_list and len(country_id_list) > 0:
select_country = ", ".join(str(int(l)) for l in country_id_list)
upd_query = upd_query.replace("#COUNTRY_CONDITION#", "AND country_id IN (" + select_country + ")")
else:
upd_query = upd_query.replace("#COUNTRY_CONDITION#", "")
params = {
'start_date': start_date,
'end_date': end_date,
}
df = sql.read_sql_query(upd_query, connection, params=params)
return df