passing a date to a function in python that is calling sql server

Question

I am trying to pass date1 and date 2 to a function in python that creates a dataframe from a sql server

  import datetime as dt
  import pandas as pd
  import pyodbc
  import sqlalchemy
  from pandas import Series,DataFrame
  cnx=sqlalchemy.create_engine("mssql+pyodbc://Omnius:MainBrain1@172.31.163.135:1433/Basis?driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0")

def func1(date1,date2):
sqlquery = "select top 10000 * from Pretty_Txns where Pay_Date between '"+date1+"' and '"+date2+"'"
df=pd.read_sql(sqlquery,cnx)

When I try and call this function from ipython

  func1(dt.date(2015,05,01),dt.date(2015,05,02))

  ----> 8     sqlquery = "select top 10000 * from Pretty_Txns where Pay_Date between  '"+date1+"' and '"+date2+"'"

 TypeError: cannot concatenate 'str' and 'datetime.date' objects

How should I call the function so that I dont get the type error.

generally speaking, you don't quote placeholders. `between ? and ?` are two placeholders, while `between '?' and '?'` are two STRINGS that happen to contain question marks. — Marc B, Jun 13 '16 at 20:07

score 0 · Answer 1 · answered Jun 13 '16 at 20:09

0

sqlquery = "select top 10000 * from Pretty_Txns where Pay_Date between :date1 and :date2"

df=pd.read_sql(sqlquery,cnx, params=dict(date1=date1, date2=date2))

If your driver does not support named placeholders then use question marks as before and pass in a list of [date1, date2] as the value to params.

answered Jun 13 '16 at 20:09

Paul Harrington

772
4
9

1

It is essential for my code that I call the function in this manner func1(dt.date(2015,05,01),dt.date(2015,05,02)). So the proposed solution will not work. – mysterious_guy Jun 13 '16 at 20:20

Merlin · Accepted Answer · 2016-06-13T23:50:34.870

0

This should get you started. Your SQL will likely not work unless the date formats match. Print out the sqlquery and test in CLI.

Example conversion:

t = datetime.datetime(2012, 2, 23, 0, 0)
t.strftime('%m/%d/%Y')


def func1(date1,date2):
      strDate1 =  date1.strftime('%m/%d/%Y')
      strDate2 =  date2.strftime('%m/%d/%Y')

      sql = "select top 10000 * from Pretty_Txns where Pay_Date > '%s' and Pay_Date < '%s'  " %    (strDate1, strDate2)
      return sql

sqlquery = func1( DateOne,DateTwo )
#print sqlquery. 

df=pd.read_sql(sqlquery,cnx)

edited Jun 13 '16 at 23:50

answered Jun 13 '16 at 20:36

Merlin

24,552
41
131
206

that worked partially..getting below error: DataError: (pyodbc.DataError) ('22018', '[22018] [Microsoft][SQL Server Native Client 11.0][SQL Server]Operand type clash: date is incompatible with int (206) (SQLExecDirectW)') [SQL: 'select top 10000 * from Pretty_Txns where Pay_Date > 05/01/2015 and Pay_Date < 05/02/2015'] SQL syntax for date is '2015-05-01', – mysterious_guy Jun 13 '16 at 21:08
'%m/%d/%Y' >> %Y-%m-%d Check the docs! https://docs.python.org/2/library/datetime.html – Merlin Jun 13 '16 at 21:13
SQL datetime literals needs to be enclosed in single quotes i.e. `Pay_Date > '%s' and Pay_Date < '%s'` – Paul Harrington Jun 13 '16 at 22:17
1

Mixing SQL and string substitution is a [bad idea](https://en.wikipedia.org/wiki/SQL_injection), use [parameters](https://mkleehammer.github.io/pyodbc/#params). – Bryan Jun 13 '16 at 22:41
@Bryan, Look at OP request, Writing for relative newbie, without rewriting entire SQL statement... – Merlin Jun 13 '16 at 23:57
Thanks everyone for inputs..@PaulHarrington..your suggestion worked. – mysterious_guy Jun 14 '16 at 14:51
@vishalbhatt, if the anser helped, please upvote, – Merlin Jun 14 '16 at 14:53

passing a date to a function in python that is calling sql server

2 Answers2

Linked