分頁查詢及其擴充應用案例

分頁查詢

分頁查詢是處理大量資料時常用的技術，透過分頁可以將資料分成多個小部分，方便使用者逐頁檢視。SQLAlchemy 提供了簡單易用的方法來實現分頁查詢。

本篇我們也會在最終實現這樣的分頁效果：

1. 什麼是分頁查詢

分頁查詢是將查詢結果按照一定數量分成多頁展示，每頁顯示固定數量的記錄。分頁查詢通常使用兩個引數：

• limit：每頁顯示的記錄數量。
• offset：跳過的記錄數量。

例如，要查詢第二頁，每頁顯示 10 條記錄：

• limit：10
• offset：10

2. 使用 SQLAlchemy 實現分頁查詢

基本查詢

首先，我們需要一個基本的查詢來獲取資料：

import db
from model import Student

def basic_query():
    students = db.session.query(Student).all()
    for stu in students:
        print(stu.to_dict())

使用 limit 和 offset

前文中，我們已經瞭解到 SQLAlchemy 提供了 limit 和 offset 方法來實現分頁查詢。limit 限制返回的記錄數量，offset 跳過指定數量的記錄。

import db
from model import Student

def paginated_query(page, per_page):
    q = db.select(Student).limit(per_page).offset((page - 1) * per_page)
    students = db.session.execute(q).scalars()
    for stu in students:
        print(stu.to_dict())

例如，要獲取第 2 頁，每頁顯示 10 條記錄：

paginated_query(2, 10)

對應的 SQL 語句：

SELECT * FROM tb_student LIMIT 10 OFFSET 10;

3. 前後端實現分頁功能

後端分頁

在後端實現分頁功能時，可以建立一個函式來處理分頁邏輯。這個函式接受 page 和 per_page 引數，並返回當前頁的資料和總頁數。

import db
from model import Student

def get_paginated_students(page, per_page):
    total = db.session.query(Student).count()
    q = db.select(Student).limit(per_page).offset((page - 1) * per_page)
    students = db.session.execute(q).scalars()
    
    return {
        'total': total,
        'page': page,
        'per_page': per_page,
        'pages': (total + per_page - 1) // per_page,
        'data': [stu.to_dict() for stu in students]
    }

前端分頁

在前端實現分頁時，可以使用後端提供的分頁資料來渲染頁面：

{
    "total": 100,
    "page": 2,
    "per_page": 10,
    "pages": 10,
    "data": [
        {"id": 11, "name": "Student 11", ...},
        {"id": 12, "name": "Student 12", ...},
        ...
    ]
}

前端可以根據這些資料渲染分頁控制元件和當前頁的資料。

[擴充] Flask 分頁演示

下面是一個前後端不分離的 Flask 專案，程式碼檔案比較多，你需要自行理一下。同時也要保證 Flask 、 Flask-SQLAlchemy 與 Flask-MysqlDB 的安裝。

pip install flask
pip install flask-sqlalchemy  # 相容 Flask 的 SQLAlchemy 框架，提供 ORM 功能
pip install flask-mysqldb  # 為 Flask-SQLAlchemy 提供 MySQL 驅動

Flask 專案目錄如下：

flask_app/  # 專案目錄
├── templates/  # 模板目錄
│   └── list.html  # 模板檔案
├── config.py  # Flask 配置檔案
├── db.py  # 資料庫核心檔案，包含重要操作
├── manage.py  # Flask 路由和業務檢視檔案
└── models.py  # 資料庫模型檔案

首先看一下配置檔案 config.py：

class Config:
    SQLALCHEMY_DATABASE_URI = 'mysql://root:0908@localhost:3306/db_flask_demo_school?charset=utf8mb4'  # 資料庫連線。自行替換資料庫使用者名稱稱和密碼以及實際資料庫名
    SQLALCHEMY_ECHO = False  # 是否列印執行的 SQL 語句及其耗時
    DEBUG = True  # 是否啟用除錯模式

db.py

"""
Create database:
    > create database db_flask_demo_school charset=utf8mb4
"""
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import *

db = SQLAlchemy()

models.py

from db import *


class Student(db.Model):
    __tablename__ = 'tb_student2'

    id = db.Column(db.Integer, primary_key=True, comment="主鍵")
    name = db.Column(db.String(15), index=True, comment="姓名")
    age = db.Column(db.SmallInteger, comment="年齡")
    sex = db.Column(db.Boolean, comment="性別")
    email = db.Column(db.String(128), unique=True, comment="郵箱地址")
    money = db.Column(db.Numeric(10, 2), default=0.0, comment="錢包")

    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'age': self.age,
            'sex': self.sex,
            'email': self.email,
            'money': float(self.money)
        }

    def __repr__(self):
        return f'<{self.__class__.__name__}: {self.name}>'

