在python中将sql结果修改为json

Question

我知道这可能是一个0级问题，但我被困在这里了

我正在使用SQL炼金术来获得SQL结果

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

上面代码段的结果是

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

但是我怎样才能得到有名字的回复呢

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

Answer 1

你可以做以下事情

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

注意：您也不需要调用conn.close()。默认情况下，这将在with db.connect()上下文管理器的末尾调用

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