tessl/pypi-flask-sqlalchemy

Add SQLAlchemy support to your Flask application.

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Overview

Eval results

Files

Pagination

Name: tessl/pypi-flask-sqlalchemy
Author: tessl

Built-in pagination support for both modern SQLAlchemy select statements and legacy Query objects, with request parameter integration and pagination widget helpers.

Capabilities

Pagination Base Class

Base class for paginating query results with page navigation and metadata.

class Pagination:
    """
    Base pagination class for managing paged query results.
    
    Provides page navigation, item access, and metadata for building
    pagination widgets in web applications. Don't create instances
    manually - use db.paginate() or query.paginate() instead.
    """
    
    def __init__(
        self,
        page: int | None = None,
        per_page: int | None = None,
        max_per_page: int | None = 100,
        error_out: bool = True,
        count: bool = True,
        **kwargs: Any,
    ) -> None:
        """
        Initialize pagination object.
        
        Parameters:
        - page: Current page number (from request args if None)
        - per_page: Items per page (from request args if None)
        - max_per_page: Maximum allowed per_page value
        - error_out: Abort with 404 on invalid parameters
        - count: Calculate total item count
        - kwargs: Query-specific arguments for subclasses
        """

    # Properties for current page state
    page: int  # Current page number
    per_page: int  # Items per page
    max_per_page: int | None  # Maximum allowed per_page
    items: list[Any]  # Items on current page
    total: int | None  # Total items across all pages
    
    @property
    def first(self) -> int:
        """Number of first item on page (1-based) or 0 if no items."""
    
    @property  
    def last(self) -> int:
        """Number of last item on page (1-based) or 0 if no items."""
    
    @property
    def pages(self) -> int:
        """Total number of pages."""
    
    @property
    def has_prev(self) -> bool:
        """True if this is not the first page."""
    
    @property
    def prev_num(self) -> int | None:
        """Previous page number or None if first page."""
    
    def prev(self, *, error_out: bool = False) -> Pagination:
        """
        Get Pagination object for previous page.
        
        Parameters:
        - error_out: Abort with 404 on invalid page
        
        Returns:
        Pagination object for previous page
        """
    
    @property
    def has_next(self) -> bool:
        """True if this is not the last page."""
    
    @property
    def next_num(self) -> int | None:
        """Next page number or None if last page."""
    
    def next(self, *, error_out: bool = False) -> Pagination:
        """
        Get Pagination object for next page.
        
        Parameters:
        - error_out: Abort with 404 on invalid page
        
        Returns:
        Pagination object for next page
        """
    
    def iter_pages(
        self,
        *,
        left_edge: int = 2,
        left_current: int = 2,
        right_current: int = 4,
        right_edge: int = 2,
    ) -> Iterator[int | None]:
        """
        Yield page numbers for pagination widget with smart truncation.
        
        Parameters:
        - left_edge: Pages to show from start
        - left_current: Pages to show left of current
        - right_current: Pages to show right of current  
        - right_edge: Pages to show from end
        
        Yields:
        Page numbers or None for gaps (e.g., 1, 2, None, 7, 8, 9, None, 19, 20)
        """
    
    def __iter__(self) -> Iterator[Any]:
        """Iterate over items on current page."""

Select Statement Pagination

Pagination implementation for modern SQLAlchemy select statements.

class SelectPagination(Pagination):
    """
    Pagination for SQLAlchemy select statements.
    
    Used by db.paginate() for modern SQLAlchemy 2.x select() queries.
    Returned by SQLAlchemy.paginate() method.
    """
    
    def _query_items(self) -> list[Any]:
        """Execute paginated select query to get items for current page."""
    
    def _query_count(self) -> int:
        """Execute count query to get total number of items."""

Query Pagination

Pagination implementation for legacy Query objects.

class QueryPagination(Pagination):
    """
    Pagination for legacy SQLAlchemy Query objects.
    
    Used by Query.paginate() for SQLAlchemy 1.x style queries.
    Returned by Query.paginate() method.
    """
    
    def _query_items(self) -> list[Any]:
        """Execute paginated query to get items for current page."""
    
    def _query_count(self) -> int:
        """Execute count query to get total number of items."""

Usage Examples

Basic Pagination with Select Statements

@app.route('/users')
def list_users():
    page = request.args.get('page', 1, type=int)
    
    # Modern SQLAlchemy 2.x style pagination
    pagination = db.paginate(
        db.select(User).order_by(User.username),
        page=page,
        per_page=20,
        max_per_page=100
    )
    
    return render_template('users.html', pagination=pagination)

Pagination with Query Objects

