Search code examples
pythonsql-serverstored-proceduressqlalchemy

Execute Stored Procedure with SQL Alchemy Connection

I want to execute a stored procedure using an SQL Alchemy Connection. I get no errors during execution, but no changes are reflected on the database. If I run the stored procedure directly on my Azure SQL Server, I get the expected results (which are updating a table).

I tried running just the procedure and get this: enter image description here

Any ideas on why it is not working?

I am also open to other ideas! I am trying to execute my stored procedure after upserting a table.

Solution

  • The recommended approach for current versions of SQLAlchemy is to use a context manager (with block) to begin() the transaction. When the context manager exits the transaction will automatically be committed unless an error has occurred:

    with engine.begin() as conn:
    conn.exec_driver_sql("EXEC my_stored_procedure")

    Bonus tip: Naming stored procedures with an sp_ prefix is discouraged because it can lead to confusion with SQL Server's built-in "system procedures". More details here:

    https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix