0

I am trying to create a python script that does the following:

  • Parse a CSV file
  • Send the CSV file to a remote server via REST API

The code I have is working to parse the CSV file and convert it to a JSON object.

However, when it imports to the remote server, an error message is returned: org.json.simple.JSONArray cannot be cast to org.json.simple.JSONObject

I think this might be because my code is creating an array of objects rather than a JSON object. How can I solve the issue so that my script will upload the CSV data and properly map the columns and values to the remote table?

#Requests package for python import requests
import csv
import json
import requests

f = open('example_import_csv.csv', 'rU')
reader = csv.DictReader(f, fieldnames = ("u_date","u_product","u_serial_number"))
out = json.dumps([row for row in reader])
print(out) 

#Set request parameters
url = 'xxxxxx'
user = 'xxxxxx'
pwd = 'xxxxxxxx'

#Set proper headers
headers = {"Content-Type":"application/json","Accept":"application/json"}

response = requests.post(url, auth=(user, pwd), headers=headers ,data=out)

#Check for HTTP codes other than 200
if response.status_code != 200:
    print('Status:', response.status_code, 'Headers:', response.headers, 'Response Text', response.text, 'Error Response:',response.json())
    exit()

#Decode the JSON response into a dictionary and use the data
print('Status:',response.status_code,'Headers:',response.headers,'Response:',response.json())

OUTPUT

[{"u_date": "u_date", "u_product": "u_product", "u_serial_number": "u_serial_number"}, {"u_date": "1/12/15", "u_product": "Apples", "u_serial_number": "11"}, {"u_date": "1/29/15", "u_product": "Pears", "u_serial_number": "12"}, {"u_date": "1/12/15", "u_product": "Oranges", "u_serial_number": "13"}, {"u_date": "1/29/15", "u_product": "Blackberries", "u_serial_number": "14"}, {"u_date": "2/5/15", "u_product": "Blueberries", "u_serial_number": "15"}, {"u_date": "2/7/15", "u_product": "Bananas", "u_serial_number": "16"}, {"u_date": "2/7/15", "u_product": "Strawberries", "u_serial_number": "17"}]

Status: 200 Headers: {'Strict-Transport-Security': 'max-age=15768000; includeSubDomains;','Transfer-Encoding': 'chunked', 'Content-Encoding': 'gzip', 'Content-Type': 'application/json;charset=utf-8'}
Response: {'reason': None, 'error': 'org.json.simple.JSONArray cannot be cast to org.json.simple.JSONObject'}

EDIT:

The request must be a JSON object. I need the format to be a JSON object containing a single array.

I tested by manually creating a json object in this format and it works. How can I parse the CSV file to get it into this particular format?

{"records":[{"u_date": "u_date", "u_product": "u_product", "u_serial_number": "u_serial_number"}, {"u_date": "1/12/15", "u_product": "Apples", "u_serial_number": "11"}, {"u_date": "1/29/15", "u_product": "Pears", "u_serial_number": "12"}, {"u_date": "1/12/15", "u_product": "Oranges", "u_serial_number": "13"}, {"u_date": "1/29/15", "u_product": "Blackberries", "u_serial_number": "14"}, {"u_date": "2/5/15", "u_product": "Blueberries", "u_serial_number": "15"}, {"u_date": "2/7/15", "u_product": "Bananas", "u_serial_number": "16"}, {"u_date": "2/7/15", "u_product": "Strawberries", "u_serial_number": "17"}]}

1 Answer 1

1

Just wrap your array in a suitable dict:

out = json.dumps({'records': [row for row in reader]})

You could also leave the encoding to requests by passing the object to be encoded as json parameter instead of data:

out = {'records': [row for row in reader]}
requests.post(url, json=out, ...)

In this case requests will also automatically set the content type header to application/json, so you don't have to do that manually.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.