技术干货：Linux服务器上一键查询SQL并导出Excel文件详解

在日常的运维和开发工作中，我们经常需要从部署在Linux服务器上的数据库中提取数据，并以更易分析和分享的格式（如Excel）保存到服务器本地。这种自动化操作对于生成日报、数据备份或数据交接等场景至关重要。本文将详细讲解如何在Linux服务器上，通过编写一个Shell脚本，完美实现查询SQL并导出Excel文件的全流程。

核心思路

整个过程可以分解为三个核心步骤：

1. 连接数据库：使用命令行工具（如mysql）连接到目标数据库。

2. 执行SQL查询：运行指定的SQL查询语句，获取结果集。

3. 格式化并导出为Excel：将查询结果转换为Excel格式（.xlsx）并保存到服务器指定目录。

我们将使用最常用的MySQL数据库为例，其他数据库（如PostgreSQL）原理类似，只需更换连接工具和命令即可。

方法一：使用 mysql 客户端与 spark-sql（推荐，处理大量数据更高效）

此方法利用mysql命令的-e参数执行SQL，并通过管道将结果传递给spark-sql的--output-format选项直接生成Excel。这种方式非常适合处理海量数据。

1. 环境准备

首先，确保服务器上已安装以下工具：

• MySQL客户端：用于连接数据库。

• Spark SQL CLI：用于数据转换。

bash

# 安装MySQL客户端（以CentOS为例）
sudo yum install mysql -y

# 安装Spark（请根据实际情况调整版本和路径）
wget https://archive.apache.org/dist/spark/spark-3.2.1/spark-3.2.1-bin-hadoop3.2.tgz
tar -xzf spark-3.2.1-bin-hadoop3.2.tgz
sudo mv spark-3.2.1-bin-hadoop3.2 /opt/spark

2. 编写Shell脚本

创建一个名为export_data.sh的脚本文件。

#!/bin/bash

# 配置数据库连接信息
DB_HOST="your_mysql_host"
DB_USER="your_username"
DB_PASS="your_password"
DB_NAME="your_database_name"

# 配置输出文件路径
OUTPUT_FILE="/path/to/your/output/data_$(date +%Y%m%d_%H%M%S).xlsx"

# 你的SQL查询语句
SQL_QUERY=“SELECT id, name, email, create_time FROM users WHERE create_time >= CURDATE();”

echo "开始导出数据..."

# 执行核心操作
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "$SQL_QUERY" | \
/opt/spark/bin/spark-sql --packages com.crealytics:spark-excel_2.12:0.13.7 \
--output-format excel \
--output-file "$OUTPUT_FILE"

# 检查上一条命令是否执行成功
if [ $? -eq 0 ]; then
    echo "数据导出成功！文件保存在：$OUTPUT_FILE"
else
    echo "数据导出失败，请检查配置和SQL语句。"
    exit 1
fi

脚本说明：

• DB_HOST, DB_USER, DB_PASS, DB_NAME：请替换为你的实际数据库信息。

• OUTPUT_FILE：导出的Excel文件路径，$(date +%Y%m%d_%H%M%S) 用于生成带时间戳的文件名，避免覆盖。

• SQL_QUERY：替换为你需要执行的实际SQL语句。

• 通过管道 | 将 mysql 查询结果传递给 spark-sql 进行处理。

3. 运行脚本

给脚本添加执行权限并运行。

chmod +x export_data.sh
./export_data.sh

方法二：使用Python脚本（灵活性强，功能丰富）

如果服务器上有Python环境，使用Python脚本是更灵活、功能更强大的选择。我们可以利用 pymysql 连接数据库和 pandas 库来处理数据并导出Excel。

1. 环境准备

安装必要的Python库。

pip3 install pandas openpyxl pymysql

• pandas：强大的数据处理库，核心工具。

• openpyxl：用于读写Excel .xlsx 文件的引擎。

• pymysql：Python的MySQL数据库连接器。

2. 编写Python脚本

创建一个名为export_to_excel.py的Python脚本。

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import pandas as pd
import pymysql
from datetime import datetime
import os

# 数据库连接配置
db_config = {
    'host': 'your_mysql_host',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database_name',
    'charset': 'utf8mb4'
}

# SQL查询语句
sql_query = “SELECT id, name, email, create_time FROM users WHERE create_time >= CURDATE();”

# 输出文件路径
output_dir = ‘/path/to/your/output’
if not os.path.exists(output_dir):
    os.makedirs(output_dir)  # 如果目录不存在则创建
output_file = os.path.join(output_dir, f“data_export_{datetime.now().strftime(’%Y%m%d_%H%M%S')}.xlsx”)

try:
    print(“正在连接数据库并查询数据...”)
    # 1. 连接数据库并读取数据到pandas DataFrame
    connection = pymysql.connect(**db_config)
    df = pd.read_sql(sql_query, connection)

    print(f“查询到 {len(df)} 行数据。”)

    # 2. 将DataFrame导出为Excel文件
    # 使用openpyxl作为引擎，支持.xlsx格式
    df.to_excel(output_file, index=False, engine=’openpyxl’) 

    print(f“数据导出成功！文件保存在：{output_file}”)

except Exception as e:
    print(f“操作失败：{e}”)
    exit(1)

finally:
    # 确保关闭数据库连接
    if ‘connection’ in locals() and connection.open:
        connection.close()
        print(“数据库连接已关闭。”)

3. 运行Python脚本

python3 export_to_excel.py

重要注意事项

1. 安全第一！

   • 密码安全：将数据库密码直接写在脚本中存在安全风险。在生产环境中，强烈建议使用配置文件（设置严格权限，如600）、环境变量或密钥管理服务来存储敏感信息。

   • SQL注入：如果SQL语句需要动态参数，切勿直接拼接字符串！请使用参数化查询。

2. 依赖管理：确保脚本运行环境的依赖包已正确安装，并且版本兼容。

3. 权限问题：确保运行脚本的用户具有读取数据库的权限，以及在服务器指定目录写入文件的权限。

4. 内存考虑：如果要导出的数据量非常大（例如上百万行），使用方法二的Python脚本时需要注意内存消耗。可以尝试分块读取和写入数据。

总结

本文介绍了两种在Linux服务器上查询SQL并导出Excel的有效方法：

• 方法一（Shell脚本）：轻量、快速，适合简单的、数据量大的导出任务，与运维自动化流程集成方便。

• 方法二（Python脚本）：功能强大、灵活可控，可以在导出前后进行复杂的数据清洗、处理和格式化，是开发者的首选。