» Python: Build a REST API with Flask » 2. Development » 2.10 Pagination

Pagination

If you have millions of records in your database, you wouldn't want to display them all on one page. Doing so would be overwhelming for both the client and the server. That's why pagination is necessary.

Add pagination logic

Tune method parameters in domain/gateway/book_manager.py:

@@ -22,5 +22,5 @@ class BookManager(ABC):
         pass
 
     @abstractmethod
-    def get_books(self) -> List[Book]:
+    def get_books(self, offset: int) -> List[Book]:
         pass

Add a parameter named offset for the get_books method.

Add page_size config item in infrastructure/config/config.py:

@@ -25,6 +25,7 @@ class CacheConfig:
 @dataclass
 class ApplicationConfig:
     port: int
+    page_size: int
 
 
 @dataclass

Setting a value for page_size in config.yml:

@@ -1,5 +1,6 @@
 app:
   port: 5000
+  page_size: 5
 db:
   file_name: "test.db"
   host: "127.0.0.1"

Wire in page_size in application/wire_helper.py:

@@ -12,7 +12,7 @@ class WireHelper:
 
     @classmethod
     def new(cls, c: Config):
-        db = MySQLPersistence(c.db)
+        db = MySQLPersistence(c.db, c.app.page_size)
         mdb = MongoPersistence(c.db.mongo_uri, c.db.mongo_db_name)
         kv = RedisCache(c.cache)
         return cls(db, mdb, kv)

Update the query logic to include offset and page_size in infrastructure/database/mysql.py:

@@ -8,7 +8,8 @@ from ...domain.model import Book
 
 
 class MySQLPersistence(BookManager):
