I have created a python script and am able to run it in SQL using the sp_execute_external_script procedure. My text response is: (Annoymised)

App ID,External ID,Surname,Forename,Job title,Branch name,Branch external ID
6735377,MIL019,SNAME,FNAME,Driver,BRANCH,BRANCH EID
6735377,MIL019,SNAME,FNAME,Driver,BRANCH,BRANCH EID
6735378,MIL002,SNAME,FNAME,Assistant Village Manager,BRANCH,BRANCH EID
6735386,MIL022,SNAME,FNAME,Housekeeping Manager,BRANCH,BRANCH EID
6735386,MIL022,SNAME,FNAME,Housekeeping Manager,BRANCH,BRANCH EID

I would like to output these to a table within sql but so far have been able to return a single row comprising of everything separated by commas.

This is the code I have...

DECLARE @PyScript NVARCHAR(MAX) = N'
import requests
import pandas as pd
import numpy as np
import json 
result_array = []
url = "MY URL"
payload= {
        "secret"   : "MY PASSWORD",
        "date_from": "2021-01-01",
        "date_to"  : "2021-02-01"
     }
files=[]
headers = {
        "Authorization": "MY AUTHORISATION",
        "Cookie"         : "sessionid=MY SESSION ID; csrftoken=MY TOKEN"
      }
r = requests.request("POST", url, headers=headers, data=payload, files=files)
result_array.append(r.text)
df = pd.DataFrame(np.array(result_array))
results = df
'
EXECUTE sp_execute_external_script
@language  = N'Python'
,@script   = @PyScript
,@output_data_1_name = N'results'

CodePudding user response：

I managed to find the answer to this here:

https://datascience.stackexchange.com/questions/67768/convert-csv-from-an-api-to-a-dataframe-format-in-python

I hope this thread helps someone as this has helped me a lot.