API -> pandas -> database

[chrismc]

Firstly, apologies if I'm not using the right terminology, I'm trying to run before I walk and you will see the problems I'm having. I know how to code in other languages but I've got a tight deadline to meet on this one ....

As the title says I'm trying to get data from an API, do some slight massaging in pandas and write to a relational database.

When I was testing last week I was able to save the API output as a JSON file and read this into python and process it in pandas.

Now I want to read directly from the API and process it the same way. The API is confidential, so I can't post details about it here (but that's not the problem).

This is what I have so far:

Within a function I get the data from the API like this:
req = requests.get(...)
Return the data from the function like this:
return req.content

It seems to get returned as a bytestream, so I make it a string by doing this:
data = data.decode()

This still doesn't look like JSON format (but it's close), so I run a series of replace functions to get the data looking like the file and make it look like this:



But when I try to convert it to a dataframe:
df0 = pd.DataFrame(data)

I get an error.

Can anyone see what I'm doing wrong ?
Is there a better way to do this ?

[stullis]

Can you post the error traceback?

When a JSON is read in Python, it becomes a dict. So, that output is your JSON.

[Larz60+]

Please use code tags, see BBCode
for output use [output][/output]

Please post the url you are getting so that we can try here.
Thanks

[chrismc]

Just to close this off, I figured it out, the answer is :

Output:
    data =  data.decode()

    data = data.replace("{\"data\":","")
    data = data.replace("}","")
    
    df = pd.DataFrame(ast.literal_eval(data),columns=['index','data'])