How to Return Google BigQuery Results in Multiple Json Format in Python

Asked 2 years ago, Updated 2 years ago, 134 views

We have created an API that allows us to do the following in the Python framework, Flask.
I use Google BigQuery as the destination DB for SQL.

複数 Obtain multiple request parameters (luid) in a list and store each in a variable by loop processing
②Execute SQL using the variables in の as the conditions in Where section
* SQL content returns True if the requested luid cv_date column has a value.
③Parameter value: Returns the result to the client in the Json format of the true or false value.

Currently, the above functionality can be implemented to return the authenticity value.
However, if you look at the return value, only the result of the first luid in the list of request_luids is displayed.
The original requirement is to return the results for all the luids obtained as follows.
for row in query_res:return jsonify({request_luid:str(row[0])})
printing the last result I think the current way of describing in the external loop can only handle the first parameter, but I can't think of how to change the logic and code.
I would appreciate it if you could give me some advice on the direction.

In the first place, I thought about whether I need to change the entire code or create and utilize a function that makes the results and parameters into a dictionary in the form of key:value, but it is not organized.

Current State Output

{
    "XXXXXXX5e30ab17f6b536879d25555": "True" ⬅ 結果 Results show that true/false values are currently functioning without any problems
}

ideal output

{
    "XXXXXX5e30ab17f6b536879d25555": "True",
    "XXXXXX8r30ab17f6b536879d25555": "False",
    "XXXXXXX9t30ab17f6b536879d25555": "True",
}

Endpoints

https://test-project-galvanic-ripsaw-281806.df.r.appspot.com?luid=XXXXXXX5e30ab17f6b536879d25555&luid=XXXXXXX8r30ab17f6b536879d25555
&luid=XXXXXX9t30ab17f6b536879d25555

main.py

@app.route('/')
default_request():
    request_luids = request.args.getlist('luid') or '''
    for i in range (len(request_luids)) :
        request_luid=request_luids[i]
        client=bigquery.Client()
        query=""SELECT EXISTS(
                     SELECT1
                     FROM`test-project-281806.hitobito_test.test3`asp
                     WHERE p.luid='{}'
                     AND p.cv_date IS NOT NULL limit 1000)" ".format(request_luid)


        job_config = bigquery.QueryJobConfig(
            query_parameters = [
                bigquery.ScalarQueryParameter("request_luid", "STRING", request_luid)
            ]
        )

        query_job=client.query(query)
        query_res=query_job.result()


        for row in query_res:
            return jsonify({request_luid:str(row[0])})


if__name__=="__main__":
    app.run()

python python3 flask google-bigquery

2022-09-30 17:40

1 Answers

The following code will solve the problem.

def get_request():
    request_luids = request.args.getlist('luid') or '''
    result={} 
    for i in range (len(request_luids)) :
        request_luid=str(request_luids[i])
        client=bigquery.Client()
        query=""SELECT EXISTS(
                     SELECT1
                     FROM`test-project-281806.hitobito_test.test3`asp
                     WHERE p.luid='{}'
                     AND p.cv_date IS NOT NULL limit 1000)" ".format(request_luid)


        job_config = bigquery.QueryJobConfig(
            query_parameters = [
                bigquery.ScalarQueryParameter("request_luid", "STRING", request_luid)
            ]
        )

        query_job=client.query(query)
        query_res=query_job.result()
        
        for row in query_res:
            temp_result=jsonify({request_luid:str(row[0])})
            break

        result.update(temp_result)  

    
    return result

if__name__=="__main__":
    app.run()


2022-09-30 17:40

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.