Python inside MS Sql

Question

How can i get set index value as a column inside "sp_execute_external_script" with result set output?

here is my code, i have done all calculation

EXEC sp_execute_external_script
@language = N'Python',
@script = @pscript,
@input_data_1 = @sqlscript
WITH RESULT SETS(
 (
     Col1 FLOAT,
     Col2 FLOAT, 
     Col3 FLOAT 
    ));

So you need to have the index of the Pandas Dataframe as a column in the output result set? I'm not sure how to do this but as a work-around could you copy the index to a new column at the end of your Python script and access it that way? — Parrybird, Mar 19 '18 at 10:46

score 0 · Answer 1 · answered Apr 26 '18 at 17:16

Take a peek at this relevant question. You can make your index a column in the pandas dataframe for output.

Right before your output within your python script @pscript you can add: df.reset_index(inplace=True).

Then in your WITH RESULT SETS statement you can add the index column first in the list:

WITH RESULT SETS(
(
 MyIndex int,
 Col1 FLOAT,
 Col2 FLOAT, 
 Col3 FLOAT 
));

Here is a full simplified example since I can't see your exact code:

EXEC sp_execute_external_script  @language =N'Python',
@script=N'
import pandas as pd
s = pd.Series([5,6,7,8,9,10])
df = pd.DataFrame(s)
df.reset_index(inplace=True)
OutputDataSet = df
'
WITH RESULT SETS (( MyIndex int, test int ));

Python inside MS Sql

1 Answers1