6
\$\begingroup\$

I'm parsing a Pandas DataFrame table for an API post request (using requests, of course). I'm working on building my script in Jupyter Notebooks.

result = df.to_json(orient="records")
parsed_json = json.loads(result)

I've seen a lot of JSON manipulations with format(), so my first impulse was to use f-strings:

je_json = json.dumps({
    'BatchId': '1',
    'userId': 'myID',
    'journalEntries': f'{parsed_json }'
})

It would be very readable and elegant, but of course it doesn't work as the result is escaped and will not POST:

'{"BatchId": "1", "userId": "myID", "journalEntries": "[{\\"JENumber\\":\\"PR2022-01-23\\",\\"Date\\":\\"01\\\\/23\\\\/2022\\",\\"JEComment\\": [...]

I solved the problem by reconstructing the list/dicts:

je_json = {
    'BatchId': '1',
    'userId': 'myID',
    'journalEntries': ''
}

je_json['journalEntries'] = []

for record in parsed_json:
    je_json['journalEntries'].append(record)

payload = json.dumps(je_json)

Is there a simpler or more elegant solution?


Request

data_headers = {
    'Authorization': str(parsed_data.get('BearerToken')),
    'Content-Type': 'application/json',
    'Cookie': 'ss-opt=temp; ss-pid=xxxxxxxxxxxxxxxxxxx'
}

response = requests.request(method="POST", 
    url=URL, 
    headers=data_headers, 
    data=payload)

Explanation of my testing methodology

Since running tests in Jupyter notebooks can be tricky, I'm going to mention here my methodology. Right now, I'm testing the payload variable and request in a separate cells. After a successful record insertion via the API, I don't reload the whole notebook, and simply reload the payload and request cells: 1) Change the successful payload cell into type 'raw'; 2) Add new cell with modified payload variable for testing; 3) reload the payload and request cells; 4) examine the results; 5) toggle cell type 'code' and 'raw' to return to the success version and verify underlying code; 6) repeat. What makes this work is toggling cell type, and never mutating variables once assigned--there is no assignment to any variable name assigned to a cell above it.

\$\endgroup\$

1 Answer 1

9
\$\begingroup\$

I fail to see a reason for the long way around. If you already have parsed JSON, why not use it directly?

result = df.to_json(orient="records")
parsed_json = json.loads(result)

je_json = json.dumps({
    'BatchId': '1',
    'userId': 'myID',
    'journalEntries': parsed_json
})

It would be even better to not serialize and deserialize the JSON data twice:

je_json = json.dumps({
    'BatchId': '1',
    'userId': 'myID',
    'journalEntries': df.to_dict(orient="records")
})

On a side-note I find it funny, how pandas' method name to_dict() is absolutely misleading, since it returns a list in the above case.

Also, you don't need to serialize the JSON payload manually, since requests will do it for you:

from requests import post


payload = {
    'BatchId': '1',
    'userId': 'myID',
    'journalEntries': df.to_dict(orient="records")
}

response = post('https://yourapp.com/submit', json=payload)
\$\endgroup\$
4
  • \$\begingroup\$ #1 & #2 worked perfectly. Exactly what I hoped for. Example #3 didn't work without json.dumps() : '{"ResponseStatus":{"ErrorCode":"SerializationException","Message":"Could not deserialize \'application/json\'; Maybe something to do with my request? I posted this line above. \$\endgroup\$
    – xtian
    Jan 29 at 17:01
  • \$\begingroup\$ Ok. Just to be clear. With json.dumps() everything works as expected. Help me out here. I'm a noob with JSON and REST. Can I arrive at the same conclusion if, say, I read the requests docs from start to finish? \$\endgroup\$
    – xtian
    Jan 29 at 20:02
  • 2
    \$\begingroup\$ @xtian They should both work, i.e. a POST request with data=json.dumps(payload) and 'Content-Type': 'application/json' passed in explicitly as one of the headers is equivalent to a request with just json=payload, where the header is set for you in the latter case (see examples at the end of More complicated POST requests). I would double-check your request that uses json= to make sure you're not overlooking anything. \$\endgroup\$
    – Setris
    Jan 29 at 21:17
  • \$\begingroup\$ > "double-check your request that uses json=.." That's it. I need to read a good book on requests, because that was just not obvious--until you pointed it out. \$\endgroup\$
    – xtian
    Jan 30 at 2:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.