Execute SQL Server stored procedure from python on database directly-CodePudding

I want to run an existing SQL Server stored procedure on a database using Python. To clarify, I do not want to get the output in Python. I want the output to be saved directly in the database itself.

I connected to the database using pymssql using the following code:

storedProc = "Exec TEST;"
cursor.execute( storedProc )

This is the code of my procedure:

CREATE PROCEDURE dbo.TEST
as
set nocount on

IF OBJECT_ID('tempdb..#A', 'U')IS not NULL DROP TABLE #A
CREATE TABLE #A
([id]           [INT] identity(1,1), 
 [CustomerName] [VARCHAR](30) NULL, 
 [OrderCount]   [INT] NULL
)
ON [PRIMARY]

Insert into #A ([CustomerName],[OrderCount]) values('Rajjjj',22),('Kusum',3),('Akshita',4),('John',5),('Dan',6)
IF OBJECT_ID('dbo.TEST_A', 'U')IS NULL
CREATE TABLE dbo.TEST_A
(
 [CustomerName] [VARCHAR](30) NULL, 
 [OrderCount]   [INT] NULL
);
truncate table  dbo.TEST_A
insert into dbo.TEST_A

This code runs the procedure, but the output is returned to Python, which is not what I want.

CodePudding user response：

The query sent to the database must do the insert, then. Considering a table named [proc_output] with the same structure as the return of the procedure, the code would be:

storedProc = """
insert into proc_output
    Exec Procedure_Name;
"""
cursor.execute( storedProc )

CodePudding user response：

As I described in the post, I lacked conn.commit() line in my code, as described in the following post: link