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 pd

url = "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 = 0
    content = 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 #TempResult
    EXEC sp_execute_external_script
    @language = N'Python',
    @script = N'
from urllib.request import urlopen, Request
from urllib.parse import urlencode
import pandas as pd

url = 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 = 0
    content = 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 = 200
    BEGIN
        -- 确保日志表存在
        IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'WebsiteStatusLog')
        BEGIN
            CREATE 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';