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.
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
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
this postshould be useful. It uses R, but the end goal remains the same.