How to set and reference date range in Python and then apply to a query?-CodePudding

This is what im trying to do in Spyder

from datetime import datetime, date
start_date = date(year=2022, month=8, day=9)
end_date = date(year=2022, month=9, day=16)

start_date_str = start_date.strftime("%Y-%m-%d")
end_date_str = end_date.strftime("%Y-%m-%d")

test_query = """
SELECT COUNT(DISTINCT(advertising_id)) FROM dataset
WHERE group_assignment = "TEST" AND day BETWEEN "{start_date}" AND "{end_date}"
"""

I get the error Could not cast literal "{start_date}" to type DATE at [3:49] How do I just reference code in python within my queries? It seems so hard to find the answer

I need to set a fixed date in python so I dont have to change the date of every query im pulling using bigquery within python

CodePudding user response：

from datetime import datetime

# Set the start date to January 1st, 2020
start_date = datetime(2020, 1, 1)

# Set the end date to December 31st, 2020
end_date = datetime(2020, 12, 31)

More infos here.

CodePudding user response：

As one of the comments has correctly pointed out, the block of code requires an f to be an f-string (short for formatted string literals).

That's all.

So the code looks like this:

from datetime import datetime, date
start_date = date(year=2022, month=8, day=9)
end_date = date(year=2022, month=9, day=16)

start_date_str = start_date.strftime("%Y-%m-%d")
end_date_str = end_date.strftime("%Y-%m-%d")

test_query = f"""
SELECT COUNT(DISTINCT(advertising_id)) FROM dataset
WHERE group_assignment = "TEST" AND day BETWEEN "{start_date}" AND "{end_date}"
"""

print(test_query)

And the result is this:

SELECT COUNT(DISTINCT(advertising_id)) FROM dataset
WHERE group_assignment = "TEST" AND day BETWEEN "2022-08-09" AND "2022-09-16"