sqlite-utils - SQLite 数据工具箱

技能脚本入口

本技能附带两套等价包装脚本（Unix .sh + Windows PowerShell .ps1），提供友好子命令入口与安装自检，免去记忆原始参数。

macOS / Linux（bash）：

bash scripts/sqlite-utils-skill.sh <subcommand> [args...]
# 查看完整用法与示例
bash scripts/sqlite-utils-skill.sh help

Windows（PowerShell）：

.\scripts\sqlite-utils-skill.ps1 <subcommand> [args...]
# 若被执行策略拦截，用下面这种调用绕过：
powershell -ExecutionPolicy Bypass -File scripts\sqlite-utils-skill.ps1 <subcommand> [args...]

可用子命令： insert、query、tables、schema、rows、transform、upsert、update、delete、enable-fts、search、rebuild-fts、diff、export、create-index、drop-index、load-extension、enable-extensions、vacuum、optimize、shell、python

脚本选择规则： macOS / Linux 用 .sh；Windows 用 .ps1。两者子命令完全一致。

> 脚本仅作便捷封装，最终调用系统的 sqlite-utils 二进制；运行前请确保已安装对应工具（安装方式见下文各节）。说明：SkillHub 不支持 .cmd / .bat 文件类型，故不附带 Windows cmd 版；无 PowerShell 的环境可用 Git Bash 运行 .sh 版。个别依赖 Unix 管道工具的子命令（如 gron 的 sed / fzf）在 Windows 下建议用 Git Bash 运行 .sh 版。

这是什么

sqlite-utils (simonw/sqlite-utils, 4.2k ⭐) 是 SQLite 的 CLI 工具 + Python 库。核心能力：从 CSV/JSON/Excel/TSV/Parquet 导入数据、SQL 查询、数据转换、数据库 diff、全文搜索 (FTS)、启用扩展 (SpatiaLite、sqlite-vec 等)。它是 Datasette 生态的基础组件，适合轻量级分析数据库场景。

• GitHub: https://github.com/simonw/sqlite-utils
• 文档: https://sqlite-utils.datasette.io/
• Python 编写，pip install sqlite-utils，单命令 sqlite-utils

何时使用我

当用户出现以下意图时加载此技能：

• "sqlite-utils"、"sqlite 工具"、"导入 csv 到 sqlite"
• "用 SQL 查询 csv/json"、"csv 做复杂 join/聚合"
• "sqlite 全文搜索"、"FTS5"、"模糊搜索"
• "数据库 diff/对比"、"schema 对比"、"数据对比"
• "启用 sqlite 扩展"、"spatialite"、"sqlite-vec 向量搜索"
• "转换数据格式"、"csv 转 json"、"json 规范化"
• "轻量级分析数据库"、"不想装 PostgreSQL/MySQL"
• "Python 脚本操作 SQLite"、"批量插入/更新"

核心能力

1. 数据导入 (核心功能)

# CSV -> SQLite (自动建表、推断类型)
sqlite-utils insert data.db table_name data.csv

# JSON -> SQLite
sqlite-utils insert data.db table_name data.json

# JSON Lines -> SQLite
sqlite-utils insert data.db table_name data.jsonl

# Excel -> SQLite (需 openpyxl)
sqlite-utils insert data.db table_name data.xlsx

# TSV -> SQLite
sqlite-utils insert data.db table_name data.tsv

# Parquet -> SQLite (需 pyarrow)
sqlite-utils insert data.db table_name data.parquet

# 从 stdin 导入
cat data.csv | sqlite-utils insert data.db table_name -

# 指定主键、类型、不推断
sqlite-utils insert data.db users users.csv --pk id --type name:text --type age:integer

# 批量导入多文件 (通配符)
sqlite-utils insert data.db "sales_*" sales_*.csv

2. SQL 查询

# 执行查询，输出表格
sqlite-utils query data.db "SELECT * FROM users WHERE age > 30"

# JSON 输出
sqlite-utils query data.db "SELECT * FROM users" --json

# CSV 输出
sqlite-utils query data.db "SELECT * FROM users" --csv

# TSV 输出
sqlite-utils query data.db "SELECT * FROM users" --tsv

# 参数化查询 (防注入)
sqlite-utils query data.db "SELECT * FROM users WHERE age > ?" 30

# 多语句 (事务)
sqlite-utils query data.db "
  CREATE INDEX idx_email ON users(email);
  SELECT * FROM users WHERE email LIKE '%@gmail.com';
"

# 交互式 SQL shell
sqlite-utils data.db

3. 表操作

# 列出所有表
sqlite-utils tables data.db

# 显示表结构
sqlite-utils schema data.db users

# 显示表行数
sqlite-utils rows data.db users

# 重命名表
sqlite-utils transform data.db users --rename new_users

# 删除表
sqlite-utils drop-table data.db old_table

# 创建索引
sqlite-utils create-index data.db users email

# 删除索引
sqlite-utils drop-index data.db idx_users_email

