Reference library

Database scaling & optimization

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

5 matches

Database indexing and query timing optimization in Python

Create SQLite indexes and time query performance to measure speedup for large table lookups in Python.

import sqlite3
import time


def time_query(db_path, query, params=()):
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA journal_mode = WAL")
    start = time.perf_counter()
    result = conn.execute(query, params).fetchall()
    elapsed = time.perf_counter() - start
    conn.close()
    return result, ela…

14 0 Open

Database scaling & optimization easy

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 *.

sqlite mock testing

Python

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 Create a Database Helper Class for Beginners in Python

Build a beginner-friendly SQLite helper class with indexing and batch inserts to optimize database queries in Python.

sqlite database indexing

Python

import sqlite3


class DatabaseHelper:
    def __init__(self, db_path):
        self.connection = sqlite3.connect(db_path)
        self.cursor = self.connection.cursor()

    def create_table_with_index(self, table_name, columns, indexed_column):
        columns_sql = ", ".join(f"{name} {dtype}" for name, dtype in col…

13 0 Open

Database scaling & optimization easy

How to Insert a Mock Route Record Using SQLite in Python

This code creates an in-memory SQLite table for routes and inserts a mock route record, returning the inserted row for verification.

sqlite database insert

Python

import sqlite3
from datetime import datetime

def insert_mock_record(db_path=":memory:"):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS routes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            origin TEXT NOT NULL,
            des…

14 0 Open

Database scaling & optimization easy

How to Optimize SQLite Database Performance in Python

A Python helper that creates an index, enables WAL mode, and tunes synchronous settings to optimize SQLite database performance.

sqlite database optimization

Python

import sqlite3

DATABASE_PATH = "beginners.db"
UNOPTIMIZED_TABLE_SCHEMA = """
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL
)
"""


def optimize_database(db_path: str = DATABASE_PATH) -> dict:
    with sqlite3.connect(db_path) as connection:
        curs…

14 0 Open

Browse by section

Each section groups closely related Python snippets.