sqlite-ops

# 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