0

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)
av8
  • 1
  • 1
  • What is the problem you are facing? – kadamb Jul 31 '20 at 08:29
  • That select query I wrote doesn't work written this way. – av8 Jul 31 '20 at 08:31
  • Try putting start_date and end_date in quotes. Also, print your query, and show your code. Are you getting any error? – kadamb Jul 31 '20 at 08: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 Jul 31 '20 at 08:41
  • See for example https://stackoverflow.com/questions/24408557/pandas-read-sql-with-parameters 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ä Jul 31 '20 at 09:14
  • [Set a date variable to SQL query in Python](https://stackoverflow.com/questions/47043508/set-a-date-variable-to-sql-query-in-python/47045041#47045041) – Ilja Everilä Jul 31 '20 at 11:27

1 Answers1

-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;"""
av8
  • 1
  • 1