Python Forum
Problem adding keys/values to dictionary where keynames = "property" and "value" - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Problem adding keys/values to dictionary where keynames = "property" and "value" (/thread-23237.html)



Problem adding keys/values to dictionary where keynames = "property" and "value" - jasonashaw - Dec-17-2019

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


RE: Problem adding keys/values to dictionary where keynames = "property" and "value" - jasonashaw - Dec-17-2019

I found the answer. The json_data1 = {} just needed to be moved inside the loop. Yay!