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

本系列文章主要講解SQL Server 2017實現Python資料分析的文章，同時對比兩者的優劣。前一篇文章主要講解SQL Server開發Python環境的安裝過程及基本的資料分析程式碼實現，本文主要講解T-SQL實現表的查詢及簡單的資料分析實驗。基礎性文章，該方面知識也較少，自己也仍在不斷學習中，希望對你有所幫助。

推薦官網學習文件：https://docs.microsoft.com/zh-cn/sql/advanced-analytics/?view=sql-server-2017

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指令碼入門知識

指令碼語言的基本語法如下，推薦官方文章 sp_execute_external_script (TRANSACT-SQL) 。

sp_execute_external_script   
    @language = N'language',   
    @script = N'script'  
    [ , @input_data_1 = N'input_data_1' ]   
    [ , @input_data_1_name = N'input_data_1_name' ]  
    [ , @output_data_1_name = N'output_data_1_name' ]  
    [ , @parallel = 0 | 1 ]  
    [ , @params = N'@parameter_name data_type [ OUT | OUTPUT ] [ ,...n ]' ] 
    [ , @parameter1 = 'value1' [ OUT | OUTPUT ] [ ,...n ] ]

引數如下：
@language = N’語言’： 具體取決於你的 SQL Server 版本，有效的值為 R (SQL Server 2016 及更高版本)、 Python (SQL Server 2017 及更高版本) 和 Java （SQL Server 2019 預覽版）。
@script = u’指令碼語言’： 需要被執行的外部指令碼，指令碼指定為引數或變數的輸入的外部語言指令碼， 指令碼是nvarchar （max）。
@input_data_1 = N’input_data_1’： 外部語言指令碼的輸入資料。
@input_data_1_name = N’input_data_1_name’： 可選輸入名，用於表示定義的查詢變數名稱@input_data_1，預設名inputDataSet。注意：外部指令碼變數中的資料型別取決於語言。 對於 R，則輸入的變數是資料幀；對於 Python，輸入必須為表格。
@output_data_1_name = N’output_data_1name’： 輸出名，預設名為OutputDataSet。指定的變數名稱中包含要返回到的資料的外部指令碼 SQL Server 儲存過程呼叫完成後， 外部指令碼的變數中的資料型別取決於語言。
@parrallel = 0 | 1 ： 啟用並行執行指令碼@parallel引數為 1，此引數預設值為 0 （不能並行）。 如果@parallel = 1和輸出進行流式處理直接向客戶端計算機，則WITH RESULT SETS子句是必需的並且必須指定輸出架構。
@params = N’parameter_name data_type [輸出] [，…n]’： 外部指令碼中使用的輸入的引數宣告的列表。
@parameter1 = ‘value1：’ 有關使用外部指令碼的輸入引數的值的列表。

下面是T-SQL執行Python指令碼程式碼最基本的情況，如下所示：

execute sp_execute_external_script 
@language = N'Python', 
@script = N'
import math
a = 1
b = 2
c = a*b
print(a,b,c)
d = math.pi/6
print(math.sin(d))
'

輸出結果如下所示：

推薦這篇文章供大家學習基礎知識：使用 T-SQL 執行 Python

接在講述匯入sklearn庫進行線性迴歸預測的程式碼，如下所示：

execute sp_execute_external_script 
@language = N'Python', 
@script = N'
from sklearn import linear_model      
import matplotlib.pyplot as plt     
import numpy as np
#X表示匹薩尺寸 Y表示匹薩價格
X = [[6], [8], [10], [14], [18]]
Y = [[7], [9], [13], [17.5], [18]]
print(X)
print(Y)
#迴歸訓練
clf = linear_model.LinearRegression() 
clf.fit(X, Y)                         
res = clf.predict(np.array([12]).reshape(-1, 1))[0]
print(u"預測一張12英寸匹薩價格：$%.2f" % res)
#預測結果
X2 = [[0], [10], [14], [25]]
Y2 = clf.predict(X2)
'

輸出結果可以看到線性迴歸預測的價格。

---

二. T-SQL含查詢的Python指令碼

1.查詢顯示所有資料庫

下面結合SQL語句查詢編寫Python指令碼，程式碼如下：其中輸入資料為"select 1 as Coll”，輸出結果賦值為MyOutput變數，最後輸出的表頭定義為“ResultValue”。

execute sp_execute_external_script 
@language = N'Python', 
@script = N'
MyOutput = MyInput
',
@input_data_1_name = N'MyInput',
@input_data_1 = N'SELECT 1 as Col1',
@output_data_1_name = N'MyOutput'
WITH RESULT SETS ((ResultValue int))

輸出結果如下所示：

另一種獲取表格的方法如下所示：

--	自定義輸入的資料集變數，如：dbname
execute sp_execute_external_script 
@language = N'Python', 
@script = N'
print(dbname)
print(type(dbname))
',@input_data_1 = N'SELECT database_id,name FROM sys.databases'
,@input_data_1_name = N'dbname'

輸出結果如下所示，包括程式碼及執行結果（資料庫名稱）。

