数据库查询与导出Skill database-query-and-export

---

name: 数据库查询与导出 description: “查询SQLite、PostgreSQL和MySQL数据库并导出结果到CSV/JSON。使用场景：(1) 提取数据用于报告，(2) 数据库备份和迁移，(3) 数据分析工作流，或(4) 自动化数据库查询。”

数据库查询与导出

查询关系型数据库（SQLite、PostgreSQL、MySQL）并将结果导出到CSV、JSON或其他格式。对于数据提取、报告、备份自动化和分析流程至关重要。

何时使用

• 使用场景1：当用户要求查询数据库并导出结果时
• 使用场景2：当需要提取数据用于分析或报告时
• 使用场景3：用于备份和数据迁移工作流
• 使用场景4：当构建自动化数据库监控和警报时

所需工具/API

• SQLite — 轻量级基于文件的数据库（通常预安装）
• PostgreSQL客户端 — 用于PostgreSQL数据库
• MySQL客户端 — 用于MySQL/MariaDB数据库
• 无需外部API

安装选项：

# Ubuntu/Debian
sudo apt-get install -y sqlite3 postgresql-client mysql-client

# macOS
brew install sqlite3 postgresql mysql-client

# Node.js（数据库驱动）
npm install better-sqlite3  # SQLite
npm install pg              # PostgreSQL
npm install mysql2          # MySQL

技能

query_sqlite_to_json

查询SQLite数据库并导出为JSON格式。

# 基本查询到JSON
sqlite3 database.db "SELECT * FROM users LIMIT 10;" -json

# 使用jq进行漂亮格式化
sqlite3 database.db "SELECT * FROM users WHERE active=1;" -json | jq '.'

# 导出整个表到JSON文件
sqlite3 database.db "SELECT * FROM orders;" -json > orders.json

# 使用WHERE子句查询
sqlite3 database.db "SELECT id, name, email FROM users WHERE created_at > '2024-01-01';" -json

Node.js:

const Database = require('better-sqlite3');

function querySQLiteToJSON(dbPath, query) {
  const db = new Database(dbPath, { readonly: true });
  const rows = db.prepare(query).all();
  db.close();
  return rows;
}

// 用法
// const users = querySQLiteToJSON('./database.db', 'SELECT * FROM users LIMIT 10');
// console.log(JSON.stringify(users, null, 2));

query_sqlite_to_csv

查询SQLite数据库并导出为CSV格式。

# 基本查询到CSV
sqlite3 database.db <<EOF
.mode csv
.headers on
SELECT * FROM users LIMIT 10;
EOF

# 导出到CSV文件
sqlite3 database.db <<EOF
.mode csv
.headers on
.output users.csv
SELECT id, name, email, created_at FROM users WHERE active=1;
EOF

# 使用JOIN查询多个表
sqlite3 database.db <<EOF
.mode csv
.headers on
SELECT u.name, o.order_id, o.total 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.created_at > '2024-01-01';
EOF

Node.js:

const Database = require('better-sqlite3');
const fs = require('fs');

