阅读SQL Server机器学习服务中我的Python GET请求的结果

Question

我正在整理一个简单的Python脚本，利用SQL Server中的Python机器学习服务功能。目标是我的SQL服务器可以使用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=[e-mail]&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"和无效的API密钥;到SQL Server存储过程中的两个变量中，以便在下一步中使用这些值。如果我必须手动从JSON中解析这些，那么我很好，但理想情况下，它们将在SQL输出中作为单独的列出现。

我已经尝试使用r.json()在各种排列，但我得到了很多不同的错误:

对于SQL Server机器学习，我知道我需要创建一个名为OutputDataSet&quot的变量。它的类型需要是&;pandas dataframe&;，所以我需要转换&;&;到pandas数据框架中。然而，我在这方面所做的一切都给了我神秘的错误代码

所以例如:

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

给出:"Error in execution.">

或

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

给出"AttributeError: 'str'对象没有属性'values'">

是语法转换JSON到一个数据框架，我没有得到?或者是否需要额外的步骤或库来将Requests库的输出转换为Pandas库可以接受的内容?

Answer 1

经过反复试验，我有了一个可行的解决方案:

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

在我的InputData"表中有以下列:

参考-我公司的专有参考号，这样我就可以知道这个API数据与哪个公司的记录有关。

APIURL-我的API请求的预配置URL。此字段包含此引用的API请求的所有参数，以及此API的API密钥。基本上整个HTTP API请求都在这里。我在前面的SQL步骤中创建了这个URL，因为用Python来创建这个URL对我来说很尴尬，而且在这个实例中也不需要这样做。

JSONDATA—数据类型为MAX (nvarchar)的空列，该列中所有记录为NULL。这是一种作弊的方法。通过在输入数据中将这个NULL值作为列传递，我们不必在输出数据中指定它或命名它。因为在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每条记录。