Home > database >  Execute Stored Procedure with Parameters in python
Execute Stored Procedure with Parameters in python

Time:04-22

I am trying to execute a stored procedure but it is getting complicated, my code in python is:

import pyodbc as pyodbc

import pandas as pd

server = 'server'

bd = 'bd'

usuario = 'usuario'

contrasena = 'contrasena'

try:
      conexion = pyodbc.connect('DRIVER={ODBC DRIVER 17 for SQL Server};SERVER=' server ';DATABASE=' bd ';UID=' usuario ';PWD=' contrasena, autocommit=True)
      cursor=conexion.cursor()

      print('Sucess')

except:

      print('error trying to connect')


sql = "exec Usp_UltimosRQGeneradosxUsuario @Usuario=?"

params = ('Felix')

cursor.execute(sql, params)

cursor.commit()

I don't get any errors, I would like to know what I am missing in order to see the data.

My stored procedure is:

ALTER PROCEDURE [dbo].[Usp_UltimosRQGeneradosxUsuario]  
@Usuario varchar(50)


as
BEGIN

SELECT ROW_NUMBER() OVER(PARTITION BY RequestorID ORDER BY RequestDate Desc) As Row#,
    Company, RequestorID Usuario, ReqNum, StatusType,RequestDate Fecha
INTO #Temporal
From ReqHead with (nolock)
WHERE (StatusType = 'O' OR StatusType = 'P')
    AND RequestDate <= GETDATE()
    AND RequestorID = @Usuario
    AND Company = 'TEC01'
    Order By RequestorID, RequestDate Desc

SELECT MAX (Row#) NFila, Company, Usuario, ReqNum, StatusType, Fecha FROM #Temporal 
WHERE Row# <= 5
GROUP BY Row#, Company, Usuario, ReqNum, StatusType, Fecha
ORDER BY Usuario, NFila Asc

Thanks

CodePudding user response:

sql = """SET NOCOUNT ON; exec Usp_UltimosRQGeneradosxUsuario @Usuario=?"""
params = ('Felix',)
cursor.execute(sql, params)
cursor.fetchall()
cursor.commit()
conexion.close()
print("connection terminated")

I changed this last part with the changes that were indicated in the comments, but it still does not bring me the data, but it does not generate an error

CodePudding user response:

I got to correct the error in this way:

sql = """SET NOCOUNT ON; exec Usp_UltimosRQGeneradosxUsuario @Usuario=?"""
params = ('Felix',)
cursor.execute(sql, params)
result=cursor.fetchall()
cursor.commit()
conexion.close()
print(result)
print("Conexion Finalizada")
  • Related