SQLAlchemy and going through a large result set

pagination sqlalchemy
Kevin Burke picture Kevin Burke · Mar 28, 2012 · Viewed 47.3k times · Source

I need to read data from all of the rows of a large table, but I don't want to pull all of the data into memory at one time. Is there a SQLAlchemy function that will handle paging? That is, pull several rows into memory and then fetch more when necessary.

I understand you can do this with limit and offset as this article suggests, but I'd rather not handle that if I don't have to.

Answer

radeklos picture radeklos · Nov 12, 2013

If you are using Flask-SqlAlchemy, see the paginate method of query. paginate offers several method to simplify pagination.

record_query = Record.query.paginate(page, per_page, False)
total = record_query.total
record_items = record_query.items

First page should be 1 otherwise the .total returns exception divided by zero

Related questions

How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Check if a user has scrolled to the bottom

I'm making a pagination system (sort of like Facebook) where the content loads when the user scrolls to the bottom. I imagine the best way to do that is to find when the user is at the bottom of the …

Read More
How to do paging in AngularJS?

I have a dataset of about 1000 items in-memory and am attempting to create a pager for this dataset, but I'm not sure on how to do this. I'm using a custom filter function to filter the results, and that works …

Read More