CODEWITHSUNDEEP
pythonsqlsqlalchemy
Xiaolong
Xiaolong

Reputation: 259

sqlalchemy: previous row and next row by id

I have a table Images with id and name. I want to query its previous image and next image in the database using sqlalchemy. How to do it in only one query?

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?
...

Suppose it is possible that some rows have been deleted, i.e., the ids may not be continuous. For example,

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

Upvotes: 4

Views: 4065

Answers (4)

snakecharmerb
snakecharmerb

Reputation: 55620

This can be accomplished in a "single" query by taking the UNION of two queries, one to select the previous and target records and one to select the next record (unless the backend is SQLite, which does not permit an ORDER BY before the final statement in a UNION):

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)

producing

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

Note that we could make the second query the same as the first, apart from reversing the inequality

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

and we would get the same result as the union would remove the duplicate target row.

If the requirement were to find the surrounding rows for a selection of rows we could use the lag and lead window functions, if they are supported.

# 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)

Output:

{'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}

Upvotes: 0

Gaston Traberg
Gaston Traberg

Reputation: 111

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()

Upvotes: 11

van
van

Reputation: 76962

# 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)

Upvotes: 0

freedom
freedom

Reputation: 751

To iterate through your records. I think that this is what you're looking for.

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

Upvotes: -6

Related Questions