[Python] putting looped API Call results into a dataframe in Python

i need some help. have got a part of a python script which accesses a url field in a sql database, and then calls an api based using the url in the field. Now i cannot get the data into a dataframe to then flatten and output to csv, can anyone help. I am not very proficient in Python. Have done it for paginated API Calls, but the looping of a call, put into df, go to next call, put in df etc is breaking me

The code so far is below;

import pyodbc
import pandas as pd
import requests as re
from pathlib import Path
import json
import os

server = 'TestServer'
database = 'TestDB'
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';Trusted_Connection=yes')
cursor = cnxn.cursor()


# select rows from SQL table to insert in dataframe.
websites = "select TOP 2 URL FROM [dbo].[API_URL_TEST];"
UUID_df = pd.read_sql(websites, cnxn)
print(UUID_df.head(10))
#print(df)

from urllib.request import urlopen

for link in UUID_df['URL']:
with urlopen(link) as response:
myfile = response.read()
print(myfile)
data=response.json()
df = pd.json_normalize(data['match'])

print(df)


i get json data from the api, I have been using response.json() but i am only getting the data from the 1st api call from the 2 i am using for testing. When i print(myfile) i can see data from both api calls. i suspect it is me not knowing how to put each call in the dataframe before the next api call. in total once completed will be around 1k api's to call and get data from.

Continue reading...