CODEWITHSUNDEEP
pythonsql-serversqlalchemy
Dave
Dave

Reputation: 2601

Is it possible to successfully execute a parametrized stored procedure in MSSQL using SqlAlchemey in Python?

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', "[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)")

@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 "@oarm1". (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?

Upvotes: 0

Views: 233

Answers (1)

Dave
Dave

Reputation: 2601

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()

Upvotes: 0

Related Questions