Flask实现前端分页的办法

自己实现前端分页的办法

前端用bootstrap组件

  <nav aria-label="Page navigation example">

    <ul class="pagination justify-content-end">
      <li class="page-item"><a class="page-link" href="?page_size={{ page_params.page_size }}&page_number=1">首页</a>
      </li>
      {% for page_num in page_params['page_number_list'] %}
        <li class="page-item {% if page_num == page_params.current_page_number %}active{% endif %}"><a class="page-link"
                                                                                                       href="?page_size={{ page_params.page_size }}&page_number={{ page_num }}">{{ page_num }}</a>
        </li>
      {% endfor %}
      <li class="page-item"><a class="page-link"
                               href="?page_size={{ page_params.page_size }}&page_number={{ page_params.total_page }}">尾页</a>
      </li>
    </ul>
  </nav>

后端做参数处理

def get_clean_page_params():
    """获取清理后的分页参数"""
    default_page_size = 10

    page_number = request.args.get("page_number")
    page_size = request.args.get("page_size")
    if not page_number or not str(page_number).isnumeric():
        page_number = 1

    if not page_size or not str(page_size).isnumeric():
        page_size = default_page_size

    page_number, page_size = int(page_number), int(page_size)

    if page_size > default_page_size:
        page_size = default_page_size

    return page_number, page_size

其中page_number是第几页，page_size是每页的尺寸条目数目

具体查询SQL的办法

先从数据库查询所有的数据（这个办法性能不好，但是可以是实现分页）

    sql = """
        select *
        from (select * from tb_boiler) t1
                 left join (select boiler_id, count(1) as task_count
                            from tb_train_task
                            group by boiler_id) t2 on (t1.id = t2.boiler_id)
        order by t2.task_count desc, t1.create_time desc
    """
    df = dbmysql_utils.do_query_sql_todf(sql)

依赖的方法：

def do_query_sql_todf(sql) -> pd.DataFrame:
    """查询数据到pandas的dataframe"""
    return pd.read_sql(sql, con=dbmysql_config.get_conn())

对df做分页参数处理：

    # 分页处理
    start = (page_number - 1) * page_size
    end = page_number * page_size
    sub_df = df.iloc[start:end]

    total_page = math.ceil(len(df) / page_size)
    page_params = dict(
        total_page=total_page,
        next_page_number=page_number if page_number == total_page else page_number + 1,
        is_last=page_number == total_page,
        page_number_list=[x + 1 for x in range(total_page)],
        page_size=page_size,
        current_page_number=page_number,
    )