当输入为空时获取所有数据API

Question

我创建了第一个API，可以从Bigquery中的表中获取数据。我可以根据下面的两个输入获得所需的所有数据，但当输入为空时，我也试图获得整个表，但这是我做不到的。谢谢你的帮忙

@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"])

Answer 1

当输入为空时，您可以像这样进行remove the where clause：

@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"])