How to output values from sp_execute_external_script into table

Question

Having the following code, how can I output the values from the external python script into a sql table that I can query after.

EXECUTE sp_execute_external_script 
@language = N'Python',
@script = N'
            import pandas as pd
            import numpy as np

            rd = np.random.randn(100)
            df = pd.DataFrame(rd).describe()
            print(df)'

Thanks in advance.

[`this post`](https://stackoverflow.com/questions/39841023/r-in-sql-server-output-data-frame-into-a-table) should be useful. It uses R, but the end goal remains the same. — Abdou, Dec 23 '17 at 17:15

score 6 · Answer 1 · answered Dec 23 '17 at 22:00

Indeed, as the R version shows, consider INSERT INTO myTable ... using results from the Python executed script in TSQL, specifying @output_data. However, first create table to be appended to, aligning columns and types accordingly.

Additionally, since you use describe(), consider renaming columns prior to output. Finally, pandas is included by default for the Python Machine Learning Services in SQL Server 2016 as mentioned in this tutorial, so no need to import (possibly same with numpy).

DROP TABLE IF EXISTS myTable;

CREATE TABLE myTable (
    [aggregate] varchar(50) NOT NULL,
    [value] float NOT NULL
    )
GO

INSERT INTO myTable    
EXECUTE sp_execute_external_script 
@language = N'Python',
@script = N'import numpy as np

            rd = np.random.randn(100)
            df = pandas.DataFrame(rd).describe().reset_index().rename(columns={"index":"aggregate", 0:"value"})',
@output_data_1_name = N'df';

GO

@Hiram, not quite sure what you mean but convert dictionary to pandas data frame. For more, please ask a new question. — Parfait, May 31 '19 at 14:46

Hiram · Answer 2 · 2019-05-31T01:36:31.700

declare @py nvarchar(max);

set @py = N'from pandas.io.json import json_normalize

rdd = {"documents": [{"id": "1", "score": 0.97},{"id": "2","score": 0.87},{"id": "3", "score": 0.78}],"errors": []}
print(type(rdd))

df = json_normalize(rdd, "documents")
print(df)
print(type(df))
'; 

drop table if exists apiresults
create table apiresults (id int, score float)

insert into apiresults
exec sp_execute_external_script 
    @language = N'Python',
    @script = @py,
    @output_data_1_name =N'df'

select * from apiresults

How to output values from sp_execute_external_script into table

2 Answers2