Formatting MySQL results in the correct property/value JSON format for Hubpot

[jasonashaw]

I am pulling from a MySQL database using Python and trying to get the data into the specific JSON format to work with Hubspot.

This is our code:

cursor.execute("SELECT email, firstname, lastname from users")
row_headers = [x[0] for x in cursor.description]

rows = cursor.fetchall()

# Print rows in json format
json_data = []
   for result in rows:
       json_data.append(dict(zip(row_headers, result)))
   print(json.dumps(json_data))

Resulting in this format:

[
  {
     "firstname": "Codey",
     "lastname": "Huang",
     "email": "testingapis@hubspot.com"
  }
]

But we need it in this format.

{
   "email": "testingapis@hubspot.com",
   "properties": [
        {
            "property": "firstname",
            "value": "Codey"
        },
        {
            "property": "lastname",
            "value": "Huang"
        }
]
}

Is this format required?

What is this JSON format called?
and do you have suggestions on how to do this using Python?

Thank you very much!

Thanks,
Jason

[WendyGoh]

Hey @jasonashaw,

Just to clarify your end goal here, you're looking to format the results that you fetch from your database so that it matches the format for the Create or update a group of contacts | Contacts API?

If so, yes this is a required format. The format is json object within an array and for this you can look into resources such as,

How to parse JSON Array of objects in python - Stack Overflow

how to add element to json list - python - Stack Overflow

Tag: JSON - Python Tutorial

[jasonashaw]

I am now much closer. The problem was that I needed to move this inside the column loop: json_data1 = {}.

This works perfectly for a single record, but now the rows are overwriting each other because the key name "email" looks like a duplicate, so instead of adding a new dictionary it is updating the existing dictionary.

I think I've read every topic written the the web trying to figure this out...

I'm at a complete loss now....

# CODE WORKS PERFECTLY FOR A SINGLE ROW. FOR MULTIPLE ROWS IT OVERWRITES AND ONLY INCLUDES THE LAST RECORD VALUES.

import json
import mysql.connector
# from hubspot3.contacts import ContactsClient

# Test Acct
API_KEY = "#############################################"

mydb = mysql.connector.connect(
host="hostname",
user="username",
passwd="password",
database="database"
)

cursor = mydb.cursor()
select_stmt = "SELECT core_users.email, core_users.firstname, core_users.lastname FROM core_users WHERE (core_users.id=%(user)s)"
cursor.execute(select_stmt, {'user': 117405})

# JSON style 1
rows = cursor.fetchall()
json_email = {}

# columns in query set
x = len(rows[0])

for result in rows:
     json_data = []
     json_email1 = {}

     json_email1[cursor.column_names[0]] = result[0]

          for i in range(1, x):
          json_data1 = {}  #<-- THIS FIXED THE FIRST PROBLEM
          json_data1['property'] = cursor.column_names[i]
          json_data1['value'] = result[i]
          json_data.append(json_data1)

          json_email1['properties'] = json_data
          json_email.update(json_email1)   #<--- THIS IS THE PROBLEM. OVERWRITES INSTEAD OF ADDING NEW DICT.
          json_email1 = {}

print(json.dumps(json_email))

[WendyGoh]

Hey @jasonashaw,

When looking at the code, I noticed this line:

cursor.execute(select_stmt, {'user': 117405})

Digging deeper into this <here>, it looks to me that you're pulling only one data and that is user 117405? Could this be the reason why you're seeing overwrites?

If that's not it, I'm also curious to learn why did you place 

     json_email1[cursor.column_names[0]] = result[0]

above the for loop instead of looping through all the return response? 

[jasonashaw]

I had that one user there for testing. It was overwriting because I wasn't putting it in a list and was trying to put it just in a dictionary which wouldn't accept another dict with the same key - but does in a list, and I realzied that the Hubspot requirement does have all the results in a list which I missed the first time I looked at it.

I put this here: 

json_email1[cursor.column_names[0]] = result[0]

to pull out the emai which was the primary key before the rest of the data which is in a list.

Then loop through the rest of the results from [1] to [x].

Just have to make sure to put the email as the first column in the query.

[WendyGoh]

Hey @jasonashaw,

Thanks for sharing the solution that works for you! I'm glad all's well! 🙂 

[jasonashaw · Accepted solution]

Hi Wendy - I got it!

It seems to be working perfectly.

I hope this example code can help save some users time. Please let me know if you have any suggestions for improvement.

=======================================================

import json
import mysql.connector
import requests

# Test Acct
API_KEY = "#############################################"

mydb = mysql.connector.connect(
    host="hostname",
    user="username",
    passwd="password",
    database="database"
)

cursor = mydb.cursor()
# must be set to midnight UTC for the date you want
# had to convert the date to unix_timestamp, convert to midnight and then back to unix_timestamp *1000 for milliseconds

# Email must be the first column in select

select_stmt = "SELECT core_users.email, (unix_timestamp(DATE_FORMAT(unix_timestamp(core_users.createDate), '%Y-%m-%d 00:00:00'))*1000) AS mpc_create_date_2 FROM core_users WHERE (core_users.id in (117405,117406))"
cursor.execute(select_stmt)

# convert rows and columns to JSON
rows = cursor.fetchall()
json_email = {}
json_complete = []

# number of columns in query set
x = len(rows[0])

# loop through rows
for result in rows:
    json_data = []
    json_email1 = {}
    json_email1[cursor.column_names[0]] = result[0]

         # loop through query columns
         for i in range(1, x):
              json_data1 = {}
              json_data1['property'] = cursor.column_names[i]
              json_data1['value'] = result[i]
              json_data.append(json_data1)

         json_email1['properties'] = json_data
         json_complete.append(json_email1)
         json_email1 = {}

# output
print(json.dumps(json_complete))

url = "https://api.hubapi.com/contacts/v1/contact/batch"

querystring = {"hapikey": "#############################################"}

payload = json.dumps(json_complete)

headers = {
       'Content-Type': "application/json",
}

response = requests.request(
        "POST", url, data=payload, headers=headers, params=querystring)

print(response.status_code)