sqlite-ops

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

SQLite Operations

SQLite 操作实践

Patterns for SQLite databases in Python projects.

Python项目中SQLite数据库的实践模式。

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

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

python

from contextlib import contextmanager

@contextmanager
def db_transaction(conn: sqlite3.Connection):
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise

WAL Mode

WAL模式

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

启用以支持并发读写：

python

conn.execute("PRAGMA journal_mode=WAL")

模式	读取	写入	适用场景
DELETE（默认）	写入时被阻塞	单线程写入	简单脚本
WAL	并发读取	单线程写入	Web应用、MCP服务器

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`

问题	解决方案
"数据库已锁定"	使用WAL模式
查询缓慢	添加索引，查看EXPLAIN QUERY PLAN
线程安全问题	使用 `check_same_thread=False`
外键未生效	执行 `PRAGMA foreign_keys=ON`

CLI Quick Reference

CLI快速参考

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

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

本地状态/配置存储
缓存层
事件日志
MCP服务器持久化
中小型数据集

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

如需了解详细模式，请查看：

```
./references/schema-patterns.md
```
- 状态、缓存、事件、队列表设计
```
./references/async-patterns.md
```
- aiosqlite增删改查、批量处理、连接池
```
./references/migration-patterns.md
```
- 版本迁移、JSON处理