Call variable in pyodbc SQL Queury in Python

Question

I'm trying to replace a hardcoded date in my sql queury using '{Variable}' but I can't make it work.

Here is what I used to do (which works fine):

conn_drm = pyodbc.connect(
   )
query_drm =  '''
SELECT *
FROM 
WHERE base_file_date = '2022-04-25'
'''
DF = pd.read_sql_query(query_drm, conn_drm)

Now, I would like do to somethig like this:

Yesterday = date.today()  - timedelta(days=1)
Yesterday = str(Yesterday)

conn_drm = pyodbc.connect(
   )
query_drm =  '''
SELECT *
FROM 
WHERE base_file_date = '{Yesterday}'
'''
DF = pd.read_sql_query(query_drm, conn_drm)

but I get the error: Conversion failed when converting date and/or time from character string. (241)

Could someone plz help?

score 0 · Answer 1 · answered Apr 26 '22 at 15:16

You have to use a formatable string to insert values like this. For this you should add a f in front of the string :

query_drm =  f'''
SELECT *
FROM 
WHERE base_file_date = '{Yesterday}'
'''

However, please take note that if you have any other input here other than the date and where a user can interact with, this method is dangerous and you should prefer to use the method here : pyodbc - How to perform a select statement using a variable for a parameter

Call variable in pyodbc SQL Queury in Python

1 Answers1