BI Query - 智能问数

Connect to MySQL, generate SQL from natural language, execute safely, and interpret results.

Config

Database connection is configured in config.json (same directory as SKILL.md).

{
  "host": "127.0.0.1",
  "port": 3306,
  "user": "root",
  "password": "",
  "database": "",
  "default_limit": 1000,
  "query_timeout": 30
}

First run: Edit config.json with real connection info, then generate schema cache.

Workflow

Step 0: Ensure pymysql is installed

pip install pymysql

Step 1: Generate schema cache (first time or after schema changes)

python scripts/get_schema.py --mode index --output references/schema.md

This creates a lightweight table index in references/schema.md. Only re-run when database structure changes.

Step 2: Answer user questions

When a user asks a data question:

1. Read schema index: read references/schema.md — identify which tables are relevant
2. Get table details (on demand): python scripts/get_schema.py --mode detail --tables t1,t2 — fetch column details for relevant tables only
3. Generate SQL: Based on table structures, write the appropriate SQL query
4. Execute: python scripts/query_mysql.py --sql "SELECT ..."
5. Interpret: Analyze the returned JSON and present a clear, natural-language answer

SQL Generation Guidelines

• Always use proper table aliases for readability
• Prefer WHERE filters to reduce data volume
• Use appropriate aggregation (COUNT, SUM, AVG, GROUP BY) for statistical questions
• Add ORDER BY when ranking or trending
• Use LIMIT — the script auto-injects it, but explicit limits are better for performance
• Handle NULL values appropriately (IFNULL, COALESCE)
• For time-range questions, use proper date functions: DATE(created_at), YEAR(), MONTH(), etc.
• For large tables (>1M rows), always include a WHERE clause with index-friendly conditions

Response Format

After getting query results, present:

1. Answer: Clear natural language summary of findings
2. Key numbers: Highlight important metrics
3. Data table: Show results in a readable format (first 20 rows max)

重要限制：不要向用户展示生成的 SQL 语句。用户只需要自然语言的答案，不需要看到技术细节。

Error Handling

• Connection failed: Check config.json connection info
• Query timeout: Suggest adding more WHERE filters, or narrow the date range
• No results: Confirm with user whether filters are correct
• SQL error: Read the error message, fix the SQL, retry once

Security

All queries are validated server-side:

• Only SELECT, SHOW, DESCRIBE, EXPLAIN allowed
• INSERT, UPDATE, DELETE, DROP, etc. are blocked
• Multiple statements (semicolons) are blocked
• Default LIMIT 1000 applies when no explicit LIMIT is set
• Query timeout defaults to 30 seconds