How to execute sql stored procedure with multiple date parameters using sqlalchemy in pandas

Question

I am able to execute stored procedure without parameters:

import pandas as pd
import sqlalchemy
import pyodbc
import datetime as dt  

engine = sqlalchemy.create_engine('mssql+pymssql://MyServer/MyDB')
df = pd.read_sql_query('EXEC dbo.TestProcedure' , engine)  # stored procedure without parameters
print(df)

But unable to execute stored procedure with parameters:

import pandas as pd
import sqlalchemy
import pyodbc
import datetime as dt  

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

engine = sqlalchemy.create_engine('mssql+pymssql://MyServer/MyDB')
df = pd.read_sql_query('EXEC PythonTest_Align_RSrptAccountCurrentMunich @EffectiveDateFrom=?,@EffectiveDateTo=?,@ProducerLocationID=?', engine, params=myparams)
print(df)

Error message:

  File "src\pymssql.pyx", line 465, in pymssql.Cursor.execute
sqlalchemy.exc.ProgrammingError: (pymssql.ProgrammingError) (102, b"Incorrect syntax near '?'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
[SQL: EXEC PythonTest_Align_RSrptAccountCurrentMunich @EffectiveDateFrom=?,@EffectiveDateTo=?,@ProducerLocationID=?]
[parameters: ('2017-02-01', '2017-02-28', None)]
(Background on this error at: http://sqlalche.me/e/f405)

How can I pass parameters using sqlalchemy?

Answer 1

If you are executing a raw SQL query with parameter placeholders then you must use the paramstyle supported by the DBAPI layer. pymssql used the "format" paramstyle %s, not the "qmark" paramstyle ? (which pyodbc uses).

However, you can avoid the ambiguity by wrapping the query in a SQLAlchemy text object and consistently use the "named" paramstyle. SQLAlchemy will automatically translate the parameter placeholders to the appropriate style for the DBAPI you are using. For example, to call a stored procedure named echo_datetimes:

import datetime
import sqlalchemy as sa

# ...

query = sa.text("EXEC echo_datetimes @p1 = :param1, @p2 = :param2")
values = {'param1': datetime.datetime(2020, 1, 1, 1, 1, 1),
          'param2': datetime.datetime(2020, 2, 2, 2, 2, 2)}
df = pd.read_sql_query(query, engine, params=values)
print(df)
#              dt_start              dt_end
# 0 2020-01-01 01:01:01 2020-02-02 02:02:02