Python3：自动发送账单邮件
Python3：自动发送账单邮件
一、前言
民间借贷，没有信用卡那样，每月会收到账单；为了民间借贷管理更加合理化，写了个还款账单小程序。
二、源码
（1）配置文件代码：
[dbmysql] ip = localhost port = 3306 user = root password = ****** dbname = pythondb [dbtime] starttime = 220000 [dbemail] username = ******@qq.com password = ****** smtp = smtp.qq.com sender = ******@qq.com receiver = ******@qq.com sign_company = ****** sign_addr = ****** sign_dept = ****** sign_name =****** sgin_email = ******@qq.com sign_phone = ****** sign_qq = ****** sign_www = http://www.******.com [dbpath] logpath = E:\logs\autoemail
（2）程序代码：
# python3 # author:lizm # date:2018-07-19 10:00:00 # -*- coding: utf-8 -*- ''' description:自动发送账单邮件 ''' import logging import configparser import sys import datetime,time from smtplib import SMTP_SSL from email.header import Header from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart import pymysql from selenium import webdriver from sqlalchemy import Column, Integer, String, DateTime, create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import and_, func class Email(object): def __init__(self, hkrq, sqjyje, rll, bqyhlx, bqhkje, bqjyje, remark): self.hkrq = hkrq self.sqjyje = sqjyje self.rll = rll self.bqyhlx = bqyhlx self.bqhkje = bqhkje self.bqjyje = bqjyje self.remark = remark def __get__(self, instance, cls): if instance is None: return self else: return instance.__dict__[self.name] def __set__(self, instance, value): instance.__dict__[self.name] = value def __delete__(self, instance): del instance.__dict__[self.name] # 获取配置文件信息 def dbconfig(): # 生成config对象 cfg = configparser.ConfigParser() # 读取配置文件（此处是utf-8-sig，而不是utf-8） cfg.read(sys.path[0]+"\dbconfig.ini",encoding="utf-8-sig") # dbmysql ip = cfg.get("dbmysql","ip") port = cfg.get("dbmysql","port") user = cfg.get("dbmysql","user") pwd = cfg.get("dbmysql","password") dbname = cfg.get("dbmysql","dbname") # dbtime starttime = cfg.get("dbtime","starttime") # dbemail username = cfg.get("dbemail","username") password = cfg.get("dbemail","password") smtp = cfg.get("dbemail","smtp") sender = cfg.get("dbemail","sender") receiver = cfg.get("dbemail","receiver") sign_company = cfg.get("dbemail","sign_company") sign_addr = cfg.get("dbemail","sign_addr") sign_dept = cfg.get("dbemail","sign_dept") sign_name = cfg.get("dbemail","sign_name") sgin_email = cfg.get("dbemail","sgin_email") sign_phone = cfg.get("dbemail","sign_phone") sign_qq = cfg.get("dbemail","sign_qq") sign_www = cfg.get("dbemail","sign_www") # dbpath logpath = cfg.get("dbpath","logpath") return (ip,port,user,pwd,dbname,starttime,username,password,smtp,sender,receiver,sign_company,sign_addr,sign_dept,sign_name,sgin_email,sign_phone,sign_qq,sign_www,logpath) #邮件定义 mail_info = { "from": dbconfig()[9], "to": dbconfig()[10], "hostname": dbconfig()[8], "username": dbconfig()[6], "password": dbconfig()[7], "mail_subject": "还款账单（"+time.strftime("%Y%m%d")+"）", "mail_text": "你好, this is a test email, sended by py", "mail_encoding": "utf-8", "sign_all": "签名" } # 配置日志 logger = logging.getLogger() # 设置文件 file = logging.FileHandler(sys.path[0]+"\logs"+time.strftime("%Y%m%d")+".log") #file = logging.FileHandler(dbconfig()[11]+"\log"+time.strftime("%Y%m%d")+".log") # set formatter formatter = logging.Formatter("%(asctime)s %(levelname)s %(message)s") file.setFormatter(formatter) logger.addHandler(file) # set log level logger.setLevel(logging.NOTSET) #查询最新的数据 def qryData(): try: # 打开数据库连接 conn = pymysql.connect(host=dbconfig()[0], user=dbconfig()[2],passwd=dbconfig()[3], db=dbconfig()[4], port=int(dbconfig()[1]), charset='utf8') # 获取一个游标 cursor = conn.cursor() # 查询数据库中的最新数据 sql_date = """select bqjyje,rll from """+dbconfig()[4]+""".py_email order by hkrq desc limit 1 ;""" #print('sql_check>>>:%s' %sql_date) cursor.execute(sql_date) results = cursor.fetchall() # 判断是否有记录数 if len(results) != 0: r_date = results[0] cursor.close() # 关闭游标 conn.close() # 释放数据库资源 except: logger.info("Email异常（qryData）：获取最新数据失败") print("Email异常（qryData）：获取最新数据失败") r_bqjyje = r_date[0] r_rll = r_date[1] return (r_bqjyje,r_rll) #数据入库处理 def saveData(rp): r_code = 0 try: # 打开数据库连接 conn = pymysql.connect(host=dbconfig()[0], user=dbconfig()[2],passwd=dbconfig()[3], db=dbconfig()[4], port=int(dbconfig()[1]), charset='utf8') # 获取一个游标 cursor = conn.cursor() sql = """INSERT INTO """+dbconfig()[4]+""".py_email(hkrq,sqjyje,rll,bqyhlx,bqhkje,bqjyje,remark,createtime) VALUES('"""+rp.hkrq+"""','""" + rp.sqjyje + """','"""+rp.rll+"""','""" + rp.bqyhlx + """','""" + rp.bqhkje + """','""" + rp.bqjyje + """','',sysdate());""" try: #print('sql>>>:' + sql) # 执行sql语句 cursor.execute(sql) # 提交到数据库执行 conn.commit() r_code = 0 except: # 如果发生错误则回滚 conn.rollback() r_code = 1 cursor.close() # 关闭游标 conn.close() # 释放数据库资源 except: r_code = 1 if r_code ==1: logger.info("Email异常（saveData）：数据入库失败") print("Email异常（saveData）：数据入库失败") else: logger.info("还款账单已生成并发送邮件") print("还款账单已生成并发送邮件") return r_code def send(rp): #使用SMTP_SSL就是默认使用465端口 smtp = SMTP_SSL(mail_info["hostname"]) #set_debuglevel()是用来调试的。参数值为1表示开启调试模式，参数值为0关闭调试模式 smtp.set_debuglevel(0) #连接服务器 smtp.ehlo(mail_info["hostname"]) #邮箱登录 smtp.login(mail_info["username"], mail_info["password"]) msg = MIMEMultipart() #填写正文内容 main_html = """ <html> <head></head> <body> <div font-size:15px;background-color:#FFFFFF;"> <div> <span style="line-height:3;">------------------------还款账单（"""+rp.hkrq+"""）------------------------</span> </div> <div> <span style="line-height:1.5;">应还总金额："""+rp.sqjyje+"""元</span> </div> <div> <span style="line-height:1.5;">本期还款："""+rp.bqhkje+"""元</span> </div> <div> <span style="line-height:1.5;">本期利息："""+rp.sqjyje+"""（本金）*"""+rp.rll+"""（万分之三/日）*30（天）="""+rp.bqyhlx+"""元</span> </div> <div> <span style="line-height:1.5;">------------------------</span> </div> <div> <span style="line-height:1.5;">剩余应还总金额："""+rp.sqjyje+"""（本金）+"""+rp.bqyhlx+"""（利息）-"""+rp.bqhkje+"""（本期还款）="""+rp.bqjyje+"""元</span> </div> <p><br/></p> <div> <span style="line-height:1.5;">————————————</span> </div> </div> <div style="font-size:13px;background-color:#FFFFFF;"> <div style="font-family:Verdana;"> <div> <span style="font-size:13px;line-height:1.5;">公司："""+dbconfig()[11]+"""</span> </div> <div> <span style="font-size:13px;line-height:1.5;">通讯地址："""+dbconfig()[12]+"""</span> </div> <div> <span style="font-size:13px;line-height:1.5;">"""+dbconfig()[13]+"""："""+dbconfig()[14]+"""</span> </div> <div> <span style="font-size:13px;line-height:1.5;">邮箱：<a href='mailto:"""+dbconfig()[15]+"""' target="_blank">"""+dbconfig()[15]+"""</a></span> </div> <div> <span style="font-size:13px;line-height:1.5;">手机："""+dbconfig()[16]+"""</span> </div> <div"> <span style="font-size:13px;line-height:1.5;">QQ："""+dbconfig()[17]+"""</span> </div> <div> <span style="font-size:13px;line-height:1.5;">公司网址：<a href='"""+dbconfig()[18]+"""' target="_blank">"""+dbconfig()[18]+"""</a></span> </div> </div> </div> <p><br/></p> </body> </html> """ main_msg = MIMEText(main_html, "html", mail_info["mail_encoding"]) msg.attach(main_msg) #填写邮件标题 msg["Subject"] = Header(mail_info["mail_subject"], mail_info["mail_encoding"]) #发送者邮箱地址 msg["from"] = mail_info["from"] #接收者邮件地址 msg["to"] = mail_info["to"] try: #发送邮件 smtp.sendmail(mail_info["from"], mail_info["to"], msg.as_string()) #退出 smtp.quit() #入库操作 saveData(rp) except: logger.info("还款账单生成失败") print("还款账单生成失败") if __name__ == '__main__': bqhkje = input("请输入还款金额:") print("还款账单生成中...") hkrq = time.strftime('%Y%m%d') rdata = qryData() sqjyje = rdata[0] rll = rdata[1] bqhklx = str(float(sqjyje)*float(rll)*30) #bqhkje = '3500.00' bqjyje = str(float(sqjyje)+float(bqhklx)-float(bqhkje)) remark = "" #print("参数%s,%s,%s,%s,%s,%s" %(hkrq,sqjyje,rll,bqhklx,bqhkje,bqjyje)) rp = Email(hkrq,sqjyje,rll,bqhklx,bqhkje,bqjyje,remark) send(rp)
三、效果
