CODEWITHSUNDEEP
pythonsql-serverstored-proceduressqlalchemy
Muposat
Muposat

Reputation: 1506

Calling MSSQL stored procedure from SqlAlchemy

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.

Upvotes: 20

Views: 25756

Answers (4)

mrduncle1
mrduncle1

Reputation: 659

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']))

Upvotes: 1

Kaustub
Kaustub

Reputation: 21

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

Upvotes: 0

MATEITA LUCIAN
MATEITA LUCIAN

Reputation: 151

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

Upvotes: 15

Batman
Batman

Reputation: 8927

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.

Upvotes: 4

Related Questions