使用Python脚本实现连接SQL server，将指定查询内容保存到本地Excel，定时转发给指定邮箱。

辙风

于 2024-06-06 12:45:42 发布

阅读量178

点赞数 5

文章标签： python excel 开发语言

本文链接：https://blog.csdn.net/ck1677445858/article/details/139490651

版权

# import部分导入所需的库和模块。

import pyodbc  # 用于数据库连接
import openpyxl  # 用于操作Excel文件
import pandas as pd  # 用于数据处理
import schedule  # 用于定时任务调度
import time  # 用于时间处理
import smtplib  # 用于发送邮件
from email.mime.multipart import MIMEMultipart  # 用于创建包含附件的邮件
from email.mime.base import MIMEBase  # 用于邮件附件处理
from email.mime.text import MIMEText  # 用于创建文本邮件内容
from email import encoders  # 用于编码邮件附件
from email.header import Header  # 用于处理邮件头
import logging  # 用于日志记录
import os  # 用于操作文件和路径

# 配置日志记录

# 在所运行的文件夹内生成log.txt文件并记录logging的内容。

logging.basicConfig(filename='log.txt', level=logging.DEBUG,
                    format='%(asctime)s - %(levelname)s - %(message)s')

# 将提取数据附加到Excel文件中。

def fetch_data_and_prepare_file():
    try:
        logging.info("Starting the fetch_data_and_prepare_file process") #记录日志

# 连接数据库

# 连接数据库
        connection = pyodbc.connect(
            'DRIVER={ODBC Driver 17 for SQL Server};'
            'SERVER=192.168.60.215;' # 连接名
            'DATABASE=live;'         # 数据库名称
            'UID=ck;'                # 登录账号
            'PWD=ck2024!'            # 登录密码
        )
        logging.info("Database connection established")  #记录日志

# 从视图中抓取数据

query = "SELECT * FROM [dbo].[V_SHHW_立库库位利用率]" #写入查询命令

df = pd.read_sql(query, connection)  
  #使用pandas库中的read_sql函数来执行一个SQL查询并将结果读取到一个DataFrame中。

logging.info("Data fetched from the database")       #记录日志

# 指定要写入的 Excel 文件的绝对路径

file_path = r'D:\桌面\利用率.xlsx'

# 加载现有的 Excel 文件

wb = openpyxl.load_workbook(file_path)

# 选择要插入数据的工作表

ws = wb.active 
# wb.active 是 Openpyxl 中的一个方法，用来获取 Excel 工作簿中当前活跃的工作表（通常是打开工作簿 时默认显示的那个工作表）。
# 若想插入名为‘sheet2’的工作表，写法如下
# ws = wb["sheet2"]

# 将 DataFrame 数据插入到工作表中

for index, row in df.iterrows(): 
#是一个循环，遍历 DataFrame 中的每一行。
    ws.append(row.tolist())  
#将 Series 对象转换为列表。这样做是为了能够以列表形式将整行数据插入到工作表中。

# 保存修改后的 Excel 文件

wb.save(file_path)
    logging.info(f"Data written to Excel file: {file_path}") #记录日志

    # 确认文件存在并且大小大于0
    if os.path.exists(file_path) and os.path.getsize(file_path) > 0:
        logging.info(f"File {file_path} exists and is ready to be attached.") #记录日志
        return file_path
    else:
        logging.error(f"File {file_path} does not exist or is empty.") #记录错误日志
        return None

except pyodbc.Error as e:
    logging.error(f"Database error: {e}")#记录错误日志
except Exception as e:
    logging.error(f"An error occurred: {e}")#记录错误日志
finally:
    if 'connection' in locals() and connection:
        connection.close()
        logging.info("Database connection closed")#记录日志

#发送包含Excel附件的电子邮件。

def send_email(receiver_email, file_path):
    try:
        # 发送邮件
        sender_email = "152********@163.com"
        sender_password = "ALGKJZGWNCD***FC"  # 使用生成的授权码
        subject = "利用率数据"
        body = "请查收附件中的 SHHW库位利用率 数据。"

        msg = MIMEMultipart()  # 创建多部分邮件
        msg['From'] = sender_email  # 设置发件人
        msg['To'] = receiver_email  # 设置收件人
        msg['Subject'] = Header(subject, 'utf-8')  # 设置邮件主题

        msg.attach(MIMEText(body, 'plain', 'utf-8'))  # 添加邮件正文

# 添加 Excel 文件附件

