Dec-17-2019, 06:55 PM
See my code below. After about 30 hours I am close, but cannot figure out how to add a new {key , value} for each column in the mysql result set.
I am looping through each column but as the values write they overwrite each time and I end up with the value of the last column for all the keys.
My goal is this result:
{"email": "[email protected]", "properties": [{"property": "firstname", "value": "Codey"},{"property": "lastname","value": "Huang"}]}
but we are getting this:
{"email": "[email protected]", "properties": [{"property": "lastname", "value": "Huang"}, {"property": "lastname", "value": "Huang"}]}
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})
rows = cursor.fetchall()
json_email = {}
json_data1 = {}
json_data = []
# number columns in query set
x = len(rows[0])
for result in rows:
json_email[cursor.column_names[0]] = result[0]
i = 1
while i < x:
json_data1['property'] = cursor.column_names[i]
json_data1['value'] = result[i]
i = i + 1
json_data.append(json_data1)
json_email['properties'] = json_data
print(json.dumps(json_email))
Thanks!
Jason
I am looping through each column but as the values write they overwrite each time and I end up with the value of the last column for all the keys.
My goal is this result:
{"email": "[email protected]", "properties": [{"property": "firstname", "value": "Codey"},{"property": "lastname","value": "Huang"}]}
but we are getting this:
{"email": "[email protected]", "properties": [{"property": "lastname", "value": "Huang"}, {"property": "lastname", "value": "Huang"}]}
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})
rows = cursor.fetchall()
json_email = {}
json_data1 = {}
json_data = []
# number columns in query set
x = len(rows[0])
for result in rows:
json_email[cursor.column_names[0]] = result[0]
i = 1
while i < x:
json_data1['property'] = cursor.column_names[i]
json_data1['value'] = result[i]
i = i + 1
json_data.append(json_data1)
json_email['properties'] = json_data
print(json.dumps(json_email))
Thanks!
Jason