CODEWITHSUNDEEP
pythongoogle-bigqueryjupyter-notebook
Misscurious
Misscurious

Reputation: 39

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

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!

Upvotes: 0

Views: 2557

Answers (1)

Ricco D
Ricco D

Reputation: 7277

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:

enter image description here

Table 2 (initial value):

enter image description here

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):

enter image description here

Upvotes: 1

Related Questions