sqlalchemy: previous row and next row by id

Question

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 `id`s may not be continuous. For example, ``` Table: Images ------------ id | name ------------ 1 | 'a.jpg' 2 | 'b.jpg' 4 | 'd.jpg' ------------ ```

Accepted Answer

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

sqlalchemy: previous row and next row by id

Solution

Problem