简单的连数据库 拼接数据 发邮件Python脚本

首先要下载python3的环境

简版

import pymysql
import time
from email import encoders
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
import smtplib
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart


db = pymysql.connect( host='127.0.0.1', user='root', password='root', db='test')



fromaddr = 'support@163.com'
password = '163163'

#toaddrs = ['xxx@xxx.com','xxx@xxx.com','xxx@xxx.com']
toaddrs = ['xuxiejp@163.com']

date = time.strftime('%Y-%m-%d', time.localtime(time.time()))

def getTenantList():
    cursor = db.cursor()
    sql = "SELECT id from _field"
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    return data

def getCoustomer(tenantId):
    cursor = db.cursor()
    sql = "select * from _field_customer_group_data where field_id= " + str(tenantId)
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    return data

def getMessage(space,customer,groupName):
    sp = "null"
    if str(space) == str(1000):
        sp="拉萨"
    elif str(space) == str(1001):
        sp="长春"
    elif str(space) == str(1002):
        sp="南宁"
    else:
        sp="鄂尔多斯"
    sql = sp + "  客户 ： " + str(customer)+ "  组： " + str(groupName)+"  低于99%;"
    return sql

def stringBuffer(old,space):
    sp = old + "\n" + space 
    return sp;

def statistics():
    flag = ""
    data = getTenantList()

    for tenant in data:

        dataList = getCoustomer(tenant[0])
        for customer in dataList:
            if customer[8]>0:
                if customer[9]*9.9>=customer[8]:
                    mess = getMessage(customer[1],customer[4],customer[2])
                    flag = stringBuffer(flag,mess)
                #print(flag)
    return flag


def sendMail(mess):
    m = MIMEMultipart('related')

    m["From"] = "xx科技"  # 发件人
    m["To"] = ";".join(toaddrs)  # 收件人
    m["Subject"] = date + "掉线统计表"

    msg = MIMEText(mess, 'plain', 'utf-8')
    m.attach(msg)

    try:
        server = smtplib.SMTP('smtp.exmail.qq.com',587)
        server.starttls()
        server.login(fromaddr, password)
        server.sendmail(fromaddr, toaddrs, m.as_string())
        server.quit()

    except smtplib.SMTPException as e:
        print('error:', e)  # 打印错误




mes = statistics()
if len(mes)>3:
    print(date)    
    print(mes)
    sendMail(mes)	

原版

#!/usr/bin/python3
# coding=utf-8

import os
import time
from email import encoders
from email.mime.base import MIMEBase
from email.mime.text import MIMEText

import pymysql
from openpyxl import load_workbook
import smtplib


from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart

db = pymysql.connect("127.0.0.1", "root", "root", "platform")

fromaddr = 'support@163.cn'
password = '163163'
toaddrs = ['wa@163.cn']

template_path='d:/Template.xlsx'
file_path="d:/"
date = time.strftime('%Y-%m-%d', time.localtime(time.time()))
file_name = date+" 租户统计报表.xlsx"
def getTenantList():
    cursor = db.cursor()
    sql = "select * FROM _tenant"
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    return data


def getUsers(tenantId):
    cursor = db.cursor()
    sql = "select count(1) as value FROM _user where tenant_id= " + str(tenantId)
    cursor.execute(sql)
    data = cursor.fetchone()
    cursor.close()
    return data


def getServerNum(tenantId):
    cursor = db.cursor()
    sql = "select count(1) as value FROM cnm_server where tenant_id= " + str(tenantId)
    cursor.execute(sql)
    data = cursor.fetchone()
    cursor.close()
    return data


def getNetworkDeviceNum(tenantId):
    cursor = db.cursor()
    sql = "select count(1) as value FROM cnm_network_device where tenant_id= "+str(tenantId)
    cursor.execute(sql)
    data = cursor.fetchone()
    cursor.close()
    return data


def getAgent(tenantId):
    cursor = db.cursor()
    sql = "select count(1) as value FROM _collector where collector_type=1 and tenant_id= " + str(tenantId)
    cursor.execute(sql)
    data = cursor.fetchone()
    cursor.close()
    return data


def getProxy(tenantId):
    cursor = db.cursor()
    sql = "select count(1) as value FROM _collector where collector_type=2 and tenant_id= " + str(tenantId)
    cursor.execute(sql)
    data = cursor.fetchone()
    cursor.close()
    return data


def statistics():
    data = getTenantList()
    wb = load_workbook(template_path)
    ws = wb['Sheet1']
    index = 3
    for tenant in data:
        tenantId = tenant[0]
        createAt = str(tenant[9])
        ws['A'+str(index)] = tenant[1]
        ws['B'+str(index)] = createAt
        ws['C'+str(index)] = getUsers(tenantId)[0]
        ws['D'+str(index)] = getServerNum(tenantId)[0]+getNetworkDeviceNum(tenantId)[0]
        ws['E'+str(index)] = getAgent(tenantId)[0]
        ws['F'+str(index)] = getProxy(tenantId)[0]
        index = index+1
    file = file_path+file_name
    if os.path.exists(file):
        os.remove(file)
    wb.save(file)
    # for tenant in data:
    #     print(tenant[0] + tenant[1] + str(tenant[9]))


def sendMail():

    content = "附件是截止"+date+"租户统计情况"
    file = file_path + file_name
    att = MIMEBase('application', 'octet-stream')
    excelApart = MIMEImage(open(file, 'rb').read(), file_name.split('.')[-1])
    excelApart.add_header('Content-Disposition', 'attachment', filename=('gbk', '', file_name))
    encoders.encode_base64(excelApart)
    m = MIMEMultipart()
    m["From"] = "xx"  # 发件人
    m["To"] = ";".join(toaddrs)  # 收件人
    m["Subject"] = date + "统计报表"
    msg = MIMEText(content, 'html', 'utf-8')
    m.attach(excelApart)
    m.attach(msg)
    try:
        server = smtplib.SMTP('mail.qicloud.net.cn')
        server.login(fromaddr, password)
        server.sendmail(fromaddr, toaddrs, m.as_string())
        server.quit()
    except smtplib.SMTPException as e:
        print('error:', e)  # 打印错误

statistics()
sendMail()
os.remove(file_path+file_name)