[MSPy] sp_execute_external_script를 활용한 Python Script 실행 예제-2

/*******************************************************************************************************************
-- Title : [MSPy] sp_execute_external_script를 활용한 Python Script 실행 예제-2
-- Reference : www.sqlshack.com
-- Key word : sp_execute_external_script .csv .json csv json data_load bulk insert json.load
*******************************************************************************************************************/

■ Scripts

-- https://www.sqlshack.com/data-interpolation-and-transformation-using-python-in-sql-server-2017/
 
use tempdb;
go
 
-------------------------------------------------
-- Creating table
-------------------------------------------------
DROP TABLE IF EXISTS dbo.[tbl_loanPrediction]
GO
 
CREATE TABLE [dbo].[tbl_loanPrediction] (
Loan_ID varchar(50),
gender varchar(50),
Married varchar(50),
Dependents varchar(50),
Education varchar(50),
SelfEmployed varchar(50),
ApplicationIncome varchar(50),
CoapplicantIncome varchar(50),
LoanAmount float ,
Loan_Amount_Term varchar(50),
Credit_History  varchar(50),
Property_Area varchar(50)
);
 


-------------------------------------------------
-- loading csv into table
-------------------------------------------------
BULK
INSERT [tbl_LoanPrediction]
FROM 'D:\sample.csv'
WITH
(
FIRSTROW = 2,    -- field명 때문에 2행부터 적재
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
 
 
-------------------------------------------------
-- Checking data
-------------------------------------------------
SELECT *
FROM [tbl_LoanPrediction]
GO
 
 
-------------------------------------------------
-- Querying csv using pandas
-------------------------------------------------
EXEC sp_execute_external_script 
@language = N'Python',
@script = N'
import pandas as pd
import numpy as np
df = pd.read_csv("D:\\sample.csv", index_col="Loan_ID")
print(df)';
 
 
-------------------------------------------------
-- Extraction data using logical conditiion
-------------------------------------------------
exec sp_execute_external_script 
@language = N'Python',
@script = N'
import pandas as pd
import numpy as np
df = pd.read_csv("D:\\sample.csv", index_col="Loan_ID")
print(df[["Gender","Education"]])
print("-"*30)
print(df.loc[(df["Gender"]=="Female") & (df["Education"]=="Not Graduate"), ["Gender","Education"]])
';
 
 
-------------------------------------------------
-- Data loading into the Temptable using pandas
-------------------------------------------------
DROP TABLE IF EXISTS dbo.#Loan
GO
 
SELECT TOP 5 *  FROM tbl_LoanPrediction;
 
CREATE TABLE #Loan
( Loan_ID varchar(20)
, Gender varchar(10) null
, Education varchar(30)null
, MaritalStatus char(3)
);
 
INSERT INTO #Loan
exec sp_execute_external_script 
@language = N'Python',
@script = N'
import pandas as pd
import numpy as np
df = InputDataSet
out_df = df.loc[(df["gender"]=="Female") & (df["Education"]=="Not Graduate") & (df["Married"]=="No"), 
["Loan_ID","gender","Education","Married"]]',
@input_data_1 = N'SELECT * FROM [tbl_LoanPrediction]',
@output_data_1_name = N'out_df';
 
select * from #Loan;
 
 
-------------------------------------------------
-- Pivoting
-------------------------------------------------
EXEC sp_execute_external_script @language =N'Python',
@script=N'
import pandas as pd
import numpy as np
df = pd.read_csv("D:\\sample.csv", index_col="Loan_ID")
#-- Determine pivot table
Out_df = df.pivot_table(values=["LoanAmount"],index=["Gender","Married","Self_Employed"], aggfunc=np.mean)
print(Out_df)',
@output_data_1_name = N'Out_df';
 
 
-------------------------------------------------
-- Pivoting-2
-------------------------------------------------
DROP TABLE IF EXISTS dbo.#Movies
GO
 
CREATE TABLE #Movies
(MovieType VARCHAR(20), SalesYear INT, MovieSales int);
GO
 
INSERT INTO #Movies VALUES('Comedy', 2014, 11201);
INSERT INTO #Movies VALUES('History', 2014, 12939);
INSERT INTO #Movies VALUES('Comedy', 2013, 10436);
INSERT INTO #Movies VALUES('Comedy', 2013, 9346);
INSERT INTO #Movies VALUES('Comedy', 2014, 7214);
INSERT INTO #Movies VALUES('Comedy', 2014, 5800);
INSERT INTO #Movies VALUES('Comedy', 2013, 8922);
INSERT INTO #Movies VALUES('History', 2013, 7462);
 
SELECT * FROM #Movies;
 
SELECT *
FROM #Movies
  PIVOT(SUM(MovieSales) 
  FOR SalesYear IN([2013], [2014])
  ) AS PivotSales;
 
exec sp_execute_external_script 
@language = N'Python',
@script=N'
import pandas as pd
import numpy as np
in_df = InputDataSet
#Determine pivot table
out_df = in_df.pivot_table(values=["MovieSales"], index=["MovieType","SalesYear"], aggfunc=np.sum)
print(out_df)
',
@input_data_1 = N'SELECT * FROM #Movies',
@output_data_1_name = N'out_df';
 
 
-------------------------------------------------
-- Extraction data from JSON
-------------------------------------------------
EXEC sp_execute_external_script @language =N'Python',
@script=N'
import os
import json
script_dir = os.path.dirname("d:\\")
file_path = os.path.join(script_dir, "sample.json")
print(file_path)
print("Name" + "----- "+"Size" + "---- " + "%Free")
with open(file_path) as json_file:  
    data = json.load(json_file)
    for r in data["diskspace"]:
        if (float(r["freespace"]) > 5.00):
            print(r["server"] + "-- "+ r["size"] +"-- " +r["percentage"])
';

 
■ Files

sample.csv

sample.json