[FIXED] How to re-structure the below JSON data which is a result set of SQL query using cursors in python

Issue

def query_db(query, args=(), one=False):
cur = connection.cursor()
cur.execute(query, args)
r = [dict((cur.description[i][0], value)
           for i, value in enumerate(row)) for row in cur.fetchall()]
cur.connection.close()
return (r[0] if r else None) if one else r
my_query = query_db("select top 1 email as email_address,status = 'subscribed',firstname,lasstname from users")
json_output = json.dumps(my_query)
print json_output

Result is this:

    [{
    "status": "subscribed",
    "lastname": "Engineer",
    "email": "theengineer@yahoo.com",
    "firstname": "The"}]

what I want is this

    {
    "email_address":"yash@yahoo.com", 
    "status":"subscribed",
    'merge_fields': {
       'firstname': 'yash',
       'lastname': 'chakka',
     }

I don't have any column called merge-fields in database but, I want this merge-fields header for every email-id with first name and last name under it to post it to Mailchimp. what modification do i have to do to my cursor get desired output. Any help will be appreciated. Thanks!

Solution

 I'm adding this so that future users of mailchimp API version3 can get an idea of how I achieved this. 

Here is what i did I've added another function which accepts values in other word I capture all the values which are resulted from myquery and pass it to this below function

def get_users_mc_format(query):
users = query_db(query)
new_list = []
for user in users:
    new_list.append({
        "email_address": user["email"],
        "status": user["status"],
        "merge_fields": {
            "FNAME": user["firstname"],
            "LNAME": user["lastname"],
        },
        "interests": {
            "1b0896641e": bool(user["hardware"]),
        }
    })
return new_list

Answered By - Yaswanth Kumar

Answer Checked By - Katrina (PHPFixing Volunteer)