How to use variables in query_to_pandas

Just got dumped into SQL with BigQuery and stuff so I don’t know alot of terms for this kinda stuff. Currently trying to make a method for which you input a string (the dataset name you want to take out). But I can’t seem to put in a string into the variable I want without it returning errors.

I looked up how to put in variables for SQL stuff but most of those solutions weren’t for my case. Then I ended up with adding $s and adding s before the “”” variable. (this ended up with a syntax error)

import pandas as pd
import bq_helper
from bq_helper import BigQueryHelper
# Some code about using BQ_helper to get the data, if you need it lmk

# test = `data.patentsview.application`

query1 = s"""
SELECT * FROM $s
LIMIT
 20;
        """
response1 = patentsview.query_to_pandas_safe(query1)
response1.head(20)

With the code above it returns the error code

File "<ipython-input-63-6b07957ebb81>", line 8
    """

^
SyntaxError: invalid syntax

EDIT: The original code that worked but would have to be manually bruteforced is this

query1 = """
SELECT * FROM `patents-public-data.patentsview.application`
LIMIT
 20;
        """
response1 = patentsview.query_to_pandas_safe(query1)
response1.head(20)

Answer

If I understand you correctly, this may be what you’re looking for:

#making up some variables:
vars = ['`patents-public-data.patentsview.application','`patents-private-data.patentsview.application']

for var in vars:
    query = f"""SELECT * FROM {var}
LIMIT
 20;
 """
    print(query)

Output:

 SELECT * FROM `patents-public-data.patentsview.application
LIMIT
 20;

SELECT * FROM `patents-private-data.patentsview.application
LIMIT
 20;

Advertisement

Answer