Employee Management System using Python

Last Updated : 19 Jun, 2024

The task is to create a Database-driven Employee Management System in Python that will store the information in the MySQL Database. The script will contain the following operations :

Add Employee
Remove Employee
Promote Employee
Display Employees

The idea is that we perform different changes in our Employee Record by using different functions for example the Add_Employee will insert a new row in our Employee, also, we will create a Remove Employee Function which will delete the record of any particular existing employee in our Employee table. This System works on the concepts of taking the information from the database making required changes in the fetched data and applying the changes in the record which we will see in our Promote Employee System. We can also have information about all the existing employees by using the Display Employee function. The main advantage of connecting our program to the database is that the information becomes lossless even after closing our program a number of times.

Getting Started

For creating the Employee Management System in Python that uses MySQL database we need to connect Python with MySQL.

For making a connection we need to install mysqlconnector which can be done by writing the following command in the command prompt on Windows.

pip install mysql-connector-python

Now after the successful installation of the MySQL connector, we can connect MySQL with Python which can be done by writing the following code

Python

import mysql.connector


con = mysql.connector.connect(
    host="localhost", user="root", password="password", database="emp")

Now we are Done with the connections, so we can focus on our Employee Management System

Table in Use:

The idea is that we keep all the information about the Employee in the above table and manipulate the table whenever required. So now we will look at the working of each operation in detail.

Check Employee Function

The check employee function takes employee id as a parameter and checks whether any employee with given id exists in the employee details record or not. For checking this it uses cursor.rowcount() function which counts the number of rows that match with given details. It is a utility function, and we will see its use in later operations like Add employee function, etc.,

Python

def check_employee(employee_id):
    # Query to select all rows from the employees table where id matches
    sql = 'SELECT * FROM employees WHERE id=%s'

    # Making cursor buffered to make rowcount method work properly
    cursor = con.cursor(buffered=True)
    data = (employee_id,)

    # Executing the SQL Query
    cursor.execute(sql, data)

    # Fetch the first row to check if employee exists
    employee = cursor.fetchone()

    # Closing the cursor
    cursor.close()

    # If employee is found, return True, else return False
    return employee is not None

Add Employee Function

The Add Employee function will ask for the Employee Id and uses the check employee function to check whether the employee to be added already exist in our record or not if employee details do not already exist then it asks for details of the employee to be added like Employee Name, Post of Employee and Salary of the employee. Now after getting all such details from the user of that system it simply inserts the information in our Employee details table.

Python

def add_employee():
    Id = input("Enter Employee Id: ")

    # Checking if Employee with given Id already exists
    if check_employee(Id):
        print("Employee already exists. Please try again.")
        return
    
    else:
        Name = input("Enter Employee Name: ")
        Post = input("Enter Employee Post: ")
        Salary = input("Enter Employee Salary: ")

        # Inserting Employee details into the employees table
        sql = 'INSERT INTO employees (id, name, position, salary) VALUES (%s, %s, %s, %s)'
        data = (Id, Name, Post, Salary)
        cursor = con.cursor()

        try:
            # Executing the SQL Query
            cursor.execute(sql, data)

            # Committing the transaction
            con.commit()
            print("Employee Added Successfully")
        
        except mysql.connector.Error as err:
            print(f"Error: {err}")
            con.rollback()
        
        finally:
            # Closing the cursor
            cursor.close()

Remove Employee Function

The Remove Employee Function will simply ask for Id of the employee to be removed because Id is Primary key in our Employee Details Record as there can be two employees with the same name, but they must have a unique id. The Remove Employee function uses the check employee function to check whether the employee to be removed exists in our record or not if employee details exist then after getting a valid employee id it deletes the record corresponding to that employee id.

Python

def remove_employee():
    Id = input("Enter Employee Id: ")

    # Checking if Employee with given Id exists
    if not check_employee(Id):
        print("Employee does not exist. Please try again.")
        return
    
    else:
        # Query to delete employee from the employees table
        sql = 'DELETE FROM employees WHERE id=%s'
        data = (Id,)
        cursor = con.cursor()

        try:
            # Executing the SQL Query
            cursor.execute(sql, data)

            # Committing the transaction
            con.commit()
            print("Employee Removed Successfully")
        
        except mysql.connector.Error as err:
            print(f"Error: {err}")
            con.rollback()
        
        finally:
            # Closing the cursor
            cursor.close()

Promote Employee Function

The Promote Employee function will ask for the Employee Id and uses the check employee function to check whether the employee to be Promoted exist in our record or not if employee details exist then it will ask for the amount by which his salary is to be increased. After getting the valid details it increases the salary of the employee with the given id by the given amount.

Python

def promote_employee():
    Id = input("Enter Employee's Id: ")

    # Checking if Employee with given Id exists
    if not check_employee(Id):
        print("Employee does not exist. Please try again.")
        return
    
    else:
        try:
            Amount = float(input("Enter increase in Salary: "))

            # Query to Fetch Salary of Employee with given Id
            sql_select = 'SELECT salary FROM employees WHERE id=%s'
            data = (Id,)
            cursor = con.cursor()

            # Executing the SQL Query
            cursor.execute(sql_select, data)

            # Fetching Salary of Employee with given Id
            current_salary = cursor.fetchone()[0]
            new_salary = current_salary + Amount

            # Query to Update Salary of Employee with given Id
            sql_update = 'UPDATE employees SET salary=%s WHERE id=%s'
            data_update = (new_salary, Id)

            # Executing the SQL Query to update salary
            cursor.execute(sql_update, data_update)

            # Committing the transaction
            con.commit()
            print("Employee Promoted Successfully")

        except (ValueError, mysql.connector.Error) as e:
            print(f"Error: {e}")
            con.rollback()

        finally:
            # Closing the cursor
            cursor.close()

