shop-mcp
shop-mcp — 只读 SQLite MCP Server
基于 Python 的 MCP 服务器,通过 stdio 为 AI 代理(例如 Pi)提供对 SQLite 数据库 shop.db 的安全只读访问。
代理自行探索数据库模式、编写 SQL 查询并解决分析任务。服务器不包含现成答案——只有用于研究和执行只读查询的工具。
AI Agent (Pi)
│ stdio
▼
┌────────────────────┐
│ MCP Server │ list_tables / describe_table / read_query
└─────────┬──────────┘
▼
SQL validation ← только один SELECT / WITH ... SELECT
▼
read-only guard ← connection authorizer
▼
SQLite (mode=ro) ← файл физически невозможно изменить1. 环境要求
Python 3.13+
数据库文件
shop.db(已位于项目根目录)
Related MCP server: sqlite-analyst
2. 安装
uv syncuv 会创建虚拟环境并安装依赖项。无需手动创建 venv。
3. 数据库配置
数据库路径不是硬编码,而是通过环境变量配置。
选项 A — 环境变量(绝对路径):
export SHOP_DB_PATH=/absolute/path/to/shop.db
export MAX_RESULT_ROWS=1000 # опционально, default 1000选项 B — 不配置(fallback):如果 SHOP_DB_PATH 未设置,服务器使用项目根目录下的 shop.db。
也可以将 .env.example 复制为 .env 并在其中设置值(服务器从项目根目录读取 .env;环境变量优先):
cp .env.example .env4. 本地运行 MCP
uv run python -m shop_mcp.server服务器通过 stdio 运行,并在 stdin/stdout 上等待 MCP 协议——无需单独启动,它由客户端(Pi)自行启动。上面的手动启动仅用于调试。
不正确的配置(例如缺少数据库文件)会用 stderr 中的易读信息终止进程。
5. 将 MCP 连接到 Pi
Pi 通过 pi-mcp-adapter 包连接 MCP 服务器,并从项目根目录的 .mcp.json 读取配置。该文件已在仓库中:
{
"mcpServers": {
"shop": {
"command": "uv",
"args": ["run", "python", "-m", "shop_mcp.server"],
"cwd": "/Users/stalexsm/projects/shop-mcp"
}
}
}对于其他机器,请将 cwd 改成项目目录的绝对路径(或改为使用 SHOP_DB_PATH 变量的 env):
{
"mcpServers": {
"shop": {
"command": "uv",
"args": ["run", "python", "-m", "shop_mcp.server"],
"cwd": "/absolute/path/to/shop-mcp",
"env": {
"SHOP_DB_PATH": "/absolute/path/to/shop.db",
"MAX_RESULT_ROWS": "1000"
}
}
}
}不需要运行单独运行的 HTTP 服务器,也不需要在终端中手动保留 python server.py:Pi 会自行通过 stdio(惰性地,在首次访问工具时)启动进程。
如果适配器尚未安装:
pi install npm:pi-mcp-adapter然后在项目目录中重启 Pi。服务器工具将出现在 /mcp 面板中。
6. 可用工具
list_tables
数据库表列表,包含简短描述和行数。表结构是研究的起点。不需要 SQL。
describe_table
单表结构:列(name、type、nullable、primary_key、default)以及这种形式的 foreign keys:orders.customer_id -> customers.id。不存在的表会给出清晰的错误,并列出可用表。
read_query
执行一条只读 SQL 查询(SELECT 或 WITH ... SELECT)。
参数:
sql(必填)——查询文本。max_rows(可选)——请求的行数限制;服务器硬性上限MAX_RESULT_ROWS(默认 1000)不能被超过。
支持常规 SQLite 分析:JOIN、LEFT JOIN、GROUP BY、HAVING、ORDER BY、LIMIT/OFFSET、COUNT/SUM/AVG/MIN/MAX、DISTINCT、CASE、CTE。
结果会是结构化的 JSON:
{
"columns": ["name", "revenue"],
"rows": [["Ноутбук UltraBook 15", 6569270.0]],
"row_count": 1,
"truncated": false,
"execution_time_ms": 0.716
}truncated: true 表示由于大小限制只返回了部分行——请完善查询(LIMIT、WHERE、聚合),不必将数据视为完整。
7. 安全模型
有三层独立保护:
SQL 校验 —— 只允许一条语句,且必须以
SELECT/WITH开头。禁止INSERT、UPDATE、DELETE、REPLACE INTO、DROP、ALTER、CREATE、ATTACH、DETACH、VACUUM、REINDEX、PRAGMA以及任何其他能修改数据的操作。多语句查询(SELECT ...; DELETE ...)会被整体拒绝。校验器能理解字符串字面量、注释和带引号的标识符,因此字符串中出现的'DELETE'不被视为违规。Connection authorizer —— 所有不行于“读取”(SELECT / 读取表 / 调用函数)就会被在查询准备阶段拒绝。
mode=ro—— SQLite 的文件以只读模式打开;即使绕过了前两,也绝对无法执行物理写入。
错误会以清晰的形式返回給代理(Database failed: exception: no such column: foo)——没有 traceback、文件系统路径以及任何实现细节。
shop.db 是唯一只读来源(authoritative source):服务器不会修改文件内容或结构。这点由完整性测试保证(在运行破坏性操作前后都会进行校验和 checksum)。
8. 示例问题
将这些问题发送给 Pi 代理——它会自行调用 list_tables、describe_table 和 read_query:
告诉我所有可用的表,以及每个表包含什么信息。
消费最多的是谁?
前 5 名的热销产品是哪些?
收入最高的 3 个产品类别是?
2025 年我们产生了多少收入?
哪个客户下了最多的订单?
业务逻辑说明(代理会从工具描述中,这里不硬编码):
产品/类别的收入为
SUM(order_items.quantity * order_items.unit_price);带有状态
cancelled的状态不计入;年度收入基于
orders.order_date;如果没有订单,正确的答案是0。
关于国家的问题
有多少客户来自 Germany? —— 这个问题无法给出可靠答案:customers 表没有 country 字段(只有 first_name、last_name、email、phone、created_at)。服务器会把模式准确的信息给代理,代理必须说明这种数据不存在于数据库中,而不是从邮箱/电话中推断或推测国家。
9. 测试
uv run pytest测试套件(66 个):
tests/test_database.py—— 只读连接、模式发现、外键、连接关闭;tests/test_secrity.py—— 所有受被禁止的操作(规约第 24 节)、多语句、数据库的完整性测试;tests/test_tools.py—— 通过真实的 MCP 客户端会话(in-memory transport)对 MCP工具进行“集成测试”,包括错误处理;tests/test_analytics.py—— 与独立 SQLite 数据库进行“比对的”分析场景(第 27 节)、结果大小限制。
测试不会修改 shop.db(完整性验证用 checksum 比。
10. 问题排查
问题 | 原因与解决方案 |
|
|
在 Pi 中看不到工具 | 确保 |
| 一次 |
| 查询不是 |
结果不完整( | 触发了行数上限。添加 |
我改变行数限制 | 在环境中设置 |
项目结构
shop-mcp/
├── README.md
├── pyproject.toml
├── uv.lock
├── .env.example
├── .gitignore
├── .mcp.json # конфигурация MCP для Pi
├── shop.db # read-only source of truth
├── scripts/
│ └── smoke_stdio.py # ручной smoke-тест через реальный stdio
├── src/shop_mcp/
│ ├── __init__.py
│ ├── server.py # MCP-инструменты (stdio)
│ ├── database.py # read-only слой доступа к SQLite
│ ├── security.py # SQL validation + single-statement guard
│ ├── models.py # структуры результатов
│ └── config.py # SHOP_DB_PATH / MAX_RESULT_ROWS
└── tests/
├── test_database.py
├── test_security.py
├── test_tools.py
└── test_analytics.pyAvailable Tools
3 toolsdescribe_tableA
Describe the structure of one table: columns with name, type, nullable and primary_key flags, plus foreign-key relationships such as 'orders.customer_id -> customers.id'. Use this after list_tables to understand the schema before writing SQL. Pass a table name that was returned by list_tables; unknown names produce a clear error.
Inputs: table_name (required).
list_tablesA
List all tables available in the database, with a short description and the number of rows of each table. Use this first when you do not know the schema, before writing any SQL. No SQL is required.
Inputs: no named parameters.
read_queryA
Execute a single read-only SQL statement (SELECT or WITH ... SELECT) against the shop database. JOIN, LEFT JOIN, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, DISTINCT, CASE and CTE are supported. If you do not know the schema yet, call list_tables and describe_table first. Rules enforced by the server: exactly one statement per call (multi-statement SQL is rejected), and no data or schema modification (INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, ATTACH, DETACH, VACUUM, REINDEX, PRAGMA are rejected). The database is opened read-only, so writes are impossible. The result is structured: {columns, rows, row_count, truncated, execution_time_ms}. If truncated is true, only the first row_count rows were returned due to the server-side limit (max_rows, default 1000): refine the query, e.g. add LIMIT, WHERE or aggregation, instead of assuming the data is complete.
Inputs: sql (required), max_rows.
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 3 tools
Each tool serves a distinct, non-overlapping purpose: listing tables, describing a single table's schema, and executing read-only SQL queries. No ambiguity in selecting between them.
All tool names follow a consistent verb_noun snake_case pattern: list_tables, describe_table, read_query. This is uniform and predictable.
Three tools are exactly right for this focused read-only database exploration server. Each tool earns its place with no redundancy, and the count falls within the typical well-scoped range.
The tool surface covers the full workflow for safe database discovery: discover schema (list_tables), inspect structure (describe_table), and query data (read_query). No obvious gaps for the stated purpose, and write operations are intentionally excluded.
Maintenance
Related MCP Connectors
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Explore, query, and inspect SQLite databases with ease. List tables, preview results, and view det…
Query 40 databases from Claude, ChatGPT, or Cursor — on any device. Read-only, encrypted, audited.
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Related MCP Servers
- AlicenseAqualityDmaintenanceEnables safe, read-only SQL access to SQLite databases for AI agents, allowing schema exploration and SELECT queries with defense-in-depth protections.3MIT
- AlicenseNot gradedqualityDmaintenanceEnables AI assistants to explore and query SQLite databases through read-only tools, with defense-in-depth sandboxing preventing any data modifications.MIT
- AlicenseNot gradedqualityCmaintenanceEnables AI agents to safely query and explore SQLite databases through read-only, guard-protected tools that block writes, sensitive table access, and runaway queries.1MIT
- AlicenseBqualityCmaintenanceEnables AI assistants to safely run read-only SQL against SQLite databases through whitelist-validated queries, capped result sets, and confined file paths. It also exposes schema inspection so models can discover tables and columns before querying.2MIT