Python Code
Samples
Copy-ready Python snippets by topic and difficulty — short, focused, and runnable in the browser editor.
Create an In-Memory SQLite Table and Query It in Python
This code creates an in-memory SQLite database, defines an employees table, inserts sample rows, and runs a filtered query with sorted results.
import sqlite3
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department TEXT NOT NULL,
salary REAL
)
""")
employees = [
(1, "Alice", "Engineering", 95000),
(2, "Bob", "…Export SQLite Query Results to CSV in Python
Connects to a SQLite database, runs a query, and writes the result rows and column headers to a CSV file using the standard library.
import sqlite3
import csv
def export_query_to_csv(db_path, query, csv_path):
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
cursor.execute(query)
rows = cursor.fetchall()
column_names = [description[0] for description in cursor.description]
with open(csv_path, 'w', newline='', encodi…How to Bulk Insert Rows into SQLite in Python
Insert many rows into an SQLite table in one call with cursor.executemany, then verify them with a SELECT query.
import sqlite3
# Create an in-memory database and a table
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("CREATE TABLE products (name TEXT, price REAL, quantity INTEGER)")
# Data to insert in bulk
products = [
("Laptop", 999.99, 5),
("Mouse", 19.99, 50),
("Keyboard", 49.99, 30),…Parameterize SQL queries in Python to prevent SQL injection
Safely fetch users from a SQLite database using parameterized queries to prevent SQL injection attacks.
import sqlite3
def get_users_by_name(name):
"""Fetch users safely using parameterized query."""
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
# Create sample table and data
cursor.execute('CREATE TABLE users (id INTEGER, name TEXT)')
cursor.executemany('INSERT INTO users (name…Read SQLite database with sqlite3 module in Python
Connect to a SQLite database and query rows with the standard library sqlite3 module, returning results as dictionaries.
import sqlite3
from pathlib import Path
# Create an in-memory database and a sample table
connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department TEXT NOT NULL,
salary REAL
)
""")
# Inser…How to Backup an SQLite Database with a Timestamp in Python
Backs up an SQLite database file to a timestamped copy using the sqlite3 backup API.
import sqlite3
import shutil
from datetime import datetime
from pathlib import Path
def backup_database(db_path: str, backup_dir: str = "backups") -> Path:
db = Path(db_path)
backup_folder = Path(backup_dir)
backup_folder.mkdir(exist_ok=True)
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
…Restore sqlite from latest backup file in Python
This script finds the most recently modified backup file in a directory and restores it to the main database path, then verifies the restored data.
import sqlite3
import glob
import os
import shutil
def restore_latest_backup(db_path, backup_dir):
backups = sorted(glob.glob(os.path.join(backup_dir, "*.db")), key=os.path.getmtime)
if not backups:
raise FileNotFoundError("No backup files found")
latest = backups[-1]
shutil.copy2(latest, db_p…How to Implement Incremental Load with Watermark by updated_at in Python
Load only new or changed rows into SQLite by comparing an updated_at timestamp against a stored watermark, returning counts and the new watermark.
import sqlite3
from datetime import datetime, timedelta
def watermark_incremental_load(db_path, table_name, last_watermark, source_data):
"""Load only rows with updated_at greater than the last watermark."""
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# Create table if it doesn't exist
…How to Implement SCD Type 1 Overwrite in Python with SQLite
Implement SCD Type 1 dimension updates in Python using SQLite — overwrite existing rows with new data while preserving keys.
import sqlite3
# Simulate a dimension table with SCD Type 1 (overwrite)
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
# Create dimension table
cursor.execute("""
CREATE TABLE customer_dim (
customer_id INTEGER PRIMARY KEY,
customer_name TEXT,
city TEXT,
updated_at TEXT…Use pytest fixture to mock a database connection in Python
This code shows how to use a pytest fixture and unittest.mock to replace a database connection with a Mock, enabling isolated tests without a real database.
import pytest
import sqlite3
from unittest.mock import Mock
class Database:
def __init__(self, connection):
self.connection = connection
def get_user(self, user_id):
cursor = self.connection.cursor()
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))
return cursor.…Outbox pattern reliable publish in Python with SQLite
Implements a transactional outbox with SQLite, ensuring reliable message publishing by storing events in the same DB transaction as business changes.
import sqlite3
from contextlib import contextmanager
from datetime import datetime, timezone
class Outbox:
def __init__(self, db_path=":memory:"):
self.conn = sqlite3.connect(db_path)
self.conn.execute("""
CREATE TABLE IF NOT EXISTS outbox (
id INTEGER PRIMARY KEY AUTO…Implement the Transactional Outbox Pattern with SQLite in Python
A Python implementation of the transactional outbox pattern using SQLite, ensuring atomic writes of order data and outbox events in a single transaction while supporting reliable message publishing and consumption.
import sqlite3
from dataclasses import dataclass
from datetime import datetime, timezone
import json
@dataclass
class Order:
order_id: str
amount: float
status: str
class TransactionalOutbox:
def __init__(self, db_path=":memory:"):
self.conn = sqlite3.connect(db_path)
self._create_tab…How to Create a Deep Health Check Database in Python
Setup a SQLite-backed health check database, insert mock data with response times and statuses, and generate a report ordered by most recent check.
import sqlite3
from datetime import datetime, timedelta
from pathlib import Path
DB_PATH = Path("deep_health_check.db")
def setup_database():
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS health_checks (
id INTEGER PRIMARY KEY AU…How to Demonstrate the Shared Database Antipattern in Python
This code simulates a shared database where multiple services write and read the same SQLite table, illustrating tight coupling and its pitfalls.
import sqlite3
from pathlib import Path
def create_shared_db(db_path: Path) -> None:
"""Mock demonstrating the shared database antipattern where multiple
services access the same database, causing tight coupling."""
conn = sqlite3.connect(db_path)
cur = conn.cursor()
cur.execute("""
CREATE…How to select specific columns in Python with SQLite
A reusable function that connects to a SQLite database and returns only the requested columns from a given table.
import sqlite3
def select_pruned_columns(db_path, table, columns):
with sqlite3.connect(db_path) as conn:
cursor = conn.cursor()
col_list = ", ".join(columns)
query = f"SELECT {col_list} FROM {table}"
return cursor.execute(query).fetchall()
if __name__ == "__main__":
conn = sq…Composite index leftmost prefix in Python
Simulate a composite index in SQLite and check whether query columns match the leftmost prefix rule for index usage.
import sqlite3
def get_indexed_columns(table_name):
"""Simulate a composite index by reading column names that start with 'idx_'."""
conn = sqlite3.connect(":memory:")
conn.execute(f"CREATE TABLE {table_name} (id INTEGER, idx_col1 TEXT, idx_col2 INTEGER, other TEXT)")
conn.execute(f"CREATE INDEX idx_…Database Helper in Python with SQLite Scaling Optimization
Build a beginner-friendly SQLite database helper class with WAL, indexed queries, and efficient batch inserts for scaling.
import sqlite3
from contextlib import contextmanager
class DatabaseHelper:
"""Beginner-friendly helper for SQLite database operations with scaling tips."""
def __init__(self, db_path):
self.db_path = db_path
@contextmanager
def connection(self):
"""Context manager for automatic comm…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…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 Create a Covering Index with INCLUDE Columns in Python
Create a covering index with INCLUDE columns in SQLite from Python and inspect the query plan to confirm the index covers the query.
import sqlite3
def create_covering_index_mock():
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT,
salary INTEGER
)
""")
employe…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.
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…How to Eager Load with JOIN to Reduce N+1 Queries in Python
Demonstrates eager loading with a SQL JOIN to reduce N+1 query patterns down to a single database call when fetching related data.
import sqlite3
def eager_load_join_reduce(mock_db_path=":memory:"):
"""Demonstrate eager loading where joins reduce query count from N+1 to 1."""
conn = sqlite3.connect(mock_db_path)
cursor = conn.cursor()
cursor.executescript(
"""
CREATE TABLE authors (id INTEGER PRIMARY KEY, name TE…How to Explain SQLite Query Plans in Python
Build a Python function that runs EXPLAIN QUERY PLAN on SQLite in-memory tables and prints the optimizer's execution plan for any SELECT statement.
import sqlite3
def explain_query(sql: str) -> str:
"""Return the SQLite query plan for the given SQL statement."""
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
# Create sample data for a realistic plan
cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
c…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.
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…Browse by section
Each section groups closely related Python snippets.
Guide: free Python code samples library
Copy-ready Python snippets for learners and developers
PythonSkillset code samples are short, focused examples organised by topic and difficulty. Every snippet is server-rendered HTML — readable by search engines and easy to copy. Open any sample, read the notes, copy the code, then press Try in editor to run it in the browser with Pyodide.
How to use this library
- Pick a topic section — strings, lists, files, functions, and more
- Open a sample, read How it works, and copy the code block
- Run it in the IDE, tweak values, then take a related quiz or tutorial lesson
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.