Launch SQL stored procedures from python with sqlalchemy?

Question

I can successfully connect to SQL Server Management Studio from my jupyter notebook with this script:

from sqlalchemy import create_engine
import pyodbc 
import csv
import time
import urllib

params = urllib.parse.quote_plus('''DRIVER={SQL Server Native Client 11.0};
                                    SERVER=SV;
                                    DATABASE=DB;
                                    TRUSTED_CONNECTION=YES;''')

engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

I managed to execute some SQL scripts like this:

engine.execute("delete from table_name_X")

However, I can't execute stored procedures. I tried the following scripts from what I've seen in stored procedures with sqlAlchemy. These following scripts have an output like "sqlalchemy.engine.result.ResultProxy at 0x173ed18e470", but the procedure wasn't executed in reality (nothing happened):

# test 1
engine.execute('stored_procedure_name')
# test 2
from sqlalchemy import func
from sqlalchemy.orm import sessionmaker
session = sessionmaker(bind=engine)()
session.execute(func.upper('stored_procedure_name'))

Could you please give me the correct way to execute stored procedures?

The thread you linked has a lot of information that seems useful to you. Specifically, that `func` is limited. But also other comments that you should read as well. How do you know the stored procedure "was executed but nothing really happened?" This is not possible, to be honest. Either the stored procedure did not execute on the server, or it executed. Have you tried simply doing `session.execute('your_stored_proc')`? Are you including the schema? Does your proc require parameters? — Jacob H, Dec 13 '19 at 13:39
check this information ...in the part Calling Stored Procedures - > https://docs.sqlalchemy.org/en/13/core/connections.html — GiovaniSalazar, Dec 13 '19 at 13:42
Indeed, I read and tried to adapt all the comments. I said that the stored procedure was executed but nothing happened as the python output looks like it was working whereas when I check in my sql tables, I see that the procedure didn't work — Ewdlam, Dec 13 '19 at 13:44
in your example you use delete ....if you try with a select....work? — GiovaniSalazar, Dec 13 '19 at 13:48
Doesn't work with a select. I just put this example to show that I succeeded to execute some sql script with the engine. Thanks for your help, I'am checking your link — Ewdlam, Dec 13 '19 at 13:51
in your engine try add parameter case_sensitive.... create_engine(..., case_sensitive=False) — GiovaniSalazar, Dec 13 '19 at 13:52
I tried to add case_sensitive=False in create_engine but it didn't change something :/ — Ewdlam, Dec 13 '19 at 13:56
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/204209/discussion-between-giovanisalazar-and-ewdlam). — GiovaniSalazar, Dec 13 '19 at 13:57
Just for clarity of definitions, SQL Server Management Studio (SSMS) is a *Client User Interface* for the SQL Server Database. And as such you never connect to it. You are connecting to SQL Server (which is the SQL Server Database Engine). — Dale K, Dec 13 '19 at 21:54

score 0 · Answer 1 · edited Dec 16 '19 at 12:56

0

The way you can call a stored procedure using pyodbc is :

cursor.execute("{CALL usp_StoreProcedure}")

I found a solutions in reference to this link . https://github.com/mkleehammer/pyodbc/wiki/Calling-Stored-Procedures

Here a example :

import pyodbc
import urllib
import sqlalchemy as sa


params = urllib.parse.quote_plus("DRIVER={SQL Server Native Client 11.0};"
                                 "SERVER=xxx.xxx.xxx.xxx;"
                                 "DATABASE=DB;"
                                 "UID=user;"
                                 "PWD=pass")

engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params))

connection = engine.raw_connection()
try:
    cursor = connection.cursor()
    cursor.execute("{CALL stored_procedure_name}")
    result = cursor.fetchall()
    print(result)
    connection.commit()
finally:
    connection.close()

edited Dec 16 '19 at 12:56

marc_s

732,580
175
1,330
1,459

answered Dec 13 '19 at 14:43

GiovaniSalazar

1,999
2
8
15

I have this error when I try your solution : in 3 cursor = connection.cursor() 4 cursor.execute("{CALL procedure_name}") ----> 5 result = cursor.fetchall() 6 print(result) 7 connection.commit() ProgrammingError: No results. Previous SQL was not a query. – Ewdlam Dec 13 '19 at 15:33
1

@Ewdlam check this https://stackoverflow.com/questions/41302866/error-previous-sql-was-not-a-query-in-python – GiovaniSalazar Dec 13 '19 at 15:55

score 0 · Accepted Answer · answered Jan 09 '20 at 15:38

Finally solved my problem with the following function :

def execute_stored_procedure(engine, procedure_name):
    res = {}
    connection = engine.raw_connection()
    try:
        cursor = connection.cursor()
        cursor.execute("EXEC "+procedure_name)
        cursor.close()
        connection.commit()
        res['status'] = 'OK'
    except Exception as e:
        res['status'] = 'ERROR'
        res['error'] = e
    finally:
        connection.close() 
    return res

Launch SQL stored procedures from python with sqlalchemy?

2 Answers2