update_bad_emails.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\samuel.taylor\\OneDrive - Green Dot Public Schools\\Desktop\\Git_Directory\\CP\\CustomPlanet_Work\\Email_Scraper\\Scraping_Emails\\modules\\db_operations_aws.py:24: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df_SQL = pd.read_sql_query(query, con=self.conn)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "Prior Unique Subject Line: Local Supplier for Baseball\n",
      "Prior Unique Subject Line You have 573 email responses to the specified search criteria Local Supplier for Baseball in the outbox with a filter date of 03/04/2024\n",
      "Prior Unique Subject Line You have 138 email responses to the specified search criteria Delivery Status Notification in the inbox with a filter date of 03/04/2024\n",
      "Updating bad emails table with 130 emails - subject line Local Supplier for Baseball\n",
      "Tunnel local bind port: 52713\n",
      "Tunnel is active: True\n",
      "Email james.berry@lathropschools.com already exists\n",
      "Email crose@usd289.org already exists\n",
      "Email lprice@sherwoodk12.net already exists\n",
      "Email daulbert@odessar7.net already exists\n",
      "Email weersk@usd416.org already exists\n",
      "Email sseratte@usd497.org already exists\n",
      "Email Lice@usd497.org already exists\n",
      "Email MHabermehl@holdenschools.org already exists\n",
      "Email tom.doherty@grandviewc4.net already exists\n",
      "Email blazevicd@usd204.net already exists\n",
      "Email cpope@winston.k12.mo.us already exists\n",
      "Email barthur@stewartsville.k12.mo.us already exists\n",
      "Email kackerman@richhill.k12.mo.us already exists\n",
      "Email jake.duvall@butler.k12.mo.us already exists\n",
      "Email chusak@ballard.k12.ia.us already exists\n",
      "Email vince.armilio@lps53.org already exists\n",
      "Email edoane@thelogisticsstore.com already exists\n",
      "Email mbaker@bsd124.org already exists\n",
      "Email John.Gage@lsr7.net already exists\n",
      "Email matt.blackburn@bisd.net already exists\n",
      "Email roger.lower@lps53.org already exists\n",
      "Email gogle@gvr5.net already exists\n",
      "Email jason.pleacher@raytownschools.org already exists\n",
      "Email mdryer@ndsion.edu already exists\n",
      "Email KKleinmeyer@liberty.k12.mo.us already exists\n",
      "Email rmendez@sjakeepingfaith.org already exists\n",
      "Email brandithomsen@ogr6.org already exists\n",
      "Email bkindle@usd289.org already exists\n",
      "Email tayler.ross@sherwoodk12.net already exists\n",
      "First except block An error occured: (1366, \"Incorrect string value: '\\\\xEF\\\\xBF\\\\xBD\\\\xEF\\\\xBF\\\\xBD...' for column 'emailaddress' at row 1\")\n",
      "Email dcanon@usd456.org already exists\n",
      "Email wendi_jones@isdschools.org already exists\n",
      "Email shunter@gvr5.net already exists\n",
      "Email lclark@bsd124.org already exists\n",
      "Email sgossey@fortosage.net already exists\n",
      "Email sarah.baker@harrisonvilleschools.org already exists\n",
      "Email dana.green@grandviewc4.net already exists\n",
      "Email Christi.Adams@usd453.org already exists\n",
      "Email LeahCogswell@smsd.org already exists\n",
      "Email mwillis@usd289.org already exists\n",
      "Email Todd@usd230.org already exists\n",
      "Email melewis@sherwoodk12.net already exists\n",
      "Email CDickes@piperschools.com already exists\n",
      "Email kostk@usd290.org already exists\n",
      "Email keatonc@usd416.org already exists\n",
      "Email kbranson@usd497.org already exists\n",
      "Email mary.cooper@usd497.org already exists\n",
      "Email timothy.leffert@usd497.org already exists\n",
      "Email chrystal.mayfield@harrisonvilleschools.org already exists\n",
      "Email kambrose@ga.essd40.com already exists\n",
      "Email courtney.pippin@ccr3.k12.mo.us already exists\n",
      "Email asherman@lexington.k12.mo.us already exists\n",
      "Email kristin.lininger@raypec.org already exists\n",
      "Email leslie.allen@raypec.org already exists\n",
      "Email dtreglia@pembrokehill.org already exists\n",
      "Email tina.linick@overlandchristian.org already exists\n",
      "Email howardg@usd230.org already exists\n",
      "Email jdoll@sherwoodk12.net already exists\n",
      "Email claym@pv362.org already exists\n",
      "Email rcronander@easton449.org already exists\n",
      "Email ernest.fields@grandviewc4.net already exists\n",
      "Email veatchm@usd231.com already exists\n",
      "Email canderson@cameronschools.org already exists\n",
      "Email cbrecheisen@usd348.com already exists\n",
      "Email tshannon@wntigers.net already exists\n",
      "Email dmalan@santafechiefs.k12.mo.us already exists\n",
      "Email derek.lorence@osbornwildcats.org already exists\n",
      "Email kschott@orrick.k12.mo.us already exists\n",
      "Email jfenstermaker@lonejackc6.net already exists\n",
      "Email kajohnson@gallatin.k12.mo.us already exists\n",
      "Email nancy.sutton@butler.k12.mo.us already exists\n",
      "Email schambers@kcpublicschool.org already exists\n",
      "Email haggj@usd290.org already exists\n",
      "Email abe.lewis@adrian.k12.mo.us already exists\n",
      "Email marlene.devilbiss@raytownschools.org already exists\n",
      "Email msimecek@winston.k12.mo.us already exists\n",
      "Email rmason@orrick.k12.mo.us already exists\n",
      "Email ssnow@lonejackc6.net already exists\n",
      "Email amygreenwald@smsd.org already exists\n",
      "Email stephen.miller@butler.k12.mo.us already exists\n",
      "Email venitakoukol@smsd.org already exists\n",
      "Email lamicone@kcmsd.net already exists\n",
      "Email LancellottiS@usd230.org already exists\n",
      "Email samsons@usd287.org already exists\n",
      "Email christene_sharp@isdschools.org already exists\n",
      "Email connie_daoust@isdschools.org already exists\n",
      "Email cjohnston@sherwoodk12.net already exists\n",
      "Email brucek@usd230.org already exists\n",
      "Email emetsker@usd497.org already exists\n",
      "Email betty_maday@isdschools.org already exists\n",
      "Email feuerbornv@mclouth.org already exists\n",
      "Email blaueb@usd290.org already exists\n",
      "Email tresa.kelly@lathropschools.com already exists\n",
      "Email tabitha_mabie@isdschools.org already exists\n",
      "Email marian.radar@grandviewc4.net already exists\n",
      "Email diamodo@usd290.org already exists\n",
      "Email sarahjane.russell@usd497.org already exists\n",
      "Email kschulte@usd288.org already exists\n",
      "Email anthony_mitchell@isdschools.org already exists\n",
      "Email rwillis@usd497.org already exists\n",
      "Email ktempleton@polo.k12.mo.us already exists\n",
      "Email walkerjm@usd231.com already exists\n",
      "Email JayDee.Nelson@lvpioneers.org already exists\n",
      "Email laura.gonzalez@lvpioneers.org already exists\n",
      "Email charris@usd348.com already exists\n",
      "Email johna.sutton@lsr7.net already exists\n",
      "Email micah.fritz@lsr7.net already exists\n",
      "Email mbridges@ballard.k12.mo.us already exists\n",
      "Email lauren.gechter@raytownschools.org already exists\n",
      "Email nlett@usd497.org already exists\n",
      "Email aisha.safir@raytownschools.org already exists\n",
      "Email ARowland@usd497.org already exists\n",
      "Email MLovelace@usd497.org already exists\n",
      "Email lawanda.wofford@raytownschools.org already exists\n",
      "Email laura.healy@raytownschools.org already exists\n",
      "Email alicia.benazzi@raytownschools.org already exists\n",
      "Email amy.tuttle@raypec.org already exists\n",
      "Email kelley.weida@raypec.org already exists\n",
      "Email stacey_elkinton@usd368.org already exists\n",
      "Email bnebel@pleasanthillschools.com already exists\n",
      "Email marianne.thomas@raytownschools.org already exists\n",
      "Email candace.blumhardt@lps53.org already exists\n",
      "Email flor.medrano@kckps.org already exists\n",
      "Email sandrareighard@smd.org already exists\n",
      "Email sherylhuerter@smsd.org already exists\n",
      "Email kimberly.alonzo@lsr7.net already exists\n",
      "Email JenniferJuday@smsd.org already exists\n",
      "Email lrooney@pembrokehill.org already exists\n",
      "Email sworden@bsd124.org already exists\n",
      "Email jsuber@greenville.k12.sc.us already exists\n",
      "All bad emails updated succesfully in db\n",
      "\n",
      "\n",
      "Prior Unique Subject Line: Official MLB Jerseys in Stock!!\n",
      "Prior Unique Subject Line There are no responses to the specified emails search criteria Official MLB Jerseys in Stock!! in the outbox with a filter date of 03/04/2024\n",
      "Prior Unique Subject Line You have 138 email responses to the specified search criteria Delivery Status Notification in the inbox with a filter date of 03/04/2024\n",
      "No bad emails to update with Official MLB Jerseys in Stock!!\n",
      "No emails to update\n",
      "\n",
      "\n",
      "Prior Unique Subject Line: Official Nike MLB Jerseys In Stock!!\n",
      "Prior Unique Subject Line There are no responses to the specified emails search criteria Official Nike MLB Jerseys In Stock!! in the outbox with a filter date of 03/04/2024\n",
      "Prior Unique Subject Line You have 138 email responses to the specified search criteria Delivery Status Notification in the inbox with a filter date of 03/04/2024\n",
      "No bad emails to update with Official Nike MLB Jerseys In Stock!!\n",
      "No emails to update\n",
      "\n",
      "\n",
      "Prior Unique Subject Line: Official MLB Little League Nike Jerseys - IN STOCK!!\n",
      "Prior Unique Subject Line There are no responses to the specified emails search criteria Official MLB Little League Nike Jerseys - IN STOCK!! in the outbox with a filter date of 03/04/2024\n",
      "Prior Unique Subject Line You have 138 email responses to the specified search criteria Delivery Status Notification in the inbox with a filter date of 03/04/2024\n",
      "No bad emails to update with Official MLB Little League Nike Jerseys - IN STOCK!!\n",
      "No emails to update\n",
      "\n",
      "\n",
      "Prior Unique Subject Line: Local Supplier for Baseball Uniforms & Apparel\n",
      "Prior Unique Subject Line There are no responses to the specified emails search criteria Local Supplier for Baseball Uniforms & Apparel in the outbox with a filter date of 03/04/2024\n",
      "Prior Unique Subject Line You have 138 email responses to the specified search criteria Delivery Status Notification in the inbox with a filter date of 03/04/2024\n",
      "No bad emails to update with Local Supplier for Baseball Uniforms & Apparel\n",
      "No emails to update\n",
      "\n",
      "\n",
      "Prior Unique Subject Line: Official MLB Jerseys\n",
      "Prior Unique Subject Line There are no responses to the specified emails search criteria Official MLB Jerseys in the outbox with a filter date of 03/04/2024\n",
      "Prior Unique Subject Line You have 138 email responses to the specified search criteria Delivery Status Notification in the inbox with a filter date of 03/04/2024\n",
      "No bad emails to update with Official MLB Jerseys\n",
      "No emails to update\n"
     ]
    }
   ],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "import logging\n",
    "import warnings\n",
    "import os\n",
    "import pandas as pd\n",
    "from config import *\n",
    "from Scraping_Emails.modules.scrapes import scrape\n",
    "from Scraping_Emails.modules.db_operations_aws import DatabaseConnector\n",
    "from MySQL_Scripts.modules.email_failures import  gather_and_send_bad_emails_to_db\n",
    "from Sending_Emails.modules.sends import EmailConfig\n",
    "warnings.filterwarnings(\"ignore\", category=UserWarning, module=\"pandas\")\n",
    "\n",
    "#create log dir and file\n",
    "logpath_creation = os.getcwd() + '\\\\Logs'\n",
    "if not os.path.exists(logpath_creation):\n",
    "    os.makedirs(logpath_creation)\n",
    "\n",
    "logging.basicConfig(filename= logpath_creation + '\\\\Email_Scraper.log', level=logging.INFO,\n",
    "                    format='%(asctime)s - %(message)s', datefmt='%d-%b-%y %H:%M:%S', force=True)\n",
    "\n",
    "\n",
    "# ----------------------------------Updating Faulty Emails in the DB before Send----------------------------------\n",
    "#Searches GMAIL on the daily for bad emails that were sent back, and sends data to unsubscribed email table\n",
    "#Goes through all distinct subject lines in the DB\n",
    "\n",
    "\n",
    "db_connector_email_history = DatabaseConnector(email_config.server, email_config.db_user, email_config.db_pass, email_config.database, email_config.table_name)\n",
    "query = db_connector_email_history.SQL_query(f'SELECT DISTINCT subject FROM [{email_config.database}].[dbo].[{email_config.table_name}]')\n",
    "    \n",
    "#Iterate through all unique subject lines to update the db table with all faulty email addresses\n",
    "#Keep in mind this assumes that the func is ran everyday with the function filtering up to yesterday\n",
    "for line in query['subject']:\n",
    "    print('\\n')\n",
    "    print(f'Prior Unique Subject Line: {line}')\n",
    "                                    # subject_line, email_address, email_pass, log_type, override_date = None\n",
    "    gather_and_send_bad_emails_to_db(line, email_config.EMAIL_ADDRESS_FROM, email_config.EMAIL_PASS, 'Prior Unique Subject Line', email_config.filter_date)\n",
    "\n",
    "\n",
    "#Special chars can not be recognized in subejct lines such as '&'\n",
    "#For example 'Local Supplier for Baseball Uniforms & Apparel' is not recognized"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}