CODEWITHSUNDEEP
pythongoogle-bigquery
Tristan Thompson
Tristan Thompson

Reputation: 103

Assign query result to variable - GBQ Python Client

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?

Upvotes: 0

Views: 2641

Answers (3)

Shradha
Shradha

Reputation: 2432

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

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

Upvotes: 0

shollyman
shollyman

Reputation: 4384

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

Upvotes: 1

Ka Boom
Ka Boom

Reputation: 484

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.

Upvotes: 1

Related Questions