Assign query result to variable - GBQ Python Client

Question

I'm using the Google Big Query Python Client Library. One of the queries I'm running is a simple count of the number of tables in the dataset and I wanted to know if there is a neater way to assign the query result to a variable than the way I am currently doing it.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT count(*)
    AS table_count
    FROM project.dataset.INFORMATION_SCHEMA.TABLES
"""


table_count = client.query(query) # returns a query job object

for row in table_count
    count = row[0] # assign count value to single variable

Is there a way to do this without needing to use a loop to access the values in the table?

Ka Boom · Accepted Answer · 2021-06-25 14:43:42Z

1

Learn about the pandas, then you can assign your result directly to Pandas data frame. With this data frame, you can do whatever you want to process your data. Here is the example link

PS: pandas is famous data processing framework in Python. If you want to learn how to process data with Python, pandas is a must.

answered Jun 25, 2021 at 14:43

Ka Boom

4841 gold badge3 silver badges10 bronze badges

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

1 Comment

Tristan Thompson Over a year ago

Thanks! That looks like what I needed!

shollyman · Accepted Answer · 2021-06-25 17:54:19Z

1

It's likely easiest to simply use the list constructs you're happiest with and break once you have the first row, as the results are available via an iterable.

Adapting your example:

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT count(*)
    AS table_count
    FROM project.dataset.INFORMATION_SCHEMA.TABLES
"""

job = client.query(query)

results = job.result()

for row in results:
  print(f'there were {row[0]} tables')
  break

answered Jun 25, 2021 at 17:54

shollyman

4,42422 silver badges18 bronze badges

Comments

Shradha · Accepted Answer · 2021-06-26 16:02:42Z

0

You can convert the query job object into a dataframe as follows-

df = client.query(query).to_dataframe()

answered Jun 26, 2021 at 16:02

Shradha

2,4721 gold badge16 silver badges27 bronze badges

Collectives™ on Stack Overflow

Assign query result to variable - GBQ Python Client

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related