在内网离线环境下，如果不能使用像pymysql这样的第三方库来操作MySQL，我们可以采用Python的标准库subprocess来调用Shell命令执行MySQL查询，同时使用smtplib和email.mime来构建并发送邮件。下面是一个基于这些标准库的简化示例：
请注意，这种方法依赖于系统中已安装MySQL客户端（如mysql-client）和配置好发送邮件的工具（如msmtp）。
import subprocess
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
# MySQL数据库连接配置
DB_USER = "your_username"
DB_PASS = "your_password"
DB_HOST = "localhost"
DB_NAME = "your_database"
TABLE_NAME = "your_table"
# SQL查询语句
SQL_QUERY = f"SELECT column1, column2, column3 FROM {TABLE_NAME};"
# 构建MySQL命令
mysql_cmd = f'mysql -u{DB_USER} -p{DB_PASS} -h{DB_HOST} {DB_NAME} -e "{SQL_QUERY}"'
# 执行MySQL命令并获取输出
try:
result = subprocess.check_output(mysql_cmd, shell=True, stderr=subprocess.STDOUT)
except subprocess.CalledProcessError as e:
print(f"Error executing MySQL command: {e.output.decode('utf-8')}")
exit(1)
# 解析结果并转换为HTML表格
lines = result.decode('utf-8').split('\n')
headers = lines[0].strip().split('\t')
data_lines = [line.strip().split('\t') for line in lines[1:] if line]
html_table = "<html><body><table border='1'>"
html_table += "<tr>" + "".join([f"<th>{header}</th>" for header in headers]) + "</tr>"
for row in data_lines:
html_table += "<tr>" + "".join([f"<td>{cell}</td>" for cell in row]) + "</tr>"
html_table += "</table></body></html>"
# 邮件配置
TO_EMAIL = "recipient@example.com"
FROM_EMAIL = "your_email@example.com"
SMTP_SERVER = "smtp.example.com"
SMTP_PORT = 587
SMTP_USERNAME = "your_email@example.com"
SMTP_PASSWORD = "your_password" # 注意：这种方式处理密码不够安全，仅作示例
SUBJECT = "Daily Report from {}".format(TABLE_NAME)
# 构造邮件
msg = MIMEMultipart()
msg['From'] = FROM_EMAIL
msg['To'] = TO_EMAIL
msg['Subject'] = SUBJECT
msg.attach(MIMEText(html_table, 'html'))
# 使用SMTP发送邮件
try:
with smtplib.SMTP(SMTP_SERVER, SMTP_PORT) as server:
server.starttls() # 启用TLS加密
server.login(SMTP_USERNAME, SMTP_PASSWORD)
server.send_message(msg)
print("Email sent successfully")
except Exception as e:
print(f"Failed to send email: {e}")
这个脚本首先通过subprocess模块执行MySQL查询命令并捕获输出，然后构建HTML格式的表格内容，最后利用smtplib和email.mime标准库发送邮件。请确保替换脚本中的占位符（如数据库凭据、邮箱地址、SMTP服务器信息）为实际的值，并根据你的环境调整SMTP的端口和安全设置。