How to paginate SQLite database?

Question

I have written simple code that displays data from a SQLite database in a table. The table already contains pages to go through the entire contents of the database.

The problem is that right now the entire database is loaded into memory at once. How to make it split the query to the server into pages? The first part, i.e. loading and displaying a limited number of results when the page is first run, is not difficult:

list_of_games = list_of_games.order_by(GamesDatabase.date_added)[0:10]

But how to make the page send a query for the next results (e.g. 11-20) and display them only after pressing the button (e.g. "next page")? I want to create two separate buttons: "next page" and "previous page", which would be responsible for that.

I'm using Python with Flask (with SQLalchemy), SQLite.

PYTHON

@app.route("/test")
def test():
    list_of_games = GamesDatabase.query
    # print(request.args)

    if "rating_min" in request.args:
        rating_min = request.args.get("rating_min", 1, int)
        list_of_games = list_of_games.filter(GamesDatabase.rating >= rating_min)

    if "rating_max" in request.args:
        rating_max = request.args.get("rating_max", 100, int)
        list_of_games = list_of_games.filter(GamesDatabase.rating <= rating_max)

    if "genre" in request.args:
        list_of_games = list_of_games.filter(GamesDatabase.genre == request.args["genre"].upper())

    list_of_games = list_of_games.order_by(GamesDatabase.date_added)


    # args = []
    # if request.args:
    #     for arg, val in request.args.items():
    #         args.append({"name": arg, "value": val})


    return render_template("test.html", list_of_games=list_of_games) #args=args

HTML

{% extends "base.html" %}

{% block content %}

   
<head>
<title> Bootstrap SORT table Example </title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.22/css/dataTables.bootstrap4.min.css">
<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/dataTables.bootstrap4.min.js"></script>
</head>
<body>

<button class="btn btn-primary" onclick="document.location='{{url_for('index')}}'"style="margin-left: 790px;">go back</button>

   
<div class="container" style="width:60%">
<h2>all games </h2>
<table class="table table-striped table-bordered" id="sortTable">
<thead>
<tr>
<th>Name</th>
<th>Genre</th>
<th>Rating</th>
<th>Price</th>

</tr>
</thead>
<tbody>

       {% for game in list_of_games %}
        <tr>
          <td>{{ game.name}}</td>
          <td>{{ game.genre }}</td>
          <td>{{ game.rating }}%</td>
          <td>${{ game.price }}</td>
        </tr>
        {% endfor %}

</tbody>
</table>
</div>

<form action="" method="GET">
    {%  for arg in args %}
    <input type="hidden" name="{{ arg.name }}" value="{{ arg.value }} ">
    {% endfor %}
    <button type="submit">next_page </button>

</form>
  
<script>
$('#sortTable').DataTable();
</script>
</body>

<center>
    <form action="" method="GET" >

    <div class="input-group" style="width: 30%">
      <div class="input-group-prepend">
          <span class="input-group-text"  id="rating">Rating</span>
      </div>
      <input type="number" id="rating_min"  placeholder="min: 1" class="form-control" min="1" max="100"  name="rating_min" />
      <input type="number" id="rating_max"  placeholder="max: 100" class="form-control" min="1" max="100" name="rating_max" />
    </div>

    <div class="input-group" style="width: 30%">
      <div class="input-group-prepend">
        <span class="input-group-text" id="genre_">Genre </span>
      </div>
      <input id="genre" placeholder="RPG" class="form-control" type="text" name="genre" required/>
    </div>

    <button class="btn btn-primary" onclick="document.location='{{url_for('test')}}'" style="margin-left: 0px;">submit</button>
    </form>

</center>
<br>
   
{% endblock %}

