smart-query

Smart Query - Intelligent Data Querying

Securely connect to online databases through an SSH tunnel, supporting natural language queries and SQL execution.

Trigger Scenarios

• User asks "query some xxx data", "help me look at the xxx table"
• User needs to query an online database
• User asks "what tables are there", "what is the table structure"

Quick Start

1. Test Connection

python .opencode/skills/smart-query/scripts/db_connector.py

2. Execute SQL Query

python .opencode/skills/smart-query/scripts/query.py "SELECT * FROM table_name LIMIT 10"
python .opencode/skills/smart-query/scripts/query.py "SHOW TABLES"
python .opencode/skills/smart-query/scripts/query.py "DESC table_name"

Parameters:

• -n 50: Limit the number of returned rows
• -f json: JSON format output
• --raw: Output raw results (including metadata)

3. Generate Table Structure Documentation

python .opencode/skills/smart-query/scripts/schema_loader.py

Generate references/schema.md, including all table structure information.

Natural Language Query Workflow

1. Understand user intent: Analyze what data the user wants to query
2. Consult table structure: Read references/schema.md to understand the table structure
3. Generate SQL: Write correct SQL based on the table structure
4. Execute query: Use query.py to execute
5. Interpret results: Explain the query results in plain language

Configuration Instructions

Configuration file:config/settings.json

{
  "ssh": {
    "host": "SSH跳板机地址",
    "port": 22,
    "username": "用户名",
    "password": "密码",
    "key_file": null
  },
  "database": {
    "type": "mysql",
    "host": "数据库内网地址",
    "port": 3306,
    "database": "库名",
    "username": "数据库用户",
    "password": "数据库密码"
  }
}

Share with Colleagues

1. Copy the entire smart-query/ directory
2. Colleague copies config/settings.json.example as settings.json
3. Fill in your own SSH and database connection information
4. Install dependencies:pip install paramiko sshtunnel pymysql

Security Tips

• config/settings.json contains sensitive information,do not commit to Git
• It is recommended to add config/settings.json to .gitignore
• Only execute SELECT queries, avoid UPDATE/DELETE operations

Dependency Installation

pip install paramiko sshtunnel pymysql

Script List

Script	Purpose
scripts/db_connector.py	SSH tunnel + database connection, can be run separately to test the connection
scripts/query.py	Execute SQL queries, supports table/JSON output
scripts/schema_loader.py	Load table structure, generate schema.md

Reference Documentation

Document	Description
references/schema.md	Database table structure (generated by running schema_loader.py)