SQLite Operations
Patterns for SQLite databases in Python projects.
Quick Connection
python
import sqlite3
def get_connection(db_path: str) -> sqlite3.Connection:
conn = sqlite3.connect(db_path, check_same_thread=False)
conn.row_factory = sqlite3.Row # Dict-like access
conn.execute("PRAGMA journal_mode=WAL") # Better concurrency
conn.execute("PRAGMA foreign_keys=ON")
return conn
Context Manager Pattern
python
from contextlib import contextmanager
@contextmanager
def db_transaction(conn: sqlite3.Connection):
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
WAL Mode
Enable for concurrent read/write:
python
conn.execute("PRAGMA journal_mode=WAL")
| Mode | Reads | Writes | Best For |
|---|---|---|---|
| DELETE (default) | Blocked during write | Single | Simple scripts |
| WAL | Concurrent | Single | Web apps, MCP servers |
Common Gotchas
| Issue | Solution |
|---|---|
| "database is locked" | Use WAL mode |
| Slow queries | Add indexes, check EXPLAIN QUERY PLAN |
| Thread safety | Use check_same_thread=False |
| FK not enforced | Run PRAGMA foreign_keys=ON |
CLI Quick Reference
bash
sqlite3 mydb.sqlite # Open database .tables # Show tables .schema items # Show schema .headers on && .mode csv && .output data.csv # Export CSV VACUUM; # Reclaim space
When to Use
- •Local state/config storage
- •Caching layer
- •Event logging
- •MCP server persistence
- •Small to medium datasets
Additional Resources
For detailed patterns, load:
- •
./references/schema-patterns.md- State, cache, event, queue table designs - •
./references/async-patterns.md- aiosqlite CRUD, batching, connection pools - •
./references/migration-patterns.md- Version migrations, JSON handling