python json dumps 将对象放入 row_to_json 返回的对象中

Question

我在 python 中运行一个 Postgres 查询，它返回一个 JSON 对象数组。

    db = SQLDB('postgres://postgres:*****@localhost:5433/postgres', migrate=False)
    q = ("SELECT row_to_json(t) FROM (SELECT * FROM fn_drivetime_eu_grid_" + request.get_vars.country + "_coord(" + request.get_vars.x + ", " + request.get_vars.y + ", " + request.get_vars.sec + ")) t;")
    mySQL = db.executesql(q)
    return json.dumps(mySQL)

问题是对象在对象内部。天哪!

问题不大，但我想知道是否有更优雅的解决方案。

Answer 1

如果您转储整个结果集，就会发生这种情况。使用 t 表:

create table t (a int, b text);
insert into t (a, b) values (1,'x'), (2,'y');

使用 Psycopg2:

query = "select row_to_json(t) from t"
cursor.execute(query)
rs = cursor.fetchall()

# dump the whole result set
print json.dumps(rs)
print

# dump each column:
for r in rs:
    print json.dumps(r[0])
con.close()

输出:

[[{"a": 1, "b": "x"}], [{"a": 2, "b": "y"}]]

{"a": 1, "b": "x"}
{"a": 2, "b": "y"}