I'm making the call into a MailChimp API in which I ready 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"
}
]
}
This is the code I'm calling:
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 I execute, it returns the following error:
Exception has occurred: KeyError
'report_summary'
In append, should I call this field in any other way?
"report_summary": {
"opens": 1,
"unique_opens": 1,
"open_rate": 1,
"clicks": 0,
"subscriber_clicks": 0,
"click_rate": 0
}