Display Employees Function

The Display Employees function is simply a select query of SQL which fetches all the records stored in the employee details table and prints them line by line.

Python

def display_employees():
    try:
        # Query to select all rows from the employees table
        sql = 'SELECT * FROM employees'
        cursor = con.cursor()

        # Executing the SQL Query
        cursor.execute(sql)

        # Fetching all details of all the Employees
        employees = cursor.fetchall()
        for employee in employees:
            print("Employee Id : ", employee[0])
            print("Employee Name : ", employee[1])
            print("Employee Post : ", employee[2])
            print("Employee Salary : ", employee[3])
            print("------------------------------------")

    except mysql.connector.Error as err:
        print(f"Error: {err}")

    finally:
        # Closing the cursor
        cursor.close()

Menu Function

The Menu function displays the menu to the user and asks the user to enter his choice for performing operations like Add employee, Remove employee, etc.

Python

def menu():
    while True:
        print("\nWelcome to Employee Management Record")
        print("Press:")
        print("1 to Add Employee")
        print("2 to Remove Employee")
        print("3 to Promote Employee")
        print("4 to Display Employees")
        print("5 to Exit")
        
        # Taking choice from user
        ch = input("Enter your Choice: ")

        if ch == '1':
            add_employee()
        elif ch == '2':
            remove_employee()
        elif ch == '3':
            promote_employee()
        elif ch == '4':
            display_employees()
        elif ch == '5':
            print("Exiting the program. Goodbye!")
            break
        else:
            print("Invalid Choice! Please try again.")

Complete Code:

Python

import mysql.connector

# Database connection
con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="emp"
)

cursor = con.cursor()

# Function to check if an employee exists
def check_employee(employee_id):
    sql = 'SELECT * FROM employees WHERE id=%s'
    cursor.execute(sql, (employee_id,))
    return cursor.rowcount == 1

# Function to add an employee
def add_employee():
    Id = input("Enter Employee Id: ")
    if check_employee(Id):
        print("Employee already exists. Please try again.")
        return
    
    Name = input("Enter Employee Name: ")
    Post = input("Enter Employee Post: ")
    Salary = input("Enter Employee Salary: ")

    sql = 'INSERT INTO employees (id, name, position, salary) VALUES (%s, %s, %s, %s)'
    data = (Id, Name, Post, Salary)
    try:
        cursor.execute(sql, data)
        con.commit()
        print("Employee Added Successfully")
    except mysql.connector.Error as err:
        print(f"Error: {err}")
        con.rollback()

# Function to remove an employee
def remove_employee():
    Id = input("Enter Employee Id: ")
    if not check_employee(Id):
        print("Employee does not exist. Please try again.")
        return
    
    sql = 'DELETE FROM employees WHERE id=%s'
    data = (Id,)
    try:
        cursor.execute(sql, data)
        con.commit()
        print("Employee Removed Successfully")
    except mysql.connector.Error as err:
        print(f"Error: {err}")
        con.rollback()

# Function to promote an employee
def promote_employee():
    Id = input("Enter Employee's Id: ")
    if not check_employee(Id):
        print("Employee does not exist. Please try again.")
        return
    
    try:
        Amount = float(input("Enter increase in Salary: "))

        sql_select = 'SELECT salary FROM employees WHERE id=%s'
        cursor.execute(sql_select, (Id,))
        current_salary = cursor.fetchone()[0]
        new_salary = current_salary + Amount

        sql_update = 'UPDATE employees SET salary=%s WHERE id=%s'
        cursor.execute(sql_update, (new_salary, Id))
        con.commit()
        print("Employee Promoted Successfully")

    except (ValueError, mysql.connector.Error) as e:
        print(f"Error: {e}")
        con.rollback()

# Function to display all employees
def display_employees():
    try:
        sql = 'SELECT * FROM employees'
        cursor.execute(sql)
        employees = cursor.fetchall()
        for employee in employees:
            print("Employee Id : ", employee[0])
            print("Employee Name : ", employee[1])
            print("Employee Post : ", employee[2])
            print("Employee Salary : ", employee[3])
            print("------------------------------------")

    except mysql.connector.Error as err:
        print(f"Error: {err}")

# Function to display the menu
def menu():
    while True:
        print("\nWelcome to Employee Management Record")
        print("Press:")
        print("1 to Add Employee")
        print("2 to Remove Employee")
        print("3 to Promote Employee")
        print("4 to Display Employees")
        print("5 to Exit")
        
        ch = input("Enter your Choice: ")

        if ch == '1':
            add_employee()
        elif ch == '2':
            remove_employee()
        elif ch == '3':
            promote_employee()
        elif ch == '4':
            display_employees()
        elif ch == '5':
            print("Exiting the program. Goodbye!")
            break
        else:
            print("Invalid Choice! Please try again.")

if __name__ == "__main__":
    menu()

Output

How to make a Python auto clicker?

srivastavaharshit848

Improve

Article Tags :

Practice Tags :

python

Employee Management System using Python

Getting Started

Check Employee Function

Add Employee Function

Remove Employee Function

Promote Employee Function

Display Employees Function

Menu Function

Similar Reads

Projects for Beginners

Projects for Intermediate

Web Scraping

Automating boring Stuff Using Python

Tkinter Projects

Turtle Projects

OpenCV Projects

Python Django Projects

Python Text to Speech and Vice-Versa