I’m using the following code to execute a SQL stored procedure from Python. The stored procedure in question iterates over a number of different insert statements contained in another table. However, when ran from Python, the stored procedure doesn’t iterate over the complete set of data.
I’ve tested the stored procedure myself in SQL and it works fine. I’ve also tried editing the pool_timeout parameter on the sql_engine connection but that doesn’t seem to change anything either.
How can I ensure that the below code continues until the entire stored procedure has been completed?
import connection_manager as cm
from sqlalchemy import text
sql_engine = cm.create_db_engine('Server','Database')
with sql_engine.connect() as conn:
conn.execute(text("EXEC dbo.SomeStoredProcedure"))
conn.commit()
conn.close()
unknown9192 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Use the callproc() method provided by SQLAlchemy. The callproc() method allows you to execute a stored procedure and retrieve any result sets or output parameters returned by the procedure.
import connection_manager as cm
from sqlalchemy import text
sql_engine = cm.create_db_engine('Server', 'Database')
with sql_engine.connect() as conn:
result = conn.execute(text("CALL dbo.SomeStoredProcedure()"))
conn.commit()
# Fetch the result sets, if any
while True:
row = result.fetchone()
if row is None:
break
print(row)
conn.close()
1