I am using Flask and Flask-SQLAlchemy version 3.1 for a book library. The index.html page has a table that displays the title, author, and ISBN of each book. I would like to use the SQLAlchemy pagination method to display the books over multiple pages, with 10 books per page. However, I am receiving an AttributeError: 'Query' object has no attribute 'paginate' for the statement:
book_data = books_query.paginate(page=page, per_page=per_page)
Here is my code for the index route in routes.py:
from flask import Blueprint, render_template, redirect, session, request from books.database import Session from books.models import Book, Author, Genre, Location, Publisher, Tag, Format
pages = Blueprint( "pages", name, template_folder="templates", static_folder="static" )
@pages.route("/")
def index():
session = Session()
# Get the page number from the query parameter
page = request.args.get("page", 1, type=int)
# Number of books per page
per_page = 10
# Query to join Books and Authors tables
books_query = session.query(
Book.id,
Book.title,
Book.isbn,
Author.first_name,
Author.last_name
).join(Author, Author.id == Book.author_id)
# Execute the query and paginate the results
book_data = books_query.paginate(page=page, per_page=per_page)
# Close the session
session.close()
return render_template(
"index.html",
title="Book Library",
page_title="Library Catalog",
book_data = book_data
)
I tried to modify the above code by combining the query and the execution statements.
# Paginate the results
book_data = session.query(
Book.id,
Book.title,
Book.isbn,
Author.first_name,
Author.last_name
).join(Author, Author.id == Book.author_id).paginate(page=page, per_page=per_page)
But I still get the AttributeError for the query.
I can successfully display all the books without pagination using the code:
book_data = books_query.all()
Also all the other page queries work correctly. So the connection to the database is working properly. Most of the resources on pagination I found refer to the older (v2.0) versions of Flash SQLAlchemy. They mostly assume that I have a db instance rather than a session instance created with the Session object defined in my database.py file. here is the database.py code.
import os
from dotenv import load_dotenv
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
load_dotenv()
# Configure database URL
DATABASE_URI = os.environ.get('DATABASE_URI')
# Create the engine
engine = create_engine(DATABASE_URI)
# Create a session factory
Session = sessionmaker(bind=engine)
I assume that there is a difference on implementing the pagination with version 3.1. Here is part of my requirements.txt file.
Flask==3.0.3
Flask-SQLAlchemy==3.1.1
SQLAlchemy==2.0.30
Any help on modifying the code is much appreciated.
In Flask-SQLAlchemy 3.1.x, the function paginate() is no longer assigned to the query, but to the SQLAlchemy instance. However, it is still possible to use the Legacy Query Interface, but I advise against using it.
The documentation also states:
The statement should select a model class, like select(User). This applies unique() and scalars() modifiers to the result, so compound selects will not return the expected results.
For this reason, I recommend that you create a relationship within your models.
class Author(db.Model):
id:Mapped[int] = db.mapped_column(db.Integer, primary_key=True)
first_name:Mapped[str] = db.mapped_column(db.String(32), nullable=False)
last_name:Mapped[str] = db.mapped_column(db.String(32), nullable=False)
books:Mapped[List['Book']] = db.relationship(back_populates='author')
class Book(db.Model):
id:Mapped[int] = db.mapped_column(db.Integer, primary_key=True)
title:Mapped[str] = db.mapped_column(db.String(128), nullable=False)
isbn:Mapped[str] = db.mapped_column(db.String(13), nullable=False, unique=True)
author_id:Mapped[int]= db.mapped_column(db.Integer, db.ForeignKey('author.id'), nullable=False)
author:Mapped['Author'] = db.relationship(back_populates='books')
Even if you only ask for all books in the query, you can use the defined relationship to reach the author and the name properties.
<table>
{% for book in pagination -%}
<tr>
<td>{{ book.title }}</td>
<td>{{ book.author.first_name }} {{ book.author.last_name }}</td>
<td>{{ book.isbn }}</td>
</tr>
{% endfor -%}
</table>
You can find information on using pagination with Flask-SQLAlchemy 3.1.x here in the documentation and here in a highly recommended tutorial.
If you use the above suggestion regarding the use of relationship definitions within the models, your code could look like this.
from flask import (
Flask,
render_template,
request
)
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import (
DeclarativeBase,
Mapped
)
from typing import List
class Base(DeclarativeBase):
pass
app = Flask(__name__)
app.config.from_mapping(
SECRET_KEY='your secret here',
SQLALCHEMY_DATABASE_URI='sqlite:///example.db'
)
db = SQLAlchemy(app, model_class=Base)
class Author(db.Model):
id:Mapped[int] = db.mapped_column(db.Integer, primary_key=True)
first_name:Mapped[str] = db.mapped_column(db.String(32), nullable=False)
last_name:Mapped[str] = db.mapped_column(db.String(32), nullable=False)
books:Mapped[List['Book']] = db.relationship(back_populates='author')
class Book(db.Model):
id:Mapped[int] = db.mapped_column(db.Integer, primary_key=True)
title:Mapped[str] = db.mapped_column(db.String(128), nullable=False)
isbn:Mapped[str] = db.mapped_column(db.String(13), nullable=False, unique=True)
author_id:Mapped[int]= db.mapped_column(db.Integer, db.ForeignKey('author.id'), nullable=False)
author:Mapped['Author'] = db.relationship(back_populates='books')
@app.route('/')
def index():
page = request.args.get('page', 1, type=int)
per_page = 10
pagination = db.paginate(
db.select(Book).order_by(Book.title, Book.isbn),
page=page,
per_page=per_page,
error_out=False
)
return render_template(
'index.html',
**locals()
)