2.建立表格並顯示查詢的資料

下面自己建立表，再查詢表格中的資料。

create database yxz;

use yxz;

--學生表
create table Student(
	Sno varchar(10) primary key,
	Sname varchar(10) not null,
	Sex char(2),
	Sdept varchar(20),
	SBirthday datetime
);

insert into Student(Sno,Sname,Sex,Sdept,SBirthday)
values('S01','王建平','男','自動化','1996-01-12');
insert into Student(Sno,Sname,Sex,Sdept,SBirthday)
values('S02','劉華','女','自動化','1995-07-01'),
	('S03','範林軍','女','計算機','1994-06-30');
insert into Student(Sno,Sname,Sex,Sdept,SBirthday)
values('S04','李偉','女','數學','1995-05-01'),
	('S05','黃煙','男','數學','1996-04-01'),
	('S06','何淳','男','數學','1995-06-30');

select * from Student;

SELECT Ages = DATEDIFF(YEAR,[SBirthday],GETDATE())
FROM [yxz].[dbo].[Student];

輸出結果如下圖所示：

R指令碼

EXEC sp_execute_external_script
    @language = N'R',
	@script = N'res<-quantile(InputDataSet$Ages);
                df <- data.frame(res);',
	@input_data_1 = N'SELECT DATEDIFF(YEAR,[SBirthday],GETDATE()) AS Ages
			    FROM [yxz].[dbo].[Student];',
	@output_data_1_name = N'df'
WITH RESULT SETS (("res" int not null));

輸出結果如下所示：

   res
1  22
2  22
3  23
4  23
5  24

Python指令碼

EXEC sp_execute_external_script
@language = N'Python',
@script = N'
print(InputDataSet[["Ages","Sno"]])
',
@input_data_1 = N'SELECT DATEDIFF(YEAR,[SBirthday],GETDATE()) as Ages, Sno
		FROM [yxz].[dbo].[Student];',
@output_data_1_name = N'df'

其中，InputDataSet表示輸入資料集，需要定義到[[“Ages”, “Sno”]]中。 輸出結果如下：

而如果查詢輸出欄位包含了中文，如使用者名稱時，會提示編碼utf-8錯誤。

EXEC sp_execute_external_script
@language = N'Python',
@script = N'
print(InputDataSet["Ages","Sname","Sno"])
',
@input_data_1 = N'SELECT DATEDIFF(YEAR,[SBirthday],GETDATE()) as Ages, Sname, Sno
		FROM [yxz].[dbo].[Student];',
@output_data_1_name = N'df'

如下圖所示，在Python中可以嘗試讀入檔案指定utf-8編碼，而嵌入SQL Server中我還不知道如何解決，嘗試並百度也沒找到解決方法。
Error in execution. Check the output for more information.
UnicodeDecodeError: ‘utf-8’ codec can’t decode byte 0xcd in position 0: invalid continuation byte

---

三. Python讀取檔案及聚類分析

接下來講解Python讀取檔案並進行聚類分析的程式碼。輸入的資料是glass玻璃資料集，如下所示：

單獨的Python程式碼如下：

# -*- coding: utf-8 -*-
import pandas as pd
import matplotlib.pyplot as plt
from sklearn.decomposition import PCA
from sklearn.cluster import Birch

#獲取資料集及降維
glass = pd.read_csv("glass.csv")
pca = PCA(n_components=2)  
newData = pca.fit_transform(glass)  
print newData[:4]  
L1 = [n[0] for n in newData]  
L2 = [n[1] for n in newData]
plt.rc('font', family='SimHei', size=10) #設定字型
plt.rcParams['axes.unicode_minus'] = False #負號

#聚類 類簇數=4
clf = Birch(n_clusters=4)
clf.fit(glass)
pre = clf.predict(glass)
plt.title(u"Birch聚類 n=4")  
plt.scatter(L1,L2,c=pre,marker="s",s=100)  
plt.show()  

執行結果如下圖所示：

那怎麼通過SQL Server嵌入Python程式碼實現簡單的聚類分析呢？
首先通過下面程式碼實現讀入資料操作。

@language = N'Python', 
@script = N'
import pandas as pd
xl = pd.ExcelFile("C:/glass.xlsx")
df = xl.parse(xl.sheet_names[0])
print(df)
print(df.dtypes)
'

輸出表格內容如下圖所示：

另一箇中文資料表 glass2.xlsx 輸出如下所示：

資料分析部分同樣增加相關程式碼即可，如下：

execute sp_execute_external_script 
@language = N'Python', 
@script = N'
import pandas as pd
from sklearn.cluster import Birch
xl = pd.ExcelFile("C:/glass.xlsx")
df = xl.parse(xl.sheet_names[0])
clf = Birch(n_clusters=4)
clf.fit(df)
pre = clf.predict(df)
print(pre)
'

輸出的預測結果如下所示：

後續會繼續補充如何將匯入的資料儲存至表中，如何顯示中文資料，如何編寫儲存過程進行訓練和測試。這系列文章資料比較少，作者也在一步步學習研究中，望讀者海涵。

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