sp_execute_external_script Python script returning a dataset output

Question

I am working with SQL Server Machine Learning. I am trying to get output from the script.

DECLARE @answers NVARCHAR(MAX)
EXEC sp_execute_external_script @language=N'Python',
 @script = N'
import pandas as pd
ser = pd.Series([6, 7, 6 * 7])
df = pd.DataFrame(ser)
OutputDataSet = df',
 @output_data_1 = @answers,
 @params = N'@answers nvarchar(MAX)'
WITH RESULT SETS((Answer int))

I am getting the following error.

Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.

I think I have seen output from R scripts, but I am not able to get it from Python.

AlwaysLearning · Accepted Answer · 2020-04-30 08:24:05Z

1

@output_data_1 is not a named parameter to sp_execute_external_script.

Syntax:

sp_execute_external_script   
    @language = N'language',   
    @script = N'script'  
    [ , @input_data_1 = N'input_data_1' ]   
    [ , @input_data_1_name = N'input_data_1_name' ]  
    [ , @input_data_1_order_by_columns = N'input_data_1_order_by_columns' ]    
    [ , @input_data_1_partition_by_columns = N'input_data_1_partition_by_columns' ]  
    [ , @output_data_1_name = N'output_data_1_name' ]  
    [ , @parallel = 0 | 1 ]  
    [ , @params = N'@parameter_name data_type [ OUT | OUTPUT ] [ ,...n ]' ] 
    [ , @parameter1 = 'value1' [ OUT | OUTPUT ] [ ,...n ] ]

answered Apr 30, 2020 at 8:24

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

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

Comments

lptr · Accepted Answer · 2020-04-30 09:23:15Z

1

declare @num1 int = 10, @num2 int=5, @thesum int;

EXEC sp_execute_external_script @language=N'Python',
 @script = N'
import pandas as pd
ser = pd.Series([6, 7, 6 * 7])
df = pd.DataFrame(ser)
OutputDataSet = df

a=x123
b=y345
sumresult=a+b
',
@output_data_1_name = N'OutputDataSet',
@params = N'@x123 int, @y345 int, @sumresult int output',
@x123= @num1, @y345=@num2, @sumresult = @thesum output
WITH RESULT SETS((Answer int));

select @thesum;

answered Apr 30, 2020 at 9:23

lptr

6,8582 gold badges10 silver badges16 bronze badges

1 Comment

Phil Huhn Over a year ago

Thanks for the response, but I am looking for the series results. Ultimately, I am looking for a dataframe.

Phil Huhn · Accepted Answer · 2020-04-30 18:06:48Z

0

They both helped! I had to convert to JSON. Also look at the various options to the orient param.

DECLARE @answers NVARCHAR(MAX)
EXEC sp_execute_external_script @language=N'Python',
 @script = N'
import pandas as pd
ser = pd.Series([6, 7, 6 * 7])
df = pd.DataFrame(ser).to_json(orient=''values'')',
 @params = N'@df NVARCHAR(MAX) OUTPUT',
 @df = @answers OUTPUT
--
PRINT @answers

answered Apr 30, 2020 at 18:06

Phil Huhn

4,2514 gold badges25 silver badges47 bronze badges

Collectives™ on Stack Overflow

sp_execute_external_script Python script returning a dataset output

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related