4. 数据转换 (transform - 强大!)

# 重命名列
sqlite-utils transform data.db users --rename email,email_address

# 修改列类型
sqlite-utils transform data.db users --type age:integer --type salary:real

# 设置/移除主键
sqlite-utils transform data.db users --pk id
sqlite-utils transform data.db users --drop-pk

# 设置 NOT NULL
sqlite-utils transform data.db users --not-null email

# 设置默认值
sqlite-utils transform data.db users --default status:active

# 添加/删除列
sqlite-utils transform data.db users --add created_at:timestamp
sqlite-utils transform data.db users --drop old_column

# 列重排序
sqlite-utils transform data.db users --reorder id,name,email,created_at,*

# 执行任意 SQL 更新
sqlite-utils transform data.db users --sql "UPDATE users SET name = upper(name)"

5. 数据更新与 Upsert

# 插入或更新 (基于主键)
sqlite-utils upsert data.db users users.csv --pk id

# 批量更新 (从 CSV)
sqlite-utils update data.db users users.csv --pk id

# 删除行
sqlite-utils delete data.db users --where "age < 18"

# 清空表
sqlite-utils truncate data.db users

6. 全文搜索 (FTS5) - 杀手级功能

# 启用 FTS (自动创建虚表 + 触发器同步)
sqlite-utils enable-fts data.db users name,email,bio

# 搜索
sqlite-utils search data.db users "john doe"
sqlite-utils search data.db users "john*" --limit 10

# 高级搜索 (FTS5 语法)
sqlite-utils search data.db users '"john doe" OR jane' --quote
sqlite-utils search data.db users "name:john bio:engineer"

# 搜索并返回 JSON
sqlite-utils search data.db users "error" --json

# 重建索引
sqlite-utils rebuild-fts data.db users

7. 数据库 Diff/对比

# Schema 对比
sqlite-utils diff schema old.db new.db

# 数据对比 (指定表)
sqlite-utils diff data old.db new.db --table users

# 完整对比
sqlite-utils diff old.db new.db

# 输出为 JSON
sqlite-utils diff old.db new.db --json

8. 导出与转换

# 表导出为 CSV
sqlite-utils export data.db users users.csv

# 表导出为 JSON
sqlite-utils export data.db users users.json

# 表导出为 JSONL
sqlite-utils export data.db users users.jsonl --nl

# 表导出为 Excel
sqlite-utils export data.db users users.xlsx

# 整库导出 (所有表)
sqlite-utils export data.db --all output_dir/

# 查询结果导出
sqlite-utils query data.db "SELECT * FROM users WHERE active=1" --csv > active_users.csv

9. 扩展管理

# 列出已加载扩展
sqlite-utils extensions data.db

# 加载扩展 (需编译好 .so/.dylib/.dll)
sqlite-utils load-extension data.db /path/to/spatialite.so

# 启用常用扩展 (内置)
sqlite-utils enable-extensions data.db  # 启用所有可用

# 常用扩展：
# - spatialite: 空间数据 (GIS)
# - sqlite-vec: 向量搜索 (RAG)
# - sqlite-regex: 正则函数
# - sqlite-ulid: ULID 生成
# - sqlite-fastrand: 快速随机数

10. Python API (库使用)

import sqlite_utils

# 连接/创建数据库
db = sqlite_utils.Database("data.db")

# 创建表并插入
db["users"].insert_all([
    {"id": 1, "name": "Alice", "email": "alice@example.com"},
    {"id": 2, "name": "Bob", "email": "bob@example.com"},
], pk="id")

# 批量插入 (生成器，流式)
def gen():
    for row in large_csv_reader():
        yield row
db["large_table"].insert_all(gen(), pk="id", batch_size=1000)

# 查询
rows = list(db.query("SELECT * FROM users WHERE age > ?", [30]))

# 表转换
db["users"].transform(types={"age": int}, pk="id")

# 全文搜索
db["users"].enable_fts(["name", "bio"])
results = list(db["users"].search("alice"))

# 启用扩展
db.enable_extension("spatialite")

典型工作流示例

场景 1：CSV 做复杂分析 (替代 Excel/透视表)

# 1. 导入多个 CSV 到同一库
sqlite-utils insert sales.db orders orders.csv --pk order_id
sqlite-utils insert sales.db customers customers.csv --pk customer_id
sqlite-utils insert sales.db products products.csv --pk product_id

# 2. 建索引加速连接
sqlite-utils create-index sales.db orders customer_id
sqlite-utils create-index sales.db orders product_id

# 3. 复杂 SQL 分析
sqlite-utils query sales.db "
  WITH customer_orders AS (
    SELECT c.customer_id, c.name, c.region,
           COUNT(o.order_id) as order_count,
           SUM(o.amount) as total_spent
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.order_date >= '2024-01-01'
    GROUP BY c.customer_id
  )
  SELECT region,
         COUNT(*) as customers,
         AVG(total_spent) as avg_ltv,
         SUM(total_spent) as region_revenue
  FROM customer_orders
  GROUP BY region
  ORDER BY region_revenue DESC;
