SendMail
├── templates
│ ├── __init__.py
│ └── templates
│ └── main.html
└── main.py
Last active
May 17, 2019 06:16
-
-
Save jinyu121/413679bcef12d8091b7481413f4bd81b to your computer and use it in GitHub Desktop.
将excel中的条目发给每个人
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# -*- coding: utf-8 -*- | |
import smtplib | |
import time | |
import pandas as pd | |
from email.mime.text import MIMEText | |
from email.header import Header | |
from tqdm import tqdm | |
from jinja2 import Environment, PackageLoader, select_autoescape | |
# 第三方 SMTP 服务 | |
mail_host = "" # 设置服务器 | |
mail_user = "" # 用户名 | |
mail_pass = "" # 口令 | |
SLEEP_TIME = 10 | |
EXCEL_FILE = "" | |
sender = mail_user | |
def get_mail(): | |
try: | |
smtpObj = smtplib.SMTP() | |
smtpObj.connect(mail_host, 25) | |
smtpObj.login(mail_user, mail_pass) | |
return smtpObj | |
except smtplib.SMTPException as e: | |
print("Error: 登录失败") | |
print(e) | |
# 加载邮件模板 | |
env = Environment( | |
loader=PackageLoader('templates', 'templates'), | |
autoescape=select_autoescape(['html', 'xml'])) | |
template = env.get_template('main.html') | |
def send(smtpObj, receiver, message): | |
"""发送邮件""" | |
try: | |
smtpObj.sendmail(sender, receiver, message.as_string()) | |
print("成功:{}".format(receiver)) | |
except Exception as e: | |
print("无法发送邮件:{}".format(receiver)) | |
print(e) | |
def get_record_by_value(id, coll, column): | |
return coll[coll[column] == id] | |
def main(): | |
with pd.ExcelFile(EXCEL_FILE) as xls_file: | |
st_summary = xls_file.parse(0) | |
st_deyu = xls_file.parse(3) | |
st_lunwen = xls_file.parse(4) | |
st_jingsai = xls_file.parse(5) | |
ith = 0 | |
smtpObj = get_mail() | |
for people in tqdm(st_summary.values): | |
ith += 1 | |
# 每次登录只能发送5封邮件,所以需要登出再登录 | |
if ith % 5 == 0: | |
smtpObj.close() | |
smtpObj = get_mail() | |
sid = people[0] | |
name = people[1] | |
stu_type = people[10] | |
jidian = people[2] | |
para_jidian_full = 4 | |
para_chengji_full = people[11] | |
score_xuexi = people[3] | |
score_keyan = people[4] | |
score_deyu = people[5] | |
score_gongzuo = people[6] | |
detail_deyu = get_record_by_value(sid, st_deyu, '学号') | |
detail_lunwen = get_record_by_value(name, st_lunwen, '姓名') | |
detail_jingsai = get_record_by_value(name, st_jingsai, '姓名') | |
html = template.render( | |
id=sid, | |
name=name, | |
stu_type=stu_type, | |
jidian=jidian, | |
para_jidian_full=para_jidian_full, | |
para_chengji_full=para_chengji_full, | |
score_xuexi=score_xuexi, | |
score_keyan=score_keyan, | |
score_deyu=score_deyu, | |
score_gongzuo=score_gongzuo, | |
detail_deyu=zip(detail_deyu.columns, detail_deyu.values[0]), | |
detail_lunwen=detail_lunwen, | |
detail_jingsai=detail_jingsai) | |
message = MIMEText(html, 'html', 'utf-8') | |
message['From'] = Header("班委成员", 'utf-8') | |
message['To'] = Header("You<{}@fudan.edu.cn>".format(sid), 'utf-8') | |
message['Subject'] = Header('奖学金评定核对', 'utf-8') | |
send(smtpObj, ["{}".format(sid)], message) | |
# 歇一会儿,防止被 ban | |
time.sleep(SLEEP_TIME) | |
if "__main__" == __name__: | |
main() |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# -*- coding: utf-8 -*- | |
import smtplib | |
from email.mime.text import MIMEText | |
import csv | |
from tqdm import tqdm | |
from jinja2 import Environment, PackageLoader, select_autoescape | |
from argparse import ArgumentParser | |
from pathlib import Path | |
from email.header import Header | |
import functools | |
import time | |
# 第三方 SMTP 服务 | |
MAIL_HOET = "" # 设置服务器 | |
MAIL_USER = "" # 用户名 | |
MAIL_PASS = "" # 口令 | |
def retry(num_attempts=3, exception_class=Exception, log=None, sleeptime=1): | |
def decorator(func): | |
@functools.wraps(func) | |
def wrapper(*args, **kwargs): | |
for i in range(num_attempts): | |
try: | |
return func(*args, **kwargs) | |
except exception_class as e: | |
if i == num_attempts - 1: | |
raise | |
else: | |
if log: | |
log.error('Failed with error %r, trying again', e) | |
time.sleep(sleeptime) | |
return wrapper | |
return decorator | |
def parse_args(): | |
parser = ArgumentParser() | |
parser.add_argument("--data", type=str, default="info.csv") | |
parser.add_argument("--skip", "-k", type=int, default=0, help="Skip X records") | |
parser.add_argument("--sleep", "-s", type=int, default=2, help="Sleep X seconds after sending a mail") | |
parser.add_argument("--max", "-m", type=int, default=5, help="Re-login after sending X mails") | |
return parser.parse_args() | |
class MailDeliver: | |
def __init__(self, data_source, sleep=10, skip=0, max_mail=5): | |
self.data = self.get_data(Path(data_source)) | |
self.sleep = sleep | |
self.skip = skip | |
self.max_mail = max_mail | |
self.template = self.get_template() | |
self._sender = None | |
self._counter = 0 | |
@retry() | |
def get_sender(self): | |
smtp_obj = smtplib.SMTP() | |
smtp_obj.connect(MAIL_HOET, 25) | |
smtp_obj.login(MAIL_USER, MAIL_PASS) | |
return smtp_obj | |
def get_template(self): | |
env = Environment(loader=PackageLoader('templates', 'templates'), | |
autoescape=select_autoescape(['html', 'xml'])) | |
template = env.get_template('main.html') | |
return template | |
def get_data(self, data_path): | |
assert data_path.exists(), "Can not open {}".format(data_path) | |
reader = csv.DictReader(Path(args.data).open()) | |
for data in reader: | |
yield data | |
@retry() | |
def send(self, sender, receiver, message): | |
if self._counter % self.max_mail == 0 or self._sender is None: | |
self._sender = self.get_sender() | |
self._counter += 1 | |
self._sender.sendmail(sender, receiver, message.as_string()) | |
def work(self): | |
for ith, info in enumerate(tqdm(self.data)): | |
if ith < self.skip: | |
continue | |
sid = info['sid'] | |
sname = info['name'] | |
try: | |
html = self.template.render(info=info) | |
message = MIMEText(html, 'html', 'utf-8') | |
message['From'] = Header("班委成员".format(MAIL_USER), 'utf-8') | |
message['To'] = Header("You<{}@fudan.edu.cn>".format(sid), 'utf-8') | |
message['Subject'] = Header('[毕业信息核对]住宿信息核对', 'utf-8') | |
self.send(MAIL_USER, ["{}@fudan.edu.cn".format(sid)], message) | |
tqdm.write("[{} {}] SUCCESS".format(sid, sname)) | |
time.sleep(self.sleep) | |
except Exception as e: | |
tqdm.write("[{} {}] FAIL".format(sid, sname)) | |
if "__main__" == __name__: | |
args = parse_args() | |
mailer = MailDeliver(args.data, args.sleep, args.skip) | |
mailer.work() |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!doctype html> | |
<html> | |
<head> | |
<meta name="viewport" content="width=device-width" /> | |
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> | |
<title>Simple Transactional Email</title> | |
<style> | |
/* ------------------------------------- | |
GLOBAL RESETS | |
------------------------------------- */ | |
img { | |
border: none; | |
-ms-interpolation-mode: bicubic; | |
max-width: 100%; | |
} | |
body { | |
background-color: #f6f6f6; | |
font-family: sans-serif; | |
-webkit-font-smoothing: antialiased; | |
font-size: 14px; | |
line-height: 1.4; | |
margin: 0; | |
padding: 0; | |
-ms-text-size-adjust: 100%; | |
-webkit-text-size-adjust: 100%; | |
} | |
table { | |
border-collapse: collapse; | |
mso-table-lspace: 0pt; | |
mso-table-rspace: 0pt; | |
width: 100%; | |
} | |
.table-striped tr { | |
border-bottom: 1px solid #ccc; | |
} | |
.table-striped tr:nth-child(odd) { | |
background: #f9f9f9; | |
} | |
.table-striped { | |
margin-bottom: 20px; | |
} | |
table td { | |
font-family: sans-serif; | |
font-size: 14px; | |
vertical-align: top; | |
} | |
.table-hover>tbody>tr:hover { | |
background-color: #f5f5f5; | |
} | |
/* ------------------------------------- | |
BODY & CONTAINER | |
------------------------------------- */ | |
.body { | |
background-color: #f6f6f6; | |
width: 100%; | |
} | |
/* Set a max-width, and make it display as block so it will automatically stretch to that width, but will also shrink down on a phone or something */ | |
.container { | |
display: block; | |
Margin: 0 auto !important; | |
/* makes it centered */ | |
max-width: 580px; | |
padding: 10px; | |
width: 580px; | |
} | |
/* This should also be a block element, so that it will fill 100% of the .container */ | |
.content { | |
box-sizing: border-box; | |
display: block; | |
Margin: 0 auto; | |
max-width: 580px; | |
padding: 10px; | |
} | |
/* ------------------------------------- | |
HEADER, FOOTER, MAIN | |
------------------------------------- */ | |
.main { | |
background: #ffffff; | |
border-radius: 3px; | |
width: 100%; | |
} | |
.wrapper { | |
box-sizing: border-box; | |
padding: 20px; | |
} | |
.content-block { | |
padding-bottom: 10px; | |
padding-top: 10px; | |
} | |
.footer { | |
clear: both; | |
Margin-top: 10px; | |
text-align: center; | |
width: 100%; | |
} | |
.footer td, | |
.footer p, | |
.footer span, | |
.footer a { | |
color: #999999; | |
font-size: 12px; | |
text-align: center; | |
} | |
/* ------------------------------------- | |
TYPOGRAPHY | |
------------------------------------- */ | |
h1, | |
h2, | |
h3, | |
h4 { | |
color: #000000; | |
font-family: sans-serif; | |
font-weight: 400; | |
line-height: 1.4; | |
margin: 15px 0; | |
} | |
h1 { | |
font-size: 35px; | |
font-weight: 300; | |
text-align: center; | |
text-transform: capitalize; | |
} | |
p, | |
ul, | |
ol { | |
font-family: sans-serif; | |
font-size: 14px; | |
font-weight: normal; | |
margin: 0; | |
Margin-bottom: 15px; | |
} | |
p li, | |
ul li, | |
ol li { | |
list-style-position: inside; | |
margin-left: 5px; | |
} | |
a { | |
color: #3498db; | |
text-decoration: underline; | |
} | |
/* ------------------------------------- | |
BUTTONS | |
------------------------------------- */ | |
.btn { | |
box-sizing: border-box; | |
width: 100%; | |
} | |
.btn>tbody>tr>td { | |
padding-bottom: 15px; | |
} | |
.btn table { | |
width: auto; | |
} | |
.btn table td { | |
background-color: #ffffff; | |
border-radius: 5px; | |
text-align: center; | |
} | |
.btn a { | |
background-color: #ffffff; | |
border: solid 1px #3498db; | |
border-radius: 5px; | |
box-sizing: border-box; | |
color: #3498db; | |
cursor: pointer; | |
display: inline-block; | |
font-size: 14px; | |
font-weight: bold; | |
margin: 0; | |
padding: 12px 25px; | |
text-decoration: none; | |
text-transform: capitalize; | |
} | |
.btn-primary table td { | |
background-color: #3498db; | |
} | |
.btn-primary a { | |
background-color: #3498db; | |
border-color: #3498db; | |
color: #ffffff; | |
} | |
/* ------------------------------------- | |
OTHER STYLES THAT MIGHT BE USEFUL | |
------------------------------------- */ | |
.last { | |
margin-bottom: 0; | |
} | |
.first { | |
margin-top: 0; | |
} | |
.align-center { | |
text-align: center; | |
} | |
.align-right { | |
text-align: right; | |
} | |
.align-left { | |
text-align: left; | |
} | |
.clear { | |
clear: both; | |
} | |
.mt0 { | |
margin-top: 0; | |
} | |
.mb0 { | |
margin-bottom: 0; | |
} | |
.preheader { | |
color: transparent; | |
display: none; | |
height: 0; | |
max-height: 0; | |
max-width: 0; | |
opacity: 0; | |
overflow: hidden; | |
mso-hide: all; | |
visibility: hidden; | |
width: 0; | |
} | |
.powered-by a { | |
text-decoration: none; | |
} | |
hr { | |
border: 0; | |
border-bottom: 1px solid #f6f6f6; | |
Margin: 20px 0; | |
} | |
/* ------------------------------------- | |
RESPONSIVE AND MOBILE FRIENDLY STYLES | |
------------------------------------- */ | |
@media only screen and (max-width: 620px) { | |
table[class=body] h1 { | |
font-size: 28px !important; | |
margin-bottom: 10px !important; | |
} | |
table[class=body] p, | |
table[class=body] ul, | |
table[class=body] ol, | |
table[class=body] td, | |
table[class=body] span, | |
table[class=body] a { | |
font-size: 16px !important; | |
} | |
table[class=body] .wrapper, | |
table[class=body] .article { | |
padding: 10px !important; | |
} | |
table[class=body] .content { | |
padding: 0 !important; | |
} | |
table[class=body] .container { | |
padding: 0 !important; | |
width: 100% !important; | |
} | |
table[class=body] .main { | |
border-left-width: 0 !important; | |
border-radius: 0 !important; | |
border-right-width: 0 !important; | |
} | |
table[class=body] .btn table { | |
width: 100% !important; | |
} | |
table[class=body] .btn a { | |
width: 100% !important; | |
} | |
table[class=body] .img-responsive { | |
height: auto !important; | |
max-width: 100% !important; | |
width: auto !important; | |
} | |
} | |
/* ------------------------------------- | |
PRESERVE THESE STYLES IN THE HEAD | |
------------------------------------- */ | |
@media all { | |
.ExternalClass { | |
width: 100%; | |
} | |
.ExternalClass, | |
.ExternalClass p, | |
.ExternalClass span, | |
.ExternalClass font, | |
.ExternalClass td, | |
.ExternalClass div { | |
line-height: 100%; | |
} | |
.apple-link a { | |
color: inherit !important; | |
font-family: inherit !important; | |
font-size: inherit !important; | |
font-weight: inherit !important; | |
line-height: inherit !important; | |
text-decoration: none !important; | |
} | |
.btn-primary table td:hover { | |
background-color: #34495e !important; | |
} | |
.btn-primary a:hover { | |
background-color: #34495e !important; | |
border-color: #34495e !important; | |
} | |
} | |
</style> | |
</head> | |
<body class=""> | |
<table border="0" cellpadding="0" cellspacing="0" class="body"> | |
<tr> | |
<td> </td> | |
<td class="container"> | |
<div class="content"> | |
<!-- START CENTERED WHITE CONTAINER --> | |
<span class="preheader">{{ name }}</b> 同学:本学期奖学金评定工作开始了。下面是一些数据,请核对一下。如有问题请及时反馈。</span> | |
<table class="main"> | |
<!-- START MAIN CONTENT AREA --> | |
<tr> | |
<td class="wrapper"> | |
<table border="0" cellpadding="0" cellspacing="0"> | |
<tr> | |
<td> | |
<div class="container"> | |
<p><b>{{ name }}</b> 同学:</p> | |
<p>你好!本学期奖学金评定工作开始了。下面是一些数据,请核对一下:</p> | |
<h2>概览</h2> | |
<table class="table table-striped table-hover table-condensed"> | |
<thead> | |
<tr> | |
<th>Key</th> | |
<th>Value</th> | |
<th>Note</th> | |
</tr> | |
</thead> | |
<tbody> | |
<tr> | |
<td>姓名</td> | |
<td>{{ name }}</td> | |
<td></td> | |
</tr> | |
<tr> | |
<td>学号</td> | |
<td>{{ id }}</td> | |
<td></td> | |
</tr> | |
<!-- | |
<tr> | |
<td>类型</td> | |
<td>{{ stu_type }}</td> | |
<td></td> | |
</tr> | |
--> | |
<tr> | |
<td>绩点</td> | |
<td>{{ jidian }}</td> | |
<td></td> | |
</tr> | |
<tr> | |
<td>学习成绩</td> | |
<td>{{ score_xuexi }}</td> | |
<td>计算方法:<code>绩点/满绩点*成绩满分</code>,<br/>其中满绩点={{ para_jidian_full }}<br/>成绩满分={{ para_chengji_full }}</td> | |
</tr> | |
<tr> | |
<td>科研成绩</td> | |
<td>{{ score_keyan }}</td> | |
<td>包括论文加分和竞赛加分<br />详见下表</td> | |
</tr> | |
<tr> | |
<td>德育成绩</td> | |
<td>{{ score_deyu }}</td> | |
<td>详见下表</td> | |
</tr> | |
<!-- | |
<tr> | |
<td>工作成绩</td> | |
<td>{{ score_gongzuo }}</td> | |
<td>分数由导师/课题组打出</td> | |
</tr>--> | |
</tbody> | |
</table> | |
<h2>论文加分</h2> | |
<table class="table table-striped table-hover table-condensed"> | |
<thead> | |
<tr> | |
{% for item in detail_lunwen.columns -%} {% if loop.index>1 -%} | |
<th>{{ item }}</th> | |
{%- endif %} {%- endfor %} | |
</tr> | |
</thead> | |
<tbody> | |
{% for item in detail_lunwen.values -%} | |
<tr> | |
{% for it in item -%} {% if loop.index>1 %} | |
<td>{{ it }}</td> | |
{% endif %} {%- endfor %} | |
</tr> | |
{%- endfor %} | |
</tbody> | |
</table> | |
<h2>竞赛加分</h2> | |
<table class="table table-striped table-hover table-condensed"> | |
<thead> | |
<tr> | |
{% for item in detail_jingsai.columns -%} {% if loop.index>1 %} | |
<th>{{ item }}</th> | |
{% endif %} {%- endfor %} | |
</tr> | |
</thead> | |
<tbody> | |
{% for item in detail_jingsai.values -%} | |
<tr> | |
{% for it in item -%} {% if loop.index>1 %} | |
<td>{{ it }}</td> | |
{% endif %} {%- endfor %} | |
</tr> | |
{%- endfor %} | |
</tbody> | |
</table> | |
<h2>德育成绩</h2> | |
<table class="table table-striped table-hover table-condensed"> | |
<thead> | |
<tr> | |
<th>Key</th> | |
<th>Value</th> | |
</tr> | |
</thead> | |
<tbody> | |
{% for item in detail_deyu -%} {% if loop.index>2 %} | |
<tr> | |
<td>{{ item[0] }}</td> | |
<td>{{ item[1] | int }}</td> | |
</tr> | |
{% endif %} {%- endfor %} | |
</tbody> | |
</table> | |
<p>注:“班委”为2分,其余各项为1分。</p> | |
<h2>问题反馈</h2> | |
<p>以上数据如有问题,<b>请于10月7日晚8点之前回复此邮件并抄送给金导<a href="mailto:lfjin@fudan.edu.cn">lfjin@fudan.edu.cn</a></b></p> | |
<p class="align-right">2016届硕士班 班委</p> | |
<p class="align-right">2017.10.6</p> | |
</div> | |
</td> | |
</tr> | |
</table> | |
</td> | |
</tr> | |
<!-- END MAIN CONTENT AREA --> | |
</table> | |
<!-- END CENTERED WHITE CONTAINER --> | |
</div> | |
</td> | |
<td> </td> | |
</tr> | |
</table> | |
</body> | |
</html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment