在SQLServer机器学习服务中读取Python GET请求的结果

Question

我正在利用SQL Server中的Python机器学习服务功能编写一个简单的Python脚本。目标是我的SQL server可以使用python脚本ping外部API，并将结果读入变量

我已经成功地构建了GET请求，我正在努力将API的输出反馈到数据库中

到目前为止，我所拥有的：

EXECUTE sp_execute_external_script @language = N'Python', 
@script = N'

import requests 
import pandas as pd
URL = "https://api.website.io/verify?email=dave@davidson.com&apikey=test_44fbcce85ba0f270273f4452bea2311de50f9c"
r = requests.get(url = URL)
data = r.text
print(data)
'
, @input_data_1 = N''
WITH RESULT SETS(([Col1] varchar(MAX) NOT NULL));

因此，print（data）在SSMS中为我提供了以下输出：

{"success":false,"message":"Invalid API key"}

但我不想打印它，我想把它读入SQL。 我真的不在乎结果如何，所以现在我只指定了一个名为“Col1”的列来保存输出，但我最终需要将“False”和“Invalid API key”加载到SQL Server存储过程中的两个变量中，以便在下一步中使用这些值。如果我必须手动解析JSON中的这些内容，那么我可以这样做，但理想情况下，它们将作为单独的列出现在SQL输出中

我尝试过在各种排列中使用r.json（），但我得到了许多不同的错误：

对于SQL Server机器学习，我知道我需要创建一个名为“OutputDataSet”的变量，该变量的类型必须为“pandas dataframe”，因此我需要将“r”中保存的JSON数据转换为pandas dataframe。然而，我在这方面所做的一切都给了我神秘的错误代码

例如：

OutputDataSet = pd.DataFrame.from_dict(r.json(), orient="index")

给出：“执行错误。”

或

OutputDataSet = pd.DataFrame.from_dict(r.text, orient="index")

给出“AttributeError:'str'对象没有属性'values'”

这是将JSON转换为我没有得到的数据帧的语法吗？ 或者是否需要额外的步骤或库来将请求库的输出转换为Pandas库可以接受的内容

Answer 1

经过反复试验，我找到了一个可行的解决方案：

我的数据库中有一个InputData表，我的存储过程从InputData读取所有行，使用Python通过API运行它们，然后将API结果返回到临时表中，我可以像使用SQL中的任何表一样使用临时表

在我的“InputData”表中，我有以下列：

参考-我公司的专有参考号，因此我可以知道该API数据与哪个公司的记录有关

APIRL-为我的API请求预先配置的URL。此字段包含此引用的API请求的所有参数以及此API的API键。基本上整个HTTP API请求都在这里。我在前面的一个SQL步骤中创建了这个URL，因为在Python中创建这个URL对我来说很麻烦，而且在这个例子中没有真正的需要

JSONDATA-数据类型为nvarchar（MAX）的空列，此列中的所有记录均为空。有点像骗子的方式。通过在输入数据中将此空值作为列传递，我们不必在输出数据中指定或命名它。因为在SQL表中有一个空列几乎没有什么区别，所以我选择了这种方式来简化从Python到SQL的输出数据

  Define query
DECLARE @sqlscript NVARCHAR(MAX);
    SET @sqlscript = N'SELECT Reference, APIURL, JSONData FROM dbo.InputData';

  Define Python script
DECLARE @pscript NVARCHAR(MAX);
    SET @pscript = N'
import requests                       # Import the "requests" library that contains the requests.get command we will use to query the API (supports SSL, https etc)
InputDF = InputDataSet                # Assign the input data (named InputDataSet by default in SQL Python) to a Python dataframe object named "InputDF"
OutputDF = InputDF                    # Copy all the input data to a new Python dataframe object named "OutputDF" 
for row in InputDF.itertuples():      # Start a loop which iterates over every row in InputDF, using "itertuples" Python function
    URL = row.KickBoxURL              # Read the KickBox API URL for this case/email address from the input dataframe
    r = requests.get(url = URL)       # Use the "get" command from the requests library to send our request to the API using that URL
    OutputDF.at[row.Index,4]= r.text  # Update column 4 (JSONData) of the output dataframe for the current iteruples row index, with the output JSON text from the .get command (stored in "r")
OutputDataSet = OutputDF              # Return the OutputDF dataset as a SQL Server "OutputDataSet" (the default name in SQL Python) which returns the data as a query output, which we will INSERT into #Results
';
 
  Declare temp table to hold results
  DROP TABLE IF EXISTS #Results
CREATE TABLE #Results (Reference varchar(10) NOT NULL PRIMARY KEY CLUSTERED,
                       APIURL varchar(1000) NOT NULL,
                       JSONOutput nvarchar(MAX) NULL)
 
  Run procedure using Python script and T-SQL query and Insert results to #Results
 INSERT #Results
   EXEC sp_execute_external_script
        @language = N'Python',
        @script = @pscript,
        @input_data_1 = @sqlscript;  

运行完上面的程序后，我现在有了一个名为#Results的临时表，其中包含了API中针对我发送的每个请求的所有参考号和响应JSON

然后，我可以对该临时表执行一些JSON查询，以从包含我所需数据的JSON中提取特定字段。因为那时我不再查询API，我现在可以执行任意多个SELECT查询，但我只在每条记录中访问过一次外部API