使用sqlalchemy在python的SQL存储过程中输入几个参数

Question

我可以使用以下脚本从jupyter笔记本成功连接到SQL Server：

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)

我可以通过以下功能从jupyter notebook成功执行SQL存储过程，无需参数：

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

对于具有多个参数的存储过程（我的例子中有两个参数），我如何转换前面的函数

Answer 1

我的问题的解决方案，仅适用于具有0或2个参数的存储过程（如果需要其他数量的参数，只需编辑第10行）：

def execute_stored_procedure(engine, procedure_name,params_dict=None):
    res = {}
    connection = engine.raw_connection()
    try:
        cursor = connection.cursor()
        if params_dict is None:
            cursor.execute("EXEC "+procedure_name)
        else:
            req  = "EXEC "+procedure_name
            req += ",".join([" @"+str(k)+"='"+str(v)+"'" for k,v in params_dict.items()])               
            cursor.execute(req)
        cursor.close()
        connection.commit()
        res['status'] = 'OK'
    except Exception as e:
        res['status'] = 'ERROR'
        res['error'] = e
    finally:
        connection.close() 
    return res