with open(file_path, 'rb') as attachment_file:
        attachment = MIMEBase('application', 'vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        attachment.set_payload(attachment_file.read())  # 读取附件文件内容
    encoders.encode_base64(attachment)  # 对附件进行Base64编码
    encoded_filename = Header('SHHW库位利用率.xlsx', 'utf-8').encode()  # 对附件文件名进行编码
    attachment.add_header('Content-Disposition', f'attachment; filename="{encoded_filename}"')  # 添加附件头信息
    msg.attach(attachment)  # 将附件添加到邮件

    logging.info("Email message created with attachment")  # 记录日志

# 使用 SMTP 发送邮件

 try:
        with smtplib.SMTP_SSL('smtp.163.com', 465) as server:
            server.set_debuglevel(1)  # 打开调试输出
            server.login(sender_email, sender_password)  # 登录SMTP服务器
            server.sendmail(sender_email, receiver_email, msg.as_string())  # 发送邮件
            logging.info("Email sent successfully to %s", receiver_email)  # 记录日志
    except smtplib.SMTPException as e:
        logging.error(f"SMTP error occurred: {e}")  # 记录SMTP错误日志

except Exception as e:
    logging.error(f"An error occurred while sending email to {receiver_email}: {e}")  # 记录一般错误日志

# 定时任务：抓取数据并发送邮件。

def job():
    file_path = fetch_data_and_prepare_file()  # 抓取数据并准备Excel文件
    if file_path:  # 如果文件成功准备
        send_email("6546546@163.com", file_path)  # 发送邮件给第一个收件人
        send_email("1546516444@qq.com", file_path)  # 发送邮件给第二个收件人

# 定时任务每天晚上10点执行

schedule.every().day.at("22:00").do(job)

logging.info("Scheduler started")  # 记录日志
while True:
    schedule.run_pending()  # 运行所有计划的任务
    time.sleep(1)  # 等待下一次任务执行

#附源码

import pyodbc
import openpyxl
import pandas as pd
import schedule
import time
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email import encoders
from email.header import Header
import logging
import os

# 配置日志记录
logging.basicConfig(filename='log.txt', level=logging.DEBUG,
                    format='%(asctime)s - %(levelname)s - %(message)s')

def fetch_data_and_prepare_file():
    try:
        logging.info("Starting the fetch_data_and_prepare_file process")

        # 连接数据库
        connection = pyodbc.connect(
            'DRIVER={ODBC Driver 17 for SQL Server};'
            'SERVER=服务器;' #←需自己填写
            'DATABASE=数据库名称;' #←需自己填写
            'UID=账号;' #←需自己填写
            'PWD=密码' #←需自己填写
        )
        logging.info("Database connection established")

        # 从视图中抓取数据
        query = "SELECT * FROM [dbo].[利用率]" #查询命令
        df = pd.read_sql(query, connection)
        logging.info("Data fetched from the database")

        # 指定要写入的 Excel 文件的绝对路径
        file_path = r'D:\桌面\利用率.xlsx' #自建表格 ←需自己填写

        # 加载现有的 Excel 文件
        wb = openpyxl.load_workbook(file_path)

        # 选择要插入数据的工作表，这里假设是第一个工作表
        ws = wb.active

        # 将 DataFrame 数据插入到工作表中
        for index, row in df.iterrows():
            ws.append(row.tolist())

        # 保存修改后的 Excel 文件
        wb.save(file_path)
        logging.info(f"Data written to Excel file: {file_path}")

        # 确认文件存在并且大小大于0
        if os.path.exists(file_path) and os.path.getsize(file_path) > 0:
            logging.info(f"File {file_path} exists and is ready to be attached.") #记录日志
            return file_path
        else:
            logging.error(f"File {file_path} does not exist or is empty.") #记录错误日志
            return None

    except pyodbc.Error as e:
        logging.error(f"Database error: {e}")#记录错误日志
    except Exception as e:
        logging.error(f"An error occurred: {e}")#记录错误日志
    finally:
        if 'connection' in locals() and connection:
            connection.close()
            logging.info("Database connection closed")#记录日志

def send_email(receiver_email, file_path):
    """
    发送包含Excel附件的电子邮件。
    """
    try:
        # 发送邮件
        sender_email = "发送方邮箱" #←需自己填写
        sender_password = "授权码"  #←需自己填写
        subject = "利用率数据"
        body = "请查收附件中的 利用率 数据。"

        msg = MIMEMultipart()  # 创建多部分邮件
        msg['From'] = sender_email  # 设置发件人
        msg['To'] = receiver_email  # 设置收件人
        msg['Subject'] = Header(subject, 'utf-8')  # 设置邮件主题

        msg.attach(MIMEText(body, 'plain', 'utf-8'))  # 添加邮件正文

        # 添加 Excel 文件附件
        with open(file_path, 'rb') as attachment_file:
            attachment = MIMEBase('application', 'vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            attachment.set_payload(attachment_file.read())  # 读取附件文件内容
        encoders.encode_base64(attachment)  # 对附件进行Base64编码
        encoded_filename = Header('利用率.xlsx', 'utf-8').encode()  # 对附件文件名进行编码 #←需自己填写Excel名称
        attachment.add_header('Content-Disposition', f'attachment; filename="{encoded_filename}"')  # 添加附件头信息
        msg.attach(attachment)  # 将附件添加到邮件

        logging.info("Email message created with attachment")  # 记录日志

        # 使用 SMTP 发送邮件
        try:
            with smtplib.SMTP_SSL('smtp.163.com', 465) as server:
                server.set_debuglevel(1)  # 打开调试输出
                server.login(sender_email, sender_password)  # 登录SMTP服务器
                server.sendmail(sender_email, receiver_email, msg.as_string())  # 发送邮件
                logging.info("Email sent successfully to %s", receiver_email)  # 记录日志
        except smtplib.SMTPException as e:
            logging.error(f"SMTP error occurred: {e}")  # 记录SMTP错误日志

    except Exception as e:
        logging.error(f"An error occurred while sending email to {receiver_email}: {e}")  # 记录一般错误日志

def job():
    """
    定时任务：抓取数据并发送邮件。
    """
    file_path = fetch_data_and_prepare_file()  # 抓取数据并准备Excel文件
    if file_path:  # 如果文件成功准备
        send_email("（第一个收件人）须填写", file_path)  #←需自己填写 # 发送邮件给第一个收件人
        send_email("（第二个收件人）须填写", file_path)  #←需自己填写 # 发送邮件给第二个收件人

# 定时任务每天晚上10点执行
schedule.every().day.at("22:00").do(job)

logging.info("Scheduler started")  # 记录日志
while True:
    schedule.run_pending()  # 运行所有计划的任务
    time.sleep(1)  # 等待下一次任务执行