Executing stored procedures in SQL Server using Python client

Question

I learned from a helpful post on StackOverflow about how to call stored procedures on SQL Server in python (pyodbc). After modifying my code to what is below, I am able to connect and run execute() from the db_engine that I created.

import pyodbc
import sqlalchemy as sal
from sqlalchemy import create_engine
import pandas as pd
import urllib

params = urllib.parse.quote_plus(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    f'SERVER=myserver.com;'
    f'DATABASE=mydb;'
    f'UID=foo;'
    f'PWD=bar')

cobnnection_string = f'mssql+pyodbc:///?odbc_connect={params}'
db_engine = create_engine(connection_string)


db_engine.execute("EXEC [dbo].[appDoThis] 'MYDB';")
<sqlalchemy.engine.result.ResultProxy at 0x1121f55e0>

db_engine.execute("EXEC [dbo].[appDoThat];")
<sqlalchemy.engine.result.ResultProxy at 0x1121f5610>

However, even though no errors are returned after running the above code in Python, when I check the database, I confirm that nothing has been executed (what is more telling is that the above commands take one or two seconds to complete whereas running these stored procedures successfully on the database admin tool takes about 5 minutes).

How should I understand what is not working correctly in the above setup in order to properly debug? I literally run the exact same code through my database admin tool with no issues - the stored procedures execute as expected. What could be preventing this from happening via Python? Does the executed SQL need to be committed? Is there a way to debug using the ResultProxy that is returned? Any advice here would be appreciated.

Gord Thompson · Accepted Answer · 2021-03-10 14:15:48Z

2

Calling .execute() directly on an Engine object is an outdated usage pattern and will emit deprecation warnings starting with SQLAlchemy version 1.4. These days the preferred approach is to use a context manager (with block) that uses engine.begin():

import sqlalchemy as sa

# …

with engine.begin() as conn:  # transaction starts here
    conn.execute(sa.text("EXEC [dbo].[appDoThis] 'MYDB';"))

# On exiting the `with` block the transaction will automatically be committed
#   if no errors have occurred. If an error has occurred the transaction will
#   automatically be rolled back.

Notes:

When passing an SQL command string it should be wrapped in a SQLAlchemy text() object.
SQL Server stored procedures (and anonymous code blocks) should begin with SET NOCOUNT ON; in the overwhelming majority of cases. Failure to do so can result in legitimate results or errors getting "stuck behind" any row counts that may have been emitted by DML statements like INSERT, UPDATE, or DELETE.

answered Mar 10, 2021 at 14:15

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

1 Comment

van Over a year ago

In addition to the answer, please check the SQL Server logs so that you can monitor what "SQL Server" sees is happening.

Collectives™ on Stack Overflow

Executing stored procedures in SQL Server using Python client

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related