Trying not to hardcode date range in SQL query (Python, SQL server)

Question

I am using Python to connect to SQL Server database and execute several 'select' type of queries that contain date range written in a particular way. All these queries have the same date range, so instead of hard-coding it, I'd prefer to have it as a string and change it in one place only when needed. So far, I found out that I can use datetime module and the following logic to convert dates to strings:

from datetime import datetime
start_date = datetime(2020,1,1).strftime("%Y-%m-%d")
end_date = datetime(2020,1,31).strftime("%Y-%m-%d")

Example of the query:

select * from where xxx='yyy' and time between start_date and end_date

How can I make it work?

EDIT my code:

import pyodbc
import sqlalchemy
from sqlalchemy import create_engine
from datetime import datetime

start_date = datetime(2020,1,1).strftime("%Y-%m-%d")
end_date = datetime(2020,1,31).strftime("%Y-%m-%d")

engine = create_engine("mssql+pyodbc://user:pwd@server/monitor2?driver=SQL+Server+Native+Client+11.0")

sql_query = """ SELECT TOP 1000 
      [mtime]
      ,[avgvalue]
  FROM [monitor2].[dbo].[t_statistics_agg]
  where place = 'Europe' and mtime between 'start_date' and 'end_date'
  order by [mtime] asc;"""

df = pd.read_sql(sql_query, engine)
print(df)

Try putting start_date and end_date in quotes. Also, print your query, and show your code. Are you getting any error? — kadamb
– kadamb, Commented Jul 31, 2020 at 8:32
Added my code to the post. It doesn't work in this form. However, it works if I simply use strings '2020-01-01' and '2020-01-31' — av8
– av8, Commented Jul 31, 2020 at 8:41
See for example stackoverflow.com/questions/24408557/… for pointers on how to properly and safely pass parameters to SQL queries in Pandas. Depending on the type of your mtime column you do not need to convert the Python datetime yourself at all, because your driver will do it for you, if using proper placeholders and passing the params separately. — Ilja Everilä
– Ilja Everilä, Commented Jul 31, 2020 at 9:14

av8 · Accepted Answer · 2020-07-31 09:23:08Z

-2

Thank you all for your input, I have found the answer to make the query work. The variables should look like:

start_date = date(2020, 1, 1)
end_date = date(2020, 1, 31)

and SQL query like:

sql_query = f""" SELECT TOP 1000 
      [mtime]
      ,[avgvalue]
  FROM [monitor2].[dbo].[t_statistics_agg]
  where place = 'Europe' and mtime between '{start_date}' and '{end_date}'
  order by [mtime] asc;"""

answered Jul 31, 2020 at 9:23

av8

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trying not to hardcode date range in SQL query (Python, SQL server)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related