-    def __init__(self, c: DBConfig):
+    def __init__(self, c: DBConfig, page_size: int):
+        self.page_size = page_size
         self.conn = mysql.connector.connect(
             host=c.host,
             port=c.port,
@@ -60,9 +61,9 @@ class MySQLPersistence(BookManager):
             return None
         return Book(**result)
 
-    def get_books(self) -> List[Book]:
+    def get_books(self, offset: int) -> List[Book]:
         self.cursor.execute('''
-            SELECT * FROM books
-        ''')
+            SELECT * FROM books LIMIT %s, %s
+        ''', (offset, self.page_size))
         results: List[Any] = self.cursor.fetchall()
         return [Book(**result) for result in results]

Tune cache keys in application/executor/book_operator.py:

@@ -23,13 +23,14 @@ class BookOperator():
     def get_book(self, id: int) -> Optional[Book]:
         return self.book_manager.get_book(id)
 
-    def get_books(self) -> List[Book]:
-        v = self.cache_helper.load(BOOKS_KEY)
+    def get_books(self, offset: int) -> List[Book]:
+        k = f"{BOOKS_KEY}-{offset}"
+        v = self.cache_helper.load(k)
         if v:
             return json.loads(v)
-        books = self.book_manager.get_books()
+        books = self.book_manager.get_books(offset)
         self.cache_helper.save(
-            BOOKS_KEY, json.dumps([_convert(b) for b in books]))
+            k, json.dumps([_convert(b) for b in books]))
         return books
 
     def update_book(self, id: int, b: Book) -> Book:

Last step, pass in the query parameters in adapter/router.py:

@@ -15,7 +15,8 @@ class RestHandler:
 
     def get_books(self):
         try:
-            books = self.book_operator.get_books()
+            offset = request.args.get("o", type=int) or 0
+            books = self.book_operator.get_books(offset)
             return jsonify(books), 200
         except Exception as e:
             self._logger.error(f"Failed to get books: {e}")

Alright! Done with code changes. Let's try it out.

Try with curl

Put in some data for test

curl -X POST -H "Content-Type: application/json" -d '{"title": "The Great Gatsby", "author": "F. Scott Fitzgerald", "published_at": "1925-04-10", "description": "A novel depicting the opulent lives of wealthy Long Island residents during the Jazz Age.", "isbn": "9780743273565", "total_pages": 218}' http://localhost:5000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "To Kill a Mockingbird", "author": "Harper Lee", "published_at": "1960-07-11", "description": "A novel set in the American South during the 1930s, dealing with themes of racial injustice and moral growth.", "isbn": "9780061120084", "total_pages": 281}' http://localhost:5000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "1984", "author": "George Orwell", "published_at": "1949-06-08", "description": "A dystopian novel depicting a totalitarian regime, surveillance, and propaganda.", "isbn": "9780451524935", "total_pages": 328}' http://localhost:5000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "Pride and Prejudice", "author": "Jane Austen", "published_at": "1813-01-28", "description": "A classic novel exploring the themes of love, reputation, and social class in Georgian England.", "isbn": "9780486284736", "total_pages": 279}' http://localhost:5000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "The Catcher in the Rye", "author": "J.D. Salinger", "published_at": "1951-07-16", "description": "A novel narrated by a disaffected teenager, exploring themes of alienation and identity.", "isbn": "9780316769488", "total_pages": 277}' http://localhost:5000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "The Lord of the Rings", "author": "J.R.R. Tolkien", "published_at": "1954-07-29", "description": "A high fantasy epic following the quest to destroy the One Ring and defeat the Dark Lord Sauron.", "isbn": "9780544003415", "total_pages": 1178}' http://localhost:5000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "Moby-Dick", "author": "Herman Melville", "published_at": "1851-10-18", "description": "A novel exploring themes of obsession, revenge, and the nature of good and evil.", "isbn": "9780142000083", "total_pages": 624}' http://localhost:5000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "The Hobbit", "author": "J.R.R. Tolkien", "published_at": "1937-09-21", "description": "A fantasy novel set in Middle-earth, following the adventure of Bilbo Baggins and the quest for treasure.", "isbn": "9780345339683", "total_pages": 310}' http://localhost:5000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "The Adventures of Huckleberry Finn", "author": "Mark Twain", "published_at": "1884-12-10", "description": "A novel depicting the journey of a young boy and an escaped slave along the Mississippi River.", "isbn": "9780486280615", "total_pages": 366}' http://localhost:5000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "War and Peace", "author": "Leo Tolstoy", "published_at": "1869-01-01", "description": "A novel depicting the Napoleonic era in Russia, exploring themes of love, war, and historical determinism.", "isbn": "9781400079988", "total_pages": 1392}' http://localhost:5000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "Alice’s Adventures in Wonderland", "author": "Lewis Carroll", "published_at": "1865-11-26", "description": "A children’s novel featuring a young girl named Alice who falls into a fantastical world populated by peculiar creatures.", "isbn": "9780141439761", "total_pages": 192}' http://localhost:5000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "The Odyssey", "author": "Homer", "published_at": "8th Century BC", "description": "An ancient Greek epic poem attributed to Homer, detailing the journey of Odysseus after the Trojan War.", "isbn": "9780140268867", "total_pages": 541}' http://localhost:5000/books

List books of first page

curl -X GET http://localhost:5000/books

Result:

[
  {
    "author": "John Doe",
    "created_at": "Fri, 01 Mar 2024 12:11:57 GMT",
    "description": "A sample book description",
    "id": 2,
    "isbn": "1234567890",
    "published_at": "2023-01-01",
    "title": "Sample Book 222",
    "total_pages": 200,
    "updated_at": "Fri, 01 Mar 2024 12:11:57 GMT"
  },
  {
    "author": "John Doe",
    "created_at": "Fri, 01 Mar 2024 12:40:16 GMT",
    "description": "A sample book description",
    "id": 3,
    "isbn": "1234567890",
    "published_at": "2023-01-01",
    "title": "Sample Book",
    "total_pages": 200,
    "updated_at": "Fri, 01 Mar 2024 12:40:16 GMT"
  },
  {
    "author": "F. Scott Fitzgerald",
    "created_at": "Sat, 02 Mar 2024 05:48:25 GMT",
    "description": "A novel depicting the opulent lives of wealthy Long Island residents during the Jazz Age.",
    "id": 4,
    "isbn": "9780743273565",
    "published_at": "1925-04-10",
    "title": "The Great Gatsby",
    "total_pages": 218,
    "updated_at": "Sat, 02 Mar 2024 05:48:25 GMT"
  },
  {
    "author": "Harper Lee",
    "created_at": "Sat, 02 Mar 2024 05:48:25 GMT",
    "description": "A novel set in the American South during the 1930s, dealing with themes of racial injustice and moral growth.",
    "id": 5,
    "isbn": "9780061120084",
    "published_at": "1960-07-11",
    "title": "To Kill a Mockingbird",
    "total_pages": 281,
    "updated_at": "Sat, 02 Mar 2024 05:48:25 GMT"
  },
  {
    "author": "George Orwell",
    "created_at": "Sat, 02 Mar 2024 05:48:25 GMT",
    "description": "A dystopian novel depicting a totalitarian regime, surveillance, and propaganda.",
    "id": 6,
    "isbn": "9780451524935",
    "published_at": "1949-06-08",
    "title": "1984",
    "total_pages": 328,
    "updated_at": "Sat, 02 Mar 2024 05:48:25 GMT"
  }
]

List books after an offset

curl -X GET "http://localhost:5000/books?o=5"

Result:

[
  {
    "author": "Jane Austen",
    "created_at": "Sat, 02 Mar 2024 05:48:25 GMT",
    "description": "A classic novel exploring the themes of love, reputation, and social class in Georgian England.",
    "id": 7,
    "isbn": "9780486284736",
    "published_at": "1813-01-28",
    "title": "Pride and Prejudice",
    "total_pages": 279,
    "updated_at": "Sat, 02 Mar 2024 05:48:25 GMT"
  },
  {
    "author": "J.D. Salinger",
    "created_at": "Sat, 02 Mar 2024 05:48:25 GMT",
    "description": "A novel narrated by a disaffected teenager, exploring themes of alienation and identity.",
    "id": 8,
    "isbn": "9780316769488",
    "published_at": "1951-07-16",
    "title": "The Catcher in the Rye",
    "total_pages": 277,
    "updated_at": "Sat, 02 Mar 2024 05:48:25 GMT"
  },
  {
    "author": "J.R.R. Tolkien",
    "created_at": "Sat, 02 Mar 2024 05:48:25 GMT",
    "description": "A high fantasy epic following the quest to destroy the One Ring and defeat the Dark Lord Sauron.",
    "id": 9,
    "isbn": "9780544003415",
    "published_at": "1954-07-29",
    "title": "The Lord of the Rings",
    "total_pages": 1178,
    "updated_at": "Sat, 02 Mar 2024 05:48:25 GMT"
  },
  {
    "author": "Herman Melville",
    "created_at": "Sat, 02 Mar 2024 05:48:25 GMT",
    "description": "A novel exploring themes of obsession, revenge, and the nature of good and evil.",
    "id": 10,
    "isbn": "9780142000083",
    "published_at": "1851-10-18",
    "title": "Moby-Dick",
    "total_pages": 624,
    "updated_at": "Sat, 02 Mar 2024 05:48:25 GMT"
  },
  {
    "author": "J.R.R. Tolkien",
    "created_at": "Sat, 02 Mar 2024 05:48:25 GMT",
    "description": "A fantasy novel set in Middle-earth, following the adventure of Bilbo Baggins and the quest for treasure.",
    "id": 11,
    "isbn": "9780345339683",
    "published_at": "1937-09-21",
    "title": "The Hobbit",
    "total_pages": 310,
    "updated_at": "Sat, 02 Mar 2024 05:48:25 GMT"
  }
]

List books after another offset

curl -X GET "http://localhost:5000/books?o=10"

Result:

[
  {
    "author": "Mark Twain",
    "created_at": "Sat, 02 Mar 2024 05:48:25 GMT",
    "description": "A novel depicting the journey of a young boy and an escaped slave along the Mississippi River.",
    "id": 12,
    "isbn": "9780486280615",
    "published_at": "1884-12-10",
    "title": "The Adventures of Huckleberry Finn",
    "total_pages": 366,
    "updated_at": "Sat, 02 Mar 2024 05:48:25 GMT"
  },
  {
    "author": "Leo Tolstoy",
    "created_at": "Sat, 02 Mar 2024 05:48:25 GMT",
    "description": "A novel depicting the Napoleonic era in Russia, exploring themes of love, war, and historical determinism.",
    "id": 13,
    "isbn": "9781400079988",
    "published_at": "1869-01-01",
    "title": "War and Peace",
    "total_pages": 1392,
    "updated_at": "Sat, 02 Mar 2024 05:48:25 GMT"
  },
  {
    "author": "Lewis Carroll",
    "created_at": "Sat, 02 Mar 2024 05:48:25 GMT",
    "description": "A children\u2019s novel featuring a young girl named Alice who falls into a fantastical world populated by peculiar creatures.",
    "id": 14,
    "isbn": "9780141439761",
    "published_at": "1865-11-26",
    "title": "Alice\u2019s Adventures in Wonderland",
    "total_pages": 192,
    "updated_at": "Sat, 02 Mar 2024 05:48:25 GMT"
  },
  {
    "author": "Homer",
    "created_at": "Sat, 02 Mar 2024 05:48:25 GMT",
    "description": "An ancient Greek epic poem attributed to Homer, detailing the journey of Odysseus after the Trojan War.",
    "id": 15,
    "isbn": "9780140268867",
    "published_at": "8th Century BC",
    "title": "The Odyssey",
    "total_pages": 541,
    "updated_at": "Sat, 02 Mar 2024 05:48:25 GMT"
  },
  {
    "author": "John Doe",
    "created_at": "Thu, 07 Mar 2024 10:17:59 GMT",
    "description": "A sample book description",
    "id": 16,
    "isbn": "1234567890",
    "published_at": "2023-01-01",
    "title": "Sample Book",
    "total_pages": 200,
    "updated_at": "Thu, 07 Mar 2024 10:17:59 GMT"
  }
]

Looks like pagination is working! So far so good!

❮ PrevNext ❯