python-3.xsql-serverpandasstored-proceduressqlalchemy

How to call Microsoft SQL Server Stored Procedure from within Python using SQL Alchemy


I would like to execute a Stored Procedure residing on Microsoft SQL Server using Python and SQL Alchemy.

So far I am able to connect to Microsoft SQL Server, run simple select query, assign it to a Panda Data Frame and save it to an csv file using the following code:

from sqlalchemy import create_engine
import pandas as pd
Server='Jaar'
Database='M'
Driver='ODBC Driver 17 for SQL Server'
Database_Con = f'mssql://@{Server}/{Database}?driver={Driver}'
engine=create_engine(Database_Con)
con = engine.connect()
df=pd.read_sql_query("SELECT * FROM [M].[Dim].[Accounts_t]",con)

However, I've tried unsuccessfully to do the same with a SP which takes two parameters

Furthermore, I have not been able to get this example to work: https://sql-query.dev/articles/1767

And haven't found much in the SQL Alchemy documentation that can help me.

Thanks

The SP that I want to execute is below:

USE [M]
GO

DECLARE @return_value int

EXEC    @return_value = [Reporting].[Finance_Payments_Mvmts_sp]
        @PeriodStart = 202212,
        @PeriodEnd = 202310

SELECT  'Return Value' = @return_value

GO

Solution

  • I couldn't work with SQL Alchemy and SQL Server so I finally found another Python module for accessing SQL Server called pymssql:

    import pymssql
    import pandas as pd
    conn = pymssql.connect(server = 'Jaar', database = 'M')
    cursor = conn.cursor(as_dict=True)
    PeriodStart = 202212
    PeriodEnd = 202310
    cursor.callproc('[Reporting].[Finance_Payments_Mvmts_sp]', (PeriodStart, PeriodEnd))
    (202212, 202310)
    rows = []
    for row in cursor:
        rows.append(row)
    
    df = pd.DataFrame(rows)
    print(df)
    
    df.to_csv("TestOutput.csv")
    

    Does what I need it to do which is to run a SQL Server Stored Procedure with input parameters

    https://pypi.org/project/pymssql/