Mar 30, 2020

11 min read

Can you store employee information using a voice bot in Python?

Absolutely!

Recently, I created a voice bot in Python to store basic employee information, for example employee id, first and last name of the employee, and pay of the employee. Not only does it use a voice bot to store basic employee information but also allows you to type in the employee information manually. Here’s the source code.

However, you need a few tools before you get started with the project. One of the most important tool is Python.

How to get started?

This project requires you to have a basic understanding of SQLITE3 module in Python since you will use the module to create the database. You will also need a basic understanding of classes and instances in Python.

For an understanding of classes and instances in Python, you can go through the first series of this video by Corey Schafer called ‘Classes and Instances’.

For a basic understanding of SQL syntax, you can follow Mike Dane’s course on SQL. For this project, however, you just need to know how to update, create, or delete tables using SQL.

After being familiar with SQL syntax, you can work your way through this SQLITE3 tutorial by Corey Schafer.

If you can work with Classes and Instances in Python and know how to use the SQLITE3 module, then wait no more and get started.

QUICK NOTE BEFORE GETTING STARTING: Don’t get confused if you see weird function names. I have included a detailed description for what each function does.

First step: Create the employee class

The employee class will be used to create the employees first name, last name, and pay. This class is just a few lines of code and could be written in the project itself, however, creating the employee class in a different python file was a way for me to get started with OOP Programming and understand how classes can be imported to another python file.

class Employees:
    """

    This class saves the employee first name, last name, and pay. It's imported in the project SQLITE3

    """
    def __init__(self, first, last, pay):
        self.first = first
        self.last = last
        self.pay = pay

Here, you create the class called Employees. Inside the Employee class, you will create the constructor method which has the parameters self, first, last, pay .The self parameter refers to the instance of the class Employee while the other parameters first, last, pay are used to initialize the data attributes of the class instance. Inside the constructor method, you will set the values of first, last, pay to the values passed in the constructor method when the instance of a class is created. For example, the parameter first is set to the value passed in the constructor method using the class instance which is self.first .

That’s it for classes. Now, let’s move onto creating the actual project….

Second step: Creating the project

Modules to import for the project:

# modules for the voice bot
import speech_recognition as sr
from gtts import gTTS
import playsound# modules for the database
import random
from employee_class import Employees
import sqlite3
import os
import re

Create the voice bot:

def speak_text(speak):
 “””
 This function makes the voice bot speak a specific command.
 “””
 rand = random.randint(1, 10000)
 filename = ‘file’ + str(rand) + ‘.mp3’
 tts = gTTS(text=speak, lang=’en’)
 tts.save(filename)
 playsound.playsound(filename)
 os.remove(filename)

The speak_text() function creates a mp3 file through which the voice bot speaks. Each time the program runs, the variable filename creates a new mp3 file since the program gives an error if the voice bot speaks through the same mp3 file. The tts variable uses the gTTS (Google-Text-To-Speech) module. The gTTS module speaks the string text which is set equal to the string passed in the function parameter speak . For our project purposes, the gTTSmodule uses lang=’en to speak in the English language, however, the module supports a wide range of languages . Since thetts variable uses the Google API to convert a string to audio, you are going to save the audio in the mp3 file filenameusing tts.save. Lastly, since you don’t want random files to be saved in your folder , you remove the filename using the os.remove(filename)method after the voice bot speaks.

Create the function get_audio():

def get_audio():
 """
This function takes input from the user through the microphone and returns an exception if the command is not understood by the voice bot""" r = sr.Recognizer()
 with sr.Microphone() as source:
 audio = r.listen(source)
 said = ‘’try:
 said = r.recognize_google(audio)
 print(said)except Exception as e:
 print(‘Exception’ + str(e))
 return saidspeak_text(‘welcome to my SQLITE project that stores employee information into a database.’)
