在開發專案過程中，更多的是通過Python訪問SQL Server資料庫介面，進行資料探勘的操作；而SQL Server2016版本之後，嵌入了強大的R、Python、Machine Learning等功能，尤其是Python程式碼置於儲存過程中，可以實現一些資料分析功能。

本系列文章主要講解SQL Server 2017實現Python資料分析的文章，同時對比兩者的優劣。前兩篇文章主要講解SQL Server開發Python環境的安裝過程，T-SQL實現表的查詢及簡單的資料分析實驗。這篇文章通過儲存過程實現Python鳶尾花資料分析，將訓練和預測分離進行實驗。本文是基礎性文章，該方面知識較少，自己也仍在不斷學習中，希望對你有所幫助。

前文：

本文主要參考 heidi steen、ilprod 大神的文章，強烈推薦大家學習。地址如下：
建立、 定型和 SQL Server 中使用儲存過程中使用 Python 模型
鳶尾花演示資料的 SQL Server 中的 Python 和 R 教程

PS：2019年1~2月作者參加了CSDN2018年部落格評選，希望您能投出寶貴的一票。我是59號，Eastmount，楊秀璋。投票地址：https://bss.csdn.net/m/topic/blog_star2018/index

五年來寫了314篇部落格，12個專欄，是真的熱愛分享，熱愛CSDN這個平臺，也想幫助更多的人，專欄包括Python、資料探勘、網路爬蟲、影象處理、C#、Android等。現在也當了兩年老師，更是覺得有義務教好每一個學生，讓貴州學子好好寫點程式碼，學點技術，"師者，傳到授業解惑也"，提前祝大家新年快樂。2019我們攜手共進，為愛而生。

---

一. T-SQL建立儲存過程

Python指令碼程式碼嵌入到儲存過程中將有效地提升資料分析的效率，通常包括兩個儲存過程，一個用於資料的訓練，另一個用於資料的預測。

1.建立資料庫及表
建立資料庫名叫鳶尾花dbiris，並使用該資料庫。

--建立資料庫
CREATE DATABASE dbiris
GO
USE dbiris
GO

建立一張表為iris_data用於儲存資料，該資料將通過sklearn包匯入。其中DROP…IF語句，通常用來避免建立表的時候，已經存在重複的表，故先刪除再進行建立。

DROP TABLE IF EXISTS iris_data;
GO
CREATE TABLE iris_data (
  id INT NOT NULL IDENTITY PRIMARY KEY
  , "Sepal.Length" FLOAT NOT NULL, "Sepal.Width" FLOAT NOT NULL
  , "Petal.Length" FLOAT NOT NULL, "Petal.Width" FLOAT NOT NULL
  , "Species" VARCHAR(100) NOT NULL, "SpeciesId" INT NOT NULL
);

建立第二張表用於儲存訓練的模型。若要儲存在 SQL Server 中的 Python （或 R） 模型，它們必須序列化和儲存的列中的型別varbinary （max）。

--建立表 儲存訓練模型
DROP TABLE IF EXISTS iris_models;
GO
CREATE TABLE iris_models (
  model_name VARCHAR(50) NOT NULL DEFAULT('default model') PRIMARY KEY,
  model VARBINARY(MAX) NOT NULL
);
GO

對應建立如下圖所示：

除了模型內容中，通常情況下，你將還新增其他有用的後設資料，例如模型的名稱、 日期進行訓練，源演算法和引數，源資料列等。 現在我們將簡單地說，並使用只是模型名稱。

2.SQL Server執行Python指令碼過程
首先，如果只想簡單的通過呼叫 sp_execute_external_script 獲取sklearn庫中的鳶尾花資料，程式碼如下：

--簡單指令碼顯示鳶尾花資料
EXEC sp_execute_external_script @language = N'Python', 
@script = N'
from sklearn import datasets
iris = datasets.load_iris()
iris_data = pandas.DataFrame(iris.data)
iris_data["Species"] = pandas.Categorical.from_codes(iris.target, iris.target_names)
iris_data["SpeciesId"] = iris.target
',
@input_data_1 = N'', 
@output_data_1_name = N'iris_data'
WITH RESULT SETS (("Sepal.Length" float not null, "Sepal.Width" float not null,
    "Petal.Length" float not null, "Petal.Width" float not null, 
	"Species" varchar(100) not null, "SpeciesId" int not null));

對應輸出的結果如下所示：

Python執行的程式碼如下所示：

3.建立儲存過程獲取相關的資料
建立儲存過程如下所示：

--建立儲存過程 get_iris_dataset
CREATE PROCEDURE get_iris_dataset
AS
BEGIN
EXEC sp_execute_external_script 
@language = N'Python', 
@script = N'
from sklearn import datasets
iris = datasets.load_iris()
iris_data = pandas.DataFrame(iris.data)
iris_data["Species"] = pandas.Categorical.from_codes(iris.target, iris.target_names)
iris_data["SpeciesId"] = iris.target
', 
@input_data_1 = N'', 
@output_data_1_name = N'iris_data'
WITH RESULT SETS (("Sepal.Length" float not null, "Sepal.Width" float not null, "Petal.Length" float not null, "Petal.Width" float not null, "Species" varchar(100) not null, "SpeciesId" int not null));
END;
GO

其中，輸出值為Python指令碼中定義的iris_data變數，將其值用 WITH RESULT SETS 賦值至對應的欄位中。

4.執行儲存過程插入資料
執行儲存過程並向指定的表 iris_data 中寫入資料。

