ETL Email Notification

Email Check With Selenium

17-01-2021

Pawan Rai
Goal To Achieve

Checking the ETL Email and Verify if Any Emails Are Missing

On the Regular basis We Receive Eighteen Email

From Which 9 Email For Stats of ETL and Other Nine Emails are with Platform Name and Status is Passed or Fail

But Main Concern Starts Where we Do not Received Email For a ETL for Two or Three Day

There has to be a Person Who need to Sit and Make and Test Which ETL Emails are Missing.

It Possible but think a Situation Where the ETL Email Across 100 Email a Day

Then Manual Checking for the Email is not Possible, But Selenium Program Can handle This Situation with the help of Python.

To Achieve This I need To login company’s Email to chrome.
How to connect Gmail to Company Email.
To Received and Send Emails Directly From Gmail
  1. Open Any Email Account Which You want to Connect
  2. Click on Setting Option on Top Right corner of the Screen

  1. Click on >> See All Settings

  1. Go to >> Account and Import

  1. Find Option Check Email From Other Account

  1. Select >> Add a mail account
  2. A Yellow Pop-up Window Appear Like this

  1. Provide Email Id Here >> Next >>

  1. Select POP3 for Importing Email >> Next >>

  1. Provide the Password and select Port 995 for SSL and 993 for non-SSL

https://support.google.com/mail/bin/answer.py?ctx=gmail&answer=21289&hl=en&authuser=0

And Click Add Account and after This Look Like this

After this It will take all Email From Server and Make a Copy on Gmail

This Will take a Hour or two to Make the Copy of all Emails

Note : You May Face a Delay in Emails about 10 to 15 Mins

Email Look Like this On Gmail(Create a New Gmail Account For Testing Purpose & Safety Concerns)

So When I Received Emails from Company its time to Read Emails.

I Separated the 18 Email in Two Parts

  1. Stats Email
  2. ETL with Status
Reading Email with Selenium Python
  1. Requirements for Stats Email
  2. import pandas as pd
  3. from selenium import webdriver
  4. from time import sleep
  5. from selenium.webdriver.chrome.options import Options
  6. from selenium.webdriver.common.keys import Keys
  7. from selenium.webdriver import ActionChains
  8. import numpy as np
  9. from bs4 import BeautifulSoup

Step Second:

