Reference library

Database scaling & optimization

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

6 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 easy

How to Batch Load JSON Data in Python for Database Optimization

This code parses JSON data into records and loads them in batches to simulate efficient database insertion, reducing load and improving performance.

json batching database

Python

import json
import time

def parse_and_load(data, batch_size=100):
    """
    Parse JSON data and batch-load into a list of dicts.
    Demonstrates batching for database efficiency.
    """
    records = json.loads(data)
    batches = []

    for i in range(0, len(records), batch_size):
        batch = records[i:i + …

12 0 Open

Database scaling & optimization easy

How to Convert Data with Scaling for Database Optimization in Python

A beginner-friendly helper that normalizes and scales numeric fields in a list of dicts, reducing storage footprint for database efficiency.

data conversion database scaling

Python

import json
from datetime import datetime

def convert_data(data: list[dict], scale_factor: int = 1) -> list[dict]:
    """Convert a list of dicts to a scaled, normalized format for database efficiency."""
    converted = []
    for row in data:
        normalized = {}
        for key, value in row.items():
          …

14 0 Open

Database scaling & optimization easy

How to Limit a Result Set to Top N Rows in Python

Sort a list of dictionaries by a numeric key and return only the top N results, formatted as a readable ranked list.

sorting slicing top-n

Python

import random

def top_n_mock(limit: int = 5):
    """Return a formatted top-N result set as a mock example."""
    # Simulated data source
    scores = [
        {"name": "Alice", "score": 87},
        {"name": "Bob", "score": 92},
        {"name": "Charlie", "score": 78},
        {"name": "Diana", "score": 95},
    …

16 0 Open

Database scaling & optimization easy

Rebalance Shard Ranges Across Nodes in Python

A mock rebalancing function that shuffles shard ranges and distributes them evenly across nodes using round-robin assignment.

sharding rebalancing dataclass

Python

import random
from dataclasses import dataclass

@dataclass
class Shard:
    id: int
    start: int
    end: int

def rebalance_shards(shards: list[Shard], node_count: int) -> dict[int, list[Shard]]:
    """Mock rebalancing of shard ranges across nodes."""
    all_ranges = [(s.start, s.end) for s in shards]
    random…

11 0 Open

Database scaling & optimization easy

Route SELECT Queries to Read Replicas in Python

A mock round-robin router that forwards SELECT queries to read replicas and sends writes to the primary.

database replication routing

Python

import random

class ReadReplicaRouter:
    """Round-robin router that sends SELECT queries to read replicas."""
    
    def __init__(self, replicas):
        self.replicas = replicas
        self.counter = 0
    
    def route(self, sql):
        if sql.strip().upper().startswith("SELECT"):
            replica = sel…

13 0 Open

Browse by section

Each section groups closely related Python snippets.