然後就是 manage.py，編寫了路由與業務程式碼：

from pathlib import Path
from flask import Flask, jsonify, request, render_template
from config import Config
from models import db, Student

app = Flask(__name__, template_folder='./templates')
app.config.from_object(Config)

db.init_app(app)


@app.route('/', methods=['GET'])
def index():
    """沒啥用，勿看"""
    title = Path(__file__).name
    return title


@app.route('/students', methods=['POST'])
def create_student():
    """採集訪問的資訊，建立學生"""
    sex = request.form.get('sex')
    sex = int(sex) if sex.isdigit() else 0
    student = Student(
        name=request.form.get('name', '未知'),
        age=request.form.get('age', 0),
        sex=bool(sex),
        email=request.form.get('email', ''),
        money=request.form.get('money', 0),
    )
    if request.form.get('id', None) is not None:
        student.id = request.form['id']

    db.session.add(student)
    db.session.commit()
    return jsonify({
        'success': True,
        'data': student.to_dict(),
        'msg': 'success'
    }), 201


@app.route('/students', methods=['DELETE'])
def delete_students():
    """刪除學生表的所有記錄"""
    db.session.execute(db.delete(Student))
    db.session.commit()
    return jsonify({
        'success': True,
        'data': None,
        'msg': 'success'
    })


@app.route('/students', methods=['GET'])
def get_students():
    # 舊版本 2.x 獲取全部資料
    # students = Student.query.all()
    # 新版本 3.1.x 獲取全部資料
    students = db.session.execute(db.select(Student).where()).scalars()
    return jsonify({
        'success': True,
        'data': {
            'count': Student.query.count(),
            'students': [student.to_dict() for student in students]
        },
        'msg': 'success'
    })


@app.route('/students/<int:student_id>', methods=['GET'])
def get_student(student_id):
    # 根據主鍵查詢資料，不存在則為 None
    student = db.session.get(Student, student_id)
    if not student:
        return jsonify({
            'success': False,
            'data': None,
            'msg': 'student not found'
        })

    return jsonify({
        'success': True,
        'data': student.to_dict(),
        'msg': 'success'
    })


@app.route('/students/data', methods=['GET'])
def students_data():
    """這裡是分頁器的使用，不同於我們所使用的 limit 和 offset 需要自己編寫"""
    # 不採取資料分頁時，大量資料時會導致伺服器運存膨脹，這是非常不妥的
    page = request.args.get('page', 1, type=int)
    per_page = request.args.get('size', 3, type=int)
    # 建立分頁器物件
    pagination = Student.query.paginate(page=page, per_page=per_page, max_per_page=20)
    print('當前頁物件', pagination)
    print('總資料量', pagination.total)
    print('當前頁資料列表', pagination.items)
    print('總頁碼', pagination.pages)
    print()
    print('是否有上一頁', pagination.has_prev)
    print('上一頁頁碼', pagination.prev_num)
    print('上一頁物件', pagination.prev())
    print('上一頁物件的資料列表', pagination.prev().items)
    print()
    print('是否有下一頁', pagination.has_next)
    print('下一頁頁碼', pagination.next_num)
    print('下一頁物件', pagination.next())
    print('下一頁物件的資料列表', pagination.next().items)

    # """前後端分離推薦使用的 json 結果，這裡沒用到"""
    data = {
        "page": pagination.page,  # 當前頁碼
        "pages": pagination.pages,  # 總頁碼
        "has_prev": pagination.has_prev,  # 是否有上一頁
        "prev_num": pagination.prev_num,  # 上一頁頁碼
        "has_next": pagination.has_next,  # 是否有下一頁
        "next_num": pagination.next_num,  # 下一頁頁碼
        "items": [{
            "id": item.id,
            "name": item.name,
            "age": item.age,
            "sex": item.sex,
            "money": item.money,
        } for item in pagination.items]
    }

    return render_template('list.html', **locals())


if __name__ == '__main__':
    with app.app_context():
        db.drop_all()  # 啟動時先刪除相關表，後建立相關表
        db.create_all()
    app.run('0.0.0.0', 9527)

最後就是 list.html 這個模板檔案，呈現一個分頁的演示：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f7fa;
            color: #333;
        }

        table {
            border-collapse: collapse;
            margin: 50px auto;
            width: 80%;
            box-shadow: 0 2px 10px rgba(0, 0, 0, 0.1);
            background-color: #fff;
        }

        th, td {
            padding: 12px 15px;
            text-align: center;
        }

        th {
            background-color: #007bff;
            color: #fff;
            text-transform: uppercase;
        }

        tr:nth-child(even) {
            background-color: #f2f2f2;
        }

        tr:hover {
            background-color: #e9f5ff;
        }

        .page {
            margin: 20px auto;
            text-align: center;
        }

        .page a, .page span {
            padding: 8px 16px;
            margin: 0 4px;
            color: #007bff;
            background: #fff;
            border: 1px solid #007bff;
            border-radius: 4px;
            text-decoration: none;
            transition: background-color 0.3s, color 0.3s;
        }

        .page a:hover {
            background-color: #007bff;
            color: #fff;
        }

        .page span {
            background-color: #007bff;
            color: #fff;
        }
    </style>