Login To Gmail With Selenium

  1. driver = webdriver.Chrome(‘./chromedriver’)
  2. driver.maximize_window()
  4. driver.get(r’https://accounts.google.com/signin/v2/identifier?continue=’+\
  5. ‘https%3A%2F%2Fmail.google.com%2Fmail%2F&service=mail&sacu=1&rip=1’+\
  6. ‘&flowName=GlifWebSignIn&flowEntry = ServiceLogin’)
  7. driver.implicitly_wait(15)
  9. loginBox = driver.find_element_by_xpath(‘//*[@id =”identifierId”]’)
  10. loginBox.send_keys(‘prairarl@gmail.com’)
  12. nextButton = driver.find_elements_by_xpath(‘//*[@id =”identifierNext”]’)
  13. nextButton[0].click()
  15. passWordBox = driver.find_element_by_xpath(
  16.     ‘//*[@id =”password”]/div[1]/div / div[1]/input’)
  17. passWordBox.send_keys(‘heroishere’)
  19. nextButton = driver.find_elements_by_xpath(‘//*[@id =”passwordNext”]’)
  20. nextButton[0].click()
  22. sleep(5)

Third Step :

Apply Filter to Gmail

  1. filter_url = driver.get(‘https://mail.google.com/mail/u/0/#search/is%3Aunread+from%3A(etl_notification%40sprighub.com)+Hi+There’)

Filter Say : Read All unread Email Come From elt_notification@sprighub.com and Starts with Hi There

Fourth Step:

Read Email has three Steps

  1. Email1 = driver.find_element_by_xpath(“/html/body/div[7]/div[3]/div/div[2]/div[1]/div[2]/div/div/div/div/div[2]/div/div[1]/div/div/div[6]/div[2]/div/table/tbody/tr[1]”).click()
  3. sleep(4)
  5. data1 = driver.find_element_by_xpath(‘/html/body/div[7]/div[3]/div/div[2]/div[1]/div[2]/div/div/div/div/div[2]/div/div[1]/div/div[2]/div/table/tr/td[1]/div[2]/div[2]/div/div[3]/div/div/div/div/div/div[1]’).get_attribute(‘outerHTML’)
  7. driver.back()
  8. Click on email inside Filter list
  9. Wait for load
  10. Read Email for required Data
  11. Then Click Back

Repeat This Steps for Every Email Which is needed to Fetched

Now Read the Extracted data
  1. Step First
  2. from bs4 import BeautifulSoup
  3. soup1 = BeautifulSoup(data1,’lxml’)
  4. soup2 = BeautifulSoup(data2, ‘lxml’)
  5. soup3 = BeautifulSoup(data3, ‘lxml’)
  6. soup4 = BeautifulSoup(data4, ‘lxml’)
  7. soup5 = BeautifulSoup(data5, ‘lxml’)
  8. soup6 = BeautifulSoup(Data6, ‘lxml’)
  9. soup7 = BeautifulSoup(Data7, ‘lxml’)
  10. soup8 = BeautifulSoup(Data8, ‘lxml’)
  11. soup9 = BeautifulSoup(Data9, ‘lxml’)
  12. Beautiful soup help us to read and modify html Table

Created Empty list to Store Data

  1. first = []
  2. second = []
  3. third = []
  4. fourth = []
  5. fifth = []
  6. sixth = []
  7. seventh = []
  8. eighth = []
  9. nineth = []
  10. Extract and Save Data in list
  11. for i in soup1.findAll(‘b’):
  12.     first.append(i.text)
  14. for i in soup2.findAll(‘b’):
  15.     second.append(i.text)
  17. for i in soup3.findAll(‘b’):
  18.     third.append(i.text)
  20. for i in soup4.findAll(‘b’):
  21.     fourth.append(i.text)
  23. for i in soup5.findAll(‘b’):
  24.     fifth.append(i.text)
  26. for i in soup6.findAll(‘b’):
  27.     sixth.append(i.text)
  29. for i in soup7.findAll(‘b’):
  30.     seventh.append(i.text)
  32. for i in soup8.findAll(‘b’):
  33.     eighth.append(i.text)
  35. for i in soup9.findAll(‘b’):
  36.     nineth.append(i.text)
  37. Prepare The DataFrame
  38. df =[[‘Started’, first[0], first[1]],
  39.     [‘Started’, second[0], second[1]],
  40.     [‘Started’, third[0], third[1]],
  41.     [‘Started’, fourth[0], fourth[1]],
  42.     [‘Started’, fifth[0], fifth[1]],
  43.     [‘Started’, sixth[0], sixth[1]],
  44.     [‘Started’, seventh[0], seventh[1]],
  45.     [‘Started’, eighth[0], eighth[1]],
  46.     [‘Started’, nineth[0], nineth[1]]]
  47. df1_Started = pd.DataFrame(df)
  48. df1_Started
  49. Dataframe Look Like this

How To Read ETL with Status
  1. Login Mail Same before
  2. Filter is Change here
  3. filter_url = driver.get(‘https://mail.google.com/mail/u/0/#search/is%3Aunread+from%3A(etl_notification%40sprighub.com)+Daily’)
  4. Read Email
  5. Email1 = driver.find_element_by_xpath(“/html/body/div[7]/div[3]/div/div[2]/div[1]/div[2]/div/div/div/div/div[2]/div/div[1]/div/div/div[6]/div[2]/div/table/tbody/tr[1]”).click()
  7. sleep(4)
  9. data1_etl_status = driver.find_element_by_xpath(‘/html/body/div[7]/div[3]/div/div[2]/div[1]/div[2]/div/div/div/div/div[2]/div/div[1]/div/div[3]/div/table/tr/td[1]/div[2]/div[2]/div/div[3]/div/div/div/div/div/div[1]/div[2]/div[3]/div[3]/div/div[2]’).get_attribute(‘outerHTML’)
  11. driver.back()

Same as Before but Path Has been Changed

Repeat the Step for Every email

Extract Data From above data1_etl_status

  1. tbl1 = pd.read_html(data1_etl_status)
  2. table4_df = tbl1[0]
  3. # table4_df.columns = [‘Name’, ‘Value’]
  4. platform = table4_df.iloc[2,1]
  5. client = table4_df.iloc[1,1]
  6. status = table4_df.iloc[5,1]
  7. FirstSave = [client,platform]
  8. print(client, ‘platform’,  platform, ‘is’, status)

For every Email we have to Do this Step

Create Two List one for Predefined list which email should have come and Other one is Fetched list Which Email come

  1. Predefined_list = [[‘meetup.com’,’simplifi’], [‘meetup.com’, ‘snapchat’], [‘meetup.com’,’Linkedin’], [‘meetup.com’, ‘cj_affiliate’], [‘meetup.com’, ‘bing’], [‘meetup.com’, ‘criteo’], [‘wdc.com’, ‘bing’],  [‘wdc.com’,’criteo’], [‘wdc.com’,’cj_affiliate’]]
  2. List_Fetched = [FirstSave, SecondSave, ThirdSave, FourthSave, FifthSave, SixthSave, SeventhSave, EighthSave, NinethSave]

Compare The both Dataframe for mismatch

  1. df1 = pd.merge(df_Predefined, df_List_Fetched, on=[‘Client’,’Platform’], how=’left’, indicator=’Exist’)
  2. df1[‘Exist’] = np.where(df1.Exist == ‘both’, True, False)
  3. new_df = df1
  4. df2 = new_df[new_df[‘Exist’] == False]

and Find Difference and Create Data frame with Difference

After Finding the Difference we need to send the Difference Via Email

Sending Both DF as inside the Email

  1. import smtplib, ssl
  2. from email.mime.text import MIMEText
  3. from email.mime.multipart import MIMEMultipart
  4. from pretty_html_table import build_table
  6. sender_email = “prai@sprighub.com”
  7. receiver_email = “pawanrai852@gmail.com”
  8. password = input(“Type your password and press enter:”)
  10. message = MIMEMultipart(“alternative”)
  11. message[“Subject”] = “ETL Email Status”
  12. message[“From”] = sender_email
  13. message[“To”] = receiver_email
  15. # Create the plain-text and HTML version of your message
  16. text = “””\
  17. Hi
  18. Email Not Received For Platform Mention Below,
  19. “””
  20. html = “””\
  21. <html>
  22.   <body>
  23.            <p>Hi!<br>
  24.            <b>Email Not Reveived(If You Not See Any Table, All Email is Recevied):</b><br>
  25.            {0}
  26.            <br><b>Status of the Started Email:</b><br>
  27.            {1}
  28.            <br>
  29.            Thanks & Regards,
  30.            </p>
  31.   </body>
  32. </html>
  33. “”” .format(build_table(df2, ‘blue_light’), (build_table(df1_Started, ‘blue_light’)))
  35. # Turn these into plain/html MIMEText objects
  36. part1 = MIMEText(text, “plain”)
  37. part2 = MIMEText(html, “html”)
  39. # Add HTML/plain-text parts to MIMEMultipart message
  40. # The email client will try to render the last part first
  41. message.attach(part1)
  42. message.attach(part2)
  44. # Create secure connection with server and send email
  45. context = ssl.create_default_context()
  46. with smtplib.SMTP_SSL(“mail.sprighub.com”, 465, context=context) as server:
  47.     server.login(sender_email, password)
  48.     server.sendmail(
  49.         sender_email, receiver_email, message.as_string()
  50.     )

Email Look Like This

If there is Difference in Expected Email and Received Emails then there are two table but Today we Did not miss any Emails so only one table is their Which is Stats Table.