Database scaling & optimization
Indexing, connection pooling, read replicas, query tuning, and throughput-aware SQL.
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…How to Speed Up Column Lookups with DataFrame Index in Python
Use pandas set_index to make repeated column value lookups O(1)-style fast instead of scanning the whole DataFrame each time.
import pandas as pd
# Mock dataset with duplicate customer IDs
data = {"customer_id": [101, 102, 103, 101, 104, 102],
"order_amount": [250.0, 85.5, 300.0, 175.25, 420.0, 95.75]}
df = pd.DataFrame(data)
df = df.set_index("customer_id")
# Simulated lookup request
search_id = 102
# Fast index-based lookup (no…How to enforce a unique index constraint in Python
Mock a database unique index in Python that rejects duplicate rows based on one or more columns.
class MockIndex:
def __init__(self, columns):
self.columns = columns
self._values = set()
def insert(self, row):
key = tuple(row[col] for col in self.columns)
if key in self._values:
raise ValueError(f"Duplicate key {key} for columns {self.columns}")
self._v…Browse by section
Each section groups closely related Python snippets.
Database scaling & optimization — Python code examples
What you will find here
This page collects database scaling & optimization snippets — short, copy-ready Python you can paste into our free online IDE and run without installing anything. Each sample includes a plain-English explanation and the full source code.
Samples vs tutorials and challenges
Samples are quick reference — one concept per page. For step-by-step teaching, use our Python tutorials. To test yourself, try quizzes or coding challenges. Clean up style with the Python formatter.