function querySQLiteToCSV(dbPath, query, outputPath) {
  const db = new Database(dbPath, { readonly: true });
  const rows = db.prepare(query).all();
  db.close();
  
  if (rows.length === 0) {
    return '没有结果';
  }
  
  // 生成CSV
  const headers = Object.keys(rows[0]).join(',');
  const csvRows = rows.map(row => 
    Object.values(row).map(val => 
      typeof val === 'string' && val.includes(',') ? `"${val}"` : val
    ).join(',')
  );
  
  const csv = [headers, ...csvRows].join('
');
  
  if (outputPath) {
    fs.writeFileSync(outputPath, csv);
    return `已导出 ${rows.length} 行到 ${outputPath}`;
  }
  
  return csv;
}

// 用法
// querySQLiteToCSV('./database.db', 'SELECT * FROM users LIMIT 10', './users.csv');

query_postgresql

查询PostgreSQL数据库并导出结果。

# 设置连接字符串（替代：使用单独标志）
export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=mydb
export PGUSER=postgres
export PGPASSWORD=mypassword

# 查询到JSON（使用psql格式化输出）
psql -t -A -F"," -c "SELECT row_to_json(t) FROM (SELECT * FROM users LIMIT 10) t;"

# 查询到CSV
psql -c "COPY (SELECT * FROM users WHERE active=true) TO STDOUT WITH CSV HEADER;" > users.csv

# 使用连接字符串查询
psql "postgresql://user:password@localhost:5432/mydb" -c "SELECT * FROM users LIMIT 5;"

# 查询到格式化表格
psql -c "SELECT id, name, email FROM users ORDER BY created_at DESC LIMIT 10;"

Node.js:

const { Pool } = require('pg');

async function queryPostgreSQL(connectionString, query) {
  const pool = new Pool({ connectionString });
  
  try {
    const result = await pool.query(query);
    return result.rows;
  } finally {
    await pool.end();
  }
}

// 用法
// const connStr = 'postgresql://user:password@localhost:5432/mydb';
// queryPostgreSQL(connStr, 'SELECT * FROM users LIMIT 10')
//   .then(rows => console.log(JSON.stringify(rows, null, 2)));

query_mysql

查询MySQL/MariaDB数据库并导出结果。

# 查询到CSV带标题
mysql -h localhost -u root -p'mypassword' -D mydb \
  -e "SELECT * FROM users WHERE active=1;" \
  --batch --silent \
  | cat > users.csv

# 查询到类JSON格式（需要jq进行适当格式化）
mysql -h localhost -u root -p'mypassword' -D mydb \
  -e "SELECT * FROM users LIMIT 10;" \
  --batch --silent

# 导出整个表到CSV
mysql -h localhost -u root -p'mypassword' -D mydb \
  -e "SELECT * FROM orders INTO OUTFILE '/tmp/orders.csv' 
      FIELDS TERMINATED BY ',' 
      ENCLOSED BY '\"' 
      LINES TERMINATED BY '
';"

# 带超时查询
mysql -h localhost -u root -p'mypassword' -D mydb \
  --connect-timeout=10 \
  -e "SELECT COUNT(*) as total FROM users;"

Node.js:

const mysql = require('mysql2/promise');

async function queryMySQL(config, query) {
  const connection = await mysql.createConnection({
    host: config.host || 'localhost',
    user: config.user,
    password: config.password,
    database: config.database,
    connectTimeout: 10000
  });
  
  try {
    const [rows] = await connection.execute(query);
    return rows;
  } finally {
    await connection.end();
  }
}

// 用法
// const config = {
//   host: 'localhost',
//   user: 'root',
//   password: 'mypassword',
//   database: 'mydb'
// };
// queryMySQL(config, 'SELECT * FROM users LIMIT 10')
//   .then(rows => console.log(JSON.stringify(rows, null, 2)));

advanced_sqlite_export_with_error_handling

生产就绪的SQLite导出，带验证和错误处理。

#!/bin/bash
DB_PATH="database.db"
QUERY="SELECT * FROM users WHERE active=1;"
OUTPUT_FILE="users.csv"

# 检查数据库是否存在
if [ ! -f "$DB_PATH" ]; then
  echo "错误：数据库文件未找到: $DB_PATH" >&2
  exit 1
fi

# 检查表是否存在
if ! sqlite3 "$DB_PATH" "SELECT name FROM sqlite_master WHERE type='table' AND name='users';" | grep -q "users"; then
  echo "错误：数据库中未找到表 'users'" >&2
  exit 1
fi

# 执行查询并导出到CSV
if sqlite3 "$DB_PATH" <<EOF > "$OUTPUT_FILE" 2>&1
.mode csv
.headers on
$QUERY
EOF
then
  ROW_COUNT=$(wc -l < "$OUTPUT_FILE")
  echo "成功：已导出 $((ROW_COUNT - 1)) 行到 $OUTPUT_FILE"
else
  echo "错误：查询失败" >&2
  exit 1
fi

Node.js:

const Database = require('better-sqlite3');
const fs = require('fs');

async function exportSQLiteWithValidation(options) {
  const { dbPath, query, outputPath, format = 'json' } = options;
  
  // 验证数据库存在
  if (!fs.existsSync(dbPath)) {
    throw new Error(`数据库文件未找到: ${dbPath}`);
  }
  
  let db;
  try {
    db = new Database(dbPath, { readonly: true, timeout: 10000 });
    
    // 准备并执行查询
    const stmt = db.prepare(query);
    const rows = stmt.all();
    
    if (rows.length === 0) {
      return { success: true, rowCount: 0, message: '没有返回行' };
    }
    
    // 基于格式导出
    let output;
    if (format === 'json') {
      output = JSON.stringify(rows, null, 2);
    } else if (format === 'csv') {
      const headers = Object.keys(rows[0]).join(',');
      const csvRows = rows.map(row => 
        Object.values(row).map(val => 
          typeof val === 'string' && val.includes(',') ? `"${val.replace(/"/g, '""')}"` : val
        ).join(',')
      );
      output = [headers, ...csvRows].join('
');
    } else {
      throw new Error(`不支持格式: ${format}`);
    }
    
    // 写入文件
    fs.writeFileSync(outputPath, output);
    
    return {
      success: true,
      rowCount: rows.length,
      outputPath,
      format,
      message: `已导出 ${rows.length} 行到 ${outputPath}`
    };
    
  } catch (err) {
    throw new Error(`数据库导出失败: ${err.message}`);
  } finally {
    if (db) db.close();
  }
}

// 用法
// exportSQLiteWithValidation({
//   dbPath: './database.db',
//   query: 'SELECT * FROM users WHERE active=1',
//   outputPath: './users.json',
//   format: 'json'
// }).then(result => console.log(result));

速率限制/最佳实践

• ✅ 使用只读连接 — 在只查询时以只读模式打开数据库
• ✅ 设置连接超时 — 使用10-30秒超时防止挂起
• ✅ 验证输入 — 在查询前检查数据库文件/表是否存在
• ✅ 转义用户输入 — 使用参数化查询防止SQL注入
• ✅ 处理大数据集 — 对大表使用LIMIT/OFFSET进行分页
• ✅ 关闭连接 — 查询后始终关闭数据库连接
• ⚠️ 安全凭证 — 将数据库密码存储在环境变量中，永不硬编码
• ⚠️ 导出文件权限 — 确保导出目录具有适当的写入权限

代理提示

您具备数据库查询和导出能力。当用户要求查询数据库时：

1. 从以下识别数据库类型（SQLite、PostgreSQL、MySQL）：
   - 文件扩展名（.db、.sqlite、.sqlite3 = SQLite）
   - 连接字符串（postgresql://、mysql://）
   - 用户指定

2. 对于SQLite：
   - 使用 `sqlite3 database.db "QUERY" -json` 用于JSON输出
   - 使用 `.mode csv` 和 `.headers on` 用于CSV输出
   - 始终首先检查数据库文件是否存在

3. 对于PostgreSQL：
   - 使用 `psql` 带连接字符串或环境变量
   - 使用 `COPY ... TO STDOUT WITH CSV HEADER` 用于CSV导出
   - 使用 `row_to_json()` 函数导出JSON

4. 对于MySQL：
   - 使用 `mysql` 带 `-e` 标志进行查询
   - 使用 `--batch --silent` 用于类CSV输出
   - 使用 `--connect-timeout=10` 设置连接超时

5. 始终：
   - 在查询前验证数据库/表是否存在
   - 在只读时使用只读连接
   - 优雅处理错误并给出清晰消息
   - 清理输出（在CSV中转义逗号，引用字符串）

6. 对于大数据集：
   - 向查询添加LIMIT子句
   - 对非常大的表使用OFFSET分页
   - 如果结果集可能很大，警告用户

故障排除

错误：“无法打开数据库文件”

• 症状：SQLite无法找到或访问数据库文件
• 解决方案：检查文件路径是否正确以及文件是否有读取权限

错误：“连接被拒绝”

• 症状：无法连接到PostgreSQL或MySQL服务器
• 解决方案：验证主机/端口是否正确，数据库服务正在运行，防火墙允许连接

错误：“认证失败”

• 症状：数据库拒绝用户名/密码
• 解决方案：验证凭证是否正确，用户具有必要权限

错误：“表不存在”

• 症状：查询引用不存在的表
• 解决方案：首先列出可用表（sqlite3 db.db ".tables" 或在psql中使用 \dt）

CSV输出格式损坏：

• 症状：数据中的逗号破坏CSV列
• 解决方案：正确使用引号转义含逗号的值，转义现有引号

查询时间过长：

• 症状：查询挂起或运行数分钟
• 解决方案：添加LIMIT子句，使用索引优化查询，增加超时

另请参阅

• …/json-and-csv-data-transformation/SKILL.md — 在格式之间转换导出的数据
• …/file-tracker/SKILL.md — 跟踪数据库文件随时间的变化
• …/chat-logger/SKILL.md — SQLite用于日志记录的示例