RSWQueueBot Take Control E-Mail Processor

This python program connects to an email account via IMAP and searches for emails whose subject contains the keyword Take Control.
It then Takes the Subject and Body and then places it into a SQL database called RSWQUEUEBOTDB into a table called Email_Event.

The Database columns must contain ID,subject,description and processed

Once an email is processed into the databsae, it is then automatically deleted from the email account.

This runs every 15 seconds in a loop.


Required SQL statements to be ran:

CREATE DATABASE RSWQueueBotDB;

CREATE TABLE Email_Event (
    EmailID INT IDENTITY(1,1) PRIMARY KEY,
    Subject NVARCHAR(255),
    Body NVARCHAR(MAX)
);

ALTER TABLE Email_Event
ADD processed BIT DEFAULT 0 NOT NULL;

Here is the main python code for the program:

# -*- coding: utf-8 -*-
"""
Created on Fri May  3 23:37:00 2024
 
@author: jcorona
"""
 
#This function will not run in a python schedule, I spent a large amount of time trying to figure out why and instead gave up and decided to just run it in a loop. ;'
 
import imaplib
import email
from email.header import decode_header
import pyodbc
import time
 
def process_emails():
    # Database setup
    server = 'localhost\\liberty'  # Note double backslashes for escaping
    database = 'RswQueueBotDB'
    username = 'Sa'
    password = 'Rsw02p@ss'
    cnxn = pyodbc.connect(f'DRIVER={{SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}')
    cursor = cnxn.cursor()
 
    # Email settings
    imap_url = "imap.gmail.com"
    email_username = "rswqueuebot@gmail.com"
    email_password = "mnejrhrlqdkdjwqo"
    keyword = "Take Control"
 
    mail = imaplib.IMAP4_SSL(imap_url)
    mail.login(email_username, email_password)
    mail.select('inbox')
 
#Searches all unread messages for the keyword, in this case, "take control"
    status, messages = mail.search(None, f'(UNSEEN SUBJECT "{keyword}")')
    messages = messages[0].split()
 
    for msg_num in messages:
        res, msg_data = mail.fetch(msg_num, '(RFC822)')
        for response in msg_data:
            if isinstance(response, tuple):
                msg = email.message_from_bytes(response[1])
                subject = decode_header(msg['subject'])[0][0]
                if isinstance(subject, bytes):
                    subject = subject.decode()
                print('Subject:', subject)
 
                body = ""
                if msg.is_multipart():
                    for part in msg.walk():
                        if part.get_content_type() == "text/plain":
                            body = part.get_payload(decode=True).decode()
                            break
                else:
                    body = msg.get_payload(decode=True).decode()
 
                print('Body:', body)
                cursor.execute('INSERT INTO Email_Event (Subject, Body, processed) VALUES (?, ?, 0)', (subject, body))
                cnxn.commit()
 
                # Correctly use the message number to mark the email as deleted
                mail.store(msg_num, '+FLAGS', '\\Deleted')
 
    mail.expunge()  # Permanently remove emails marked as deleted
    mail.close()
    mail.logout()
    cnxn.close()
 
# Run the function every minute indefinitely
while True:
    process_emails()
    time.sleep(15)  # Wait for 15 seconds before running the function again