SQLAlchemy and going through a large result set [duplicate]

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.

3 Answers

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

If you aren't using Flask, you can use SqlAlchemy function 'slice' or a combo of 'limit' & 'offset', as mentioned here. E.g.:

some_query = Query([TableBlaa]) query = some_query.limit(number_of_rows_per_page).offset(page_number*number_of_rows_per_page) # -- OR -- query = some_query.slice(page_number*number_of_rows_per_page, (page_number*number_of_rows_per_page)+number_of_rows_per_page) current_pages_rows = session.execute(query).fetchall()

If you are building an api to use with ReactJs, vueJs or other frontEnd framework, you can process like:

Notice:

page: current page that you need

error_out: Not display errors

max_per_page or per_page : the limit

Documentaion: SqlAchemy pagination

 record_query = Record.query.paginate(page=*Number*, error_out=False, max_per_page=15) result = dict(datas=record_query.items, total=record_query.total, current_page=record_query.page, per_page=record_query.per_page)

On record_query you can use :

next(error_out=False)