[SOLVED] pass datetime in sql query while fetching the data with bulk api simple

pass datetime in sql query while fetching the data with bulk api simple_salesforce Python

I have a code that will be running automatically and I need to save the datetime of the last run so that the next time my code runs it only grabs the data that has been updated later than last run time. I am using simple_salesforce library with BULK API call. Here is my code:

# open json file and retrieve last_run_time
with open('last_run_time.json', 'r') as openfile: 
    last_run_time = json.load(openfile)
print(last_run_time)
# '2020-05-14T14:48:38Z'

# sql query 
sf_data = sf.bulk.Opportunity.query("SELECT ID FROM Opportunity where CreatedDate > last_run_time AND AdPoint_Id__c <> NULL")

today_date = datetime.now()
today_date_time = today_date.strftime("%Y-%m-%d"+"T"+"%H:%M:%S"+"Z")

# save datetime in json file, overwriting the old value
with open("last_run_time.json", "w") as outfile: 
    json.dump(today_date_time, outfile)

My query throws an error IndexError: list index out of range. How can I pass last_run_time variable in sql statement?

Solution

You need to break out the last_run_time variable from the rest of the query string. In the current form, the literal string 'last_run_time' is included in your query.

The amended query would be:

sf_data = sf.bulk.Opportunity.query("SELECT ID FROM Opportunity where CreatedDate > " + last_run_time + " AND AdPoint_Id__c <> NULL")