Have you seen [this post](https://stackoverflow.com/q/43103585/15368978)? — Patrick Yoder, May 23 '22 at 19:51
See https://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-ix-pagination — Dave W. Smith, May 23 '22 at 19:57
@PatrickYoder Than you, I will definetely check out both sources. — askren, May 24 '22 at 12:40

Detlef · Answer 1 · 2022-05-23T23:47:24.683

You should take a look at flask-sqlalchemy's paginate function. It returns an object of type Pagination, which makes your work easier. So you can request all the data page by page and you will receive all the information you need to create your buttons.

However, since you are also using DataTables in your example, I have written an example below of how you can use the pagination of DataTables using AJAX and the function mentioned above. For this I also use Flask-Marshmallow and Marshmallow-SQLAlchemy to convert the data to JSON.

from flask import (
    Flask,
    jsonify,
    render_template,
    request
)
from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow
import random

app = Flask(__name__)
db = SQLAlchemy(app)
ma = Marshmallow(app)

class Game(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    genre = db.Column(db.String)
    rating = db.Column(db.Integer)
    price = db.Column(db.Integer)

class GameSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Game

with app.app_context():
    db.drop_all()
    db.create_all()

    games = [Game(
        name=f'Game-{i+1}',
        genre=random.choice(['RPG', 'Sports', 'Shooter']),
        rating=random.randint(0,100),
        price=i
    ) for i in range(100)]
    db.session.add_all(games)
    db.session.commit()


@app.route('/')
def index():
    genres = [genre for genre, *_ in Game.query.order_by(Game.genre).with_entities(Game.genre).distinct().all()]
    return render_template('index.html', **locals())

@app.route('/data')
def data():
    draw = request.args.get('draw', 0, int)
    length = request.args.get('length', 10, int)
    start = request.args.get('start', 0, int)

    query = Game.query.filter(Game.rating.between(
        request.args.get('rating_min', 0, type=int),
        request.args.get('rating_max', 100, type=int)
    ))
    genre = request.args.get('genre')
    if genre:
        query = query.filter_by(genre=genre)

    games = query.paginate(int(start/length) + 1, length)
    games_schema = GameSchema(many=True)
    data = games_schema.dump(games.items)
    return jsonify(
        draw=draw,
        recordsTotal=games.total,
        recordsFiltered=games.total,
        data=data
    )

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title></title>

    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
    <link href="https://cdn.datatables.net/1.12.0/css/dataTables.bootstrap5.min.css" rel="stylesheet" type="text/css">
  </head>
  <body>

    <div class="container">

      <form name="search" class="my-4">

        <div class="row">
          <div class="col-md-4">
            <div class="input-group mb-3">
              <span class="input-group-text"  id="rating">Rating</span>
              <input
                type="number"
                name="rating_min"
                id="rating_min"
                class="form-control"
                min="0" max="100" value="0"
              />

              <input
                type="number"
                name="rating_max"
                id="rating_max"
                class="form-control"
                min="1" max="100" value="100"
              />
            </div>
          </div>

          <div class="col-md-6">
            <div class="input-group mb-3">
              <span class="input-group-text" id="genre_">Genre </span>
              <select class="form-select" name="genre">
                <option value>Choose a genre</option>
                {% for genre in genres -%}
                <option value="{{genre}}">{{genre}}</option>
                {% endfor -%}
              </select>
            </div>
          </div>

          <div class="col-md-2">
            <div class="d-grid gap-2">
              <button class="btn btn-primary">Submit</button>
            </div>
          </div>

        </div>
      </form>

      <div class="my-4">

        <table class="table table-striped table-bordered" id="sortTable" style="width: 100%">
          <thead>
            <tr>
              <th>Name</th>
              <th>Genre</th>
              <th>Rating</th>
              <th>Price</th>
            </tr>
          </thead>
          <tbody>
          </tbody>
        </table>
      </div>

    </div>

    <script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p" crossorigin="anonymous"></script>
    <script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.12.0/js/dataTables.bootstrap5.min.js"></script>
    <script type="text/javascript">
      $(document).ready(function() {

        let data = {};

        const table = $('#sortTable').DataTable({
          processing: true,
          serverSide: true,
          searching: false,
          ordering: false,
          pagingType: 'simple',
          ajax: {
            url: {{ url_for('data') | tojson }},
            dataSrc: 'data',
            data: function (d) {
              d = $.extend(d, data);
            }
          },
          columns: [
            { data: 'name' },
            { data: 'genre' },
            { data: 'rating' },
            { data: 'price' },
          ]
        });

        $('form[name="search"]').submit(function(evt) {
          evt.preventDefault();
          $.each($(this).serializeArray(), function(index, value) {
            data[value.name] = value.value;
          });
          table.ajax.reload();
        });

        $('#rating_min, #rating_max').change(function(evt) {
          const val0 = $('#rating_min').val();
          const val1 = $('#rating_max').val();
          if (parseInt(val0) >= parseInt(val1)) {
            $('#rating_min').val(val1-1);
            $('#rating_max').val(val1);
          }
        });

      });
    </script>
  </body>
</html>

Wow, thank you! The quality of this code is amazing. I will definitely learn a lot. — askren, May 24 '22 at 12:47

How to paginate SQLite database?

1 Answers1