CODEWITHSUNDEEP
pythonsql-servert-sql
lignin
lignin

Reputation: 459

SQL query date range in python

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)`

Upvotes: 1

Views: 4014

Answers (1)

Scratch'N'Purr
Scratch'N'Purr

Reputation: 10427

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.

Upvotes: 1

Related Questions