CODEWITHSUNDEEP
pythonjson
astroboy1
astroboy1

Reputation: 197

modify the sql result to json in python

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"}]}

Upvotes: 0

Views: 38

Answers (1)

Mat
Mat

Reputation: 1403

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

Upvotes: 1

Related Questions