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](http://stackoverflow.com/questions/32699299/using-openquery-with-an-oracle-database-to-query-a-date-range). 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, Sep 15 '16 at 14:02

score 1 · Answer 1 · answered Sep 12 '16 at 13:42

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 '16 at 13:42

Scratch'N'Purr

9,959
2
35
51

IF you think about it for a bit, it totally makes sense, right. Good detective work. – Dec 19 '16 at 22:29

SQL query date range in python

1 Answers1