👾 GitHub Link: https://github.com/Benjamin-James-Reitz/attendancebot-3.0
As one of my first Python scripts, I coded a data conversion bot with automated email functionality in Python 3.
As you go over this tutorial, feel free to watch my explainer video as well:
[Python Project] Data Conversion Bot with Automated Email Functionality
![[Python Project] Data Conversion Bot with Automated Email Functionality](https://blog.finxter.com/wp-content/plugins/wp-youtube-lyte/lyteCache.php?origThumbUrl=https%3A%2F%2Fi.ytimg.com%2Fvi%2FNmgoTae4D6c%2F0.jpg)
Why This Project?
The script helped me save time by letting the computer do the busy work of composing and sending customized email updates to students several times throughout the semester.
Before I started any coding I spent some time deciding on my inputs and outputs, breaking down the desired functionality into smaller chunks, and noting which parts of the code I needed to research.
In the process of researching, coding, and revising, I learned first-hand the benefits of avoiding repetitive code.
How It Started
This project began with a real-life problem.
Last spring, I was tasked with coaching fitness at a private school in Maryland where I was teaching Mandarin.
Students needed to attend at least 50% of practices in order to pass fitness. Careful attendance records and regular email updates were necessary in order to keep students informed of their progress.
I decided to try my hand at writing an original python script to automate the task of calculating attendance rates and emailing students.
Project Specifications
My first step before doing any coding was to chart out and decide on the desired functionality of the script, as well as the inputs and outputs of the code. My goal was to break larger problems into several smaller, simpler problems.
- Input: After exploring ways to use Google sheets I ended up deciding to use a comma-separated values (CSV) file of the attendance records as my input for the sake of simplicity.
- Output: The output would be automated emails sent out to two distinct groups: seniors and non-seniors. The seniors finished the school year earlier than non seniors. Each email would include a paragraph update on the number of practices attended, number of absences, current grade (passing or failing), number of remaining practices, and the number of remaining practices needed to attend to pass.
Here are a few examples of email messages generated from my test CSV data:
perfectstudent35@protonmail.com Spring Fitness Attendance Update Dear ben perfectstudent, Hello varsity walker. I'm coach Reitz' python3 attendancebot! You are currently passing Spring Fitness. Your current attendance rate is:100.0%. (You have attended 8 sessions, and have been absent for 0 sessions.) You still need to attend at least 3 practices of the remaining 16 in the school year in order to reach the critical threshold of the majority of practices (i.e. an attendance rate of at least 50%) and pass Spring Fitness. Your remaining practice dates are: 4-25, 4-26, 4-27, 4-28, 5-2, 5-3, 5-4, 5-5, 5-9, 5-10, 5-11, 5-12, 5-16, 5-17, 5-18, 5-19. Sincerely, Mr Coach's Python3 Attendance-bot ben averagestudent sending to: benbadstudent26@university.edu Spring Fitness Attendance Update Dear ben averagestudent, Hello varsity walker. I'm coach Reitz' python3 attendancebot! You are currently failing Spring Fitness. Your current attendance rate is:50.0%. (You have attended 4 sessions, and have been absent for 4 sessions.) You still need to attend at least 7 practices of the remaining 16 in the school year in order to reach the critical threshold of the majority of practices (i.e. an attendance rate of at least 50%) and pass Spring Fitness. Your remaining practice dates are: 4-25, 4-26, 4-27, 4-28, 5-2, 5-3, 5-4, 5-5, 5-9, 5-10, 5-11, 5-12, 5-16, 5-17, 5-18, 5-19. Sincerely, Mr Coach's Python3 Attendance-bot benbadstudent27@university.edu Spring Fitness Attendance Update Dear ben badstudent, Hello varsity walker. I'm coach Reitz' python3 attendancebot! You are currently failing Spring Fitness. Your current attendance rate is:25.0%. (You have attended 2 sessions, and have been absent for 6 sessions.) You still need to attend at least 9 practices of the remaining 16 in the school year in order to reach the critical threshold of the majority of practices (i.e. an attendance rate of at least 50%) and pass Spring Fitness. Your remaining practice dates are: 4-25, 4-26, 4-27, 4-28, 5-2, 5-3, 5-4, 5-5, 5-9, 5-10, 5-11, 5-12, 5-16, 5-17, 5-18, 5-19. Sincerely, Mr Coach's Python3 Attendance-bot seniorslacker22@gmail.com Dear Senior Slacker, Hello varsity walker. I'm coach Reitz' python3 attendancebot! You are currently not passing Spring Fitness. Your current attendance rate is:12.5%. (You have attended 1 sessions, and have been absent for 7 sessions.) You still need to attend at least 6 practices of the remaining 8 in the school year in order to reach the critical threshold of the majority of practices (i.e. an attendance rate of at least 50%) and pass Spring Fitness. Your remaining practice dates are: 4-25, 4-26, 4-27, 4-28, 5-2, 5-3, 5-4, 5-5. Sincerely, Mr Coach's Python3 Attendance-bot
Project Code
My first task was to sort out seniors from non-seniors into two lists. The following code snippet identifies and creates lists of seniors based on the string “22” appearing in their email addresses.
import sys, csv
#create email_report str
email_report = ""
#search string for "22" to sort out the seniors
# (the year they will graduate is built into their email addresses)
word_search = str(22)
seniors = []
everyone_else = []
#read csv, and split on "," the line
file = open("attendance.csv", newline='')
csvreader = csv.reader(file, delimiter=',')
header = next(csvreader)
rows = []
for row in csvreader:
rows.append(row)
line_count = (len(rows))
#sort entries into two new lists: seniors and non-seniors
#first, initialize the lists
file = open("attendance.csv")
csvreader = csv.reader(file)
header = next(csvreader)
while line_count >= 1:
for row in rows:
if word_search in row[1]:
seniors.append(row)
line_count -= line_count
else:
everyone_else.append(row)
line_count -= line_count
print("seniors list: ", seniors)
print("everyone else list: ", everyone_else)
The script uses two large loops to read the CSV data and compute each variable to be inserted into email templates that would be sent out periodically to students throughout the semester.
The snippet of code below shows the loop for non-seniors.
The variables to be counted include:
skipnum(how many sessions did the student skip),attnum(how many sessions did the student attend),attrate(current attendance rate),passfail(current status i.e. passing or failing),neednum(how many sessions of the remaining sessions the student still needs to attend in order to pass).
remaining_dates_everyone_else = [
'4-25', '4-26', '4-27', '4-28',
'5-2', '5-3', '5-4', '5-5',
'5-9', '5-10', '5-11', '5-12',
'5-16', '5-17', '5-18', '5-19'
]
remaining_number_sessions_non_seniors = len(remaining_dates_everyone_else)
remaining_dates_everyone_else_string=''
remaining_dates_everyone_else_string=''.join([str(item) + ", " for item in remaining_dates_everyone_else])
remaining_dates_everyone_else_string_stripped=remaining_dates_everyone_else_string[:-2]
# Step 1: process attendance data and insert attnum, skipnum, attrate,
# remaining_number_sessions_non_seniors, and neednum_everyone_else into list
for student in everyone_else:
skipnum = 0
attnum = 0
for i in student:
if i=="P":
attnum = attnum+1
elif i=="A":
skipnum = skipnum+1
i += i
student.append(attnum)
student.append(skipnum)
attrate = ((attnum/(attnum+skipnum))*100)
format_attrate = "{:.2f}".format(attrate)
student.append(format_attrate)
student.append(remaining_number_sessions_non_seniors)
# calculate the minimum needed sessions to pass with more than 50% attendance
if int(float(format_attrate)) >= 50:
passfail="passing"
student.append(passfail)
else:
passfail="failing"
student.append(passfail)
neednum_everyone_else = remaining_number_sessions_non_seniors
while (attnum+neednum_everyone_else)/(attnum+skipnum+remaining_number_sessions_non_seniors) >= 0.5:
neednum_everyone_else = (int(neednum_everyone_else) - 1)
else:
student.append(neednum_everyone_else + 1)
# delete the unneeded attendance records
del student[2:]
#compose custom email message
entry = "Dear " + student[0].strip() + ",\n\nHello varsity walker. I'm coach Reitz' python3 attendancebot!\n\nYou are currently " + passfail + " Spring Fitness. \n\nYour current attendance rate is:" + str(attrate) + "%. (You have attended " + str(attnum) + " sessions, and have been absent for " + str(skipnum) + " sessions.)\n\nYou still need to attend at least " + str(neednum_everyone_else) + " practices of the remaining " + str(remaining_number_sessions_non_seniors) + " in the school year in order to reach the critical threshold of the majority of practices (i.e. an attendance rate of at least 50%) and pass Spring Fitness. \n\nYour remaining practice dates are: " + str(remaining_dates_everyone_else_string_stripped) + ". \n\nSincerely, \n Mr Coach's Python3 Attendance-bot \n\n"
email_report = str(entry)
#attach the email report to string
student.append(email_report)
The following code snippet shows how the emails are generated and sent out via a Gmail account.
To avoid hardcoding the access credentials for the Gmail account I saved them as lines 1 and 2 of a different file, credentials.txt.
#email each student with customized email message
import smtplib, ssl
def read_creds():
user = passw = ""
with open("credentials.txt", "r") as f:
file = f.readlines()
user = file[0].strip()
passw = file[1].strip()
return user, passw
port = 465
sender, password = read_creds()
i_inner=[]
for student in everyone_else:
for i_inner in student:
print("And now we will prep to send the following message: \n")
print(student[0])
receive = student[1]
print("sending to: ")
print(receive)
SUBJECT = "Spring Fitness Attendance Update"
print(SUBJECT)
TEXT = student[2]
print(TEXT)
message = 'Subject: {}\n\n{}'.format(SUBJECT, TEXT)
context = ssl.create_default_context()
print("Starting to send to non-seniors")
with smtplib.SMTP_SSL("smtp.gmail.com", port, context=context) as server:
server.login(sender, password)
server.sendmail(sender, receive, message)
print("sent emails to non-seniors!")
break
After putting my code aside for a few months, I discovered that my code no longer worked to send emails through the Python script because Google had discontinued their login option for less secure apps.
After some research, I discovered that by adding an app password to my Google account, the script was able to log in and send emails again. To do this 2 factor authentication needs to be enabled before generating an app password.
Project Learnings
The experience of writing this Python script and using it for my attendance records and email updates to students helped me learn several important principles of coding:
1. The importance of proper punctuation/notation
Always remember to use [] for lists, {} for dictionaries, and "" for strings.
Sometimes it is necessary to convert a data type in order for it to work properly in the code.
That is, when generating an email text body as a string, numbers must be converted to strings first before inserting into a string because Python can’t concatenate two different data types (i.e. strings and integers).
2. Eliminate unnecessary data from the code, and reuse variables
Originally my code saved all of the variables in a new list before generating the automated email messages.
I later revised the code by deleting unneeded items from the lists and reusing the variable values from the loops instead. This is simpler and is good practice because it streamlines the code and makes it less resource intensive.
3. Always be careful when using test data
If the formatting differs at all from the actual data to be processed, problems may occur outside of testing.
Original Code for Python Data Conversion/Email Bot
import sys, csv
#create email_report str
email_report = ""
#search string for "22" to sort out the seniors
# (the year they will graduate is built into their email addresses)
word_search = str(22)
seniors = []
everyone_else = []
#read csv, and split on "," the line
file = open("attendance.csv", newline='')
csvreader = csv.reader(file, delimiter=',')
header = next(csvreader)
rows = []
for row in csvreader:
rows.append(row)
line_count = (len(rows))
#sort entries into two new lists: seniors and non-seniors
#first, initialize the lists
file = open("attendance.csv")
csvreader = csv.reader(file)
header = next(csvreader)
while line_count >= 1:
for row in rows:
if word_search in row[1]:
seniors.append(row)
line_count -= line_count
else:
everyone_else.append(row)
line_count -= line_count
print("seniors list: ", seniors)
print("everyone else list: ", everyone_else)
#set the variables list item 0=name, 1=email address 2=atnum,
# 3=skipnum, 4=attrate, 5=remaining_number of sessions, 6=passfail 7=still need to attend 8=email message
attrate = 0
remaining_number_sessions_seniors = 0
neednum_seniors = 0
neednum_everyone_else = 0
remaining_dates_seniors = [
'4-25', '4-26', '4-27', '4-28',
'5-2', '5-3', '5-4', '5-5'
]
remaining_number_sessions_seniors = len(remaining_dates_seniors)
remaining_dates_sessions_seniors_string=''
remaining_dates_sessions_seniors_string=''.join([str(item) + ", " for item in remaining_dates_seniors])
remaining_dates_sessions_seniors_string_stripped=remaining_dates_sessions_seniors_string[:-2]
0
remaining_dates_everyone_else = [
'4-25', '4-26', '4-27', '4-28',
'5-2', '5-3', '5-4', '5-5',
'5-9', '5-10', '5-11', '5-12',
'5-16', '5-17', '5-18', '5-19'
]
remaining_number_sessions_non_seniors = len(remaining_dates_everyone_else)
remaining_dates_everyone_else_string=''
remaining_dates_everyone_else_string=''.join([str(item) + ", " for item in remaining_dates_everyone_else])
remaining_dates_everyone_else_string_stripped=remaining_dates_everyone_else_string[:-2]
# Step 1: process attendance data and insert attnum, skipnum, attrate,
# remaining_number_sessions_non_seniors, and neednum_everyone_else into list
for student in everyone_else:
skipnum = 0
attnum = 0
for i in student:
if i=="P":
attnum = attnum+1
elif i=="A":
skipnum = skipnum+1
i += i
student.append(attnum)
student.append(skipnum)
attrate = ((attnum/(attnum+skipnum))*100)
format_attrate = "{:.2f}".format(attrate)
student.append(format_attrate)
student.append(remaining_number_sessions_non_seniors)
# calculate the minimum needed sessions to pass with more than 50% attendance
if int(float(format_attrate)) >= 50:
passfail="passing"
student.append(passfail)
else:
passfail="failing"
student.append(passfail)
neednum_everyone_else = remaining_number_sessions_non_seniors
while (attnum+neednum_everyone_else)/(attnum+skipnum+remaining_number_sessions_non_seniors) >= 0.5:
neednum_everyone_else = (int(neednum_everyone_else) - 1)
else:
student.append(neednum_everyone_else + 1)
# delete the unneeded attendance records
del student[2:]
#compose custom email message
entry = "Dear " + student[0].strip() + ",\n\nHello varsity walker. I'm coach Reitz' python3 attendancebot!\n\nYou are currently " + passfail + " Spring Fitness. \n\nYour current attendance rate is:" + str(attrate) + "%. (You have attended " + str(attnum) + " sessions, and have been absent for " + str(skipnum) + " sessions.)\n\nYou still need to attend at least " + str(neednum_everyone_else) + " practices of the remaining " + str(remaining_number_sessions_non_seniors) + " in the school year in order to reach the critical threshold of the majority of practices (i.e. an attendance rate of at least 50%) and pass Spring Fitness. \n\nYour remaining practice dates are: " + str(remaining_dates_everyone_else_string_stripped) + ". \n\nSincerely, \n Mr Coach's Python3 Attendance-bot \n\n"
email_report = str(entry)
#attach the email report to string
student.append(email_report)
# Step 2: process attendance data for seniors and insert attnum, skipnum, attrate,
# remaining_number_sessions_non_seniors, and neednum_everyone_else into list
for student in seniors:
skipnum = 0
attnum = 0
for i in student:
print([[i]])
if i == "P":
attnum = attnum + 1
elif i == "A":
skipnum = skipnum + 1
i += i
student.append(attnum)
student.append(skipnum)
attrate = ((attnum / (attnum + skipnum)) * 100)
format_attrate = "{:.2f}".format(attrate)
student.append(format_attrate)
student.append(remaining_number_sessions_seniors)
# calculate the minimum needed sessions to pass with more than 50% attendance
if int(float(format_attrate)) >= 50:
passfail = "passing"
student.append(passfail)
else:
passfail = "not passing"
student.append(passfail)
neednum_seniors = remaining_number_sessions_seniors
while neednum_seniors >= 0:
if (attnum + neednum_seniors) / (attnum + skipnum + remaining_number_sessions_seniors) >= 0.5:
student.append(neednum_seniors)
break
else:
neednum_seniors = str(neednum_seniors - 1)
if int(float(format_attrate)) >= 50:
passfail="passing"
else:
passfail="not passing"
# delete the unneeded attendance records
del student[2:]
# calculate the minimum needed sessions to pass with more than 50% attendance
neednum_seniors = remaining_number_sessions_seniors
while (attnum+neednum_seniors)/(attnum+skipnum+remaining_number_sessions_seniors) >= 0.5:
neednum_seniors = int(neednum_seniors - 1)
else:
student.append(neednum_seniors + 1)
# delete the unneeded attendance records
del student[2:]
#compose custom email message
entry = "Dear " + student[0].strip() + ",\n\nHello varsity walker. I'm coach Reitz' python3 attendancebot!\n\nYou are currently " + passfail + " Spring Fitness. \n\nYour current attendance rate is:" + str(attrate) + "%. (You have attended " + str(attnum) + " sessions, and have been absent for " + str(skipnum) + " sessions.)\n\nYou still need to attend at least " + str(neednum_seniors) + " practices of the remaining " + str(remaining_number_sessions_seniors) + " in the school year in order to reach the critical threshold of the majority of practices (i.e. an attendance rate of at least 50%) and pass Spring Fitness. \n\nYour remaining practice dates are: " + str(remaining_dates_sessions_seniors_string_stripped) + ". \n\nSincerely, \n Mr Coach's Python3 Attendance-bot \n\n"
email_report = str(entry)
student.append(email_report)
#email each student with customized email message
import smtplib, ssl
def read_creds():
user = passw = ""
with open("credentials.txt", "r") as f:
file = f.readlines()
user = file[0].strip()
passw = file[1].strip()
return user, passw
port = 465
sender, password = read_creds()
i_inner=[]
for student in everyone_else:
for i_inner in student:
print("And now we will prep to send the following message: \n")
print(student[0])
receive = student[1]
print("sending to: ")
print(receive)
SUBJECT = "Spring Fitness Attendance Update"
print(SUBJECT)
TEXT = student[2]
print(TEXT)
message = 'Subject: {}\n\n{}'.format(SUBJECT, TEXT)
context = ssl.create_default_context()
print("Starting to send to non-seniors")
with smtplib.SMTP_SSL("smtp.gmail.com", port, context=context) as server:
server.login(sender, password)
server.sendmail(sender, receive, message)
print("sent emails to non-seniors!")
break
#create a report.csv file of the new lists
Details = ['name', 'email', 'email']
with open('report.csv', 'w') as f:
write = csv.writer(f)
write.writerow(Details)
for item in everyone_else:
write.writerow(item[0:2])
print(item[0:2])
for item in seniors:
write.writerow(item[0:2])
print(item[0:2])
i_inner=[]
for student in seniors:
for element in student:
receive = student[1]
print(receive)
SUBJECT = "Spring Fitness Attendance Update"
TEXT = student[2]
print(TEXT)
message = 'Subject: {}\n\n{}'.format(SUBJECT, TEXT)
context = ssl.create_default_context()
print("Starting to send to seniors")
with smtplib.SMTP_SSL("smtp.gmail.com", port, context=context) as server:
server.login(sender, password)
server.sendmail(sender, receive, message)
print("sent emails to seniors!")
break
print(seniors)
print("\n")
print(everyone_else)
I am a freelance ethical hacker/penetration tester. I have extensive experience in penetration testing and vulnerability assessments on web apps and servers. I am also fluent in Mandarin and have 15 years of experience as an edTech integration specialist, curriculum designer, and foreign language teacher. Here’s my personal website.