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

/*******************************************************************************************************************
-- Title : [MSPy] sp_execute_external_script를 활용한 Python Script 실행 예제
-- Reference : www.sqlshack.com
-- Key word : sp_execute_external_script csv pandas dataframe .csv
*******************************************************************************************************************/

■ Scripts

-- https://www.sqlshack.com/how-to-use-python-in-sql-server-2017-to-obtain-advanced-data-analytics/
 
/**************************************************
-- 기본 사용 개요
**************************************************/ 
 
-- Print the input value
exec sp_execute_external_script 
@language =N'Python',
@script=N'
OutputDataSet = InputDataSet
print("Input data is {0}".format(InputDataSet))', 
@input_data_1 = N'SELECT 1 as col';
 
-- Find mean of a given list
execute sp_execute_external_script 
@language = N'Python',
@script = N'
l = [15, 18, 2, 36, 12, 78, 5, 6, 9]
print(sum(l) / float(len(l)))';
 
-- Format operator
exec sp_execute_external_script
@language = N'Python',
@script= N'
print("""jan:{2} feb:{0} mar:{2} Apr:{1} May:{2} Jun:{1} Jul:{2} Aug:{2} Sep:{1} Oct:{2} Nov:{1} Dec:{2}"""
     .format(InputDataSet.AA, InputDataSet.BB, InputDataSet.CC))',
@input_data_1 = N'select 28 as AA, 30 as BB, 31 as CC';
 
-- Using loops and branches
execute sp_execute_external_script 
@language = N'Python',
@script = N'
for i in range(5):
    if i<3 :
        print("i is now:", i*2)';
 
 
/**************************************************
-- Input/Output 처리
**************************************************/
 
-- Create table
USE Tempdb;
go
 
DROP TABLE IF EXISTS dbo.EMP;
go
 
CREATE TABLE [dbo].[EMP]
( [empno] [int] NOT NULL
, [ename] [varchar](10) NULL
, [job] [varchar](9) NULL
, [mgr] [int] NULL
, [hiredate] [datetime] NULL
, [sal] float NULL
, [comm] [numeric](7, 2) NULL
, [dept] [int] NULL
, PRIMARY KEY CLUSTERED ([empno])
);
 
-- Insert data
INSERT INTO EMP VALUES
  (1,'Prashanth','ADMIN',6,'12-17-1990',18000,NULL,4)
, (2,'Jayaram','MANAGER',9,'02-02-1998',52000,300,3)
, (3,'thanVitha','SALES I',2,'01-02-1996',25000,500,3);
 
-- Select data
SELECT EMPNO, ENAME, SAL from EMP;
 
-- Output w/o column name
EXECUTE sp_execute_external_script 
@language = N'Python',
@script=N'
OutputDataSet = InputDataSet',
@input_data_1 = N'SELECT empno, ename, sal from EMP';
 
-- Output w/ column name
EXECUTE sp_execute_external_script 
@language = N'Python',
@script=N'
OutputDataSet = InputDataSet',
@input_data_1 = N'SELECT [empno],[ename],sal,0 as Bonus from EMP'
WITH RESULT SETS ((EMPN0 int, ENAME1 varchar(10), SAL2 float, Bonus3 float))
 
-- Output w/ calculation
EXECUTE sp_execute_external_script 
@language = N'Python',
@script=N'
OutputDataSet = InputDataSet
for i in OutputDataSet["sal"]:
    print(i)
    OutputDataSet["Bonus"]=OutputDataSet["sal"]*0.05',
@input_data_1 = N'SELECT [empno],[ename],sal,0 as Bonus from EMP'
WITH RESULT SETS ((EMPNO2 int, ENAME varchar(10), SAL float, Bonus float));
 
 
/**************************************************
-- Textfile 및 Pandas 처리
**************************************************/
 
-- Read Textfile w/ .tsv
EXECUTE sp_execute_external_script  
@language = N'Python',
@script = N'
import csv
with open(''D:\\PyProject\\20171225_MSPython\\python.txt'') as f:
    reader = csv.DictReader(f, delimiter = ''\t'')
    for row in reader:
        print (row)';
 
-- Using Pandas w/ .csv
EXECUTE sp_execute_external_script  
@language = N'Python',  
@script = N'
import pandas as ps
df = ps.read_csv("D:\\PyProject\\20171225_MSPython\\InputServer_1.csv")
print (df)
#Finding the number of rows
print (df.shape)
#Looking at the first row of the data
print (df.head(1))
#Find the average of each statistic
print (df.mean())';
 
 
/**************************************************
-- 통계 칼럼을 통한 평균 찾기
**************************************************/
 
-- Create table and insert data
DROP TABLE IF EXISTS MyData;
 
CREATE TABLE MyData([Col1] INT NOT NULL) ON [PRIMARY];
INSERT INTO MyData VALUES(1);
INSERT INTO MyData VALUES(10);
INSERT INTO MyData VALUES(100);
GO
 
-- Select all rows of MyData table
SELECT * FROM MyData;
 
-- Find mean of Col1 
EXECUTE sp_execute_external_script  
@language = N'Python',
@script = N'
import pandas as ps
print("*******************************")
OutputDataSet = InputDataSet
print (OutputDataSet)
print (OutputDataSet["col1"].mean())
print("*******************************")
vMeans = OutputDataSet["col1"].mean()
OutputDataSet["col2"] = vMeans',
@input_data_1 = N'SELECT col1, 0 as col2 from Mydata'
WITH RESULT SETS ((col1 int, col2_mean float));

■ Files

InputServer_1.csv

python.txt