Get all data API when inputs are empty-CodePudding

I created my first API where I can get data from my tables in Bigquery. I can get all the data I need based on the 2 inputs below, but I am also trying to get the whole table when the inputs are empty, which I cannot do. Thanks for your help

@app.route("/tracking", methods=['GET'])
def tracking_data():

    haulier_id_tracking = request.args.get('haulier_id_tracking')
    month_tracking = request.args.get('month_tracking')



    query_job = bq_client.query("""
            WITH t AS (
                SELECT *
                FROM mart.monthly_vehicle_stats
                WHERE dt_fr_month = '{month_tracking}-01' AND (haulier_id_tracking = '{haulier_id_tracking}')
            
            
            SELECT TO_JSON_STRING(STRUCT(ARRAY_AGG(STRUCT(dt_fr_month, haulier_id_tracking, vehicle_id , nb_days_tracked, 
            data_access, date_first_camp, invoiced)) AS data)) json
            FROM t
        """.format(month_tracking = month_tracking, haulier_id_tracking = haulier_id_tracking))

    for row in query_job:

        return json.loads(row["json"])

CodePudding user response：

You can remove the where clause when the input is empty like this:

@app.route("/tracking", methods=['GET'])
def tracking_data():

    haulier_id_tracking = request.args.get('haulier_id_tracking')
    month_tracking = request.args.get('month_tracking')

    where_clause = ''
    if haulier_id_tracking != '' and month_tracking != '':
        where_clause = f"WHERE dt_fr_month = '{month_tracking}-01' AND (haulier_id_tracking = '{haulier_id_tracking}'"



    query_job = bq_client.query(f"""
            WITH t AS (
                SELECT * FROM mart.monthly_vehicle_stats {where_clause})


            SELECT TO_JSON_STRING(STRUCT(ARRAY_AGG(STRUCT(dt_fr_month, haulier_id_tracking, vehicle_id , nb_days_tracked,
            data_access, date_first_camp, invoiced)) AS data)) json
            FROM t
        """)

    for row in query_job:
        return json.loads(row["json"])