34

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.

Antti Haapala -- Слава Україні
  • 129,958
  • 22
  • 279
  • 321
Kevin Burke
  • 61,194
  • 76
  • 188
  • 305
  • 1
    See [this answer](https://stackoverflow.com/questions/7389759/memory-efficient-built-in-sqlalchemy-iterator-generator). In short, you can use the [`yield_per`](http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=yield_per#sqlalchemy.orm.query.Query.yield_per) operator. – MrGomez Mar 28 '12 at 21:50
  • The answers on the referenced page are not as good as the ones on this page. – chaostheory Mar 01 '23 at 22:00

3 Answers3

41

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

Steven Kalt
  • 1,116
  • 15
  • 25
radeklos
  • 2,168
  • 21
  • 19
  • Disregard my other comments, this works excellently as is, I was just doing `dict(**request.args)` stupidly in an unrelated area and getting unexpected results. – robru Aug 12 '15 at 01:11
  • 1
    No this could still be a bad idea as if you check the source they are using limit and offset. Now if the offset value is too big then the database would go through all of those values thus causing a performance problem.. – Abhishek J Jun 28 '17 at 12:32
19

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()
CubeBot88
  • 1,080
  • 11
  • 14
  • 6
    Limit offset is bad if the offset is high as it has go through all of the previous rows to fetch the offsetted rows thus causing a performance problem – Abhishek J Jun 28 '17 at 12:21
  • 2
    @AbhishekJebaraj if we don't use limit offset ? any recommend ??? –  Aug 23 '18 at 06:04
  • 2
    @tyan Datetime or any other suitable filters are a good way to do them. Because limit offset always fetches the rows and then offsets. Better to filter them and then fetch the filtered ones instead. – Abhishek J Aug 23 '18 at 12:45
  • proper pagination also returns useful information such as total number of records, number of records in the current page, whether this is the first page or the last page, etc. – nurettin Aug 12 '19 at 22:49
  • 5
    the logic here seems wrong. It should be something like `some_query.limit(number_of_rows_per_page).offset((page_number - 1) * number_of_rows_per_page)` The slice logic also seems wrong... – jgozal Jul 31 '20 at 01:18
  • 1
    @jgozal you logic also seems to be wrong offset should be equal to: ((page_number - 1) * page_size)+1 For page 1 - offset should be 1 not 0 and for page 2- offset should be 11 not 10 – Bilal Ahmed Yaseen Mar 05 '21 at 12:57
10

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)

Returns a Pagination object for the next page.

next_num

Number of the next page

page = None

the current page number (1 indexed)

pages

The total number of pages

per_page = None

the number of items to be displayed on a page.

prev(error_out=False)

Returns a Pagination object for the previous page.

prev_num

Number of the previous page.

query = None

the unlimited query object that was used to create this pagination object.

total = None

the total number of items matching the query

Hope it help you!

Boston Kenne
  • 778
  • 10
  • 15