Reference library

Database scaling & optimization

Indexing, connection pooling, read replicas, query tuning, and throughput-aware SQL.

5 matches

How to Avoid SELECT * and Mock SQL Column Queries in Python

Mock a SQLite cursor to verify that queries specify explicit columns instead of using SELECT *.

import sqlite3
from unittest.mock import Mock, patch


def get_user_emails(connection):
    """Fetch only the required columns instead of SELECT *."""
    cursor = connection.cursor()
    cursor.execute("SELECT email FROM users")
    return [row[0] for row in cursor.fetchall()]


def test_get_user_emails_specific_colu…

13 0 Open

Database scaling & optimization medium

How to Implement Keyset Pagination in Python (Seek Method)

Implement keyset (seek) pagination in Python with a mock paginator that efficiently fetches pages based on the last row rather than OFFSET.

pagination keyset seek-method

Python

from dataclasses import dataclass
from typing import List, Optional


@dataclass
class Row:
    id: int
    name: str

    def __lt__(self, other: "Row") -> bool:
        return (self.id, self.name) < (other.id, other.name)


class MockKeysetPaginator:
    """Pagination using keyset (seek) method instead of OFFSET."""…

14 0 Open

Database scaling & optimization medium

How to Mock SQLite executemany When Batch Inserting in Python

Batch insert many rows into SQLite with executemany and mock the cursor for isolated tests.

sqlite3 executemany mock

Python

import sqlite3
from unittest.mock import Mock, patch

def insert_users(conn, users):
    """Insert multiple user records using executemany."""
    cursor = conn.cursor()
    cursor.executemany(
        "INSERT INTO users (name, age) VALUES (?, ?)",
        users
    )
    conn.commit()
    return cursor.rowcount

if _…

15 0 Open

Database scaling & optimization medium

How to Simulate a Stable Sort Cursor in Python

Build a MongoDB-style cursor mock that stably sorts records by a key while preserving original order for ties, with next() and rewind() methods.

sorting cursors database

Python

```python
import random

class CursorStableSortMock:
    """Simulates stable sorting with a cursor-like pointer for MongoDB-style queries."""
    
    def __init__(self, data, sort_key, reverse=False):
        self.data = list(data)
        self.sort_key = sort_key
        self.reverse = reverse
        self._index = …

13 0 Open

Database scaling & optimization medium

Offset vs Keyset Pagination in Python

Demonstrate offset-based pagination and keyset (cursor) pagination with a simple in-memory dataset, showing how each returns pages of records.

pagination keyset offset

Python

"""Demonstrate pagination using offset vs keyset (cursor) approach."""

ITEMS = [
    {"id": 1, "name": "Alice"},
    {"id": 2, "name": "Bob"},
    {"id": 3, "name": "Carol"},
    {"id": 4, "name": "David"},
    {"id": 5, "name": "Eve"},
]

def offset_paginate(items, page, page_size):
    """Return a page using offset…

14 0 Open

Browse by section

Each section groups closely related Python snippets.