I would like to retrieve data from bigquery table executing a select statement from python and using a variable in my where clause. here is my code :
bq_client = Client()
var1 = 'New York'
sql = """
SELECT *
FROM `myTable`
WHERE town = var1
LIMIT 10
"""
df = bq_client.query(sql).to_dataframe()
print(df)
This doesn't work because var1 is not replaced by its value when running
To use a variable in your python code, as you have shown, you have to tell BigQuery that these variables exist using a job config, you can then call them with syntax: @var1
For example:
date = "2020-01-01"
job_config = bigquery.QueryJobConfig()
sql = """
SELECT *
FROM dataset.table
WHERE date = @date
"""
query_params = [bigquery.ScalarQueryParameter('date', 'DATE', date)]
job_config.query_parameters = query_params
You can find more information on the official documentation.
Your query doesn't have a variable in it- it has the string var1.
If you wanted to do that something like
sql = """
select *
from `myTable`
where town = {}
LIMIT 10
""".format(var1)
If this is for production code, you generally don't want to use string interpolation in sql. I don't know the big query client library, but most python sql libraries have the option to pass params to them (and have those params cleaned against sql injction type stuff, and types fixed, and whatnot)
