Создание парсера электронной почты с помощью Python и SQL

Повысьте продуктивность за счет автоматического извлечения данных из писем

А что это? Электронный ... парсер?

Вам может быть интересно, что такое анализатор электронной почты и зачем он вам может понадобиться.

Короче говоря, парсер электронной почты - это программа, которая ищет и извлекает данные из входящих писем и вложений. Что еще более важно, синтаксический анализатор электронной почты использует условную обработку для извлечения конкретных данных, которые важны для вас.

Так почему это важно? Если вы когда-либо работали на обычной офисной работе, вы, вероятно, хорошо знакомы с отчетами и, как следствие, с копированием строк текста из Microsoft Outlook в Excel или Word.

Возможно, вы даже будете делать один и тот же отчет неделю за неделей. Добавьте форматирование и проверку орфографии, и это приведет к огромной трате времени, когда вы сможете сосредоточиться на более важных задачах.

Хорошая новость в том, что вы можете автоматизировать большую часть этого процесса с помощью Python и SQL.

В этом посте я расскажу, как открывать электронные письма Outlook с помощью Python и извлекать основной текст в виде HTML. Затем я расскажу, как это проанализировать в Python и как загрузить окончательные данные в базу данных SQL. Оттуда вы можете записать эти данные в Excel или преобразовать их в фрейм данных Pandas.

Начиная

Здесь мы будем использовать несколько ключевых библиотек Python, а именно os, sqlite3 и pywin32.

Для начала нам нужно решить, что мы хотим извлечь из электронных писем. Например, предположим, что у нас есть набор электронных писем, каждое из которых содержит список таких новостных статей:

Допустим, мы хотим извлечь заголовок каждого пункта списка, который включает заголовок, публикацию, медиа-платформы и URL-ссылки. Короче говоря, мы хотим взять весь заголовок каждого пункта списка, а затем разбить его на четыре разные части.

Наш псевдокод должен выглядеть примерно так:

1. Create list of emails that we want to parse
2. Open first email
3. Iterate over each bullet point
4. Extract data from bullet point
5. Upload data from bullet point to a database
6. Repeat until all data is parsed, then move to next email

Создание базы данных SQL

Прежде чем анализировать наши электронные письма, мы сначала хотим настроить базу данных SQL с помощью Python. Мы сделаем это, установив соединение с базой данных SQLite с помощью объекта соединения, который мы назовем db.

# Create & connect to database
db = sqlite3.connect("emails.db")

Если она еще не существует, будет создана новая база данных как emails.db. Затем мы можем создать таблицы в нашей базе данных, в которые наш анализатор электронной почты сможет писать позже.

# Create empty tables
db.execute("""
CREATE TABLE IF NOT EXISTS "articles" (
"id" INTEGER,
"title" TEXT UNIQUE,
"publication" TEXT,
PRIMARY KEY("id" AUTOINCREMENT))
""")
db.execute("""
CREATE TABLE IF NOT EXISTS "links" (
"article_id"    INTEGER,
"link0" TEXT,
"link1" TEXT,
"link2" TEXT,
PRIMARY KEY("article_id"))
""")
db.execute("""
CREATE TABLE IF NOT EXISTS "platforms" (
"article_id"    INTEGER,
"platform0" TEXT,
"platform1" TEXT,
"platform2" TEXT,
PRIMARY KEY("article_id"))
""")

По сути, мы создаем три таблицы, где наша основная таблица - «статьи», которая имеет отношение «один ко многим» с «платформами» и «ссылками». Другими словами, это отражает то, как одна статья может иметь много разных платформ и ссылок.

Доступ к вашим письмам в Python

Вам нужно переместить электронные письма, которые вы хотите проанализировать, из Outlook в папку. Самый простой способ сделать это - перетащить.

Затем создайте переменную, в которой хранится путь к папке с вашими электронными письмами. Вы можете сделать это вручную, например. folder_path = r‘C:\Users\Username\EmailFolder’ или с tkinter и os, которые вызовут приглашение файлового проводника для выбора папки.

# Create an folder input dialog with tkinter
folder_path = os.path.normpath(askdirectory(title='Select Folder'))

Здесь мы используем приглашение для ввода файла, созданное с помощью tkinter, чтобы сохранить путь к нашей папке, а затем нормализуем путь с помощью os, чтобы удалить любые лишние разделители.

Затем нам нужно получить заголовки пути для каждого электронного письма. Мы можем сделать это с помощью os.listdir(), который дает список всех файлов в указанном каталоге.

# Initialise & populate list of emails
email_list = 
[file for file in os.listdir(folder_path) if file.endswith(".msg")]

Это сохранит имя файла каждого электронного письма в списке, к которому мы сможем получить доступ позже.

Подключение Outlook к Python

Затем вам нужно создать объект, который позволит нам управлять Outlook из Python. Это обеспечивается с помощью pywin32 библиотеки, которая помогает подключать Python к Outlook через Microsoft API обмена сообщениями Outlook (MAPI).

