How to pass date parameters to execute sql stored procedure in python pandas

Question

This way works:

import pandas
import pyodbc
import datetime as dt  

server = 'myserver'
db = 'mydb' 
myparams = ['2017-02-01','2017-02-28', None]  # None substitutes NULL in sql

connection_string = pyodbc.connect('DRIVER={SQL Server};server='+server+';DATABASE='+ db+';Trusted_Connection=yes;')
df = pandas.read_sql_query('EXEC PythonTest_Align_RSrptAccountCurrentMunich @EffectiveDateFrom=?,@EffectiveDateTo=?,@ProducerLocationID=?', connection_string, params = myparams)

But this way does not work:

import datetime as dt

today = dt.date.today()
prev_monday = today - dt.timedelta(days=9)
prev_sunday = today - dt.timedelta(days=3)

myparams = [prev_monday,prev_sunday, None]  # None substitutes NULL in sql

connection_string = pyodbc.connect('DRIVER={SQL Server};server='+server+';DATABASE='+ db+';Trusted_Connection=yes;')
df = pandas.read_sql_query('EXEC PythonTest_Align_RSrptAccountCurrentMunich @EffectiveDateFrom=?,@EffectiveDateTo=?,@ProducerLocationID=?', connection_string, params = myparams)

Error message:

    cur.execute(*args)
pandas.io.sql.DatabaseError: Execution failed on sql 'EXEC PythonTest_Align_RSrptAccountCurrentMunich @EffectiveDateFrom=?,@EffectiveDateTo=?,@ProducerLocationID=?': ('HYC00', '[HYC00] [Microsoft][ODBC SQL Server Driver]Optional feature not implemented (0) (SQLBindParameter)')

Is there specific way for passing date parameters in python?

I've not executed a stored procedure in pandas before (although trying to figure out how to do this in databricks..), but can you try pd.read_sql() instead ? — Umar.H
– Umar.H, Commented Oct 23, 2019 at 22:03

Umar.H · Accepted Answer · 2019-10-23 22:10:47Z

1

my guess here is that SQL can't handle pandas datetime values,

try passing the datetime values into strigns using strftime

import datetime as dt

today = dt.date.today()
prev_monday = (today - dt.timedelta(days=9)).strftime('%Y-%m-%d')
prev_sunday = (today - dt.timedelta(days=3)).strftime('%Y-%m-%d')

myparams = [prev_monday,prev_sunday, None]
print(myparams)
['2019-10-14', '2019-10-20', None]

answered Oct 23, 2019 at 22:10

Umar.H

23.1k7 gold badges50 silver badges94 bronze badges

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

5 Comments

Suraj Motaparthy Over a year ago

What's funny is that '{}'.format(dt.date.today()) gives '2019-10-23' which is the same string notation. That's why it's a bit puzzling

Serdia Over a year ago

With this approach it returns empty dataframe

Umar.H Over a year ago

but the stored procedure executes? have you tried it in SQL to see if the values exist in the dB?

Serdia Over a year ago

Thanks. Sorry, I switched to development environment, there is no data there. Works perfect. Thank you!

Umar.H Over a year ago

@oleg interesting wonder if sql alchemy has the same behavior, thank you.

Collectives™ on Stack Overflow

How to pass date parameters to execute sql stored procedure in python pandas

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related