speak_text(‘you can enter the data manually or use the voice bot’)`

The get_audio() function accepts the voice command by the user through the microphone. First, you create the variable r to create a recognition object from the speech_recognition module. Inside the statement with sr.Microphone()as source , you create the audio variable which listens to the audio from the microphone, source. Inside the try statement, you use the Google API r.recognize_google (audio) to recognize the audio. It returns an exception when the Google API doesn’t recognize what the user said.

Lastly, you use the speak_text() function to introduce users to the project.

Create the database:

conn = sqlite3.connect('employee.db')

c = conn.cursor()

'''  Create a table called 'employees'  using SQLITE3 to store data'''


c.execute(""" CREATE TABLE IF NOT EXISTS employees (
              employee_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
              first TEXT,
              last TEXT,
              pay INTEGER
            )""")

conn.commit()

In order to create the database, you create the variable conn that saves the database as employee.db .The variable cis set equal toconn.cursor(). The cursor() method allows you to write SQL commands. After that, you can use the execute method in c.execute() to create the table employees. The table stores in the employee_id, first name, last name, and pay. The column employee_id is used to automatically assign a surrogate key to an employee so that users can distinguish among employees if they have the same name. After naming each column, you assign the data type each column is going to hold. Finally, you save the changes made to your table using the conn.commit() method.

Create a set of functions that inserts, deletes, or gets employees from the table:

In order to make changes to your employee table and make things easy, you will have to create functions that inserts an employee, deletes an employee, or gets every employee information from the table.

Assuming you know basic SQLITE3 commands, this should not be too complicated to understand.

Function for inserting an employee:

def insert_emp(emp): 
“”” 
This function inserts the employee data into the table
 “”” 
with conn:
 c.execute(“”” INSERT INTO employees VALUES (?,?,?,?) “””, (None,   emp.first, emp.last, emp.pay)) 
conn.commit()

The function insert_emp takes in emp as its parameter. Since you are inserting an employee into the data, you will use the context manager with conn , where connis the variable in which you saved your file employee.db . The with is a context manager which opens the database, writes some data to it and then closes it when the data is updated. The context manager is useful because if you forget closing the database, then the program runs into errors. Inside thec.executemethod, the SQL command INSERT INTO employees VALUES inserts the tuple values None, emp.first, emp.last, emp.pay into the table. The tuple values emp.first, emp.last, emp.pay are the instances of the employee class while None is the employee_id incremented automatically. In order to save changes, you will write conn.commit() at the end.

Function for deleting an employee:

def delete_emp(id_emp):   
 """    This function deletes the employee from the data    """    with conn:       
 c.execute(""" DELETE FROM employees WHERE employee_id=:employee_id""",
 {'employee_id': id_emp})conn.commit()

The function delete_emp takes in the parameter id_emp . Since employee id is unique to each employee, deleting the employee information through the employee id will ensure you that users don’t delete employees with the same name. Just like the insert_emp function, you use the context manager conn and the method c.execute() . However, this time you’re using the SQL command DELETE FROM employees to delete the id of the employee where the employee_id in the table is equal to the argument passed in the parameter id_emp by the user. To save all changes, you are going to use conn.commit() .

Function to get information of all employees:

def get_employees():  
# gets all employee names   
 """    This function prints out all the employees in the data    """    
c.execute(""" SELECT * FROM employees """)    
return c.fetchall()conn.commit()

The function get_employees() uses the SQL command SELECT * FROM employees inside the method c.execute to get information of all employees. Then, the function returns the SQL command c.fetchall() which prints out everything mentioned in the method c.execute.

Create the employee_info() function:

def employee_info(): while True:speak_text(‘please enter the first name of the employee’) print(‘\nPlease enter the first name of the employee:-’) 
first_name = input() if re.findall(‘[a-z]’, first_name): 
  break while True: 
  speak_text(‘please enter the last name of the employee’)               last_name = input()  print(‘Please enter the last name of the employee:-’)if re.findall(‘[a-z]’, last_name): 
  break while True: 
 speak_text(‘please enter the pay of the employee’)  print(‘Please enter the pay of the employee:-’)  pay_emp = input()
 
if re.findall(“\d”, pay_emp):  
  break user_emp = Employees(first_name, last_name, pay_emp)    
  
  speak_text(‘employee was successfully added to the database’)      return insert_emp(user_emp)

The employee_info() function is the first function to be run in the program. When the function runs, it asks the user to enter the employees first name, last name, and pay. The id of the employee is already incremented by the table. The while loop checks if the user entered in the correct data type for a specific information. After getting the first name, last name, and pay of the employee, the function stores the information in the table employees.

Create the select_program() function:

def select_program(): 
“””
 This function makes the user select from writing manual commands to edit the employee data or use the voice bot“””
print(‘Type in voice bot to use the \”voice bot\” to enter your employee data or type in \”manual\” to enter the data manually :- ‘) 
select = input()if select == ‘voice bot’ or select == ‘ voice bot’: 
 voice_commands()elif select == ‘manual’ or select == ‘ manual’: 
 manual_commands()
 
else: 
 select_program()

The select_program() function asks the user if they want to use the voice bot to enter the employee information or type in employee information manually. Typing in ‘manual’ will run the manual_commands() function, but if the user types in voice_commands() , the voice_commands() function is run.

Create the manual_commands() function:

def manual_commands():
   """    
This function is executed when the user decides to edit the data manually. The if-elif-else statements are used to update, delete or get employees    """print('1. \nWrite \"update\" to add another employee into the data')    
print('2. Write \"delete\" to delete an employee from the data')    print('3. Write \"get employees\" to delete an employee from the data')    
print('update, delete, or get employees....?')    
command = input()if command == 'update' or command == ' update':   employee_info()
   speak_text('Here are the employees up until now')
   print('Here are the employees up until now ' + '\n' +    str(get_employees()))       
   loop_commands_function()elif text == 'delete':   
     
  print("Which employee do you want me to delete. Please select the id of the employee from the data below: " + '\n'
+str(get_employees()))        
 delete_employee = input()        
 delete_emp(delete_employee)        
 speak_text('employee successfully deleted')        
 speak_text('Here are the employees up until now ')   
 print('Here are the employees up until now' + '\n' +    str(get_employees()))        
  
  loop_commands_function()elif command == 'get employees' or command == ' get employees':          speak_text('Here are all the employees')           
  print(str(get_employees()))        
  loop_commands_function()else:        
 print('\n' + 'Please say from one of the following commands')         
 print('\n' + manual_commands())

The manual_commands()function runs when the user decides to enter the employee information manually instead of using the voice bot. The function is pretty simple. It asks for the user if they want to update(insert), delete, or get the information of all employees. If the user enters in ‘update’, then the employee_info() function runs which asks for the first name, last name, and pay of the employee. If the user enters in ‘delete’, the program first prints out all the employees and their ids so that the user deletes the correct employee . Finally, if the user types in ‘get employees’, then the program gets the information of all employees. However, if the user types in wrong commands, then the program tells which commands the program accepts. At the end of each if statement, you put the loop_commands() function which asks the user if they want to continue with the program.

Here’s the loop_commands() function…..

def loop_commands_function(): 
“”” 
This function asks the user if he/she wants to continue with the program “”” 
print(‘Do you want me to continue? Write Yes or No’) 
cont = input() 
if cont == ‘Yes’ or cont == ‘ Yes’: 
 select_program() 
elif cont == ‘No’ or cont == ‘ No’: 
 speak_text(‘thank you for your time’) 
 speak_text(‘goodbye now’) 
 exit() 
else: 
 speak_text(‘please type in yes or no’) print(‘Please type in Yes or No!’)

Final step: Create the voice_commands() function

def voice_commands():   
 """    This function uses the voice bot to insert, delete, or get employees from the data. Similar to the manual_commands() function, but it just uses voice.    """    
speak_text('\nSay update to insert another employee into the data....')    
speak_text('Say delete to delete the employee from the data....')    speak_text('Say get employees to get the information of all employees...')     
print('\n1. Say update to insert the employee into the data')    print('2. Say delete to delete the employee from the data')    print('3. Say get employees to get the information of all employees')     
text = get_audio()     if text == 'update':       
 employee_info()        
 speak_text('Here are the employees up until now ')         
 print('Here are the employees up until now ' + '\n' + str(get_employees()))        
 loop_commands_function()    elif text == 'delete':        
 print("Which employee do you want me to delete. Please select the id of the employee from the data below: " + '\n' +str(get_employees()))        
 delete_employee = input()        
 delete_emp(delete_employee)        
 speak_text('employee successfully deleted')        
 speak_text('Here are the employees up until now ')         
print('Here are the employees up until now' + '\n' + str(get_employees()))        
 loop_commands_function()     elif text == 'get employees':        
 speak_text('\nHere are all the employees')         print(str(get_employees()))        
 loop_commands_function()     else:        
 speak_text('Please say from one of the following commands')         
 voice_commands()

The voice_commands() is the same as the manual_commands() function, however, it requires the user input through the microphone using the get_audio function. Users can say ‘insert ’ to insert an employee, ‘delete’ to delete an employee, or ‘get employees’ to get information of all employees. The speak_text() function notifies the user whenever an employee from the table is inserted or deleted.

Order of functions in the program

It’s essential to execute the function by calling it. Therefore, you will need to call the following functions in the order mentioned to run the program:

employee_info()
select_program()

Final thoughts

I apologize if the program has some weird function names, however, I tried my best to explain the program and its functions. Feel free to use the source code to either add functionality to the program or change the function names.

Interacting with databases is one of the most valuable tool for a data scientist, therefore, if you are in the process of being a data scientist like me, this project can be a great learning experience for you to interact with databases.

Not only was this project a learning opportunity for me but also marked a milestone since it was my first ever project committed to GitHub. However, the journey of any newbie data scientist doesn’t stop here…

If you want more projects like these, please be sure to leave a clap and/or a feedback ;)