1

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.

jarlh
  • 42,561
  • 8
  • 45
  • 63
nahimmedto
  • 25
  • 6
  • @python_user engine = create_engine(conn_str, fast_executemany=True) metadata = MetaData(bind=engine) session = engine.connect() return session, engine, metadata – nahimmedto May 25 '23 at 16:11
  • @python_user I get 'Connection' object has no attribute 'commit' – nahimmedto May 25 '23 at 16:15
  • try this: `conn.execute("""CALL sp_updateActivityInsertDate();""")` Some more examples are here: https://stackoverflow.com/questions/3563738/stored-procedures-with-sqlalchemy – 99problems May 25 '23 at 16:23

2 Answers2

1

use 'conn.commit()' to commit changes to the db then use 'conn.close()' to end the connection

Implementation:

import sqlite3
conn = sqlite3.connect('database.sqlite3')
conn.execute(f"insert into Users Values('email', 'password')")
conn.commit()
conn.close()
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Upbeat25YT
  • 21
  • 4
1

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

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thanks a ton! This is the only thing that worked for me. And thanks for the info on naming stored procedures. – nahimmedto May 25 '23 at 17:39