SQL Server执行安装python环境

安装注意事项

启用python脚本支持

sp_configure 'external scripts enabled', 1;
RECONFIGURE;

安装后接受 Python EULA协议

接受python授权
setup.exe /qs /ACTION=Install /FEATURES=SQL_INST_MR /INSTANCENAME=您的实例名 /IACCEPTROPENLICENSETERMS=1 /IACCEPTPYTHONLICENSETERMS=1

重启SQL服务器

查询python版本 3.7

EXEC sp_execute_external_script
@language = N'Python',
@script = N'
import sys
print(sys.version)
'

不用requests（需要安装，安装失败）调用URL

EXEC sp_execute_external_script
@language = N'Python',
@script = N'
from urllib.request import urlopen, Request
from urllib.parse import urlencode
import pandas as pdurl = "http://localhost/index.html"
params = {"key1": "value1", "key2": "value2"}
full_url = f"{url}?{urlencode(params)}"try:request = Request(full_url)with urlopen(request, timeout=10) as response:status_code = response.getcode()content = response.read().decode("utf-8")[:1000]
except Exception as e:status_code = 0content = str(e)OutputDataSet = pd.DataFrame({"URL": [full_url],"Status": [status_code],"Content": [content]
})
'
WITH RESULT SETS ((URL varchar(1000), Status int, Content varchar(1000)));

结果如下

存储过程：

CREATE PROCEDURE usp_CheckWebsiteStatus@URL varchar(1000)
AS
BEGIN-- 创建临时表存储结果CREATE TABLE #TempResult (URL varchar(1000),Status int,Content varchar(1000));-- 使用INSERT EXEC模式捕获结果INSERT INTO #TempResultEXEC sp_execute_external_script@language = N'Python',@script = N'
from urllib.request import urlopen, Request
from urllib.parse import urlencode
import pandas as pdurl = URL_param
try:request = Request(url)with urlopen(request, timeout=10) as response:status_code = response.getcode()content = response.read().decode("utf-8")[:1000]
except Exception as e:status_code = 0content = str(e)OutputDataSet = pd.DataFrame({"URL": [url],"Status": [status_code],"Content": [content]
})',@params = N'@URL_param varchar(1000)',@URL_param = @URL;-- 返回结果SELECT * FROM #TempResult;-- 根据状态码执行不同业务逻辑DECLARE @Status int;SELECT @Status = Status FROM #TempResult;IF @Status = 200BEGIN-- 确保日志表存在IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'WebsiteStatusLog')BEGINCREATE TABLE WebsiteStatusLog (LogID int IDENTITY(1,1) PRIMARY KEY,CheckTime datetime NOT NULL,URL varchar(1000) NOT NULL,Status int NOT NULL,Content varchar(1000) NULL);END-- 记录错误日志INSERT INTO WebsiteStatusLog(CheckTime, URL, Status, Content)SELECT GETDATE(), URL, Status, Content FROM #TempResult;END-- 清理临时表DROP TABLE #TempResult;
END;
GO-- 使用存储过程
EXEC usp_CheckWebsiteStatus 'http://localhost/index.html';