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...
![Sqlalchemy and Going Through a Large Result Set [Duplicate]](https://www.alledaags.nl/uploads/sqlalchemy-and-going-through-a-large-result_1200.jpg)
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.
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)
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!
David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.
