使用sqlalchemy的row_to_json的语法 [英] Syntax for row_to_json with sqlalchemy

问题描述

我想弄清楚如何将PostgreSQL(9.2)row_to_json与SqlAlchemy一起使用.但是我还无法提出任何有效的语法.

I would like to figure out how to use Postgres' (9.2) row_to_json with SqlAlchemy. However I haven't been able to come up with any working syntax.

details_foo_row_q = select([Foo.*]
    ).where(Foo.bar_id == Bar.id
).alias('details_foo_row_q')

details_foo_q = select([
    func.row_to_json(details_foo_row_q).label('details')
]).where(details_foo_row_q.c.bar_id == Bar.id
).alias('details_foo_q')

理想情况下，我希望不必键入表格模型中的每个字段.

I would ideally like to not to have to type out each and every field from the table model if possible.

从"mn"那里得到答案:

Got the answer from 'mn':

应该更像这样:

details_foo_row_q = select([Foo]).where(Foo.bar_id == Bar.id).alias('details_foo_row_q')

details_foo_q = select([
    func.row_to_json(literal_column(details_foo_row_q.name)).label('details')
]).select_from(details_foo_row_q).where(
    details_foo_row_q.c.bar_id == Bar.id
).alias('details_foo_q')

谢谢你，很棒！

推荐答案

您的查询生成了不正确的SQL

Your query generates an incorrect SQL

SELECT row_to_json(SELECT ... FROM foo) AS details
FROM (SELECT ... FROM foo) AS details_foo_row_q

应该是

SELECT row_to_json(details_foo_row_q) AS details
FROM (SELECT ... FROM foo) AS details_foo_row_q

您需要使用select作为 literal_column

You need to use select as literal_column

from sqlalchemy.sql.expression import literal_column

details_foo_q = select([
    func.row_to_json(literal_column(details_foo_row_q.name)).label('details')
]).select_from(details_foo_row_q).where(
    details_foo_row_q.c.bar_id == Bar.id
).alias('details_foo_q')

这篇关于使用sqlalchemy的row_to_json的语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！