python Outlook 发送邮件内容包含图片和表格(设置Chrome不显示，添加retry)
帅煌
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.common.by import By
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.wait import WebDriverWait
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
import os
import base64
import smtplib
import psycopg2
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from tabulate import tabulate
import sys
from loguru import logger
from tenacity import retry
from tenacity.stop import stop_after_attempt
class logs:
def __init__(self):
logger.add('sendemail.log',
format="{time} | {level} |{message}", level="debug")
class GetData:
def __init__(self, buckerid):
self.xpathmap = {23007: '//*[@id="panel-60"]', 1205: '//*[@id="panel-67"]',
20555: '//*[@id="panel-66"]', 20550: '//*[@id="panel-68"]'}
self.idhmap = {23007: 60, 1205: 67,
20555: 66, 20550: 68}
def getbucketData(self, bucket_id):
sql = f"""select COALESCE(b.name,a."Team"::text),a."PoweredOff VM",a."PoweredOn VM",a."Suspended VM",a."llvm",a."zombie",a."AvgCount",a."AvgCost",a."TotalCost" from(select w."Team", sum("PoweredOff VM") as "PoweredOff VM",sum("PoweredOn VM") as "PoweredOn VM",sum("Suspended VM") as "Suspended VM",sum("llvm") as "llvm",sum("zombie") as "zombie",sum("avgcount") as "AvgCount",sum("AvgCost") as "AvgCost",sum("TotalCost") as "TotalCost" from(
select u."Team",0 as "PoweredOff VM",0 as "PoweredOn VM",0 as "Suspended VM",0 as "llvm",0 as "zombie",0 as avgcount,u."AvgCost",u."TotalCost" from (select * from public.getvmcost(array['{bucket_id}'::integer],(select round(CAST(uu.date_part*1000 AS numeric),0)::int8 from(SELECT EXTRACT(epoch FROM (select NOW()-'90 day'::INTERVAL))) uu),(select round(CAST(uuu.date_part*1000 AS numeric),0)::int8 from(SELECT EXTRACT(epoch FROM NOW())) uuu)))u
union all
select t.*,0 as avgcost,0 as totalcost from(select * from public.getvmcount(array['{bucket_id}'::integer]))t)w
group by w."Team") a left join teamlist b on a."Team"=b.id order by a."TotalCost" desc"""
return DataBase().execute_sql(sql)
@retry(stop=stop_after_attempt(7))
def getDriver(self):
try:
chrome_options = Options()
chrome_options.add_argument("--window-size=1920,4600")
# 设置chrome浏览器无界面模式
chrome_options.add_argument('--headless')
driver = webdriver.Chrome(options=chrome_options)
driver.get(
'http://#####/d/w_we_9lMk/vm-state-and-cost?orgId=1&refresh=15m')
driver.maximize_window()
wait = WebDriverWait(driver, 60)
# wait.until(EC.presence_of_element_located((By.XPATH,'//*[@id="panel-10"]/div')))
wait.until(EC.presence_of_element_located(
(By.XPATH, '//*[@id="panel-10"]/div/div/div/plugin-component/panel-plugin-table/grafana-panel/div/div[2]/ng-transclude/div[1]/div[2]/table/tbody/tr[1]')))
div = driver.find_element_by_xpath(
'//*[@id="panel-10"]/div') # '//*[@id="panel-10"]/div'
return (driver, div)
except:
print("failed")
raise Exception
@retry(stop=stop_after_attempt(7))
def getBucketDriver(self, bucket_id):
try:
self.bucketlink = f'http://##/d/w_we_9lMk/vm-state-and-cost?orgId=1&refresh=15m&fullscreen&panelId={self.idhmap[bucket_id]}'
print(self.bucketlink)
chrome_options = Options()
if bucket_id == 20555 or bucket_id == 20550:
height = 400
else:
height = 700
chrome_options.add_argument(f"--window-size=1980,{height}")
# 设置chrome浏览器无界面模式
chrome_options.add_argument('--headless')
driver = webdriver.Chrome(options=chrome_options)
driver.get(self.bucketlink)
driver.maximize_window()
wait = WebDriverWait(driver, 60)
# wait.until(EC.presence_of_element_located((By.XPATH,'//*[@id="panel-10"]/div')))
wait.until(EC.presence_of_element_located(
(By.XPATH, f'{self.xpathmap[bucket_id]}/div/div/div/plugin-component/panel-plugin-table/grafana-panel/div/div[2]/ng-transclude/div[1]/div[2]/table/tbody/tr[1]')))
div = driver.find_element_by_xpath(f'{self.xpathmap[bucket_id]}')
return (driver, div)
except:
print("failed")
raise Exception
def getimage(self):
if os.path.exists('some_image.jpg'):
os.remove('some_image.jpg')
# chrome_options = Options()
# chrome_options.add_argument("--window-size=1920,4600")
# # 设置chrome浏览器无界面模式
# chrome_options.add_argument('--headless')
# driver = webdriver.Chrome(options=chrome_options)
# driver.get(
# 'http://##/d/w_we_9lMk/vm-state-and-cost?orgId=1&refresh=15m')
# # driver.get('http://##/d/jN1NVsbik/vsphere-live-data?orgId=1&refresh=15m&var-server=public&var-datacenters=23007&var-hosts=All&var-datastores=All&var-sample_time=2020-05-06T13:50:02.239407%2B08:00&var-ds_name=Cml01')
# driver.maximize_window()
# wait = WebDriverWait(driver, 60)
# # wait.until(EC.presence_of_element_located((By.XPATH,'//*[@id="panel-10"]/div')))
# wait.until(EC.presence_of_element_located(
# (By.XPATH, '//*[@id="panel-10"]/div/div/div/plugin-component/panel-plugin-table/grafana-panel/div/div[2]/ng-transclude/div[1]/div[2]/table/tbody/tr[1]')))
# div = driver.find_element_by_xpath('//*[@id="panel-10"]/div')
driver, div = self.getDriver()
imgdata = base64.b64decode(div.screenshot_as_base64)
filename = 'some_image.jpg' # I assume you have a way of picking unique filenames
with open(filename, 'wb') as f:
f.write(imgdata)
driver.close()
driver.quit()
def getbucketimage(self, bucket_id):
if os.path.exists(f'{bucket_id}.jpg'):
os.remove(f'{bucket_id}.jpg')
# chrome_options = Options()
# if bucket_id == 20555 or bucket_id == 20550:
# height=400
# else:
# height=700
# chrome_options.add_argument(f"--window-size=1980,{height}")
# # 设置chrome浏览器无界面模式
# chrome_options.add_argument('--headless')
# driver = webdriver.Chrome(options=chrome_options)
# driver.get(self.bucketlink)
# # driver.get('http://##/d/jN1NVsbik/vsphere-live-data?orgId=1&refresh=15m&var-server=public&var-datacenters=23007&var-hosts=All&var-datastores=All&var-sample_time=2020-05-06T13:50:02.239407%2B08:00&var-ds_name=Cml01')
# driver.maximize_window()
# wait = WebDriverWait(driver, 60)
# # wait.until(EC.presence_of_element_located((By.XPATH,'//*[@id="panel-10"]/div')))
# wait.until(EC.presence_of_element_located(
# (By.XPATH, f'{self.xpathmap[bucket_id]}/div/div/div/plugin-component/panel-plugin-table/grafana-panel/div/div[2]/ng-transclude/div[1]/div[2]/table/tbody/tr[1]')))
# div = driver.find_element_by_xpath(f'{self.xpathmap[bucket_id]}')
driver, div = self.getBucketDriver(bucket_id)
imgdata = base64.b64decode(div.screenshot_as_base64)
# I assume you have a way of picking unique filenames
filename = f'{bucket_id}.jpg'
with open(filename, 'wb') as f:
f.write(imgdata)
driver.close()
driver.quit()
class DataBase:
def __int__(self):
pass
def createinstance(self):
self.con = psycopg2.connect(dbname='##', user='##', password='##', host='##',
port=5432)
self.cursor = self.con.cursor()
@logger.catch
def execute_sql(self, sql):
self.createinstance()
self.cursor.execute(sql)
result = self.cursor.fetchall()
self.cursor.close()
self.con.close()
return result
class Method:
def __init__(self):
pass
def convertStorage(self, num):
for x in ['', 'K', 'M']:
if num < 1000:
return "%3.2f%s" % (num, x)
num /= 1024
class SendEmail:
def __init__(self):
pass
@logger.catch
def sendEmail(self, to_addr_list, cc_addr_list, Subject, message):
s = smtplib.SMTP(host='##.com', port=25)
s.sendmail('##.com',
to_addr_list+cc_addr_list, message)
class SendByCatalog:
def __init__(self, bucketid):
# self.manager = ['xchen32@ra.rockwell.com','scli@ra.rockwell.com','dma@ra.rockwell.com']
self.manager = ['##.com']
self.testbucket = ['##.com']
self.logixbucket = ['##.com']
self.servicebucket = ['##.com']
self.viewbucket = ['##.com']
self.bucketmap = {"1": "LL&Test Team", "2": "ACS_Logix Team",
"3": "FTView Team", "4": "Service Team"}
self.emailtomap = {23007: self.logixbucket, 1205: self.servicebucket,
20555: self.testbucket, 20550: self.viewbucket}
self.bucketid = bucketid
@logger.catch
def sendimage(self):
# Define these once; use them twice!
GetData(self.bucketid).getimage()
if os.path.exists('some_image.jpg') and os.path.getsize('some_image.jpg') > 30000:
strFrom = '##.com'
msgRoot = MIMEMultipart('related')
msgRoot['Subject'] = 'Virtual Machine State & Cost by Team Details'
msgRoot['From'] = strFrom
msgRoot['To'] = ",".join(self.manager)
msgRoot.preamble = 'This is a multi-part message in MIME format.'
# Encapsulate the plain and HTML versions of the message body in an
# 'alternative' part, so message agents can decide which they want to display.
msgAlternative = MIMEMultipart('alternative')
msgRoot.attach(msgAlternative)
msgText = MIMEText('This is the alternative plain text message.')
msgAlternative.attach(msgText)
# We reference the image in the IMG SRC attribute by the ID we give it below
msgText = MIMEText('''<b>Hi all managers</b><br><br>Here are <b>Virtual Machine State & Cost </b>details by Team<br><br>
<img src="cid:image1"><br><br>
You can view more VM cost details by sub-team under your bucket. E.g. DataCenters > View_Datacenter >…<br>
<a href=http://10.108.184.33/d/w_we_9lMk/vm-state-and-cost?orgId=1&refresh=15m>VM Dashboard link</a>
<br>Notes: Please contact to VMs admin (Ellis Chen, Hongwei Li) for any question.<br><br>Thanks''', 'html')
msgAlternative.attach(msgText)
# This example assumes the image is in the current directory
fp = open('some_image.jpg', 'rb')
msgImage = MIMEImage(fp.read())
fp.close()
# Define the image's ID as referenced above
msgImage.add_header('Content-ID', '<image1>')
msgRoot.attach(msgImage)
SendEmail().sendEmail(to_addr_list=self.manager,
cc_addr_list=[''],
Subject='Virtual Machine State & Cost by Team Details',
message=msgRoot.as_string())
@logger.catch
def sendbucketimage(self):
# Define these once; use them twice!
GetData(self.bucketid).getbucketimage(self.bucketid)
if os.path.exists(f'{self.bucketid}.jpg') and os.path.getsize(f'{self.bucketid}.jpg') > 30000:
strFrom = '##.com'
msgRoot = MIMEMultipart('related')
msgRoot['Subject'] = 'Virtual Machine State & Cost by Team Details'
msgRoot['From'] = strFrom
msgRoot['To'] = ",".join(self.emailtomap[self.bucketid])
msgRoot['CC'] = """##com,##.com"""
msgRoot.preamble = 'This is a multi-part message in MIME format.'
# Encapsulate the plain and HTML versions of the message body in an
# 'alternative' part, so message agents can decide which they want to display.
msgAlternative = MIMEMultipart('alternative')
msgRoot.attach(msgAlternative)
msgText = MIMEText('This is the alternative plain text message.')
msgAlternative.attach(msgText)
# We reference the image in the IMG SRC attribute by the ID we give it below
msgText = MIMEText('''<b>Hi </b><br><br>Here are <b>Virtual Machine State & Cost </b>details by Team<br><br>
<img src="cid:image1"><br><br>
You can view more VM cost details by sub-team under your bucket. E.g. DataCenters > View_Datacenter >…<br>
<a href=http://10.108.184.33/d/w_we_9lMk/vm-state-and-cost?orgId=1&refresh=15m>VM Dashboard link</a>
<br>Notes: Please contact to VMs admin (Ellis Chen, Hongwei Li) for any question.<br><br>Thanks''', 'html')
msgAlternative.attach(msgText)
# This example assumes the image is in the current directory
fp = open(f'{self.bucketid}.jpg', 'rb')
msgImage = MIMEImage(fp.read())
fp.close()
# Define the image's ID as referenced above
msgImage.add_header('Content-ID', '<image1>')
msgRoot.attach(msgImage)
SendEmail().sendEmail(to_addr_list=self.emailtomap[self.bucketid],
cc_addr_list=[
'##.com', '##.com'],
Subject='Virtual Machine State & Cost by Team Details',
message=msgRoot.as_string())
@logger.catch
def sendtable(self, bucketid):
result = GetData().getbucketData(bucketid)
data = []
data.append({"Team": "Team", "PoweredOff VM": "PoweredOff VM", "PoweredOn VM": "PoweredOn VM", "Suspended VM": "Suspended VM",
"llvm": "llvm", "zombie": "zombie", "AvgCount": "AvgCount", "AvgCost": "AvgCost", "TotalCost": "TotalCost"})
for item in result:
data.append({"Team": item[0], "PoweredOff VM": item[1], "PoweredOn VM": item[2], "Suspended VM": item[3], "llvm": item[4],
"zombie": item[5], "AvgCount": item[6], "AvgCost": Method().convertStorage(item[7]), "TotalCost": Method().convertStorage(item[8])})
data[1]["Team"] = self.bucketmap[data[1]["Team"]]
text = """
Hello.
Here are Virtual Machine State & Cost details by Team:
{table}
Best Regards,
"""
html = """
<html><body><p>Hello.</p>
<p>Here are <b>Virtual Machine State & Cost </b>details by Team</p>
{table}
<p>Best Regards,</p>
</body></html>
"""
text = text.format(table=tabulate(data, headers="firstrow",
tablefmt="grid", colalign=("center",), numalign="center"))
html = html.format(table=tabulate(data, headers="firstrow",
tablefmt="html", colalign=("center",), numalign="center"))
message = MIMEMultipart(
"alternative", None, [MIMEText(text), MIMEText(html, 'html')])
strFrom = '##.com'
message['Subject'] = 'Virtual Machine State & Cost by Team Details'
message['From'] = strFrom
message['To'] = ",".join(self.emailtomap[bucketid])
SendEmail().sendEmail(to_addr_list=self.emailtomap[bucketid],
cc_addr_list=[],
Subject='Virtual Machine State & Cost by Team Details',
message=message.as_string())
if __name__ == "__main__":
if sys.argv.__len__() > 1:
if sys.argv[1] == "manager":
# logs()
SendByCatalog(1).sendimage()
else:
for item in [1205, 23007, 20555, 20550]:
SendByCatalog(item).sendbucketimage()
类似资料：