Title: Is it possible to successfully execute a parametrized stored procedure in MSSQL using SqlAlchemey in Python?
Tags: python;sql-server;sqlalchemy
Question: I have been fighting with SqlAlchemy and stored procedures for far too long. I have tried numerous and sometimes contradictory things I have found online, but no matter what I do, it ends up in errors.
Here is where I am currently.
connection = get_connection_string()
engine = sqlalchemy.create_engine(connection)
Session = sessionmaker(bind=engine,autocommit=True)
session = Session()
parms = {}
parms['@parm1'] = val1
parms['@parm2'] = val2
parms['@parm3'] = val3
parms['@parm4'] = val4
parms['@parm5'] = val5
results = session.execute('p_insert_data ?, ?, ?, ?, ?', parms)
This results in the following error.
sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('07002', '[07002] [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')
I have 5 '?' characters for the five parameters and this is the syntax I see everywhere, so I am at a loss to what it is actually complaining about.
I have also tried calling the above code with no '?' characters. That gives me this error.
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', &quot;[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Procedure or function 'p_insert__data' expects parameter '@parm1', which was not supplied. (201) (SQLExecDirectW)&quot;)
@parm1 was supplied in the dictionary, but something fails to find it there.  I tried specifically declaring it like this, but that just leads to another error.
results = session.execute('p_insert_data @parm1', parms)
'42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Must declare the scalar variable &quot;@oarm1&quot;. (137) (SQLExecDirectW)')
So back to my original question. Is it possible to successfully execute a parameterized stored procedure against MSSQL using SqlAlchemey? If so, will someone please share the correct syntax or let me know what I am failing to understand about this?
Thanks
Edit: Doing the following gets rid of the errors, however nothing happens. The table that is supposed to be populated by the stored procedure is still empty.
results = session.execute('p_insert_data parm1, parm2, parm3, parm4, parm5', parms)
Edit 2: Ok, learned a few more things. Setting 'autocommit=True' in the sessionmaker is the same as doing nothing. To get transactions to commit, you need to do the following.
session.begin()
results = session.execute('p_insert_data parm1, parm2, parm3, parm4, parm5', parms)
session.commit()
This finally gets data into the database, except the data is just the parameter names, not their actual values. Argh, so now I am back at square one. Why is the parms argument ignored?
Comment: You may have better luck using [exec_driver_sql](https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Connection.exec_driver_sql) inside a context manager (`with engine.begin() as conn:`).


Here is another answer: Ok, finally got it figured out with the help of this question.
Passing parameters not being recognized and throws SQL error when executing raw query (on SQL-Server database and Pymssql) with SqlAlchemy
sql = sqlalchemy.sql.text('p_insert_data :parm1, :parm2, :parm3, :parm4, :parm5')
parms = {}
parms['parm1'] = val1
parms['parm2'] = val2
parms['parm3'] = val3
parms['parm4'] = val4
parms['parm5'] = val5
session.begin()
results = session.execute(sql, parms)
session.commit()

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2022:00:02/24/22', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<python><sql-server><sqlalchemy>', 'Title': 'Is it possible to successfully execute a parametrized stored procedure in MSSQL using SqlAlchemey in Python?', 'ViewCount': 44, 'id': 71256773, 'user_AboutMe': None, 'user_AccountId': 3451493.0, 'user_DisplayName': 'Dave', 'user_Reputation': 2281.0, 'user_Views': 168.0, 'user_id': 2891081.0}}