# Connect to Outlook with MAPI
outlook = win32com.client.Dispatch(“Outlook.Application”)
                  .GetNamespace(“MAPI”)

После этого мы можем начать открывать каждый элемент как объект HTML и использовать регулярные выражения, то есть Regex, для извлечения основного текста каждого электронного письма.

Хотя общепринятая мудрость гласит, что вы не должны использовать Regex для синтаксического анализа HTML, нас это не беспокоит, поскольку мы хотим извлечь только очень конкретные фрагменты текста из стандартного формата электронной почты (некоторые коммерческие парсеры электронной почты, такие как Parseur В значительной степени построены на основе Regex).

С этого момента Regex можно использовать для сужения конкретных данных, которые вы хотите извлечь.

# Iterate through every email
for i, _ in enumerate(email_list):
   # Create variable storing info from current email being parsed
   msg = outlook.OpenSharedItem(os.path.join(folder_path,
   email_list[i]))
   # Search email HTML for body text
   regex = re.search(r"<body([\s\S]*)</body>", msg.HTMLBody)
   body = regex.group()

Разбор HTML-кода электронной почты с помощью Python

Вот как первый пункт нашего электронного письма может выглядеть как HTML:

Итак, мы видим, что здесь есть несколько ключевых характеристик, а именно то, что наши данные существуют в виде маркированного списка или li class=MsoListParagraph. Мы можем использовать Regex для извлечения каждого маркера.

# Search email body text for unique entries
pattern = r"li class=MsoListParagraph([\s\S]*?)</li>"
results = re.findall(pattern, body)

Каждый маркер извлекается как строка, и каждая строка сохраняется в списке. Наша первая точка маркера должна выглядеть примерно так с Regex:

Чтобы получить заголовок и публикацию, мы снова можем использовать Regex. На этот раз мы также будем использовать вызов html.unescape() в нашем тексте, чтобы помочь преобразовать наш HTML в строку, например → - (тире в Юникоде).

regex = re.search(r"[^<>]+(?=\(|sans-serif’>([\s\S]*?)</span>)", header)
# HTML unescape to get remove remaining HTML
title_pub = html.unescape(regex.group())

Отсюда это так же просто, как разделить наш текст. Мы можем использовать split_list = title_pub.split("–"), чтобы получить список: ["New Arrival: Dell G Series Gaming Computers", "Tech4tea"].

Затем мы можем удалить любые лишние пробелы и сохранить каждый элемент как переменную.

title = split_list[0].strip()
publication = split_list[1].strip()

Два вниз!

Чтобы получить наши медиа-платформы, мы воспользуемся более простым способом.

# List of publications to check for
platform_list = ["Online", "Facebook", "Instagram", "Twitter", "LinkedIn", "Youtube"]
# Create empty list to store publications
platform = []
# Iterate and check for each item in my first list
for p in platform_list:
   if p in header:
      platform.append(p)

Это даст нам список публикаций: ["Online", "Facebook", "LinkedIn"]

Теперь о URL-адресах:

# Find all links using regex
links = re.findall(r"<a href=\”([\s\S]*?)\”>", header)

Это даст нам символы, выделенные зеленым ниже:

Наши данные должны выглядеть примерно так:

Title: New Arrival: Dell G Series Gaming Computers
Publication: Tech4tea
Platform: [‘Online’, ‘Facebook’, ‘LinkedIn’]
Links: [‘http://tech4tea.com/blog/2020/06/26/new-arrival-dell-g-series-gaming-computers-monitors-keyboards/', ‘https://business.facebook.com/gotech4tea/posts/4598490146843826', ‘https://www.linkedin.com/feed/update/urn:li:activity:6682511823100542976/']

Загрузка данных в базу данных SQL

Последним шагом в этом процессе является загрузка каждого фрагмента данных в нашу базу данных SQL.

Начнем с загрузки данных о заголовке и публикации. Это можно сделать с помощью следующего кода:

# Insert title & pub by substituting values into each ? placeholder
db.execute("INSERT INTO articles (title, publication) 
VALUES (?, ?)", (title, publication))

Загрузить наши ссылки и платформы немного сложнее. Сначала мы скопируем наш основной идентификатор из нашей основной таблицы, затем переберем каждую платформу и свяжем индивидуально.

# Get article id and copy to platforms & links tables
article_id = db.execute(“SELECT id FROM articles WHERE title = ?”, (title,))
for item in article_id:
   _id = item[0]
for i, _ in enumerate(platform):
   db.execute(f”UPDATE platforms SET platform{i} = ? WHERE    
   article_id = ?”, (platform[i], _id))
for i, _ in enumerate(links):
   db.execute(f”UPDATE links SET link{i} = ? WHERE article_id = ?”, 
   (links[i], _id))
# Commit changes
db.commit()

Последний шаг здесь - зафиксировать все эти изменения в базе данных. На этом наш парсер электронной почты готов! Если хотите, вы можете использовать что-то вроде DB Browser, чтобы проверить, что содержимое вашей базы данных было успешно обновлено.

Если вам это нужно, я загрузил полный код для этого на свой сайт и Github.