SqlAlchemy prev / next accessors?

Question

Is there a way to get previous / next records from a SqlAlchemy request? For example:

record.id record.next.id record.prev.id 

I could add the previous / next methods for each model on my own, but I wonder if there is any kind of auto-path that I forgot. The corresponding web application is written in Pylons.

Edit: This is probably a dirty hack, but it works. I imported the sqlalchemy Session Session object into my model and did the following:

 def next(self): return Session.query(Blog).filter(Blog.id > self.id).order_by(Blog.id).first() def prev(self): return Session.query(Blog).filter(Blog.id < self.id).order_by(desc(Blog.id)).first() 

Answer 1

What I am proposing to do is your original query with a query of -1 and +1, so that you can easily get the identifier without having to run another query saving time while processing on the SQL server.

Your solution definitely works, but requires an additional two queries. You could even make it a subquery, so if you just want to get one row and throw out the previous / next, then the data is stored in the column created by the subquery.

Answer 2

Plus 1 and minus 1 in the last database record will work sometimes. What if you delete a couple of recent entries. The next id will not be plus one or minus one on the last id. This is not a session subject, this is a mysql thing. To get the next identifier, you will need to make some queries in the information schema as follows.

 q = "SELECT auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE table_name='table_name'" a = s.execute(q) #where s is sqlalchemy session for b in a: print b print b[0] 

which will give you something like: (163459L,) 163459

In my case, this is true.

And for prev, you can always do .first () on the last entry. This is the previous entry.

Answer 3

Here is what I use to extract next / prev from an object using sqlalchemy. The idea is to get the row_number of the object from the source list and execute another query with offset +/- 1.

I use a subquery with ROW_NUMBER ( How to use ROW_NUMBER ()? ) To retrieve row_number from the original query:

 row_number = func.row_number().over(order_by=orders).label("row_number") row_number_subquery = self.getlistquery(columns=chain([row_number], pk_columns)).subquery() 

Then I join the model on primary keys:

 join_clause = [getattr(row_number_query.c, pk.key) == getattr(model, pk.key) for pk in pk_columns] 

I filter the main keys of the object:

 filter = [getattr(model, pk.key) == pk_values[index] for index, pk in enumerate(pk_columns)] 

I create a final query to get the offset of the object:

 offset = db.session.query(row_number_subquery).join(model, *join_clause).filter(*filter).first() 

Then the element contains the offset needed for the next or offset-2 for the previous one (starting from 1 for row_number and 0 for offset)

 self.getlistquery().offset(offset)