Variable table name in BigQuery Python Client

Question

Having seen the correct format for the setting of variables for sql queries, I'm not finding a similar way to parameterise the table name. For example, I have a simple query as follows

query = """
    select price, category, title, sm_title from `product.data_set.table_name` where  
    product = @product"""
    
    
    job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ScalarQueryParameter("product", "INT64", product),
    ])
    
    
    product_data = client.query(query, job_config=job_config).to_dataframe()

I would like the table_name to be a variable that can be passed in. Is there can option that can be used like is set in the job_config?

To my knowledge no. This would get into dynamic SQL. you could dynamically set it as part of building your query string, but the SQL passed to the database must have a defined table name which isn't variable. the engine's haven't been built to allow for a "Dynamic" table since tables have rigid structures defined. Each table could/should be different so why would one want a dynamic table name... different table is a different query consisting of different objects entirely. If the tables are that similar, why not use a type to filter the like data and a parameter works... — xQbert
– xQbert, Commented Mar 1, 2022 at 17:41
cant you do that with string formatting? line 1: table_name = "tablenamegoeshere" line 2: query = f""" select price, category, title, sm_title from `product.data_set.{table_name}` where product = @product""" — Edo Akse
– Edo Akse, Commented Mar 1, 2022 at 17:45
Hi OP, have you tried @EdoAkse 's suggestion using f string formatting and confirm if it resolves your concern? — Scott B
– Scott B, Commented Mar 2, 2022 at 3:10

Scott B · Accepted Answer · 2022-03-04 10:01:14Z

1

Setting of python variable for SQL query can be done using python's f string formatting approach as suggested by @EdoAkse in the comments.

You may use below

table_name = "tablenamegoeshere"
query = f""" select price, category, title, sm_title from `product.data_set.{table_name}` where product = @product"""

Posting the answer as community wiki for the benefit of the community that might encounter this use case in the future.

Feel free to edit this answer for additional information.

answered Mar 4, 2022 at 10:01

community wiki

Scott B

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

2 Comments

plamut Over a year ago

Just a word of caution - table_name should come from a trusted source to avoid SQL injection risks.

dan-man Over a year ago

echoing @plamut - sql injection is bad. Use an ORM or find some official(ish) way to validate the safeness of the variable content. This shouldn't really be an accepted answer without a very big warning to future readers. w3schools.com/sql/sql_injection.asp

Collectives™ on Stack Overflow

Variable table name in BigQuery Python Client

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related