如何让flask-sqlalchemy的查询对象选择列（或说字段）？

发布于 2024-12-01 23:25:34 字数 823 浏览 1 评论 0原文

我遇到了一些困扰我一整天的问题。我正在使用flask/flask-sqlalchemy/postgresql，我想这样做：

published_sites = msg_published.query \
            .filter( and_(*filter_clause) ) \
            .group_by( msg_published.site_id ) \
            .order_by( order_clause ) \
            .paginate( page_no, per_page, error_out = False )

但在mysql中，这是可以的，而在postgresql中，它是错误的，并在group by子句或a中要求除site_id之外的其他字段聚合函数，我知道 postgresql 对 SQL 比 mysql 更严格，所以我必须在 msg_published 的查询对象中选择 site_id ，但在纯 sqlalchemy 中我可以这样做

published_sites = session.query( msg_published.site_id ) \
            .filter( and_(*filter_clause) ) \
            .group_by( msg_published.site_id ) \
            .order_by( order_clause ) \
            .paginate( page_no, per_page, error_out = False )

： Flask-sqlalchemy，如何让它工作？

原文

I met some problem that wired me the whole day. I'm using flask/flask-sqlalchemy/postgresql, and I want to do this:

published_sites = msg_published.query \
            .filter( and_(*filter_clause) ) \
            .group_by( msg_published.site_id ) \
            .order_by( order_clause ) \
            .paginate( page_no, per_page, error_out = False )

but in mysql, it is OK, and in postgresql it is wrong and ask for the other fields besides site_id either in a group by clause or in a aggregation function, I know that postgresql is stricter on SQL than mysql , so I must select the site_id in the query object of msg_published, but in pure sqlalchemy I can do like this:

published_sites = session.query( msg_published.site_id ) \
            .filter( and_(*filter_clause) ) \
            .group_by( msg_published.site_id ) \
            .order_by( order_clause ) \
            .paginate( page_no, per_page, error_out = False )

and in flask-sqlalchemy, how to get it work?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蛮可爱 2024-12-08 23:25:34

你大部分时间都到了那里。要在 PostgreSQL 中执行 MySQL 允许的操作，需要子选择。

published_sites_ids = session.query( msg_published.site_id ) \
            .filter( and_(*filter_clause) ) \
            .group_by( msg_published.site_id ) \
            .order_by( order_clause ) \
            .paginate( page_no, per_page, error_out = False )

published_sites = session.query(msg_published) \
                         .filter(msg_published.id.in_(published_sites_ids))

You're most of the way there. to do in PostgreSQL what MySQL allows requires a subselect.

published_sites_ids = session.query( msg_published.site_id ) \
            .filter( and_(*filter_clause) ) \
            .group_by( msg_published.site_id ) \
            .order_by( order_clause ) \
            .paginate( page_no, per_page, error_out = False )

published_sites = session.query(msg_published) \
                         .filter(msg_published.id.in_(published_sites_ids))

回复收藏 0 原文

~没有更多了~