Python - Json : Keyerror

Question

I’m making the call in a Mailchimp API in which I list the fields to insert into my DB. In the JSON provided by the documentation, the following fields appear:

{
"automations": [
  {
    "id": "b0a1c24f1a",
    "create_time": "2015-09-15T14:31:54+00:00",
    "start_time": "2015-09-15T15:45:32+00:00",
    "status": "paused",
    "emails_sent": 1,
    "recipients": {
      "list_id": "57afe96172"
    },
    "settings": {
      "title": "Freddie's Best Jokes",
      "from_name": "Freddie",
      "reply_to": "[email protected]",
      "use_conversation": false,
      "to_name": "*|FNAME|*",
      "authenticate": true,
      "auto_footer": false,
      "inline_css": false
    },
    "tracking": {
      "opens": true,
      "html_clicks": true,
      "text_clicks": true,
      "goal_tracking": true,
      "ecomm360": true,
      "google_analytics": "Freddie_s_Best_Jokes9_15_2015",
      "clicktale": ""
    },
    "trigger_settings": {
      "workflow_type": "emailSeries",
      "send_immediately": false,
      "trigger_on_import": false,
      "runtime": {
        "days": [
          "sunday",
          "monday",
          "tuesday",
          "wednesday",
          "thursday",
          "friday",
          "saturday"
        ],
        "hours": {
          "send_at": "12:00am"
        }
      },
      "workflow_emails_count": 1
    },
    "report_summary": {
      "opens": 1,
      "unique_opens": 1,
      "open_rate": 1,
      "clicks": 0,
      "subscriber_clicks": 0,
      "click_rate": 0
    },
    "_links": [
      {
        "rel": "parent",
        "href": "https://usX.api.mailchimp.com/3.0/automations",
        "method": "GET",
        "targetSchema": "https://api.mailchimp.com/schema/3.0/Automations/Collection.json",
        "schema": "https://api.mailchimp.com/schema/3.0/CollectionLinks/Automations.json"
      },
      {
        "rel": "self",
        "href": "https://usX.api.mailchimp.com/3.0/automations/b0a1c24f1a",
        "method": "GET",
        "targetSchema": "https://api.mailchimp.com/schema/3.0/Automations/Instance.json"
      },
      {
        "rel": "start-all-emails",
        "href": "https://usX.api.mailchimp.com/3.0/automations/b0a1c24f1a/actions/start-all-emails",
        "method": "POST"
      },
      {
        "rel": "pause-all-emails",
        "href": "https://usX.api.mailchimp.com/3.0/automations/b0a1c24f1a/actions/pause-all-emails",
        "method": "POST"
      },
      {
        "rel": "emails",
        "href": "https://usX.api.mailchimp.com/3.0/automations/b0a1c24f1a/emails",
        "method": "GET",
        "targetSchema": "https://api.mailchimp.com/schema/3.0/Automations/Emails/Collection.json"
      },
      {
        "rel": "removed-subscribers",
        "href": "https://usX.api.mailchimp.com/3.0/automations/b0a1c24f1a/removed-subscribers",
        "method": "GET",
        "targetSchema": "https://api.mailchimp.com/schema/3.0/Automations/RemovedSubscribers/Collection.json"
      }
    ]
  }
],
"total_items": 1,
"_links": [
  {
    "rel": "parent",
    "href": "https://usX.api.mailchimp.com/3.0/",
    "method": "GET",
    "targetSchema": "https://api.mailchimp.com/schema/3.0/Root.json"
  },
  {
    "rel": "self",
    "href": "https://usX.api.mailchimp.com/3.0/automations",
    "method": "GET",
    "targetSchema": "https://api.mailchimp.com/schema/3.0/Automations/Collection.json",
    "schema": "https://api.mailchimp.com/schema/3.0/CollectionLinks/Automations.json"
  }
]
  }

That’s the code I’m making the call:

from mailchimp3 import MailChimp
import psycopg2

client = MailChimp('KEY', 'USER')
r = client.automations.all(get_all=True)


conn = psycopg2.connect("dbname='db' user='user' host='host' 
password='password'")
insert = "INSERT INTO table(status,list_is_active,title,emails_sent,unique_opens,subscriber_clicks,open_rate,click_rate) VALUES"

info = r

gravar=[]
for infos in info['automations']:
    gravar.append((
                   infos['status'],
                   infos['recipients']['list_is_active'],
                   infos['settings']['title'],
                   infos['emails_sent'],
                   infos['report_summary']['unique_opens'],
                   infos['report_summary']['subscriber_clicks'],
                   infos['report_summary']['open_rate'],
                   infos['report_summary']['click_rate']

                ))

if len(gravar) >0 :
    cur = conn.cursor()
    y = b','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s)", x) for x in gravar)
    comando = insert + y.decode()
    try:
        cur.execute("TRUNCATE TABLE tabela")
        cur.execute(comando)
        conn.commit()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)

    cur.close()
    print('Carga Completa')
else:
    conn.close()
    print('Nada a Inserir')

When executed, returns the following error:

Exception has occurred: KeyError
'report_summary'

In the append, I must call this field some other way?

"report_summary": {
      "opens": 1,
      "unique_opens": 1,
      "open_rate": 1,
      "clicks": 0,
      "subscriber_clicks": 0,
      "click_rate": 0
    }

Answer 1

For you to understand your mistake do the following:

1) Place the json you present at the beginning of your question in a file called Infos.json (control-c/Contorl-v).

2) Open a python terminal and do:

import json
with open("infos.json", "r") as read_file:
    data = json.load(read_file) 

data['automations'][0]['report_summary'] 
{'opens': 1,
 'unique_opens': 1,
 'open_rate': 1,
 'clicks': 0,
 'subscriber_clicks': 0,
 'click_rate': 0}

# Veja que o json tem 3 elementos
len(data)
3

# Veja a "estrutura" do Json
data.keys()                                                                                                                        
dict_keys(['automations', 'total_items', '_links'])

Explaining:
The data in the json that you initially presented is stored in a structure where "Automations" is a list of dictionaries, and report_summary is a dictionary within that list, so for Acces it you need to specify which one of them (in case there is only one element in the list, try print(len(data['automations'])), therefore: data['automations'][0]['report_summary']