SQLAlchemy: 根据id获取上一行和下一行

Question

我有一张名为Images的表，其中包含id和name。我想使用sqlalchemy在数据库中查询其前一张图片和后一张图片。如何只用一个查询实现？

sel = select([images.c.id, images.c.name]).where(images.c.id == id)
res = engine.connect().execute(sel)

#How to obtain its previous and next row?
...

假设某些行已被删除，即 id 不可能连续。例如：

Table: Images
------------
id | name
------------
1  | 'a.jpg'
2  | 'b.jpg'
4  | 'd.jpg'
------------

- Xiaolong

Answer 1

prev_image = your_session.query(Images).order_by(Images.id.desc()).filter(Images.id < id).first()

next_image = your_session.query(Images).order_by(Images.id.asc()).filter(Images.id > id).first()

- Gaston Traberg

---

如果你需要使用datetime而不是id，请阅读以下内容 https://gist.github.com/danielthiel/8374607 SQLAlchemy：按日期过滤datetime字段（在SQLite中无法工作，在PostgreSQL中可以正常工作） - Márcio Moreira

我想知道第一条和最后一条记录会发生什么。 - MattSom

Answer 2

可以通过将两个查询的UNION进行组合来在“单个”查询中完成此操作，一个查询用于选择前一个和目标记录，另一个查询用于选择下一个记录（除非后端是SQLite，否则不允许在UNION的最终语句之前使用ORDER BY）：

import sqlalchemy as sa
...
with engine.connect() as conn:
    target = 3
    query1 = sa.select(tbl).where(tbl.c.id <= target).order_by(tbl.c.id.desc()).limit(2)
    query2 = sa.select(tbl).where(tbl.c.id > target).order_by(tbl.c.id.asc()).limit(1)
    res = conn.execute(query1.union(query2))
    for row in res:
        print(row)

生成

(2, 'b.jpg')
(3, 'c.jpg')
(4, 'd.jpg')

请注意，我们可以使第二个查询与第一个查询相同，除了反转不等式。

query2 = sa.select(tbl).where(tbl.c.id >= target).order_by(tbl.c.id.asc()).limit(2)

我们将得到与联合操作相同的结果，因为联合操作会删除重复的目标行。

---

如果要找到一组选定行的周围行，我们可以使用窗口函数lag和lead（如果支持的话）。

# Works in PostgreSQL, MariaDB and SQLite, at least.

with engine.connect() as conn:
    query = sa.select(
        tbl.c.id,
        tbl.c.name,
        sa.func.lag(tbl.c.name).over(order_by=tbl.c.id).label('prev'),
        sa.func.lead(tbl.c.name).over(order_by=tbl.c.id).label('next'),
    )
    res = conn.execute(query)
    for row in res:
        print(row._mapping)

输出：

{'id': 1, 'name': 'a.jpg', 'prev': None, 'next': 'b.jpg'}
{'id': 2, 'name': 'b.jpg', 'prev': 'a.jpg', 'next': 'c.jpg'}
{'id': 3, 'name': 'c.jpg', 'prev': 'b.jpg', 'next': 'd.jpg'}
{'id': 4, 'name': 'd.jpg', 'prev': 'c.jpg', 'next': 'e.jpg'}
{'id': 5, 'name': 'e.jpg', 'prev': 'd.jpg', 'next': 'f.jpg'}
{'id': 6, 'name': 'f.jpg', 'prev': 'e.jpg', 'next': None}

- snakecharmerb

Answer 3

# previous
prv = select([images.c.id, images.c.name]).where(images.c.id < id).order_by(images.c.id.desc()).limit(1)
res = engine.connect().execute(prv)
for res in res:
    print(res.id, res.name)

# next
nxt = select([images.c.id, images.c.name]).where(images.c.id > id).order_by(images.c.id).limit(1)
res = engine.connect().execute(nxt)
for res in res:
    print(res.id, res.name)

- van

Answer 4

要遍历您的记录。我认为这就是您要找的。

for row in res:
  print row.id
  print row.name

- freedom

---

这不是OP所询问的内容。此外，他的示例查询是针对单个记录而不是记录集合的。 - MattSom