TypeError: Connection.execute() got an unexpected keyword argument, when executing stored procedure

Question

I'm trying to insert data into MSSQL database table by executing a stored procedure using python script.

Here is my stored procedure -

  SET ANSI_NULLS ON GO
  SET QUOTED_IDENTIFIER ON GO
  CREATE PROCEDURE [dbo].[Sp_Test] 
( 
   @ProcessId INT
  ,@CreatedByUserName VARCHAR(12)
  ,@CreatedByMachineName VARCHAR(12)
  --,@CreatedTime DATETIME
  ,@NumberOfDataRows INT  
  ,@DataSource VARCHAR(300)
  --,@RefId VARCHAR(8)
  ,@Postpone DATETIME2
  ,@Deadline DATETIME2
  ,@NewBatchId INT OUT 
) 
  AS  
  BEGIN    
  INSERT INTO Test(ProcessId, CreatedByUserName,
  CreatedByMachineName, CreatedTime, Postpone, Deadline,
  NumberOfDataRows, DataSource, RefId)   
  VALUES(
   @ProcessId,
   @CreatedByUserName, 
   @CreatedByMachineName, 
   GETDATE(), 
   NULL, 
   NULL,
   @NumberOfDataRows, 
   @DataSource, 
   CONVERT(VARCHAR(9),
   CRYPT_GEN_RANDOM(4), 2)
)
  SET @NewBatchId = SCOPE_IDENTITY();
  RETURN 0; 
END 
GO

Here is my python code snippet.

with engine.connect() as conn:
                conn.execute(text("CALL Sp_Test(:ProcessId, :CreatedByUserName, :CreatedByMachineName, :NumberOfDataRows, :DataSource)"), ProcessId=process_code, CreatedByUserName=username,CreatedByMachineName=hostname,NumberOfDataRows=row_count,DataSource='Shared')

But when i try to run my script, it gives following error.

TypeError: Connection.execute() got an unexpected keyword argument 'ProcessId'

I tried to pass the values as a dictionary as well. But the error is same.

params = {'ProcessId': process_code,'CreatedByUserName': username,'CreatedByMachineName': hostname,'NumberOfDataRows': row_count,'DataSource': 'Shared'} 
with engine.connect() as conn: 
conn.execute(text("CALL Sp_Test(:ProcessId, :CreatedByUserName, :CreatedByMachineName, :NumberOfDataRows, :DataSource)"),**params)

Appretiate if anyone could help me to figure this out?

Answer 1

As mentioned in a comment to the question, the pyodbc documentation for calling stored procedures that return OUTPUT variables and/or RETURN values is here. However, in your case you do not need to resort to a raw DBAPI cursor because your SP does not return result set(s) so you don't need to call pyodbc's .nextset() method. You can use SQLAlchemy directly, as in the following simplified example.

Given

CREATE TABLE [dbo].[Test](
    [BatchId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ProcessId] [int] NOT NULL
)

CREATE PROCEDURE [dbo].[my_test_sp]
@ProcessId int, @NewBatchId int OUT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Test (ProcessId) VALUES (@ProcessId);
    SET @NewBatchId = SCOPE_IDENTITY();
    RETURN 0;
END

we can do

from sqlalchemy import create_engine, text

engine = create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql_199")

with engine.begin() as conn:
    sql = """\
        SET NOCOUNT ON;
        DECLARE @new_id int, @rtn int;
        EXEC @rtn = dbo.my_test_sp @ProcessId = :process_id, @NewBatchId = @new_id OUTPUT;
        SELECT @new_id AS new_batch_id, @rtn AS return_value;
        """
    result = conn.execute(text(sql), dict(process_id=123)).mappings().one()
    print(result)  # {'new_batch_id': 2, 'return_value': 0}