" --table

场景 2：日志全文搜索引擎

# 1. 导入 JSON Lines 日志
sqlite-utils insert logs.db logs app.log.jsonl --pk id

# 2. 启用全文搜索 (message 字段)
sqlite-utils enable-fts logs.db logs message,service,level

# 3. 搜索错误
sqlite-utils search logs.db logs "timeout OR connection refused" --json

# 4. 结合结构化查询
sqlite-utils query logs.db "
  SELECT * FROM logs_fts
  WHERE logs_fts MATCH 'error'
  AND level = 'ERROR'
  AND timestamp > datetime('now', '-1 day')
  ORDER BY rank
  LIMIT 20;
"

场景 3：多数据源 ETL 管道

# 1. 从 API 拉取 -> JSON -> SQLite
curl -s api.example.com/users | sqlite-utils insert staging.db users -

# 2. 清洗转换
sqlite-utils transform staging.db users \
  --rename "user_id,id" \
  --type "created_at:timestamp" \
  --drop "internal_notes"

# 3. Upsert 到生产库
sqlite-utils upsert prod.db users staging.db --table users --pk id

# 4. 验证
sqlite-utils query prod.db "SELECT COUNT(*) FROM users"

场景 4：数据库版本对比/迁移验证

# 1. 迁移前后对比
sqlite-utils diff prod_backup.db prod.db

# 2. 只看数据差异
sqlite-utils diff prod_backup.db prod.db --table orders --data-only

# 3. 导出差异报告
sqlite-utils diff prod_backup.db prod.db --json > migration_diff.json

场景 5：向量搜索 (RAG 原型)

# 1. 启用 sqlite-vec 扩展 (需预编译)
sqlite-utils load-extension data.db /path/to/vec0.so

# 2. 创建向量表
sqlite-utils query data.db "
  CREATE VIRTUAL TABLE embeddings USING vec0(
    id INTEGER PRIMARY KEY,
    text TEXT,
    embedding FLOAT[384]
  );
"

# 3. 插入向量 (Python)
# 使用 sentence-transformers 生成 embedding
# db["embeddings"].insert({"id": 1, "text": "doc", "embedding": vec})

# 4. 相似度搜索
sqlite-utils query data.db "
  SELECT text, vec_distance_cosine(embedding, ?) as dist
  FROM embeddings
  ORDER BY dist LIMIT 5;
" "[0.1, 0.2, ...]"

场景 6：空间数据 (SpatiaLite)

# 1. 加载 SpatiaLite
sqlite-utils load-extension data.db /path/to/mod_spatialite.so

# 2. 初始化空间元数据
sqlite-utils query data.db "SELECT InitSpatialMetaData(1);"

# 3. 导入 GeoJSON
sqlite-utils insert data.db places places.geojson --pk id

# 4. 空间查询
sqlite-utils query data.db "
  SELECT name, ST_Distance(geom, MakePoint(116.39, 39.90)) as dist_km
  FROM places
  WHERE ST_DWithin(geom, MakePoint(116.39, 39.90), 10)
  ORDER BY dist_km;
"

命令速查表

进阶技巧

内存数据库 (临时分析)

# 使用 :memory: 数据库，退出即丢弃
sqlite-utils :memory: "SELECT * FROM read_csv('data.csv')" --table

并行导入大文件

# Python API 并行批量插入
import sqlite_utils
from concurrent.futures import ThreadPoolExecutor

db = sqlite_utils.Database("big.db")
db["big_table"].create_table({"id": int, "data": str}, pk="id")

with ThreadPoolExecutor(4) as executor:
    for chunk in pd.read_csv("huge.csv", chunksize=50000):
        executor.submit(db["big_table"].insert_all, chunk.to_dict("records"), batch_size=1000)

触发器自动维护

# transform --sql 可创建触发器
sqlite-utils transform data.db users --sql "
  CREATE TRIGGER update_timestamp
  AFTER UPDATE ON users
  BEGIN
    UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
  END;
"

安装方式

# pip (推荐)
pip install sqlite-utils
# 或隔离环境
pipx install sqlite-utils

# 可选依赖 (按需)
pip install "sqlite-utils[excel]"      # Excel 支持
pip install "sqlite-utils[parquet]"    # Parquet 支持
pip install "sqlite-utils[full]"       # 所有可选依赖

# Homebrew (macOS)
brew install sqlite-utils

故障排查

相关技能

• xsv - CSV 统计/切片/连接 (导入前预处理)
• visidata - 交互式浏览 (导入后可视化探索)
• miller - 流式数据清洗 (导入前转换)
• dasel - 跨格式查询 (简单查询无需导入)
• yq - 格式转换 (JSON/CSV 互转)
• rq - 10+ 格式专用转换器

技能版本: 1.0.0 | 作者: asdw741111 | 适用平台: OpenCode, Claude Code, Cursor, Codex CLI, Windsurf, Gemini CLI