Extracting API Data Using Python and Loading into SQL Server

Extracting API Data Using Python and Loading into SQL Server

Hi,
I am new to Python in SQL Server. I'd like to load json data from an API into SQL Server, I thought the best way to do this is to utilise the new SQL Server Machine Learning Services with Python.

I can call the API and print the json data in SSMS:

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

# We import the requests module which allows us to make the API call
import pandas as pd
import json
import requests
 
# Call API to pull data
url = ''https://samples.openweathermap.org/data/2.5/weather?q=London,uk&appid=b6907d289e10d714a6e88b30761fae22''

response = requests.get(url = url)
response_data = response.json()

print(response_data)
'

Select allOpen in new window

I'm pretty happy using the JSON functions in SQL Server to format and parse the data into SQL tables, but with Python how do I read/access the json data from the response into an TSQL query?

Thank you