要实现一个可以连接到SQL Server，执行指定查询并将结果保存到本地Excel文件，然后定时将文件发送到指定邮箱的Python脚本，您可以使用多个Python库来完成这些任务。
下面是详细的步骤和相关代码：
安装必要的库
首先，您需要安装以下库：
pip install pyodbc pandas openpyxl smtplib schedulePython脚本
- 连接SQL Server并查询数据
- 将查询结果保存到Excel文件
- 通过邮件发送Excel文件
- 设置定时任务
import pyodbc
import pandas as pd
import openpyxl
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders
import schedule
import time
import os
# SQL Server connection settings
server = 'your_server_name'
database = 'your_database_name'
username = 'your_username'
password = 'your_password'
query = 'SELECT * FROM your_table'
# Email settings
sender_email = 'your_email@'
receiver_email = 'receiver_email@'
email_subject = 'Scheduled SQL Query Results'
email_body = 'Please find the attached Excel file with the query results.'
smtp_server = 'smtp.'
smtp_port = 587
smtp_username = 'your_smtp_username'
smtp_password = 'your_smtp_password'
def fetch_data_from_sql():
# Connect to SQL Server
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
f'SERVER={server};'
f'DATABASE={database};'
f'UID={username};'
f'PWD={password}')
# Execute query
df = pd.read_sql(query, conn)
conn.close()
return df
def save_to_excel(df, file_path):
df.to_excel(file_path, index=False)
def send_email_with_attachment(file_path):
msg = MIMEMultipart()
msg['From'] = sender_email
msg['To'] = receiver_email
msg['Subject'] = email_subject
msg.attach(MIMEText(email_body))
# Attach the file
part = MIMEBase('application', 'octet-stream')
part.set_payload(open(file_path, 'rb').read())
encoders.encode_base64(part)
part.add_header('Content-Disposition', 'attachment; filename="{}"'.format(os.path.basename(file_path)))
msg.attach(part)
# Send the email
server = smtplib.SMTP(smtp_server, smtp_port)
server.starttls()
server.login(smtp_username, smtp_password)
server.sendmail(sender_email, receiver_email, msg.as_string())
server.quit()
def job():
print("Fetching data from SQL Server...")
df = fetch_data_from_sql()
file_path = 'query_results.xlsx'
print("Saving data to Excel file...")
save_to_excel(df, file_path)
print("Sending email with attachment...")
send_email_with_attachment(file_path)
print("Job completed.")
# Schedule the job to run at a specific time every day
schedule.every().day.at("09:00").do(job)
# Keep the script running
while True:
schedule.run_pending()
time.sleep(1)
详细说明
- 连接SQL Server并查询数据： 使用
pyodbc库连接到SQL Server并执行查询，结果保存在一个Pandas DataFrame中。 - 将查询结果保存到Excel文件： 使用
pandas库将查询结果保存为Excel文件。 - 通过邮件发送Excel文件： 使用
smtplib库发送带有附件的邮件。附件是之前生成的Excel文件。 - 设置定时任务： 使用
schedul库设置定时任务，让脚本每天在特定时间执行查询并发送邮件。
为了使脚本更加丰富和功能全面，可以进行以下扩展：
- 配置文件支持：将连接信息、查询和邮件设置移动到配置文件中，以便更轻松地进行管理和更改。
- 日志记录：添加日志功能，以便在出现错误时记录日志文件，帮助调试和排查问题。
- 多线程：在处理多个任务时使用多线程，以提高效率。
- 异常处理：增强异常处理机制，确保在出现错误时有适当的处理流程。
- 更友好的邮件内容：使用HTML格式编写邮件正文，使邮件看起来更专业。
示例扩展实现
以下是包含上述扩展功能的示例实现：
import pyodbc
import pandas as pd
import openpyxl
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email import encoders
import schedule
import time
import os
import configparser
import logging
from threading import Thread
# 读取配置文件
config = configparser.ConfigParser()
config.read('config.ini')
# 设置日志
logging.basicConfig(filename='app.log', level=logging.INFO,
format='%(asctime)s - %(levelname)s - %(message)s')
# SQL Server connection settings
server = config['SQL_SERVER']['SERVER']
database = config['SQL_SERVER']['DATABASE']
username = config['SQL_SERVER']['USERNAME']
password = config['SQL_SERVER']['PASSWORD']
query = config['SQL_SERVER']['QUERY']
# Email settings
sender_email = config['EMAIL']['SENDER_EMAIL']
receiver_email = config['EMAIL']['RECEIVER_EMAIL']
email_subject = config['EMAIL']['SUBJECT']
email_body = config['EMAIL']['BODY']
smtp_server = config['EMAIL']['SMTP_SERVER']
smtp_port = config['EMAIL']['SMTP_PORT']
smtp_username = config['EMAIL']['SMTP_USERNAME']
smtp_password = config['EMAIL']['SMTP_PASSWORD']
def fetch_data_from_sql():
try:
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
f'SERVER={server};'
f'DATABASE={database};'
f'UID={username};'
f'PWD={password}')
df = pd.read_sql(query, conn)
conn.close()
('Data fetched successfully from SQL Server.')
return df
except Exception as e:
logging.error(f'Error fetching data from SQL Server: {e}')
return None
def save_to_excel(df, file_path):
try:
df.to_excel(file_path, index=False)
('Data saved to Excel file successfully.')
except Exception as e:
logging.error(f'Error saving data to Excel: {e}')
def send_email_with_attachment(file_path):
try:
msg = MIMEMultipart()
msg['From'] = sender_email
msg['To'] = receiver_email
msg['Subject'] = email_subject
msg.attach(MIMEText(email_body, 'html'))
part = MIMEBase('application', 'octet-stream')
part.set_payload(open(file_path, 'rb').read())
encoders.encode_base64(part)
part.add_header('Content-Disposition', 'attachment; filename="{}"'.format(os.path.basename(file_path)))
msg.attach(part)
server = smtplib.SMTP(smtp_server, smtp_port)
server.starttls()
server.login(smtp_username, smtp_password)
server.sendmail(sender_email, receiver_email, msg.as_string())
server.quit()
('Email sent successfully with attachment.')
except Exception as e:
logging.error(f'Error sending email: {e}')
def job():
('Job started.')
df = fetch_data_from_sql()
if df is not None:
file_path = 'query_results.xlsx'
save_to_excel(df, file_path)
send_email_with_attachment(file_path)
('Job completed.')
def schedule_job():
schedule.every().day.at("09:00").do(job)
while True:
schedule.run_pending()
time.sleep(1)
if __name__ == '__main__':
('Script started.')
thread = Thread(target=schedule_job)
thread.start()
配置文件 (config.ini)
[SQL_SERVER]
SERVER = your_server_name
DATABASE = your_database_name
USERNAME = your_username
PASSWORD = your_password
QUERY = SELECT * FROM your_table
[EMAIL]
SENDER_EMAIL = your_email@
RECEIVER_EMAIL = receiver_email@
SUBJECT = Scheduled SQL Query Results
BODY = <html><body><p>Please find the attached Excel file with the query results.</p></body></html>
SMTP_SERVER = smtp.
SMTP_PORT = 587
SMTP_USERNAME = your_smtp_username
SMTP_PASSWORD = your_smtp_password
详细说明
- 配置文件支持：使用
configparser读取配置文件，方便管理数据库和邮件的配置信息。 - 日志记录：使用
logging模块记录脚本的运行日志，包含信息级别和错误级别的日志。 - 多线程：使用
threading模块在独立线程中运行定时任务，确保主线程可以持续运行。 - 异常处理：在关键步骤（如数据库连接、数据保存和邮件发送）添加异常处理，记录错误日志。
- 更友好的邮件内容：使用HTML格式编写邮件正文，包含基本的HTML标签，确保邮件内容更易读和专业。
这种方式使脚本更具可扩展性和可维护性，便于在不同环境和需求下进行调整和扩展。
扩展内容
- 自定义查询：可以将SQL查询字符串从外部配置文件或用户输入获取，以便在不修改代码的情况下更改查询内容。
- 多文件支持：可以支持生成多个查询结果文件，并在邮件中附加多个文件。
- 更丰富的邮件内容：可以添加HTML格式的邮件内容，包含更详细的信息和格式化。
- 错误处理：添加错误处理机制，例如在数据库连接失败或邮件发送失败时进行重试或记录日志。
- 多用户支持：可以支持将邮件发送到多个接收者，或根据不同的查询结果发送给不同的接收者。