</head>
<body>
    <table border="1" align="center" width="600">
        <tr>
           <th>ID</th>
           <th>Age</th>
           <th>Name</th>
           <th>Sex</th>
           <th>Money</th>
        </tr>
        {% for student in pagination.items %}
        <tr>
           <td>{{ student.id }}</td>
           <td>{{ student.age }}</td>
           <td>{{ student.name }}</td>
           <td>{{ "男" if student.sex else "女" }}</td>
           <td>{{ student.money }}</td>
        </tr>
        {% endfor %}
        <tr align="center">
            <td colspan="5" class="page">
                {% if pagination.has_prev %}
                <a href="?page=1">首 頁</a>
                <a href="?page={{ pagination.page - 1 }}">上一頁</a>
                <a href="?page={{ pagination.page - 1 }}">{{ pagination.page - 1 }}</a>
                {% endif %}
                <span>{{ pagination.page }}</span>
                {% if pagination.has_next %}
                <a href="?page={{ pagination.page + 1 }}">{{ pagination.page + 1 }}</a>
                <a href="?page={{ pagination.page + 1 }}">下一頁</a>
                <a href="?page={{ pagination.pages }}">尾 頁</a>
                {% endif %}
            </td>
        </tr>
    </table>
</body>
</html>

為了確保能夠有一定數量的資料，請你另外新建一個 request.py，用於建立大量資料（如果你知道 faker 的使用，也可以自己弄一些資料），先啟動 manage.py，保證後端服務的開啟和路由可用，然後直接執行該檔案後可新增測試資料：

# request.py
import requests  # pip install requests

students = [  # 虛擬資料，務必當真
    {
        'name': '王毅',
        'age': 21,
        'sex': 1,
        'email': 'wangyi@gmail.com',
        'money': 4488.5
    },
    {
        'name': '張曉',
        'age': 19,
        'sex': 0,
        'email': 'zhangxiao@example.com',
        'money': 2389.75
    },
    {
        'name': '李春陽',
        'age': 23,
        'sex': 1,
        'email': 'lichunyang@outlook.com',
        'money': 6715.32
    },
    {
        'name': '劉瑞',
        'age': 20,
        'sex': 0,
        'email': 'liurui@yahoo.com',
        'money': 3456.89
    },
    {
        'name': '陳歡',
        'age': 22,
        'sex': 1,
        'email': 'chenhuan@gmail.com',
        'money': 5678.12
    },
    {
        'name': '吳娜',
        'age': 18,
        'sex': 0,
        'email': 'wuna@example.org',
        'money': 1234.56
    },
    {
        'name': '趙丹',
        'age': 24,
        'sex': 0,
        'email': 'zhaoda@outlook.com',
        'money': 7890.43
    },
    {
        'name': '孫宇',
        'age': 21,
        'sex': 1,
        'email': 'sunyu@yahoo.co.jp',
        'money': 4567.89
    },
    {
        'name': '黃宇',
        'age': 19,
        'sex': 1,
        'email': 'huangyu@gmail.com',
        'money': 2345.67
    },
    {
        'name': '楊靜',
        'age': 22,
        'sex': 0,
        'email': 'yangjing@example.com',
        'money': 6789.01
    }
]
for student in students:
    response = requests.request('POST', 'http://127.0.0.1:9527/students', data=student)
    print('新增一條記錄', response.json())

確定 Flask 專案正常啟動，並且上面的資料也完成了注入，如果你發現啟動失敗了，請檢查路由、資料庫連線是否有問題，你可能需要一定的 Flask 基礎知識。接下來如何訪問我們渲染的模板呢？

根據路由檢視和設定的訪問埠（9527）：

@app.route('/students/data', methods=['GET'])
def students_data():
    ...
    return render_template('list.html', **locals())

我們直接在瀏覽器訪問：http://127.0.0.1:9527/students/data 這個地址即可。

上述案例是演示所用，隨意寫的，小部分程式碼參考了某機構的教程程式碼示例，平臺原因無法標註，路由設計也是很隨便的，這種程式碼如果存在版權糾紛，emmm.....，請聯絡我刪除，謝謝。無私開源，只為搞懂後端開發的學習，請勿鑽牛角……