CODEWITHSUNDEEP
pythonjsonpython-3.xtablib
Marialena
Marialena

Reputation: 817

Tablib fails to convert JSON API response to XLSX file and throws error KeyError:0

I am trying to convert a JSON response I get after calling an API to an excel file. I don't need to add specific headers or get only specific data, I just need everything this call returns.

I have found the tablib library.

I managed to make it working when I fetched data from GitLab but after changing my payload to Tfs I get an error which I am not sure what the problem is or how to resolve.

This is my code:

import requests
import urllib3
import json
from requests.packages.urllib3.exceptions import InsecureRequestWarning
import tablib
import datetime
import time
import os

class gitlab():

    def get_closed():

        url = "https://IP:443/DefaultCollection/_apis/projects"

        payload = {}

        querystring = {"api-version":"4.1"}

        headers = {
            'Content-Type': "application/json-patch+json",
            'Authorization': "KEY" 
        }

        requests.packages.urllib3.disable_warnings(InsecureRequestWarning)

        response = requests.request(
            "GET", url, headers=headers, data=payload, params=querystring,  verify=False)

        return json.loads(response.text)


if __name__ == "__main__":

    list_b = gitlab.get_closed() 

    print(list_b)
    data = tablib.Dataset()

    data.json = json.dumps(list_b)

    data_export = data.export('xlsx')

    filename = os.path.dirname(os.path.realpath(__file__)) +'/closed_' + str(datetime.date.today()) + '.xlsx'

    with open(filename, 'wb') as f: 
        f.write(data_export)  

    f.close()

Executing with python script where python's version is 3.7.2 shows the following error:

{'count': 1, 'value': [{'id': 'ID', 'name': 'TFS', 'url': 'https://TFS/DefaultCollection/_apis/projects/PROJ', 'state': 'wellFormed', 'revision': 00, 'visibility': 'private'}]}

Traceback (most recent call last):  File ".\gitlab.py", line 94, in <module>    data.json = json.dumps(list_with_bugs, indent=4, ensure_ascii=False)  File "C:\Users\marialena\AppData\Local\Programs\Python\Python37\lib\site-packages\tablib\formats\_json.py", line 39, in import_set
    dset.dict = json.loads(in_stream)
  File "C:\Users\marialena\AppData\Local\Programs\Python\Python37\lib\site-packages\tablib\core.py", line 381, in _set_dict
    if isinstance(pickle[0], list):
KeyError: 0

You can see the API response as well in the output. Why is tablib failing to convert to excel?

Upvotes: 0

Views: 1044

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55933

Dataset.json expects to receive a serialised list. The code in the question is passing a serialised dict, and this is the cause of the error.

Looking at the data, it seems the value of the value key in the dictionary is what is required, so pass that to the dataset.

>>> import json
>>> import tablib
>>> d = {'count': 1, 'value': [{'id': 'ID', 'name': 'TFS', 'url': 'https://TFS/DefaultCollection/_apis/projects/PROJ', 'state': 'wellFormed', 'revision': 00, 'visibility': 'private'}]}
>>> ds = tablib.Dataset()
>>> ds.json = json.dumps(d['value'])
>>> with open('test.xlsx', 'wb') as f:
...     f.write(ds.export('xlsx'))

I see that you have raised a bug on the project's issue tracker. While this behaviour arguably isn't a bug, it would certainly be an improvement if the code emitted a more meaningful error message.

Upvotes: 2

Related Questions