modify the sql result to json in python

Question

I know this might be a level 0 question, but I'm stuck here

I'm using SQL Alchemy to get the sql result.

with db.connect() as conn:
    data = conn.execute('''select * from tables''')
    json_data = json.dumps([(dict(row.items())) for row in data])
    conn.close()
    return json_data

The result for the above snippet is

[{"id": 1, "name": "Ashton", "age": 20, "nationality": "US"}, {"id": 2, "name": "Kevin", "age": 20, "nationality": "US"}]

But how do I get the response with a name?

{"citizens":[{"id": 1, "name": "Ashton", "age": 20, "nationality": "US"}, {"id": 2, "name": "Kevin", "age": 20, "nationality": "US"}]}

Mat · Accepted Answer · 2021-05-11 13:41:36Z

1

You could do the following...

with db.connect() as conn:
    data = conn.execute('''select * from tables''')
    json_data = json.dumps({"citizens": [(dict(row.items())) for row in res]})
    return json_data

Note: you also don't need call conn.close(). This is called by default at the end of the with db.connect() context manager.

https://docs.sqlalchemy.org/en/14/core/connections.html#basic-usage

answered May 11, 2021 at 13:41

Mat

1,42311 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

modify the sql result to json in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related