Pagination is essential for managing large datasets in web applications, preventing excessive memory usage and improving user experience. In relational databases, this is typically achieved using the LIMIT and OFFSET clauses.

• Limit: Defines the maximum number of records to retreive per page.
• Offset: Specifies the number of records to skip before starting the collection.

For instance, to retrieve the second page of results with a page size of 10, the logic requires skipping the first 10 records. The SQL query would resemble:

SELECT * FROM learners LIMIT 10 OFFSET 10;

Manual Pagination with SQLAlchemy

SQLAlchemy Core allows for manual construction of pagination logic by calculating the offset based on the current page index.

from sqlalchemy import select
from db_setup import session, Learner

def fetch_page_data(page_index, page_size):
    # Calculate the number of rows to skip
    skip_rows = (page_index - 1) * page_size
    
    # Construct the query
    stmt = select(Learner).limit(page_size).offset(skip_rows)
    result_proxy = session.execute(stmt)
    records = result_proxy.scalars().all()
    
    return records

Flask Application Structure

A complete implementation involves configuring a Flask application with SQLAlchemy, defining models, and creating a view function that utilizes Flask-SQLAlchemy's built-in pagination helper.

Directory layout:

flask_pagination_demo/
├── templates/
│   └── learner_list.html
├── config.py
├── extensions.py
├── models.py
└── app.py

Configuration and Initialization

Setup the database connetcion and initialize the SQLAlchemy extension.

# config.py
class AppConfig:
    SQLALCHEMY_DATABASE_URI = 'mysql://user:password@localhost/demo_school'
    SQLALCHEMY_TRACK_MODIFICATIONS = False

# extensions.py
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

Database Model

Define a data model representing the entity to be paginated. Here, a Learner model replaces the typical student example.

# models.py
from extensions import db

class Learner(db.Model):
    __tablename__ = 'tb_learner'
    
    id = db.Column(db.Integer, primary_key=True)
    full_name = db.Column(db.String(50), nullable=False)
    grade_level = db.Column(db.Integer)
    gender = db.Column(db.Boolean, default=True)
    email_address = db.Column(db.String(120), unique=True)
    balance = db.Column(db.Numeric(10, 2), default=0.0)

    def serialize(self):
        return {
            'id': self.id,
            'name': self.full_name,
            'grade': self.grade_level,
            'email': self.email_address,
            'balance': float(self.balance)
        }

View Logic and Pagination Object

The Flask route handles the request parameters and uses the paginate method to automatically handle limit and offset calculations.

# app.py
from flask import Flask, render_template, request, jsonify
from config import AppConfig
from extensions import db
from models import Learner

app = Flask(__name__)
app.config.from_object(AppConfig)
db.init_app(app)

@app.route('/learners', methods=['GET'])
def list_learners():
    # Default to page 1, 5 items per page
    current_page = request.args.get('page', 1, type=int)
    page_size = request.args.get('size', 5, type=int)
    
    # Create the pagination object
    pagination = Learner.query.paginate(
        page=current_page, 
        per_page=page_size, 
        error_out=False
    )
    
    # For API responses
    if request.args.get('format') == 'json':
        return jsonify({
            'has_next': pagination.has_next,
            'total_items': pagination.total,
            'items': [item.serialize() for item in pagination.items]
        })
        
    # Render template with pagination data
    return render_template('learner_list.html', pagination=pagination)

if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(port=5000)

Frontend Template

The Jinja2 template iterates over the pagination items and renders navigation controls.

    <meta charset="UTF-8"></meta>
    <title>Learner Directory</title>
    <style>
        .data-grid { width: 80%; margin: 20px auto; border-collapse: collapse; }
        .data-grid th, .data-grid td { padding: 10px; border: 1px solid #ccc; text-align: center; }
        .nav-links { text-align: center; margin-top: 20px; }
        .nav-links a { padding: 8px 12px; margin: 2px; border: 1px solid #007bff; text-decoration: none; color: #007bff; }
        .nav-links a.active { background-color: #007bff; color: white; }
    </style>


    <table class="data-grid">
        <thead>
            <tr>
                <th>ID</th><th>Name</th><th>Grade</th><th>Balance</th>
            </tr>
        </thead>
        <tbody>
            {% for learner in pagination.items %}
            <tr>
                <td>{{ learner.id }}</td>
                <td>{{ learner.full_name }}</td>
                <td>{{ learner.grade_level }}</td>
                <td>{{ learner.balance }}</td>
            </tr>
            {% endfor %}
        </tbody>
    </table>
    
    <div class="nav-links">
        {% if pagination.has_prev %}
            <a href="?page=1">First</a>
            <a href="?page={{ pagination.prev_num }}">Previous</a>
        {% endif %}
        
        <span class="active">Page {{ pagination.page }}</span>
        
        {% if pagination.has_next %}
            <a href="?page={{ pagination.next_num }}">Next</a>
            <a href="?page={{ pagination.pages }}">Last</a>
        {% endif %}
    </div>

Data Seeding Script

To test the pagination functionality, run the following script to insert sample data into the database.

# seed_data.py
import requests

api_endpoint = 'http://127.0.0.1:5000/learners'

sample_data = [
    {'full_name': 'Alice Smith', 'grade_level': 10, 'email_address': 'alice@example.com', 'balance': 150.00},
    {'full_name': 'Bob Jones', 'grade_level': 11, 'email_address': 'bob@example.com', 'balance': 200.50},
    {'full_name': 'Charlie Day', 'grade_level': 10, 'email_address': 'charlie@example.com', 'balance': 50.00},
    # Add more records to test multiple pages
] * 5 # Duplicate to create enough entries

for record in sample_data:
    # Note: Ensure your API or database logic supports insertion 
    # or use a direct database insertion script here.
    pass 
    
# Alternatively, direct insertion using app context:
# from app import app
# from extensions import db
# from models import Learner
# with app.app_context():
#     for data in sample_data:
#         learner = Learner(**data)
#         db.session.add(learner)
#     db.session.commit()