[Python] Work Email generator and verification of the existing employees

I am currently working on creating a code which will be used to generate emails of the employees that are working in the selected organization and after that the code will verify if the emails are present in the real life or they are vague.

Current data that I have with myself.

1. Person's First Name
2. Person's Middle Name
3. Person's Last Name
4. Domain Name of the company
5. LinkedIn profile of the employee (if he is available on the LinkedIn)

How does a Email generation/finding part will work The code will use multiple combinations to create the email and after that it will do the verification on every email to check if the email is active, relevant, exists or is it fake and vague.

Some of the combinations will be like f = First name M = Middle Name L = Last name

1. f.l@companydomain
2. Intial letter of f.l@companydomain
3. l.f@companydomain

Above mentioned combinations are just few examples of it

How the code will verify the email if those are real or not The code will do the verification with using SMTP to check if the mail is active or not in the recent time.

The code will also do the google search to check if the created combinations of the email exist on the internet and how frequent and often they are available on the internet.

The final Rating system The code will rate every from 1-5. 1 being totally useless and 5 being the most appropriate.

Problem statements that I am facing in the code

1. 
   The name that I have collected have middle name in their name which is not relevant neither useful in the code that I am trying to make. So what are the ways that can be useful to take the first and second name only out of it. Example condition Person Name I have in database- Graham F. Sutherland Person Email I want the code to create- Graham.sutherland@xyz.com
   
   
2. 
   In this code I don't know how can let the code create a combination in which the First initial of the person name can only be taken and mixed with the last name of person or the initial letter of the last name and mixed with with first name of the person
   

Practical Examples First Name - Alex Last Name - Bonnet Email I want - A.bonnet@xyz.com or Alex.b@xyz.com

1. How to make the code that will verify these emails using the STMP to check if the email-id had been active or not coz every time i try to run the code. It gives me the rating of 3 only not above that not below that.

Below is the code that I have made so far (The code has been programmed using the Python but as I am a newbie I didn't know the right way to post it.

I hope you guys could help me with that

import mysql.connector
import re
import requests
from googlesearch import search
import smtplib
import dns.resolver


def connect_db():
try:
conn = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="usa"
)
return conn
except mysql.connector.Error as err:
print("Error:", err)
exit()


def fetch_data():
conn = connect_db()
cursor = conn.cursor()
cursor.execute("""
SELECT k.ID, k.company_name, k.Person_Name, k.Designation, u.ID AS Company_ID, u.Company_Website
FROM Key_people_info k
JOIN usa_companies_final u ON k.company_name = u.NAME
""")
data = cursor.fetchall()
cursor.close()
conn.close()
return data


def extract_domain(website_url):
match = re.search(r"https?://(?:www\.)?([^/]+)", website_url)
if match:
return match.group(1)
return None


def generate_possible_emails(name, domain):
first_name, last_name = name.lower().split(' ', 1)
emails = [
f"{first_name}.{last_name}@{domain}",
f"{first_name}{last_name}@{domain}",
f"{first_name}@{domain}",
f"{first_name[0]}{last_name}@{domain}",
f"{first_name[0]}.{last_name}@{domain}"
]
return emails


def check_email_exists(email):
domain = email.split('@')[-1]
try:
mx_records = dns.resolver.resolve(domain, 'MX')
mx_record = str(mx_records[0].exchange)

server = smtplib.SMTP()
server.set_debuglevel(0)
server.connect(mx_record)
server.helo(server.local_hostname)
server.mail('me@domain.com')
code, message = server.rcpt(email)
server.quit()

return code == 250
except Exception as e:
return False


def search_google_for_info(name, company, designation):
query = f"{name} {designation} {company} site:linkedin.com"
search_results = list(search(query, num_results=5))

linkedin_profile = None
linkedin_name = None

for url in search_results:
if 'linkedin.com/in/' in url:
linkedin_profile = url
linkedin_name = url.split('/')[-1].split('?')[0]
break

email_query = f"{name} {company} email"
search_results = list(search(email_query, num_results=10))

emails_found = set()
for url in search_results:
try:
response = requests.get(url, timeout=5)
if response.status_code == 200:
found_emails = re.findall(r"[\w\.-]+@[\w\.-]+", response.text)
emails_found.update(found_emails)
except:
continue

return linkedin_profile, linkedin_name, emails_found


def rate_email(email, smtp_status, found_in_google):
if smtp_status:
return 5
elif email in found_in_google:
return 4
else:
return 3


def process_and_store():
data = fetch_data()
results = []

for row in data:
person_id, company_name, person_name, designation, company_id, website = row
print(f"\nProcessing: {person_name} ({designation}) at {company_name}")

domain = extract_domain(website)
if not domain:
print("Could not extract domain.")
continue

linkedin_profile, linkedin_name, emails_found_in_google = search_google_for_info(person_name, company_name, designation)

possible_emails = generate_possible_emails(person_name, domain)
best_email = None
best_rating = 0

for email in possible_emails:
smtp_valid = check_email_exists(email)
rating = rate_email(email, smtp_valid, emails_found_in_google)
print(f"Checked {email}: SMTP={smtp_valid}, In Google={email in emails_found_in_google}, Rating={rating}")
if rating > best_rating:
best_email = email
best_rating = rating

if best_email:
results.append((company_id, company_name, person_name, designation, best_email, best_rating, linkedin_name, linkedin_profile))

insert_results(results)


def insert_results(results):
conn = connect_db()
cursor = conn.cursor()
for row in results:
cursor.execute("""
INSERT INTO usa_top_companies_key_people_email
(Company_ID, Company_Name, Person_Name, Designation, Email_Id, Rating, LinkedIn_Name, LinkedIn_Profile)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
Email_Id = VALUES(Email_Id),
Rating = VALUES(Rating),
LinkedIn_Name = VALUES(LinkedIn_Name),
LinkedIn_Profile = VALUES(LinkedIn_Profile)
""", row)
conn.commit()
cursor.close()
conn.close()


if __name__ == "__main__":
process_and_store()

Continue reading...