Python exercise example: Classroom Booking App

In this post, I am going  do a Python app with the next requirements:

-In this app will allow the user to book classrooms for a day, in other to do that that classroom shouldn’t be booked before that same day.

– The user that want to book a classroom need to give a name and a email address in other to complete the action.

– After the book has been completed the app will send to the user’s email an message giving the order that he/she placed.

-The app will send another email a day the classroom will be used to remind the user of the booked.

With this requirements now we can see other tools that we will need, like an user interface an a database to save the bookings, in this case, I will use mySQL.

For the interface we the following elements:

  • Entries to fill them with the name of the user and the email
  • A date picker
  • A button to submit place the order
  • Labels leave everything identify and clear for the user.

So let’s go and lets import some classes into our project.

Preparation

To start with the classes that we are going to need, we are going to use Calendar for the date picker  so we will need to download the class for it, and for that I will use pip using the following command in our  command prompt:

pip install tkcalendar

after it has been installed in your computer I also i am going to  install the mySQL functionality  with the followin pip command:

pip install pymysql

To finish we the preparation of the project we will import all the classes that are needed:

import tkinter as tk
from tkinter import ttk
from tkinter import *
from tkinter import messagebox #Until this point we are import the classes to create a user interface
import pymysql #This is to connect to our mySQL database
import smtplib, ssl #We need this to send emails
import datetime as dt 
import time #We will use this to make a function wait for a time before sending the reminder email
from tkcalendar import Calendar, DateEntry #And this will help us creating the calendar for the date picker

Classroom booking app

First because we are going to use a database I used XAMPP to create my database and connected to my python project, in order to do that you will need to install XAMPP, after that open it and start Apache and MySQL. After both option have been started, click on Admin in MySQl section this will send to myphpadmin were you can create your databases. Create a database and in this case I created the following table.

CREATE TABLE classrooms_booked(
date_id char(10),
name varchar(60),
email varchar(60),
classroom char(11)
)

Now that we created our database, let’s start coding our app:

  • Let’s create the following variables for our project:

TOTAL_CLASSROOMS = 5

choices = { ‘Classroom01′,’Classroom02′,’Classroom03′,’Classroom04′,’Classroom05’}  #We will use this variable to create a list for our dropdown menu to select the classroom that the user will select

  • Now we need to connect our app with our database so we can use it and change it through our application:

db = pymysql.connect(“localhost”,”root”,””,”name of your database” )

# value between root( username) and your database is password which normally by default is not set.

  • Time to create our interface with tkinter and add a Frame so we can organise all the element that are going to contain:

root = tk.Tk()

mainframe = Frame(root)
mainframe.grid(column=0,row=0, sticky=(N,W,E,S) )
mainframe.columnconfigure(0, weight = 1)
mainframe.rowconfigure(0, weight = 1)
mainframe.rowconfigure(4, weight = 1,pad=8)
mainframe.pack(pady = 100, padx = 100)

  •  And now we will create and to the frame some of elements we will need:

text1= Entry(mainframe)#Here will go the user’s name
text2= Entry(mainframe)#Here will go the user’s email
text1.grid(row=1, column=1)
text2.grid(row=3, column=1)

Label(mainframe, text=”Full name”).grid(row=0, column=1)
Label(mainframe, text=”Email”).grid(row=2, column=1)
date= Label(mainframe, text=”–Select a date–“) #This will contain the date that will be selected by the datepicker
date.grid(row=5, column=1)

tkvar = StringVar(mainframe)
tkvar.set(‘Classroom01’)

popupMenu= OptionMenu(mainframe, tkvar, *choices)#This will be the dropdown menu with the classroom IDs
popupMenu.grid(row=6, column=1)

  • For the date picker and the  email to request the bookings we will have first to create some function in order to give to those elements some actions, starting with the datepicker will use a button so we you click on it will pop-up a calendar where you can select a date, press “Ok” and send that information to the label already created to receive the date:

