How to pass a python dataframe to a stored procedure

Question

I have a dataframe that has this info.

University Subject Colour
Melb       Math    Red
Melb       English Blue
Ottawa     Arts    Green
Ottawa     Math    Yellow
Sydney     Math    Black
Sydney     Arts    Red

there is around 1,000 records in this dataframe

I do not want to insert them one by one through a loop

What I am thinking is to pass the whole Dataframe to a stored procedure with a "data-defined table type" in SQL Server

so I can do the insert in one shot, how to do that?

@pavel to_sql() writes to a table directly, I want to send it to a stored procedure. — asmgx
– asmgx, Commented Dec 31, 2020 at 3:12
Does this answer your question? Python call sql-server stored procedure with table valued parameter — SMor
– SMor, Commented Dec 31, 2020 at 12:38
@SMor this is not the same issue i am talking about. i want to pass dataframe as parameter if possible — asmgx
– asmgx, Commented Dec 31, 2020 at 22:55
can you share python for the same? I am trying since long. following code doesn't work. df = pd.DataFrame(mydic, index=list(range(1))) print("df") print(df) print("df ends") #param = {"TopicCode": "1", "dtRawDataList": df.to_sql } dynamicquery = f"EXEC InsertPythonListMultiple ({topicCode},{df})" cursor.execute(dynamicquery) — Ketan Kotak
– Ketan Kotak, Commented Apr 11, 2024 at 10:43

Jeremy Caney · Accepted Answer · 2022-09-27 20:42:38Z

1

You can use Create Type in SQL Server to define a input variable for a stored procedure as table.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql?view=sql-server-ver16

CREATE TYPE LocationTableType AS TABLE   
    ( LocationName VARCHAR(50)  
    , CostRate INT );  
GO  

CREATE PROCEDURE sp_InsertLocation 
@Location AS LocationTableType 
AS
....

edited Sep 27, 2022 at 20:42

Jeremy Caney

7,808115 gold badges58 silver badges86 bronze badges

answered Sep 23, 2022 at 20:43

Marcio Vieira

261 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pass a python dataframe to a stored procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related