sqlite-ops

01 前言 Front Matter

name

sqlite-ops

description

Patterns for SQLite databases in Python projects - state management, caching, and async operations. Triggers on: sqlite, sqlite3, aiosqlite, local database, database schema, migration, wal mode.

allowed-tools

Read Write Bash

compatibility

Requires Python 3.8+ with sqlite3 (standard library) or aiosqlite for async.

03 SKILL.md 正文

SQLite Operations

Patterns for SQLite databases in Python projects.

Quick Connection

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

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:

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

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

01 前言 Front Matter

02 资源文件 194

03 SKILL.md 正文

SQLite Operations

Quick Connection

Context Manager Pattern

WAL Mode

Common Gotchas

CLI Quick Reference

When to Use

Additional Resources

04 同仓库更多 Skills

model-hierarchy-skill

zoom-mcp

zoom-apps-sdk

windows

whiteboard

websockets