日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

業(yè)務(wù)需求：

1.讀取SQL中的統(tǒng)計數(shù)據(jù)

2.將統(tǒng)計數(shù)據(jù)導(dǎo)出CSV

3.把CSV發(fā)送指定郵箱

引入需要處理的包：csv（或xlwt），smtplib，MySQL

Python/ target=_blank class=infotextkey>Python版本: 3.9

在輸出表頭時使用2個數(shù)組，以順序建立對應(yīng)關(guān)系輸出，還有其他的巧妙的方法嗎？

話不多說，直接上代碼

DB建表就不貼了，一個user表（name,phone,sex）

# -*- coding: utf-8 -*-

# 導(dǎo)入CSV安裝包
import csv
# 日期
import datetime
# 郵件
import smtplib
from email.header import Header
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

# mysql
import pymysql.cursors
# Excel處理
import xlwt

# 連接數(shù)據(jù)庫
connect = pymysql.Connect(
    host='127.0.0.1',
    port=3306,
    user='test_db',
    passwd='dbmima',
    db='test',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

# 第三方 SMTP 服務(wù)
# 設(shè)置服務(wù)器
mail_host = "smtp.exmail.qq.com"
# 用戶名
mail_user = "76079901@qq.com"
# 口令
mail_pass = "mima"

# 查詢數(shù)據(jù)
sql = '''
            select * from user
    '''

# 定義表頭
array = [ 'name', 'phone', 'sex']
title = ['用戶名', '手機(jī)號', '性別']


def process():
    export_csv()
    send_email()


def export_excel():
    # 獲取游標(biāo)
    cursor = connect.cursor()
    book = xlwt.Workbook()
    sheet = book.add_sheet("sheet1")

    cursor.execute(sql)
    result = cursor.fetchall()

    # 輸出表頭
    for z, t in enumerate(title):
        sheet.write(0, z, t)

    # 輸出數(shù)據(jù)
    for x, row in enumerate(result):
        for y, field in enumerate(array):
            # print(x+1, y, row[field])
            sheet.write(x + 1, y, get_value(str(row[field])))

    bookName = 'UR_Record_%s.xls' % datetime.datetime.now().strftime('%Y%m%d')
    # 保存Excel
    book.save(bookName)

    # 關(guān)閉連接
    cursor.close()
    connect.close()


def export_csv():
    cursor = connect.cursor()

    cursor.execute(sql)
    result = cursor.fetchall()
    print("數(shù)據(jù)量: " + str(len(result)))

    book_name = 'Record_%s.csv' % datetime.datetime.now().strftime('%Y%m%d')
    # 1. 創(chuàng)建文件對象 解決表情符亂碼，設(shè)置utf-8-sig，且數(shù)據(jù)庫鏈接改為utf8mb4
    f = open(book_name, 'w', encoding='utf-8-sig', newline='')
    # 2. 基于文件對象構(gòu)建 csv寫入對象
    csv_writer = csv.writer(f)
    # 3. 構(gòu)建列表頭
    csv_writer.writerow(title)
    # 4. 寫入csv文件內(nèi)容
    for x, row in enumerate(result):
        r = ['init'] * len(array)
        for y, field in enumerate(array):
            r[y] = row[field]
        # 保存單條記錄，使用writerow(result)即可
        # print(r)
        # csv_writer.writerow([row['mobile'], row['join_at'], row['source']])
        csv_writer.writerow(r)

    print("導(dǎo)出完成")
    # 關(guān)閉連接
    f.close()


def get_value(val):
    if val == 'None':
        return ' '
    else:
        return val


def send_email():
    sender = mail_user
    # 接收郵件，可設(shè)置為你的QQ郵箱或者其他郵箱 
    receivers = ['76079901@qq.com']

    # 三個參數(shù)：第一個為文本內(nèi)容，第二個 plain 設(shè)置文本格式，第三個 utf-8 設(shè)置編碼
    message = MIMEMultipart()

    # 發(fā)送者
    message['From'] = Header("FROM", 'utf-8')
    # 接收者 
    message['To'] = Header("To", 'utf-8')
    # 主題
    message['Subject'] = Header('【Header Content】', 'utf-8')
    # 郵件正文內(nèi)容
    message.attach(MIMEText('''
        
            正文
                            
                            
        ''', 'plain', 'utf-8'))

    # 附件
    curDay = datetime.datetime.now().strftime('%Y%m%d')
    att1 = MIMEText(open('Record_%s.csv' % curDay, 'rb').read(), 'base64', 'utf-8')
    att1["Content-Type"] = 'Application/octet-stream'
    # filename = 文件名
    att1["Content-Disposition"] = 'attachment;filename="Record_%s.csv"' % curDay
    message.attach(att1)

    try:
        smtp_obj = smtplib.SMTP_SSL(mail_host, 465)
        smtp_obj.login(mail_user, mail_pass)
        smtp_obj.sendmail(sender, receivers, message.as_string())
        smtp_obj.quit()
        print("send success!!!")
    except smtplib.SMTPException as e:
        print("Error: send Fail!!! ", e)


if __name__ == '__main__':
    process()