@app.route('/posts')
def list_posts():
    # Legacy Query style pagination
    pagination = Post.query.filter_by(published=True).order_by(
        Post.created_at.desc()
    ).paginate(
        per_page=10,
        error_out=False  # Use page 1 instead of 404 for invalid pages
    )
    
    return render_template('posts.html', pagination=pagination)

Request Parameter Integration

@app.route('/search')
def search():
    query = request.args.get('q', '')
    page = request.args.get('page', 1, type=int)
    per_page = min(request.args.get('per_page', 20, type=int), 100)
    
    # Pagination automatically reads 'page' and 'per_page' from request
    # if not explicitly provided
    pagination = db.paginate(
        db.select(Post).where(Post.title.contains(query)),
        page=page,
        per_page=per_page
    )
    
    return {
        'results': [post.title for post in pagination.items],
        'pagination': {
            'page': pagination.page,
            'pages': pagination.pages,
            'per_page': pagination.per_page,
            'total': pagination.total,
            'has_next': pagination.has_next,
            'has_prev': pagination.has_prev
        }
    }

Pagination Templates

<!-- Example Jinja2 template for pagination -->
<div class="pagination">
    {% if pagination.has_prev %}
        <a href="{{ url_for(request.endpoint, page=pagination.prev_num, **request.args) }}">&laquo; Prev</a>
    {% endif %}
    
    {% for page in pagination.iter_pages() %}
        {% if page %}
            {% if page != pagination.page %}
                <a href="{{ url_for(request.endpoint, page=page, **request.args) }}">{{ page }}</a>
            {% else %}
                <strong>{{ page }}</strong>
            {% endif %}
        {% else %}
            <span>…</span>
        {% endif %}
    {% endfor %}
    
    {% if pagination.has_next %}
        <a href="{{ url_for(request.endpoint, page=pagination.next_num, **request.args) }}">Next &raquo;</a>
    {% endif %}
</div>

<!-- Display current page items -->
<div class="items">
    {% for item in pagination.items %}
        <div>{{ item.title }}</div>
    {% endfor %}
</div>

<!-- Pagination info -->
<div class="info">
    Showing {{ pagination.first }} to {{ pagination.last }} of {{ pagination.total }} items
    (Page {{ pagination.page }} of {{ pagination.pages }})
</div>

Advanced Pagination Controls

@app.route('/products')
def products():
    category = request.args.get('category')
    sort = request.args.get('sort', 'name')
    
    query = db.select(Product)
    
    if category:
        query = query.where(Product.category == category)
    
    if sort == 'price':
        query = query.order_by(Product.price)
    else:
        query = query.order_by(Product.name)
    
    pagination = db.paginate(
        query,
        per_page=12,
        max_per_page=50,
        count=True  # Calculate total for "X of Y" display
    )
    
    return render_template('products.html', 
                         pagination=pagination,
                         category=category,
                         sort=sort)

Pagination Widget Helper

def generate_page_links(pagination, endpoint, **kwargs):
    """Generate pagination links for templates."""
    links = []
    
    # Previous page
    if pagination.has_prev:
        links.append({
            'url': url_for(endpoint, page=pagination.prev_num, **kwargs),
            'text': 'Previous',
            'current': False
        })
    
    # Page numbers with smart truncation
    for page in pagination.iter_pages():
        if page is None:
            links.append({'text': '…', 'current': False})
        else:
            links.append({
                'url': url_for(endpoint, page=page, **kwargs),
                'text': str(page),
                'current': page == pagination.page
            })
    
    # Next page
    if pagination.has_next:
        links.append({
            'url': url_for(endpoint, page=pagination.next_num, **kwargs),
            'text': 'Next', 
            'current': False
        })
    
    return links

Performance Optimization

@app.route('/users')
def list_users():
    # Disable count query for better performance on large tables
    pagination = db.paginate(
        db.select(User).order_by(User.created_at.desc()),
        per_page=25,
        count=False  # Skip expensive COUNT query
    )
    
    # pagination.total will be None
    # Use has_next/has_prev for navigation instead
    return render_template('users.html', pagination=pagination)

Custom Pagination Parameters

def custom_paginate(query, page=None, per_page=None):
    """Custom pagination with different defaults."""
    return db.paginate(
        query,
        page=page or 1,
        per_page=per_page or 50,  # Higher default
        max_per_page=200,         # Higher maximum
        error_out=False           # Don't 404 on invalid pages
    )

@app.route('/items')
def list_items():
    pagination = custom_paginate(
        db.select(Item).order_by(Item.name),
        page=request.args.get('page', type=int)
    )
    return render_template('items.html', pagination=pagination)