MySQL Cron job with Apscheduler

cronapp.py

import smtplib
import MySQLdb
import datetime
from datetime import date
# from datetime import datetime
from datetime import timedelta
import time
import os
import logging
 
from apscheduler.schedulers.background import BackgroundScheduler
logging.basicConfig()

INTERVAL_FLAG = 2 #Interval in days when the script runs 
AGE_FLAG = 7670 #Years in days eg: 21 years = 7670 days

## Email ##
def emailSender(name, emailId, day_difference):
  content = 'Hello ' + name + ', Your birthday is after ' + str(day_difference) +' days'

  mail = smtplib.SMTP('smtp.gmail.com', 587)
  mail.ehlo()
  mail.starttls()
  mail.login('[email protected]', 'mi55tr33')
  mail.sendmail('[email protected]', emailId, content) #(from, to, message)
  mail.close()




def birthdayNotifier():
    ## Connect to the db ##
    db = MySQLdb.connect("localhost","root","server","qpcm" )
    cursor = db.cursor()
    cursor.execute("SELECT * FROM qpcmms_family")
    counter = 0
    for row in cursor.fetchall():
        name = row[1]
        emailId = row[2]
        birthdate = row[8] #YYYY-MM-DD
        member_id = row[17]
        birth_date_actual = str(row[8]) #taking the string of the birthday
        print 'bithday: ', birth_date_actual
        list_birth_date_actual = birth_date_actual.split('-')
        birth_year = int(list_birth_date_actual[0])
        # birth_month = int(list_birth_date_actual[1])
        # birth_date = int(list_birth_date_actual[2])
        #print birth_date_actual
        #manipulate the actual_birth_date
        today = date.today()
        #print today
        #birth_date_manipulate = date(today.year, birth_month, birth_date)
        current_age_in_days = int(str(today - birthdate).split(' ')[0]) #21 years = 7670 days
        #print current_age_in_days
        #print birth_date_manipulate
        day_difference = AGE_FLAG - current_age_in_days
        #print day_difference
        # day_difference = str(day_difference).split(' ')[0]
        print 'diff: ',int(day_difference)

        

        if int(day_difference) > INTERVAL_FLAG:
            counter = counter+1
            print 'Counter: ', counter
            cursor.execute("SELECT * from qpcmms_role where rolename='Supervisor'")
            for row in cursor.fetchall():
                supervisor_role_id = row[0]
                sql = "SELECT * from qpcmms_qpuser where role_id = '%s'",(supervisor_role_id)
                cursor.execute(*sql)
                for row in cursor.fetchall():
                    supervisor_club = row[5]
                    supervisor_mail_id = row[6]
                    supervisor_name = row[1]

                    #text = '[<club: Jinan Recreation Club>]'
                    supervisor_club_list = supervisor_club.replace('[','').replace(']','').replace('<club: ','').replace('>','').split(', ')
                    #print supervisor_mail_id
                    #print supervisor_club_list
                    for each_club_of_supervisor in supervisor_club_list:
                        #print each_club_of_supervisor
                        sqli = "SELECT * from qpcmms_member WHERE id='%s'",(member_id)
                        cursor.execute(*sqli)
                        for row in cursor.fetchall():
                            member_club_list = row[29]
                            each_club_of_member = member_club_list.replace('[','').replace(']','').replace('<club: ','').replace('>','').split(', ')
                            if each_club_of_supervisor in each_club_of_member:
                                print each_club_of_supervisor
                                print 'Mail id: ', supervisor_mail_id
                                emailSender(supervisor_name, supervisor_mail_id, day_difference)
                            # print type(each_club_of_member)
                            # print each_club_of_supervisor + 'super'
                            # print type(each_club_of_supervisor)
                            # if each_club_of_member == each_club_of_supervisor:
                                # print each_club_of_member
                    
            #emailSender(name, emailId, day_difference)


    data = cursor.fetchone()
    db.commit()
    db.close()

