Employee Management System using Python and SQLAlchemy

In this blog post, we’ll walk through creating a simple yet functional Employee Management System using Python and SQLAlchemy. We’ll cover setting up the database, implementing CRUD operations, and running the application.

Introduction

Employee management systems are essential for any organization to keep track of their workforce efficiently. In this project, we’ll use Python and SQLAlchemy to build a basic Employee Management System with functionalities to add, remove, promote, and display employees.

Project Setup

Before we begin coding, ensure you have the necessary tools and libraries installed. We need Python, SQLAlchemy, and PyMySQL.

First, install SQLAlchemy and PyMySQL using pip:

pip install sqlalchemy pymysql

Defining the Database Schema

We’ll use SQLAlchemy to define our database schema. SQLAlchemy is an ORM (Object-Relational Mapping) tool for Python, which means it allows us to interact with the database using Python classes and objects instead of writing raw SQL queries.

Start by setting up the database connection and defining the Employee model:

from sqlalchemy import create_engine, Column, Integer, String, MetaData
from sqlalchemy.orm import sessionmaker, declarative_base

DATABASE_URL = "mysql+pymysql://username:password@host/database"

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

class Employee(Base):
    __tablename__ = 'employees'
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(50), nullable=False)
    age = Column(Integer, nullable=False)
    department = Column(String(50), nullable=False)
    position = Column(String(50), nullable=False)

Base.metadata.create_all(bind=engine)

Here, we define the Employee class which represents our employees table in the database.

Implementing CRUD Operations

Next, we implement functions for each of the required operations: add, remove, promote, and display employees.

Add Employee

This function adds a new employee to the database.

def add_employee(session, name, age, department, position):
    new_employee = Employee(name=name, age=age, department=department, position=position)
    session.add(new_employee)
    session.commit()
    print(f"Employee {name} added successfully.")

Remove Employee

This function removes an employee from the database based on their ID.

def remove_employee(session, employee_id):
    employee = session.query(Employee).filter(Employee.id == employee_id).first()
    if employee:
        session.delete(employee)
        session.commit()
        print(f"Employee {employee_id} removed successfully.")
    else:
        print(f"Employee {employee_id} not found.")

Promote Employee

This function promotes an employee by updating their position in the database.

def promote_employee(session, employee_id, new_position):
    employee = session.query(Employee).filter(Employee.id == employee_id).first()
    if employee:
        employee.position = new_position
        session.commit()
        print(f"Employee {employee_id} promoted to {new_position}.")
    else:
        print(f"Employee {employee_id} not found.")

Display Employees

This function displays all employees in the database.

def display_employees(session):
    employees = session.query(Employee).all()
    if employees:
        for emp in employees:
            print(f"ID: {emp.id}, Name: {emp.name}, Age: {emp.age}, Department: {emp.department}, Position: {emp.position}")
    else:
        print("No employees found.")

Creating the Main Program

Now, we create the main program to interact with the user. This program will display a menu and allow the user to choose an operation.

def exit_program():
    print("Exiting program.")
    exit()

def main():
    session = SessionLocal()

    while True:
        print("\nEmployee Management System")
        print("1. Add Employee")
        print("2. Remove Employee")
        print("3. Promote Employee")
        print("4. Display Employees")
        print("5. Exit Program")
        
        choice = input("Enter your choice: ")
        
        if choice == '1':
            name = input("Enter name: ")
            age = int(input("Enter age: "))
            department = input("Enter department: ")
            position = input("Enter position: ")
            add_employee(session, name, age, department, position)
        elif choice == '2':
            employee_id = int(input("Enter employee ID to remove: "))
            remove_employee(session, employee_id)
        elif choice == '3':
            employee_id = int(input("Enter employee ID to promote: "))
            new_position = input("Enter new position: ")
            promote_employee(session, employee_id, new_position)
        elif choice == '4':
            display_employees(session)
        elif choice == '5':
            exit_program()
        else:
            print("Invalid choice. Please try again.")

if __name__ == "__main__":
    main()

Complete Code:

from sqlalchemy import create_engine, Column, Integer, String, MetaData
from sqlalchemy.orm import sessionmaker, declarative_base

DATABASE_URL = "mysql+pymysql://username:password@host/database"

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

class Employee(Base):
    __tablename__ = 'employees'
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(50), nullable=False)
    age = Column(Integer, nullable=False)
    department = Column(String(50), nullable=False)
    position = Column(String(50), nullable=False)

Base.metadata.create_all(bind=engine)

def add_employee(session, name, age, department, position):
    new_employee = Employee(name=name, age=age, department=department, position=position)
    session.add(new_employee)
    session.commit()
    print(f"Employee {name} added successfully.")

def remove_employee(session, employee_id):
    employee = session.query(Employee).filter(Employee.id == employee_id).first()
    if employee:
        session.delete(employee)
        session.commit()
        print(f"Employee {employee_id} removed successfully.")
    else:
        print(f"Employee {employee_id} not found.")

def promote_employee(session, employee_id, new_position):
    employee = session.query(Employee).filter(Employee.id == employee_id).first()
    if employee:
        employee.position = new_position
        session.commit()
        print(f"Employee {employee_id} promoted to {new_position}.")
    else:
        print(f"Employee {employee_id} not found.")

def display_employees(session):
    employees = session.query(Employee).all()
    if employees:
        for emp in employees:
            print(f"ID: {emp.id}, Name: {emp.name}, Age: {emp.age}, Department: {emp.department}, Position: {emp.position}")
    else:
        print("No employees found.")

def exit_program():
    print("Exiting program.")
    exit()

def main():
    session = SessionLocal()

    while True:
        print("\nEmployee Management System")
        print("1. Add Employee")
        print("2. Remove Employee")
        print("3. Promote Employee")
        print("4. Display Employees")
        print("5. Exit Program")

        choice = input("Enter your choice: ")

        if choice == '1':
            name = input("Enter name: ")
            age = int(input("Enter age: "))
            department = input("Enter department: ")
            position = input("Enter position: ")
            add_employee(session, name, age, department, position)
        elif choice == '2':
            employee_id = int(input("Enter employee ID to remove: "))
            remove_employee(session, employee_id)
        elif choice == '3':
            employee_id = int(input("Enter employee ID to promote: "))
            new_position = input("Enter new position: ")
            promote_employee(session, employee_id, new_position)
        elif choice == '4':
            display_employees(session)
        elif choice == '5':
            exit_program()
        else:
            print("Invalid choice. Please try again.")

if __name__ == "__main__":
    main()

Running the Application

To run the application, simply execute the script. You’ll be presented with a menu to manage employees:

python employee_management_system.py

The application will prompt you to add, remove, promote, or display employees, and will save the data directly in your MySQL database.

Conclusion:

In this blog post, we’ve built a simple Employee Management System using Python and SQLAlchemy. This system demonstrates the basics of CRUD operations and how to interact with a database using SQLAlchemy. You can extend this project by adding more features such as search functionality, better error handling, and a graphical user interface.

This tutorial exemplifies the power of Python libraries in creating practical solutions. Whether you’re a developer honing your skills or an individual seeking personalized task management, this guide offers a solid starting point. Moving forward, feel free to explore further customization and feature additions to tailor the app to your specific needs. With creativity and innovation, the potential for enhancement is limitless.

If you have any queries related to this article, then you can ask in the comment section, we will contact you soon, and Thank you for reading this article.

Follow me to receive more useful content:

Instagram | Twitter | Linkedin | Youtube

Thank you