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.

Would dbc.execute("EXEC list_lock_set ?, ?", ['bbc', 'pyodbc']) work? — Ross Bush
– Ross Bush, Commented Nov 4, 2016 at 15:40

MATEITA LUCIAN · Accepted Answer · 2020-04-30 15:00:27Z

15

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

answered Apr 30, 2020 at 15:00

MATEITA LUCIAN

1511 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

johanrex Over a year ago

engine.begin/( is the key here. It starts a new transaction and commits it when the with block ends. I had problems with an sp that did delete/insert that never got persisten due to the engine not committing. So it has to be explicit like this.

JustDave Over a year ago

This comes up every single time I have to work on calling procs after not doing so for a while from sql alchemy. The huge mistake that leads me chasing what the heck is going on is I forget to SET NOCOUNT ON within the stored procedure. Not setting will cause the proc to return early doing nothing. Hope that helps anyone new or otherwise. Also calling "exec some_usp" within execute on the connection object is redundant but probably force of habit.

CyberUser Over a year ago

engine.begin() was the only solution that worked for me. Thanks!

Krzysztof Krysztofczyk Over a year ago

It is good to add SET NOCOUNT ON, otherwise results can be a little bit unexpected. That can be added to the stored procedure body. It is needed when there are some manipulations on tables (like temporary tables), inserts, etc.

Batman · Accepted Answer · 2016-11-04 15:53:06Z

4

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.

answered Nov 4, 2016 at 15:53

Batman

9,0177 gold badges48 silver badges87 bronze badges

6 Comments

Muposat Over a year ago

Thanks, engine.raw_connection().cursor() gets the underlying pyodbc cursor, on which I can do things directly.

Roger Thomas Over a year ago

Hi @Batman, did u ever figure out what was going on with the asynchronous behaviour you were experiencing? I'm getting something similiar to this and can't track down the root cause.

Batman Over a year ago

Not satisfactorily. It was definitely an issue with the procedure not having finished, but other that putting in a time.sleep I never found a solution.

James Anderson Over a year ago

@RogerThomas the fix for me was to put SET NOCOUNT ON at the top of every piece of SQL I execute via SQLAlchemy. As soon as MSSQL returns any kind of result it considers the query complete. Messages showing rows effected are included in this. NOCOUNT ON stops MSSQL from returning these messages.

DonkeyKong Over a year ago

@JamesAnderson Oh man.... I forgot about this. Thank you for the reminder!!!

|

mrduncle1 · Accepted Answer · 2021-02-02 15:09:42Z

Following on from @MATEITA LUCIAN:

Add input parameters to the stored procedure

from sqlalchemy import create_engine
engine = create_engine('mssql+pyodbc://usr:passw@srv/db?driver=SQLServer', echo=True)
with engine.begin() as conn:
    conn.execute('EXEC dbo.your_proc @textinput_param = "sometext", @intinput_param = 4')

For those that aren't sure about how or what to refer to with the '....//usr:passw@srv/db?driver=SQL server', echo=True... part of creating the engine I would suggest reading this and choosing Option 1 - Provide a DSN.

Retrieve output from the stored procedure

from sqlalchemy import create_engine
engine = create_engine('mssql+pyodbc://usr:passw@srv/db?driver=SQLServer', echo=True)
with engine.begin() as conn:
    result = conn.execute('EXEC dbo.your_proc @textinput_param = "sometext", @intinput_param = 4')
    for row in result:
        print('text_output: %s \t int_output: %d' % (row['text_field'], row['int_field']))

Kaustub · Accepted Answer · 2020-11-10 11:46:36Z

0

Yes, Even I was facing the same issue. SQLAlchemy was executing the procedure but no actions were happening.

So I tweaked it by adding connection.execute("exec <Procedure_name>")

answered Nov 10, 2020 at 11:46

Kaustub

212 bronze badges

Collectives™ on Stack Overflow

Calling MSSQL stored procedure from SqlAlchemy

4 Answers 4

4 Comments

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related