--執行儲存過程插入資料至表iris_data中
INSERT INTO iris_data ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species", "SpeciesId")
EXEC dbo.get_iris_dataset;

查詢及顯示的結果如下所示：

---

二. T-SQL儲存過程實現鳶尾花訓練及預測

上面的儲存過程主要是將資料插入至表格中，接下來的兩個儲存過程將實現資料的訓練及預測。

1.建立決策樹訓練模型儲存過程
程式碼需要呼叫pickle庫實現序列化模型，將從表iris_data中將0-4列資料進行訓練。SQL語句對應獲取五個值，分別為：花萼長度、花萼寬度、花瓣長度、花瓣寬度（訓練資料），花所屬分類（訓練類標）。
select “Sepal.Length”, “Sepal.Width”, “Petal.Length”, “Petal.Width”, “SpeciesId” from iris_data;
參考：[Python資料探勘課程] 四.決策樹DTC資料分析及鳶尾資料集分析

完整程式碼如下：

--建立鳶尾花訓練的儲存過程
CREATE PROCEDURE generate_iris_model (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
EXEC sp_execute_external_script @language = N'Python',
@script = N'
import pickle
from sklearn.tree import DecisionTreeClassifier
DTC = DecisionTreeClassifier()
trained_model = pickle.dumps(DTC.fit(iris_data[[0,1,2,3]], iris_data[[4]]))
'
, @input_data_1 = N'select "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "SpeciesId" from iris_data'
, @input_data_1_name = N'iris_data'
, @params = N'@trained_model varbinary(max) OUTPUT'
, @trained_model = @trained_model OUTPUT;
END;
GO

輸出結果如下所示：

2.執行儲存過程將模型插入資料庫iris_models表中

（參考heidi steen、ilprod 大神的文章）

在此步驟中，執行該儲存過程以執行嵌入的程式碼，建立作為輸出的序列化訓練模型。 序列化為位元組流和儲存在資料庫表中的 varbinary （max） 列中以供重複使用 SQL Server 中的儲存模型。 一旦建立、 訓練、 序列化，並儲存到資料庫模型，它可以呼叫其他過程或通過預測 T-SQL評分工作負荷中的函式。

--執行儲存過程建立模型
DECLARE @model varbinary(max);
DECLARE @new_model_name varchar(50)
SET @new_model_name = 'Decision Tree Classifier'
SELECT @new_model_name 
EXEC generate_iris_model @model OUTPUT;
DELETE iris_models WHERE model_name = @new_model_name;
INSERT INTO iris_models (model_name, model) values(@new_model_name, @model);
GO

此指令碼先刪除現有的同名 (“Decision Tree Classifier”) 模型，以容納建立的新模型。模型儲存在名為的表iris_models中，執行結果如下所示：

使用 select * from iris_models 可以看到模型的名稱及對應的模型。

3.建立預測的儲存過程

執行以下程式碼以建立儲存的過程執行評分。 在執行時，此過程將載入二進位制模型，使用列[1,2,3,4]作為輸入，列[0,5,6]作為輸出。

--建立決策樹預測的儲存過程
CREATE PROCEDURE predict_species (@model varchar(100))
AS
BEGIN
DECLARE @nb_model varbinary(max) = (SELECT model FROM iris_models WHERE model_name = @model);
EXEC sp_execute_external_script 
@language = N'Python', 
@script = N'
import pickle
irismodel = pickle.loads(nb_model)
species_pred = irismodel.predict(iris_data[[1,2,3,4]])
iris_data["PredictedSpecies"] = species_pred
OutputDataSet = iris_data[[0,5,6]] 
print(OutputDataSet)
'
, @input_data_1 = N'select id, "Sepal.Length", "Sepal.Width", 
                  "Petal.Length", "Petal.Width", "SpeciesId" from iris_data'
, @input_data_1_name = N'iris_data'
, @params = N'@nb_model varbinary(max)'
, @nb_model = @nb_model
WITH RESULT SETS ( ("id" int, "SpeciesId" int, "SpeciesId.Predicted" int));
END;
GO

程式碼中，@input_data_1 作為輸入資料，為select查詢；@input_data_1_name為對應資料的名稱，可以供Python指令碼呼叫；引數 nb_model 為輸入的模型，對應的為儲存過程輸入的變數。

DECLARE @nb_model varbinary(max) = (SELECT model FROM iris_models WHERE model_name = @model);

輸出結果包括 id（資料編號）、SpeciesId（真實類標）、SpeciesId.Predicted（預測類標）。

--執行儲存過程
EXEC predict_species 'Decision Tree Classifier';
GO

執行結果如下圖所示：

執行儲存的過程時，它將返回 Python data.frame。 T-SQL 的這行指定返回的結果的架構： WITH RESULT SETS ( (“id” int, “SpeciesId” int, “SpeciesId.Predicted” int));。 可以將結果插入到一個新表，或返回到應用程式。

寫到這裡，“SQL Server儲存過程實現Python鳶尾花決策樹訓練及預測” 已經講解完畢，它的優勢是將訓練和預測分開，並嵌入儲存過程中，同時可以撰寫多個訓練模型，進行分別呼叫。

希望文章對大家有所幫助，如果有錯誤或不足之處，還請原諒。最近經歷的事情太多，有喜有悲，關閉了朋友圈，希望通過不斷學習和寫文章來忘記煩勞，將憂鬱轉換為動力，每週學習都記錄下來。
（By：Eastmount 2018-11-14 下午5點 https://blog.csdn.net/Eastmount/）