SQL query date range in python

Question

I was simply trying to query an SQLServer database in a specific date range. Somehow I just can't figure it out myself. Here is what I did:

    import pyodbc
    import pandas as pd
    con = pyodbc.connect("Driver={SQL Server}; Server=link")
    tab = pd.read_sql_query("SELECT * FROM OPENQUERY(aaa, 'SELECT * FROM bbb.ccc WHERE number like (''K12345%'')')",con)

    tab['DT']
    0    2015-09-17 08:51:41
    1    2015-09-17 09:14:09
    2    2015-09-17 09:15:03
    3    2015-09-24 15:20:55
    4    2015-09-24 15:23:47
    5    2015-10-02 08:49:59
    6    2015-10-30 14:08:40
    7    2015-10-30 14:13:38
    8    2015-11-03 14:30:06
    9    2015-11-03 14:30:22
    10   2015-11-04 07:14:40
    11   2015-11-04 10:43:51
    Name: DT, dtype: datetime64[ns]

Now I thought I should be able to select the records on the dates between 2015-09-18 and 2015-10-02 by using the following query. Somehow it failed with error

DatabaseError: Execution failed on sql: SELECT * FROM OPENQUERY(aaa, 'SELECT * FROM bbb.ccc WHERE DT between ''2015-09-18'' and ''2015-10-02''')".

Can someone help explain what I did wrong?

 tab2 = pd.read_sql_query("SELECT * FROM OPENQUERY(aaa, 'SELECT * FROM bbb.ccc `WHERE DT between ''2015-09-18'' and ''2015-10-02''')",con)`

A little more research found similar question here: link. It simply needs conversion of the date format. The following code works for me now: tab2 = pd.read_sql_query("SELECT * FROM OPENQUERY(aaa, 'SELECT * FROM bbb.ccc WHERE DT between to_date(''18-Sep-2015'') and to_date(''02-Oct-2015'')')",con) — lignin
– lignin, Commented Sep 15, 2016 at 14:02

Scratch'N'Purr · Accepted Answer · 2016-09-12 13:42:17Z

1

You should use parameter binding:

tab2 = pd.read_sql_query("SELECT * FROM bbb.ccc WHERE DT between ? and ?", con, params=['2015-09-18', '2015-10-02'])

The ? are placeholders for the values you are passing from the list. The number of ?'s must match the number of items from your list.

And I'm not sure why you have a SELECT * wrapped in another SELECT * so I simplified with just the innermost select.

answered Sep 12, 2016 at 13:42

Scratch'N'Purr

10.5k2 gold badges39 silver badges54 bronze badges

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

1 Comment

user7075507 Over a year ago

IF you think about it for a bit, it totally makes sense, right. Good detective work.

Collectives™ on Stack Overflow

SQL query date range in python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related