CODEWITHSUNDEEP
python-3.xgoogle-bigquerygoogle-cloud-functions
James Ching
James Ching

Reputation: 475

BigQuery update how to get number of updated rows

I am using Google Cloud Functions to connect to a Google Bigquery database and update some rows. The cloud function is written using Python 3.

I need help figuring out how to get the result message or the number of updated/changed rows whenever I run an update dml through the function. Any ideas? 

from google.cloud import bigquery

def my_update_function(context,data):

    BQ = bigquery.Client()
    query_job = BQ.query("Update table set etc...")
    rows = query_job.result()
    return (rows)

I understand that rows always come back as _emptyrowiterator object. Any way i can get result or result message? Documentation says I have to get it from a bigquery job method. But can't seem to figure it out.

Upvotes: 5

Views: 5602

Answers (2)

vitooh
vitooh

Reputation: 4272

I think that you are searching for QueryJob.num_dml_affected_rows. It contain number of rows affected by update or any other DML statement. If you just paste it to your code instead of rows in return statement you will get number as int or you can create some massage like :

return("Number of updated rows: " + str(job_query.num_dml_affected_rows))

I hope it will help :)

Upvotes: 17

James Ching
James Ching

Reputation: 475

Seems like there is no mention in the bigquery Python DB-API documentation on rows returned. https://googleapis.dev/python/bigquery/latest/reference.html

I decided to use a roundabout method on dealing with this issue by generating a SELECT statement first to check if there are any matches to the WHERE clause in the UPDATE statement.

Example:

from google.cloud.bigquery import dbapi as bq

def my_update_function(context,data):

        try:
            bq_connection = bq.connect()
            bq_cursor = bq_connection.cursor()
            bq_cursor.execute("select * from table where ID = 1")
            results = bq_cursor.fetchone()

            if results is None:
                print("Row not found.")

            else:
                bq_cursor.execute("UPDATE table set name = 'etc' where ID = 1")
                bq_connection.commit()
                bq_connection.close()

        except Exception as e:
            db_error = str(e)

Upvotes: 0

Related Questions