database_skill

Python-based database connectivity skill supporting MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. Provides connection management, parameterized query execution, schema introspection, and transaction management. Requires Python 3.8+.

database-skill — Python Database Connectivity Skill

English | 中文

This skill guides an AI Agent to connect to relational databases, execute queries, manage transactions, and introspect schema using Python.

---

database-skill — Python 数据库连接技能

本技能指导 AI Agent 使用 Python 连接关系型数据库、执行 SQL 查询、管理事务以及探查 Schema 元数据。

---

When to call / When not to call · 何时调用

Scenario	Call
User needs to run SQL queries	Yes
User needs schema introspection (tables, columns, indexes, FK)	Yes
User needs transaction control (commit / rollback)	Yes
Python 3.8+ not available or drivers not installed	No
User only needs text/regex analysis	No

场景	调用
用户需要执行 SQL 查询	是
用户需要 Schema 探查（表、列、索引、外键）	是
用户需要事务控制（提交/回滚）	是
无 Python 3.8+ 或驱动未安装	否
用户仅需文本/正则分析	否

---

Prerequisites · 前提条件

Requirement	Check
Python 3.8+	python --version
Target database reachable	telnet <host> <port>
Dependencies	pip install pymysql psycopg2-binary oracledb pymssql pyyaml

---

Quick start · 快速开始

bash

# List all tables
python scripts/main.py \
  --url "jdbc:mysql://localhost:3306/mydb" \
  --user "root" \
  --password "${DB_PASS}" \
  --tables

# Parameterized query
python scripts/main.py \
  --url "jdbc:mysql://localhost:3306/mydb" \
  --user "root" \
  --password "${DB_PASS}" \
  --query "SELECT * FROM user WHERE name = ?" "zhangsan"

---

Configuration (3 options) · 配置方式（三种）

A — CLI arguments (recommended) · 命令行参数（推荐）

bash

python scripts/main.py \
  --url "jdbc:mysql://host:3306/db" \
  --user "admin" \
  --password "${DB_PASS}" \
  --query "SELECT 1"

B — YAML config file · 配置文件

yaml

# datasource.yml
datasource:
  url: "jdbc:mysql://localhost:3306/mydb"
  username: "${DB_USER}"
  password: "${DB_PASS}"

bash

python scripts/main.py --config datasource.yml --tables

C — Built-in defaults · 内置默认

bash

python scripts/main.py --tables

---

Operations · 操作说明

Flag	Description	说明
--query <sql> [params...]	SELECT with ? placeholders	SELECT 查询，支持参数化
--update <sql> [params...]	UPDATE/INSERT/DELETE	更新/插入/删除
--batch <file>	Execute SQL file	批量执行文件中的 SQL
--tables	List all tables	列出所有表
--columns <table>	Show column metadata	查看表结构
--list-connections	Show saved connections	查看已保存的连接
--forget <url>	Remove a saved connection	删除已保存的连接

Examples · 示例：

bash

python scripts/main.py --url "jdbc:mysql://host:3306/db" --user root --password x --tables
python scripts/main.py --url "..." --user root --password x --query "SELECT * FROM t WHERE id = ?" 42
python scripts/main.py --url "..." --user root --password x --update "UPDATE t SET x = ? WHERE id = ?" "new" 1
python scripts/main.py --list-connections

---

Scripts · 脚本结构

text

scripts/
├── main.py                  # CLI entry point · 命令行入口
├── connection_manager.py    # Connect / disconnect · 连接管理
├── connections_store.py     # Persist connections · 连接记录持久化
├── query_executor.py        # Query, update, batch, transaction · 查询执行
├── schema_inspector.py      # Schema introspection · Schema 探查
└── exceptions.py            # Exception hierarchy · 异常层次

---

API overview · API 概览

ConnectionManager · 连接管理器

python

from connection_manager import ConnectionManager

cm = ConnectionManager(url="jdbc:mysql://localhost:3306/db",
                       username="root", password="secret",
                       driver="pymysql")
conn = cm.get_connection()                       # auto-commit
tx_conn = cm.get_connection_for_transaction()   # manual-commit
cm.close_connection(conn)

QueryExecutor · 查询执行器

python

from query_executor import QueryExecutor

qe = QueryExecutor(cm)
rows = qe.execute_query("SELECT * FROM users WHERE status = ?", "ACTIVE")
affected = qe.execute_update("UPDATE users SET status = ? WHERE id = ?", "INACTIVE", 1)
qe.execute_batch(["INSERT INTO log VALUES (1)", "INSERT INTO log VALUES (2)"])
qe.execute_transaction(lambda tx: (
    tx.execute_update("UPDATE accounts SET balance = balance - 100 WHERE id = ?", 1),
    tx.execute_update("UPDATE accounts SET balance = balance + 100 WHERE id = ?", 2),
))

CaseInsensitiveDict · 大小写不敏感字典

Query results are wrapped in CaseInsensitiveDict so column names are case-insensitive: 查询结果的列名大小写不敏感：

python

row["name"] == row["NAME"] == row["Name"]

SchemaInspector · Schema 检查器

python

from schema_inspector import SchemaInspector

si = SchemaInspector(cm)
si.get_tables()         # 获取所有表
si.get_columns("users") # 获取表结构
si.get_indexes("users") # 获取索引
si.get_foreign_keys("users")  # 获取外键

---

Saved connections · 连接记录

After a successful connection, the skill saves the URL, username, and driver to %TEMP%/.database-skill-connections.json (/tmp/ on Linux/macOS). Passwords are never stored.

连接成功后，技能会将 URL、用户名和驱动类型保存到用户临时目录。 密码不会被保存。

When no --url or --config is provided, the skill shows a numbered list: 不提供 --url 或 --config 时，显示已保存的连接供选择：

text

Saved connections:
  [0] MySQL@host1
  [1] Oracle@orahost
  [N] Enter a new connection

bash

python scripts/main.py --list-connections    # 查看所有连接
python scripts/main.py --forget "jdbc:mysql://..."  # 删除连接

---

Security rules · 安全守则

1. Never hard-code passwords — use environment variables ${DB_PASS} or CLI. 禁止硬编码密码 — 使用环境变量或命令行参数。
2. Always use parameterized queries — values go as ? placeholders. 始终使用参数化查询 — 值通过 ? 占位符传递。
3. Transactions auto-commit/rollback — handled transparently. 事务自动提交/回滚 — 无需手动处理。
4. Passwords never persisted — saved connections store only URL and username. 密码不持久化 — 只保存 URL 和用户名。

---

Building & testing · 构建与测试

bash

pip install -e ".[dev]"
pytest