HubDB API along with the Blog Posts API to batch create/publish 152 blog entries

[josephLayden]

Hi all. Im looking for some advice on using the HubDB API along with the Blog Posts API to create/publish a batch of 152 blog posts. I have a HubDB set up with all of the data. The same HubDB also is being used by a custom module to render an embargoed preview of the blog posts. Each row in the HubDB contains all of the content for a blog post. There are 152 rows of data in the DB, one for each post. I am able to publish a single post and receive a 201 status, but then receive a TypeError that breaks the function from completing more than one blog entry/row. I have included the error from Terminal at the end. Unfortunately I do not understand how to solve the error. I have been trying to debug and look up errors, but seem to be stuck in a negative feedback loop. Any advice appreciated. I am open to new approaches as well.

def get_single_row(table_id):
  """Get table by row
  GET /hubdb/api/v2/tables/:tableId/rows
  """
  xurl = "/hubdb/api/v2/tables/" + str(table_id) + "/rows?portalId=" + str(PORTAL_ID)
  url = HS_API_URL + xurl
  response = urlopen(url).read()
  table_data = json.loads(response)
  i=0
  # print(table_data)
  for key, value in table_data.items():
      blog_author_id = value[i]["values"]["2"]
      campaign = value[i]["values"]["3"]
      campaign_name = value[i]["values"]["4"]
      content_group_id = value[i]["values"]["5"]
      featured_image = value[i]["values"]["6"]
      post_body = value[i]["values"]["10"]
      slug = value[i]["values"]["14"]
      meta_description = value[i]["values"]["18"]
      year = value[i]["values"]["19"]
      quarter = value[i]["values"]["20"]
      state_name = value[i]["values"]["21"]
      market_name = value[i]["values"]["22"]
      i+=1

      POST = {
        "blog_author_id": blog_author_id,
        "campaign": campaign,
        "campaign_name": campaign_name,
        "content_group_id": content_group_id,
        "featured_image": featured_image,
        "topic_ids": [year, quarter, market_name, state_name],
        "meta_description": meta_description,
        "name": meta_description,
        "post_body": post_body,
        "publish_date": 1543520639,
        "publish_immediately": False,
        "slug": slug,
        "use_featured_image": True
      }
      # print(POST)
      xurl = "/content/api/v2/blog-posts"
      url = HS_API_URL + xurl + APIKEY
      headers = { "content-type" : "application/json" }
      response = requests.post(url, headers=headers, data=json.dumps(POST))
      # print(response.status_code, response.content, response)


get_single_row(MEOS_Q1_2019)

Error:

  Traceback (most recent call last):
  File "get_tables.py", line 71, in <module>
    get_single_row(MEOS_Q1_2019)
  File "get_tables.py", line 34, in get_single_row
    blog_author_id = value[i]["values"]["2"]
 TypeError: 'int' object is not subscriptable

My HubDB has 20 columns of data and 152 rows. But when I try to return the JSON object with key, value pairs I only receive 12. Any thoughts on how to access the full HubDB? I am loading my JSON then printing the key, value of items.

for key, value in table_data.items():
    print(value[i]["values"])

Response:

{  
 'objects':[  
  {  
     'id':6657630003,
     'createdAt':1543515074733,
     'path':None,
     'name':None,
     'values':{  
        '18':'1 Dec 2018 | Positive Job Market Expected in Final Quarter of 2018 for Alabama',
        '2':'5521284303',
        '19':'5684804132',
        '3':'71df6020-8385-4e2e-9a36-23e708fa4605',
        '20':'5634648902',
        '4':'MEOS NA',
        '21':'5634648855',
        '5':'5664168304',
        '22':'5634522727',
        '6':'https://go.manpowergroup.com/hubfs/MEOS/2019_Q1/MEOS_Q1_2019_Infographic.png',
        '10':"<embed src='https://drive.google.com/viewerng/viewer?embedded=true&amp;url=https://go.manpowergroup.com/hubfs/MEOS/2018_Q4/Hopeful%20Job%20Market%20Expected%20in%20Final%20Quarter%20of%202018%20for%20Milwaukee-Waukesha-West%20Allis,%20WI%20MSA.pdf' width='500' height='675'>",
        '14':'Q1_2019/alabama_al'
     },
     'isSoftEditable':False,
     'childTableId':0
  }
],
'total':1,
'limit':1000,
'offset':0,
'message':None,
'totalCount':1
}

[josephLayden · Accepted solution]

I posted some function definitions in Slack. @Matt_Coley replied with assistance, guiding me through several issues with my program. I have posted the updated (and working) code to github as a gist here.

I was incorrectly looping through the HubDB rows, causing a TypeError to occur. Incorrect code for example:

    for key, value in table_data.items():

The corrected code to loop through a row in the DB and use the each column in the row as a content item in the blog post. Working example:

    for row in table_data['objects']: