Beispiel #1
0
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
Beispiel #2
0
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
Beispiel #3
0
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
Beispiel #4
0
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