Python & Bigquery: Using for loop to query and insert data row by row in a table

Question

I am trying fire a query (Google BigQuery) in a for loop and in each loop inserting data into a table. However, I only see the final row in the table. So I'm assuming it is overwriting the values.

Here is the code:

for x in proc_arr:
    query = """

    BEGIN

     <QUERY>

    SELECT * FROM <table1> WHERE  procedureid = {}
 
    INSERT INTO <table> 

    SELECT procedureid FROM <tmp_proc1>
    UNION ALL
    SELECT procedureid FROM <tmp_proc2>
    ;

    END;

    """.format(x)

I have not written the actual query here as that is not required. proc_arr has 80 ids and it is only inserting data for the 80th id. Any help is appreciated. Thanks!

Seems fine to me. The issue might lie in sending the query to BigQuery. Or it could be in the missing <QUERY> sub-query. — countunique
– countunique, Commented Nov 16, 2021 at 5:09

Ricco D · Accepted Answer · 2021-11-16 05:43:45Z

1

Assuming that I have data in table 1 and I want to loop through it per ID then insert it to table 2. Using your code snippet above I was able to insert data from table 1 to table 2 per ID. Just make sure that your client.query() is in the loop.

Table 1:

Table 2 (initial value):

Code below uses the data queried from table 1 while looping through proc_arr (which I assume your IDs are stored in here) then inserting it to table 2.

from google.cloud import bigquery

def insert_data():
    client = bigquery.Client()
    proc_arr = ['asdf','test_value','cust_1']
    for x in proc_arr:
        query = """
                INSERT INTO `sandbox.table2` (customer,amount_paid)
                SELECT * FROM `sandbox.table1`
                WHERE customer =  '{}'
                ORDER BY amount_paid ASC LIMIT 1
                """.format(x)
        query_job = client.query(query=query)
        results = query_job.result()

if __name__ == "__main__":
    insert_data()

Table 2 (after running python script):

edited Nov 16, 2021 at 5:43

answered Nov 16, 2021 at 5:18

Ricco D

7,3451 gold badge11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ricco D Over a year ago

@Misscurious let me know if my answer was helpful or not, so I can update/delete it.

Collectives™ on Stack Overflow

Python & Bigquery: Using for loop to query and insert data row by row in a table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related