How to use variable in SQL query using Python
I'm trying to pull data from my Google Ads account using their API. I'm using Python
In the code example there is a query where I can specify the date range from which I want to collect results from my campaign.
I would like to dynamically change de date everyday and run the script to get data from the previous day.
My idea was to create a variable with the previous day date in the format Google API is requiering it, named "yesterday".
But I don't know how to use this variable into the query
Here's a part of the code :
import argparse import sys from google.ads.googleads.client import GoogleAdsClient from google.ads.googleads.errors import GoogleAdsException from datetime import * yesterday_date = date.today() - timedelta(days=1) yesterday = yesterday_date.strftime("%Y-%m-%d") def main(client, customer_id): ga_service = client.get_service("GoogleAdsService") query = """ SELECT campaign.id, campaign.name, metrics.cost_micros, campaign.status FROM campaign WHERE segments.date = '2021-08-19' """ # Issues a search request using streaming. response = ga_service.search_stream(customer_id=customer_id, query=query) for batch in response: for row in batch.results: print( f"Campaign with ID {row.campaign.id}, status is {row.campaign.status}, cost is {row.metrics.cost_micros} and name " f'"{row.campaign.name}" was found.' ) I would like to do something like this :
WHERE segments.date = yesterday I've tried to use sqlite3 library, to use cursor.execute() but I don't know how to use them properly.
I'm a beginner so the answer might be very easy.
There's a couple ways you could go about this, I'll provide both that I thought of initially.
Formatting the query in PythonYou can use .format() to inject a variable into your SQL query from Python. You want to be careful using this technique without cleansing inputs because it can be taken advantage of to do harm to your database. That said, since you're calculating the variable in your script, there isn't a risk of SQL injection here.
from datetime import date, timedelta yesterday_date = date.today() - timedelta(days=1) yesterday = yesterday_date.strftime("%Y-%m-%d") query = """ SELECT campaign.id, campaign.name, metrics.cost_micros, campaign.status FROM campaign WHERE segments.date = '{yesterday}' """.format(yesterday=yesterday)Results of print(query):
SELECT campaign.id, campaign.name, metrics.cost_micros, campaign.status FROM campaign WHERE segments.date = '2021-08-23' Obtaining Yesterday in SQLDepending on your flavor of SQL, you could also approach it with a native date function in SQL (which you found for your version). I saw you commented on your question stating that you found the capability to do so:
WHERE segments.date DURING YESTERDAYHere's a link to the documentation provided by Google that lists all the available date functions for their querying language referenced in this question.
Just a note for others who may look at this: you could use something like get_date() in SQL Server or curdate() in MySQL.
I'm not sure if you can do that since the variable will not exist in the SQL Server namespace. This is one way to do it.
yesterday = datetime.datetime.now().date().__str__() def create_query_string(x): return '"""'+ " SELECT campaign.id, campaign.name, metrics.cost_micros, campaign.status FROM campaign WHERE segments.date = '{}' ".format(x)+ '"""' #use this to generate query create_query_string(yday)I can't make it work, but I get the idea behind it and it's very interesting! Thank you for your answer. PS: I found a simplier solution using Google API variable
I just modified it and tested it locally. You can try this if you want to use any other custom date value generated by your python code.
Thank you so much, I'm gonna give it a try!
I found a solution that is not using my own variable but the one from Google API.
I can simply call the "Yesterday result" by this :
WHERE segments.date DURING YESTERDAY