def calendar():

    def dateSelected():
        popupMenu.configure(state=’normal’)

        for classroom in choices:
            popupMenu[‘menu’].entryconfigure(classroom, state =                        “normal”)

        date.config(text=str(cal.selection_get()))

        checkClassrooms()
        submit_btn.config(state=”normal”)

    def checkClassrooms():
        cursor = db.cursor()
        sql = “SELECT classroom FROM classrooms_booked \
        WHERE date_id = ‘%s'” % (str(cal.selection_get()))
        cursor.execute(sql)
        results = cursor.fetchall()
        if(len(results) != TOTAL_CLASSROOMS):
            for classroom in results:
                popupMenu[‘menu’].entryconfigure(classroom, state =                        “disabled”)
        else:
            popupMenu.configure(state=’disabled’)

    top = tk.Toplevel(root)
    cal = Calendar(top, font=”Arial 14″, selectmode=’day’, locale=’en_US’,
cursor=”hand1″, year=2018, month=2, day=5)

    cal.pack(fill=”both”, expand=True)
    ttk.Button(top, text=”ok”, command=dateSelected).pack()

    ttk.Button(mainframe, text=’Calendar’,    command=calendar).grid(row=4, column=1)

As you can see , I create a function “calendar” containing other functions, this functions will be excuted when we press the button “Ok”. The first function will reset the dropdown menu enabling all the options, then it will call the function checkClassroom() which will check the date in the datebase and disable all the classrooms that has been booked on that date.

  • Now, for the dropdown menu we will need to some other functions but this time they aren’t in nested each other:

def sendEmail():
    port = 465
    sender_email = “your email”
    receiver_email = text2.get()

    subject= “Classroom Booked” 
    message = “You have booked the” + tkvar.get() + “for the date” +      date[‘text’]
    email= ‘Subject: {}\n\n{}’.format(subject, message)

    context = ssl.create_default_context()

    with smtplib.SMTP_SSL(“smtp.gmail.com”, port,  context=context) as server:
    server.login(“your email”, ” password”)
    server.sendmail(sender_email, receiver_email, email)

def insertClassBook():
    cursor = db.cursor()
    sql = “INSERT INTO classrooms_booked(date_id, name, email, classroom) \
    VALUES (‘%s’, ‘%s’, ‘%s’, ‘%s’)” % \
    (date[‘text’], text1.get(), text2.get(), tkvar.get())
    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()

def book_classroom():
    insertClassBook()
    sendEmail()
    submit_btn.config(state=”disabled”)
    datebooked= date[‘text’]
    y=int(datebooked[0:4])
    m=int(datebooked[5:7])
    d=int(datebooked[8:])
    send_time = dt.datetime(y,m,d,2,0,0) # set your sending time in UTC
    while True:
        if time.time() == send_time.timestamp():
            sendEmail()

We have created 3 functions which we will see what they do:

– The function sendEmail()  will  send an email using stmp, which you will only need to provide an email and password as a sender ( this email in the configuration you will need to allow the access of less secure apps) and an email for the receiver in this cas we will use the Entry element created to store that information. Because we are using a gmail address we use that specific port.

-The function insertClassBook() will simply insert the request in the database.

For the last function, this will class the other 2 functions already mentioned and give a delay to send another email as a reminder.

  • To finish our script, I created the “Submit” button giving the action of calling the insertClassBook() when clicked.

submit_btn = ttk.Button(mainframe, text=’Sumbit’, command= book_classroom)
submit_btn.grid(row=7, column=1)
submit_btn.config(state=”disabled”)
root.mainloop()

After that has been set, now it’s time to run our script, which it will give us two windows like this ( the calendar one will show up once you click on the “Calendar button”):

(As you can see the Submit button is disabled, It will go back to normal once you select a date to make a request.)

And that’s it. We have used database with a python application to provide us a storage the information we need and it will disable the classrooms that has been booked, making impossible to book it the same day by another person.

That’s all for this example and if you want to learn more about python, you can always do it with our courses here in Bermotech.