northseadl/norix-skills / adb-mysql
adb-mysql
Verified阿里云 AnalyticDB (ADB) for MySQL 只读数据分析。 多维分析(计数/聚合/时间序列)、交叉验证、Schema 文档生成、多 Profile。
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 3.1 KB |
| adbexecutable | script | 1.5 KB |
| agents/openai.yaml | config | 199 B |
| scripts/adb_core.py | script | 12.3 KB |
| scripts/analyze.py | script | 4.6 KB |
| scripts/config.py | script | 4.5 KB |
| scripts/credential_store.py | script | 9.1 KB |
| scripts/query.py | script | 1.4 KB |
| scripts/schema.py | script | 7.4 KB |
scripts/adb_core.py
View on GitHub#!/usr/bin/env python3
"""ADB Analytics Core — Connection, Safety, and Output Engine."""
import csv
import io
import json
import os
import re
import shutil
import subprocess
import sys
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
sys.path.insert(0, SCRIPT_DIR)
from credential_store import CredentialStore
CONFIG_DIR = os.path.expanduser("~/.agents/data/adb-mysql")
PROFILES_FILE = os.path.join(CONFIG_DIR, "profiles.json")
SCHEMA_DIR = os.path.join(CONFIG_DIR, "schema")
MAX_ROWS = 200
_VAULT_SENTINEL = "***vault***"
_cred_store = CredentialStore("adb-mysql", CONFIG_DIR)
# ── Logging ──────────────────────────────────────────────────────────────────
class Log:
@staticmethod
def ok(msg): print(f"\033[0;32m✓\033[0m {msg}")
@staticmethod
def err(msg): print(f"\033[0;31m✗\033[0m {msg}", file=sys.stderr)
@staticmethod
def warn(msg): print(f"\033[1;33m⚠\033[0m {msg}", file=sys.stderr)
@staticmethod
def info(msg): print(f"\033[0;34mℹ\033[0m {msg}", file=sys.stderr)
# ── Profile Management ──────────────────────────────────────────────────────
def ensure_config_dir():
os.makedirs(CONFIG_DIR, mode=0o700, exist_ok=True)
def load_profiles():
if not os.path.exists(PROFILES_FILE):
return {"default": None, "profiles": {}}
with open(PROFILES_FILE, "r") as f:
return json.load(f)
def save_profiles(data):
ensure_config_dir()
# Extract passwords to encrypted vault before writing JSON
for name, profile in data.get("profiles", {}).items():
pw = profile.get("password", "")
if pw and pw != _VAULT_SENTINEL:
_cred_store.set(f"profile:{name}", pw)
profile["password"] = _VAULT_SENTINEL
with open(PROFILES_FILE, "w") as f:
json.dump(data, f, indent=2, ensure_ascii=False)
os.chmod(PROFILES_FILE, 0o600)
def get_profile(name=None):
"""Resolve connection profile by name, or use default."""
data = load_profiles()
if not data["profiles"]:
Log.err("无可用连接配置。请先运行: ./adb config add")
sys.exit(1)
target = name or data.get("default")
if not target or target not in data["profiles"]:
target = next(iter(data["profiles"]))
return target, data["profiles"][target]
# ── Dependency Check ─────────────────────────────────────────────────────────
def ensure_pymysql():
"""Auto-detect pymysql; prompt to install if missing. Returns the module."""
try:
import pymysql
return pymysql
except ImportError:
pass
Log.warn("未检测到 pymysql(纯 Python MySQL 驱动)")
try:
answer = input(" 是否自动安装? (pip install pymysql) [y/N]: ").strip().lower()
except (EOFError, KeyboardInterrupt):
answer = ""
if answer != "y":
Log.err("pymysql 未安装。请手动运行: pip install pymysql")
sys.exit(1)
Log.info("正在安装 pymysql ...")
result = subprocess.run(
[sys.executable, "-m", "pip", "install", "pymysql"],
capture_output=True, text=True,
)
if result.returncode != 0:
Log.err(f"安装失败:\n{result.stderr}")
sys.exit(1)
Log.ok("pymysql 安装成功")
import pymysql
return pymysql
# ── Safe Data Cleanup ────────────────────────────────────────────────────────
_PROTECTED_DIRS = frozenset({
".ssh", ".gnupg", ".gpg", ".config", ".local", ".cache",
".npm", ".nvm", ".pyenv", ".rbenv", ".rustup", ".cargo",
".docker", ".kube", ".git", ".vim", ".vscode",
".zshrc", ".bashrc", ".profile", ".bash_profile",
})
def safe_clean(data_dir, skill_name):
"""Delete a skill's data directory with multi-layer safety validation.
Layers: realpath resolve → $HOME subtree → agents data prefix → blacklist → double confirm.
"""
home = os.path.expanduser("~")
real_path = os.path.realpath(data_dir)
agents_data = os.path.join(home, ".agents", "data")
if not real_path.startswith(home + os.sep):
Log.err(f"安全拒绝: 数据目录不在用户主目录下 ({real_path})")
sys.exit(1)
# Allow paths under ~/.agents/data/ or direct $HOME children
is_agents_data = real_path.startswith(agents_data + os.sep)
is_home_child = os.path.dirname(real_path) == home
if not is_agents_data and not is_home_child:
Log.err(f"安全拒绝: 仅允许删除 ~/.agents/data/ 下的目录或 $HOME 直接子目录 ({real_path})")
sys.exit(1)
basename = os.path.basename(real_path)
if not basename.startswith("."):
Log.err(f"安全拒绝: 仅允许删除隐藏目录 ({basename})")
sys.exit(1)
if basename in _PROTECTED_DIRS:
Log.err(f"安全拒绝: {basename} 是受保护的系统目录")
sys.exit(1)
if not os.path.isdir(real_path):
Log.info(f"数据目录不存在,无需清理: {real_path}")
return
if os.path.islink(data_dir):
Log.warn(f"数据目录是符号链接 → {real_path}")
file_count = sum(len(files) for _, _, files in os.walk(real_path))
Log.warn(f"即将删除 {skill_name} 数据目录:")
Log.warn(f" 路径: {real_path}")
Log.warn(f" 文件数: {file_count}")
try:
confirm1 = input(f" 确认删除? 输入 '{skill_name}' 确认: ").strip()
except (EOFError, KeyboardInterrupt):
confirm1 = ""
if confirm1 != skill_name:
Log.info("已取消")
return
try:
confirm2 = input(" 二次确认: 此操作不可恢复,继续? [y/N]: ").strip().lower()
except (EOFError, KeyboardInterrupt):
confirm2 = ""
if confirm2 != "y":
Log.info("已取消")
return
shutil.rmtree(real_path)
Log.ok(f"已删除: {real_path}")
# ── Connection ───────────────────────────────────────────────────────────────
def _resolve_password(profile_name: str, profile: dict) -> str:
"""Resolve password: vault (NX1 encrypted) → JSON fallback."""
pw = profile.get("password", "")
if pw == _VAULT_SENTINEL or not pw:
vault_pw = _cred_store.get(f"profile:{profile_name}")
if vault_pw:
return vault_pw
return pw
def connect(profile_name=None, database=None):
"""Create a pymysql connection from a named profile."""
pymysql = ensure_pymysql()
name, profile = get_profile(profile_name)
db = database or profile.get("database", "")
try:
conn = pymysql.connect(
host=profile["host"],
port=int(profile.get("port", 3306)),
user=profile["user"],
password=_resolve_password(name, profile),
database=db if db else None,
charset="utf8mb4",
connect_timeout=10,
read_timeout=30,
cursorclass=pymysql.cursors.DictCursor,
)
return conn
except Exception as e:
Log.err(f"连接失败 [{name}]: {e}")
sys.exit(1)
# ── SQL Safety ───────────────────────────────────────────────────────────────
_WRITE_PATTERNS = re.compile(
r"^\s*(INSERT|UPDATE|DELETE|REPLACE|DROP|CREATE|ALTER|TRUNCATE|"
r"LOAD\s+DATA|CALL|EXECUTE|GRANT|REVOKE)\b",
re.IGNORECASE | re.MULTILINE,
)
_DANGEROUS_PATTERNS = re.compile(
r"(INTO\s+OUTFILE|INTO\s+DUMPFILE)",
re.IGNORECASE,
)
# SHOW/DESCRIBE/EXPLAIN start with these keywords but are read-only
_READONLY_WHITELIST = re.compile(
r"^\s*(SHOW|DESCRIBE|DESC|EXPLAIN)\b",
re.IGNORECASE,
)
def _check_readonly(sql):
"""Reject write operations. Returns (ok, reason)."""
if _READONLY_WHITELIST.match(sql):
return True, ""
if _WRITE_PATTERNS.search(sql):
return False, "检测到写入操作,违反只读策略"
if _DANGEROUS_PATTERNS.search(sql):
return False, "检测到危险操作 (OUTFILE/DUMPFILE)"
return True, ""
def _enforce_limit(sql, max_rows=MAX_ROWS):
"""Ensure user-facing SQL has a LIMIT clause not exceeding max_rows."""
# Skip LIMIT enforcement for aggregate-only queries (GROUP BY without existing LIMIT)
has_group = bool(re.search(r"\bGROUP\s+BY\b", sql, re.IGNORECASE))
limit_match = re.search(r"LIMIT\s+(\d+)", sql, re.IGNORECASE)
if limit_match:
current = int(limit_match.group(1))
if current > max_rows:
sql = re.sub(r"LIMIT\s+\d+", f"LIMIT {max_rows}", sql, count=1, flags=re.IGNORECASE)
Log.warn(f"LIMIT 已调整为 {max_rows}")
elif not has_group:
sql = sql.rstrip().rstrip(";") + f" LIMIT {max_rows}"
return sql
def _check_performance(sql):
"""Emit performance warnings for common anti-patterns."""
if re.search(r"SELECT\s+\*\s+FROM", sql, re.IGNORECASE):
Log.warn("SELECT * 在列式存储中消耗大量 IO,建议指定列名")
if not re.search(r"\bWHERE\b", sql, re.IGNORECASE):
Log.warn("缺少 WHERE 条件,无法利用分区裁剪")
if re.search(r"\bJOIN\b", sql, re.IGNORECASE) and not re.search(r"\bON\b", sql, re.IGNORECASE):
Log.warn("JOIN 缺少 ON 条件,可能导致笛卡尔积")
# ── Query Execution ──────────────────────────────────────────────────────────
def execute_raw(conn, sql, params=None):
"""Execute SQL without safety checks. For internal/metadata queries only."""
with conn.cursor() as cursor:
cursor.execute(sql, params)
rows = cursor.fetchall()
columns = [desc[0] for desc in cursor.description] if cursor.description else []
return columns, rows
def execute(conn, sql, params=None):
"""Execute user-facing SQL with readonly check, performance warnings, and LIMIT."""
ok, reason = _check_readonly(sql)
if not ok:
Log.err(reason)
sys.exit(1)
_check_performance(sql)
sql = _enforce_limit(sql)
return execute_raw(conn, sql, params)
# ── Schema Introspection ─────────────────────────────────────────────────────
def find_column_by_type(conn, table, data_types):
"""Find the first column of a given data type in a table."""
type_list = ",".join(f"'{t}'" for t in data_types)
_, rows = execute_raw(conn,
f"SELECT COLUMN_NAME FROM information_schema.COLUMNS "
f"WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '{table}' "
f"AND DATA_TYPE IN ({type_list}) LIMIT 1"
)
return rows[0]["COLUMN_NAME"] if rows else None
# ── Output Formatting ────────────────────────────────────────────────────────
def output(columns, rows, fmt="table"):
"""Format and print query results."""
if not rows:
Log.info("查询结果为空")
return
if fmt == "json":
print(json.dumps(rows, indent=2, default=str, ensure_ascii=False))
elif fmt == "csv":
buf = io.StringIO()
writer = csv.DictWriter(buf, fieldnames=columns)
writer.writeheader()
writer.writerows(rows)
print(buf.getvalue(), end="")
else:
_print_table(columns, rows)
def _print_table(columns, rows):
"""Render rows as an ASCII table."""
widths = {}
for col in columns:
max_w = len(str(col))
for row in rows:
max_w = max(max_w, len(str(row.get(col, ""))))
widths[col] = min(max_w, 40)
header = " | ".join(str(col).ljust(widths[col]) for col in columns)
separator = "-+-".join("-" * widths[col] for col in columns)
print(header)
print(separator)
for row in rows:
print(" | ".join(str(row.get(col, "")).ljust(widths[col])[:widths[col]] for col in columns))
Log.info(f"{len(rows)} 行")
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack." | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |
| ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |