Calling MSSQL stored procedure from SqlAlchemy

Question

It does not seem like SqlAlchemy supports calling stored procedures. Did anybody find a workaround for this that works with SQL Server?

Sample procedure:

CREATE PROCEDURE list_lock_set @name varchar (5), @requester varchar(30)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO list_lock (name, requester, acquired) values (@name, @requester, GETDATE())
    RETURN 0    
END
GO

This works:

import pyodbc
dbh = pyodbc.connect(driver=''{SQL Server}'', server=srv, database=db, uid=uid, pwd=pwd)
dbc = dbh.cursor()
dbc.execute("list_lock_set ?, ?", ['bbc', 'pyodbc'])
dbc.commit()

This does not produce an error but also but does not work:

from sqlalchemy import create_engine
engine = create_engine('mssql+pyodbc://usr:passw@srv/db?driver=SQL Server', echo=True)
engine.execute("list_lock_set ?, ?", ['bbc', 'sqlalchemy'])

Thank you.

EDIT: It appears the best solution is to fish out pyodbc cursor from the engine:

cursor = engine.raw_connection().cursor()
cursor.execute("list_lock_set ?, ?", ['bbc', 'using cursor'])
cursor.commit()

I can also obtain pyodbc Connection:

engine.raw_connection().connection

and set autocommit=True, but that might interfere with engine logic. Many thanks to @Batman.

Answer 1

To have it working in sqlalchemy I managed to do it this way:

from sqlalchemy import create_engine
engine = create_engine('mssql+pyodbc://usr:passw@srv/db?driver=SQL Server', echo=True)
with engine.begin() as conn:
    conn.execute("exec dbo.your_proc")

Answer 2

I remember this giving me grief before too. From memory either session.execute() or connection.execute() worked for me. There's also a callproc() method, which is probably the right way to go. http://docs.sqlalchemy.org/en/latest/core/connections.html

Also, I've had issues in the past with MSSQL which seemed to be due to something asynchronous happening where the method was returning before the procedure was finished, which resulted in unpredictable results on the database. I found that putting a time.sleep(1) (or whatever the appropriate number is) right after the call fixed this.