Appending CSV file with API data in a while loop

Question

I'm trying to use a with statement to write and append a CSV file. The with is being used in conjunction with a while loop that reads data and headers from a paginated API.

As you might see, I haven't worked out how to incorporate the with and while to achieve this.

I'm using a defined function to return the data. The API uses OAuth2. My account has multiple locations to download from (hence the location_id col):

def fetch_data_for_location(oauth_session, location_id, page=None, start_time=None, end_time=None):
    # get data from first location returned
    dataurl = "my url that I can't share"
    params = {}
    headers = {}

    # Page is added to the header of the request
    if page is not None:
        headers['start-page'] = page

    # Time filters are added to the query parameters of the request
    if start_time is not None:
        params['start-time'] = start_time

    if end_time is not None:
        params['end-time'] = end_time

    response = oauth_session.get(dataurl, params=params, headers=headers)
    data = response.json()
    response_headers = response.headers

    return data, response_headers

As you will notice, fetch_data_for_location() returns two items: data and response_headers.

Next, I generate the response_headers for my chosen location by specifying [1] after the function.

The while loop checks the headers (data_headers) for the header 'next-page' and continues to read the data until there is no header called 'next-page'.

Right now I've just set it up so the data will print when they are found.

#get response headers
data_headers = fetch_data_for_location(oauth, location1, start_time=0, end_time=None)[1]


#get all data and write to csv
    while data_headers["next-page"] is not None:
        print(fetch_data_for_location(oauth, location1, page=data_headers["next-page"], start_time=0, end_time=None)[0])
        data_headers = fetch_data_for_location(oauth, location1, page=data_headers["next-page"], start_time=0, end_time=None)[1]

I would like to append a CSV file with data from this while loop. I thought something like this would work:

with open('results.csv', 'w') as csvfile:

But, I don't know how to reference that in my while loop.

The last thing I should mention is that the data are a dict object and have a format like this:

{'locationId': 6544992693911552, 'parameters': 
[{'parameterId': 'Pressure', 'unitId': 'Unspecified', 'customParameter': True, 'readings': 
[{'timestamp': 1627044780, 'value': 0.09}, {'timestamp': 1627044840, 'value': 0.09}

The timestamps continue like that until the next parameter.

you can use python's unpacking feature `data, headers = fetch_data_for_location(oauth, location1, start_time=0, end_time=1653645628)` — Rohit Babu, Jun 01 '22 at 09:18

Cow · Answer 1 · 2022-06-01T10:05:39.253

Without knowing how your response.json() looks like, this is just an example:

#get response headers
data, response_headers = fetch_data_for_location(oauth, location1, start_time=0, end_time=1653645628)

#get all data and write to csv
with open('results.csv', 'w') as csvfile:
    while response_headers["next-page"] is not None:
        csvfile.write(f"{data['value1']},{data['value2']}")
        data, response_headers = fetch_data_for_location(oauth, location1, page=response_headers["next-page"], start_time=0, end_time=None)

Rohit Babu · Answer 2 · 2022-06-01T12:26:42.197

To write/append data to CSV file assuming print(fetch_data_for_location(oauth, location1, page=data_headers["next-page"], start_time=0, end_time=None)[0]) is what you want to write into a CSV file, try below

#get response headers
data_headers = fetch_data_for_location(oauth, location1, start_time=0, end_time=None)[1]


#get all data and write to csv
    while data_headers["next-page"] is not None:
        with open('results.csv', 'w+', encoding='UTF8') as csvfile:
           #assuming data_to_write is going to be a dict and key1, key2 are one of the keys
           data_to_write = fetch_data_for_location(oauth, location1, page=data_headers["next-page"], start_time=0, end_time=None)[0]
           csvfile.write(f'{data_to_write["key1"]},{data_to_write["key2"]}')
        data_headers = fetch_data_for_location(oauth, location1, page=data_headers["next-page"], start_time=0, end_time=None)[1]

The w+ is key here as it appends data if the file already exists, otherwise creates it. You can read more here

You can use python's unpacking feature to avoid any additional call to fetch data. Something like below to fetch data and headers in one go.

data, headers = fetch_data_for_location(oauth, location1, start_time=0, end_time=1653645628)

Hi! This seems to work if I use json.dumps() after csvfile.write(). The script takes a while to execute which seemed like a good sign but then I get an error telling me KeyError: "x-isi-next-page". Do you know why this is happening? — obrunt, Jun 01 '22 at 11:15
Can you post a sample response? You have to extract necessary data from the response and write it as comma separated values — Rohit Babu, Jun 01 '22 at 12:09
@obrunt you can use python's `csv` library to ease the csv file generation — Rohit Babu, Jun 01 '22 at 12:28

Appending CSV file with API data in a while loop

2 Answers2