0
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!

Yaswanth Kumar
  • 459
  • 1
  • 4
  • 10
  • If you're running SQL 2016, you could use `FOR JSON` and do the formatting there. Unfortunately I don't have the expertise to help with the python aspect. Resource if you're interested https://msdn.microsoft.com/en-us/library/dn921882.aspx – Xedni Jan 31 '17 at 21:18
  • Thanks for the link! unfortunately I'm using SQL Server 2012 – Yaswanth Kumar Jan 31 '17 at 21:41
  • This is hardly a direct replacement, but it got me thinking how you might go about this. The response to this question seemed like it had some similar traits to what you're trying to do. http://stackoverflow.com/questions/23255512/creating-nested-json-structure-with-multiple-key-values-in-python-from-json – Xedni Jan 31 '17 at 22:08

1 Answers1

0
 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
Yaswanth Kumar
  • 459
  • 1
  • 4
  • 10