python - 用于返回最新并使用不同列的 flask-sqlalchemy 查询

问题描述

我真的很难在 flask-sqlalchemy 中编写我想要的正确 postgres 查询。

下面是我的数据的示例表。我正在尝试返回xxx并获取每个唯一yyy的最新（最新时间戳）。 name

所以理想情况下，对于下面的数据，我希望我的查询返回底部的 4 个条目。

+------------+-------+-----+--------------------------+
|    name    |  xxx  | yyy |           time           | ... (other columns)
+------------+-------+-----+--------------------------+
| aaa        |   2   |  12 | 2021-03-11 20:27:13+00   |
| bbbb       |   9   |  13 | 2021-03-11 20:27:13+00   |
| cccc       |   2   |  16 | 2021-03-11 20:27:13+00   |
| dddd       |   10  |  26 | 2021-03-11 20:27:13+00   |
| aaa        |   4   |  13 | 2021-03-11 20:27:23+00   |
| bbbb       |   8   |  12 | 2021-03-11 20:27:23+00   |
| cccc       |   1   |  15 | 2021-03-11 20:27:23+00   |
| dddd       |   12  |  26 | 2021-03-11 20:27:23+00   |
| aaa        |   3   |  12 | 2021-03-11 20:27:33+00   |
| bbbb       |   6   |  11 | 2021-03-11 20:27:33+00   |
| cccc       |   1   |  17 | 2021-03-11 20:27:33+00   |
| dddd       |   13  |  23 | 2021-03-11 20:27:33+00   |
+------------+-------+-----|--------------------------+

我最基本的返回最新的单个条目如下所示：

single_query = MyModel \
    .query \
    .filter_by(name = 'aaaa) \
    .order_by(desc(MyModel.time)) \
    .first()

使用我的模型，我试图通过如下查询（基于其他一些 SO 答案）来获得我的预期结果：

full_query = MyModel \
    .query \
    .with_entities(MyModel.name, MyModel.time, MyModel.xxx) \
    .distinct(MyMmodel.name) \
    .all()

这让我大部分时间都在那里，但它返回随机条目（似乎至少）。我以为我可以很容易地添加一个order_by(desc(MyModel.time))，但我不能让它与上面的查询一起工作。

关于如何让它发挥作用的任何建议或一些让我朝着正确方向前进的建议？我一直在挠头一段时间。:)

我做了很多搜索，但不知道如何将这样的答案（SQLalchemy distinct, order_by different column）扩展到我的模型查询。

更新

如果我想一次查询两个表，例如通过sample_table_1and ，我如何将 postgres 转换为 flask-sqlalchemy？sample_table_2MyModelMyModel2

实现我想要的原始查询如下：我能够基于@Oluwafemi Sule 的有用答案。我可以扩展这个或解决方案查询以满足我的需要:)

SELECT
  m.name,
  m.xxx,
  m.yyy
  n.xxx,
  n.yyy
FROM (
  SELECT
    name,
    xxx,              
    yyy,
    ROW_NUMBER() OVER(PARTITION BY container_name ORDER BY time DESC) AS rn       
  FROM                          
    sample_table_1
) m, (
  SELECT
    name,
    xxx,
    yyy,
    ROW_NUMBER() OVER(PARTITION BY container_name ORDER BY time DESC) AS rn 
  FROM
    sample_table_2
) n
WHERE m.container_name = n.container_name and m.rn = 1 and n.rn = 1;

标签： pythonpostgresqlsqlalchemyflask-sqlalchemy

获得预期结果的 SQL 查询如下：

SELECT 
  name
  ,xxx
  ,yyy
  ,time 
FROM
 ( 
   SELECT 
     name
     ,xxx
     ,yyy
     ,time
     -- Number rows after partitioning by name and reverse chronological ordering
     ,ROW_NUMBER () OVER (PARTITION BY name ORDER BY time DESC) AS rn
  FROM sample_table
) subquery
WHERE rn = 1;

现在，编写 SQLAlchemy 查询应如下所示：

from sqlalchemy import func

subquery = (
    MyModel
    .query
    .with_entities(
        MyModel.name, 
        MyModel.time, 
        MyModel.xxx, 
        MyModel.yyy,
        func.row_number().over(
            partition_by=MyModel.name,
            order_by=MyModel.time.desc()
        ).label("rn")
    )
)
full_query = (
    MyModel.query.with_entities(
        subquery.c.name,
        subquery.c.time,
        subquery.c.xxx,
        subquery.c.yyy
    )
    .select_from(subquery)
    .filter(subquery.c.rn == 1)
    .all()
)

python - 用于返回最新并使用不同列的 flask-sqlalchemy 查询

问题描述

解决方案

推荐阅读