Чтение результатов моего запроса Python GET в службах машинного обучения SQL Server

Question

Я собираю простой скрипт Python, используя функции Python Machine Learning Services в SQL Server. Цель состоит в том, чтобы мой SQL-сервер мог пинговать внешний API с помощью скрипта Python и считывать результаты в переменные.

Я успешно построил запрос 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» и «Invalid API key» в две переменные в моя хранимая процедура SQL Server, чтобы я мог использовать эти значения на следующем шаге. Если мне нужно разобрать их из JSON вручную, тогда меня это устраивает, но в идеале они должны выводиться как отдельные столбцы в выводе SQL.

Я пробовал использовать r.json() в различных перестановках, но получаю много разных ошибок:

для машинного обучения SQL Server я понимаю, что мне нужно создать переменную с именем «OutputDataSet», и она должна иметь тип «pandas dataframe», поэтому мне нужно преобразовать данные JSON, содержащиеся в «r», в pandas dataframe. Однако все, что я пробую на этом фронте, дает мне загадочные коды ошибок.

так например:

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

выдает: «Ошибка выполнения».

или же

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

выдает "AttributeError: объект 'str' не имеет значений атрибута '"

Это синтаксис для преобразования JSON в DataFrame, который я не получаю? Или есть дополнительный шаг или библиотека, необходимая для преобразования вывода из библиотеки запросов во что-то, что будет принимать библиотека Pandas?

Answer 1

После долгих проб и ошибок у меня есть решение, которое работает:

У меня есть таблица InputData в моей базе данных, моя сохраненная процедура считывает все строки из InputData, запускает их через API с помощью Python, а затем возвращает результаты API во временную таблицу, которую я могу использовать, как любую таблицу в SQL.

В моей таблице "InputData" есть следующие столбцы:

Ссылка - собственный ссылочный номер моей компании, по которому я могу сказать, к какой записи компании относятся эти данные API.

APIURL - предварительно настроенный URL-адрес для моего запроса API. Это поле содержит все параметры запроса API для этого справочника и ключ API для этого API. По сути, здесь находится весь запрос HTTP API. Я создаю этот URL-адрес на более раннем этапе SQL, так как мне неудобно делать это на Python, и в этом случае нет реальной необходимости.

JSONDATA - пустой столбец с типом данных nvarchar(MAX), все записи в этом столбце имеют значение 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
InputDF = InputDataSet                # Assign the input data to a python dataframe object named "InputDF"
OutputDF = InputDF                    # Copy all the input data to a new python dataframe object named "OutputDF" 
for index, row in InputDF.iterrows(): # Start a loop which iterates over every row in InputDF, using "iterrows" Python function
    URL = row[''APIURL'']             # Read the API URL for this row 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.iloc[index,2]= r.text    # Update column 2 (JSONData) of the current row index in the output dataframe with the output JSON text from the get command (stored in "r")
OutputDataSet = OutputDF              # Return the OutputDF dataset as a SQL Server "OutputDataSet" 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, которая содержит все ссылочные номера и ответ JSON от API для каждого отдельного запроса, который я ему отправил.

Затем я могу выполнить несколько запросов JSON к этой временной таблице, чтобы извлечь из JSON определенные поля, содержащие нужные мне данные. Поскольку в этот момент я больше не запрашиваю API, я могу выполнять столько запросов SELECT, сколько хочу, но я обращался к внешнему API только один раз для каждой записи.