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?

Gord Thompson · Accepted Answer · 2020-01-20 14:11:42Z

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

user12688781 · Accepted Answer · 2020-01-20 09:53:49Z

-1

Read_sql is just for reading tables. To execute someting with parameters try using

engine.execute(your sql query)#you can try entering with parameters as how you give in your sql engine.execution_options(autocommit=True)

answered Jan 20, 2020 at 9:53

user12688781

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related