def renewNotifier():
    db = MySQLdb.connect("localhost","root","server","qpcm" )
    cursor = db.cursor()
    today = date.today()
    end_date = today + datetime.timedelta(days=15)
    sql = "SELECT * FROM qpcmms_clubstatus WHERE status='Active' AND date_of_expiry BETWEEN %s and %s",(today, end_date)
    cursor.execute(*sql)
    for row in cursor.fetchall():
        member_id = row[1]
        renewal_date = row[4]
        club_id = row[2]
        sql = "SELECT * FROM qpcmms_club WHERE id = '%s'",(club_id)
        cursor.execute(*sql)
        club_name = row[1];
        print 'Club ID: ',club_name
        cursor.execute("SELECT * from qpcmms_role where rolename='Supervisor'")
        for row in cursor.fetchall():
            supervisor_role_id = row[0]
            sql = "SELECT * from qpcmms_qpuser where role_id = '%s'",(supervisor_role_id)
            cursor.execute(*sql)
            for row in cursor.fetchall():
                supervisor_club = row[5]
                supervisor_mail_id = row[6]
                supervisor_name = row[1]

                #text = '[<club: Jinan Recreation Club>]'
                supervisor_club_list = supervisor_club.replace('[','').replace(']','').replace('<club: ','').replace('>','').split(', ')
                #print supervisor_mail_id
                #print supervisor_club_list
                for club_name in supervisor_club_list:
                    #print each_club_of_supervisor
                    sqli = "SELECT * from qpcmms_member WHERE id='%s'",(member_id)
                    cursor.execute(*sqli)
                    for row in cursor.fetchall():
                        member_club_list = row[29]
                        each_club_of_member = member_club_list.replace('[','').replace(']','').replace('<club: ','').replace('>','').split(', ')
                        if club_name in each_club_of_member:
                            print club_name
                            print 'Mail id: ', supervisor_mail_id
                            print 'Renewal Date: ', renewal_date
                            print "**************************"
                            #emailSender(supervisor_name, supervisor_mail_id, day_difference)


        cursor.execute("SELECT * from qpcmms_role where rolename='Steward'")
        for row in cursor.fetchall():
            steward_role_id = row[0]
            sql = "SELECT * from qpcmms_qpuser where role_id = '%s'",(steward_role_id)
            cursor.execute(*sql)
            for row in cursor.fetchall():
                steward_club = row[5]
                steward_mail_id = row[6]
                steward_name = row[1]

                #text = '[<club: Jinan Recreation Club>]'
                steward_club_list = steward_club.replace('[','').replace(']','').replace('<club: ','').replace('>','').split(', ')
                #print supervisor_mail_id
                #print supervisor_club_list
                for each_club_of_steward in steward_club_list:
                    #print each_club_of_supervisor
                    sqli = "SELECT * from qpcmms_member WHERE id='%s'",(member_id)
                    cursor.execute(*sqli)
                    for row in cursor.fetchall():
                        member_club_list = row[29]
                        each_club_of_member = member_club_list.replace('[','').replace(']','').replace('<club: ','').replace('>','').split(', ')
                        if each_club_of_steward in each_club_of_member:
                            print each_club_of_steward
                            print 'Mail id: ', steward_mail_id
                            print 'Renewal Date: ', renewal_date
                            print "###########################"
                            #emailSender(supervisor_name, supervisor_mail_id, day_difference)

    db.commit()
    db.close()




if __name__ == '__main__':
    scheduler = BackgroundScheduler()
    scheduler.add_job(birthdayNotifier, 'interval', seconds=120)
    scheduler.add_job(renewNotifier, 'interval', seconds=10)
    scheduler.start()
    print('Press Ctrl+{0} to exit'.format('Break' if os.name == 'nt' else 'C'))
 
    try:
        # This is here to simulate application activity (which keeps the main thread alive).
        while True:
            time.sleep(5)
    except (KeyboardInterrupt, SystemExit):
        scheduler.shutdown()  # Not strictly necessary if daemonic mode is enabled but should be done if possible


# birthdayNotifier()