D Dev Duniya
Back to Journal
6 min read

Employee Management System using Python and SQLAlchemy

Ashish Yadav

Ashish Yadav

Published on Dev Duniya
Keep exploring
Compare the details before you buy.

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

Keep reading

12 latest articles

The newest stories from Dev Duniya, excluding the article you are reading.

All articles →

From the product catalog

Explore device specifications

Specification cards update automatically from published catalog products, grouped by brand.

Apple specifications

Explore Apple devices →
Apple Apple iPad mini (2024)
Apple Apple iPad mini (2024) specifications

Hold massive power in the palm of your hand with the Apple iPad mini (2024), driven by the powerhouse A17 Pro chip for console-level gaming and advanced Apple Intelligence features. Its gorgeous 8.3-inch Liquid Retina display with True Tone delivers rich, immersive visuals in an ultra-portable, one-handed design. Capture brilliant photos or scan documents with the 12MP Wide back camera, and stay in frame during calls with the 12MP Ultra Wide front camera. With support for the Apple Pencil Pro and fast USB-C connectivity, it is the ultimate digital sketchbook and travel companion.

Size (inches)
7.69 x 5.31 x 0.25
Weight (pounds)
0.65
Resolution (pixels)
2266-by-1488 resolution at 326 ppi
Features
Liquid Retina display LED backlit Multi-Touch display with IPS technology Wide color (P3) True Tone Fingerprint-resistant oleophobic coating Fully laminated Antireflective coating 500 nits brightness Supports Apple Pencil Pro Supports Apple Pencil (USB-C)
See all specifications →
Apple Apple iPad Air 11-inch (M3) 2025
Apple Apple iPad Air 11-inch (M3) 2025 specifications

Elevate your everyday productivity with the Apple iPad Air 11-inch (M3) 2025, featuring the incredibly efficient M3 chip for lightning-fast apps and AI-driven features. The vibrant 11-inch Liquid Retina display offers an expansive canvas for taking notes, editing photos, and streaming in stunning high resolution. It features a landscape 12MP front camera optimized for video conferencing and fast Wi-Fi 6E for uninterrupted connectivity. Compatible with the Apple Pencil Pro and Magic Keyboard, this versatile tablet effortlessly bridges the gap between work and play.

Size (inches)
9.74 x 7.02 x 0.24
Weight (pounds)
1.01
Display size (inches)
10.86-inch (diagonal) LED backlit Multi‑Touch display with IPS technology
Resolution (pixels)
2360-by-1640-pixel resolution at 264 ppi
See all specifications →
Apple Apple iPad Air 13-inch (M4) 2026
Apple Apple iPad Air 13-inch (M4) 2026 specifications

Maximize your workspace with the expansive Apple iPad Air 13-inch (M4) 2026, combining a massive Liquid Retina display with the raw power of the M4 chip. Whether you are running complex spreadsheets, editing 4K video, or multitasking across multiple apps, it delivers uncompromising speed and fluidity. The relocated landscape 12MP camera ensures you look your best on video calls, while Wi-Fi 7 guarantees lightning-fast downloads. Lightweight yet incredibly powerful, it gives you pro-level screen real estate at an exceptional value.

Size (inches)
11.05 x 8.46 x 0.24
Weight (pounds)
1.36
Display size (inches)
12.9-inch (diagonal) LED backlit Multi‑Touch display with IPS technology
Resolution (pixels)
2732-by-2048-pixel resolution at 264 ppi
See all specifications →
Apple Apple iPad mini (A20 Pro) 2026
Apple Apple iPad mini (A20 Pro) 2026 specifications

Experience boundary-pushing portable performance with the Apple iPad mini (A20 Pro) 2026, featuring the next-generation A20 Pro chip for desktop-class gaming and generative AI. The edge-to-edge 8.3-inch Liquid Retina display now offers enhanced brightness and fluid responsiveness in a perfectly pocketable form factor. Capture stunning 4K video with the upgraded 12MP camera system and enjoy lightning-fast wireless speeds with Wi-Fi 7 support. Designed for creators and readers on the move, it remains the undisputed king of compact, high-performance tablets.

Size (inches)
7.69 x 5.22 x 0.21
Weight (pounds)
0.58
Resolution (pixels)
2316-by-1512 resolution at 326 ppi
Features
Super Retina XDR display OLED Wide color (P3) True Tone Fingerprint-resistant oleophobic coating Fully laminated Antireflective coating Nano-texture display glass option on 256GB and 512GB models SDR brightness: 600 nits max XDR brightness: 600 nits max full screen, 1200 nits peak (HDR content only) 1 nit minimum brightness Supports Apple Pencil Pro Supports Apple Pencil (USB-C) Apple Pencil hover
See all specifications →
Apple Apple iPad Air 11-inch (M4) 2026
Apple Apple iPad Air 11-inch (M4) 2026 specifications

Step into the future of thin-and-light computing with the Apple iPad Air 11-inch (M4) 2026, powered by the advanced M4 chip for elite multitasking and Apple Intelligence. The stunning 11-inch Liquid Retina display provides gorgeous colors and crisp text, making it perfect for creative projects, reading, and entertainment. Stay seamlessly connected with cutting-edge Wi-Fi 7 and enjoy all-day battery life that keeps up with your busiest days. With seamless integration for the Apple Pencil Pro, it is the ultimate tool for students and professionals looking for premium performance.

Size (inches)
9.75 x 7.03 x 0.24
Weight (pounds)
1.03
Display size (inches)
10.86-inch (diagonal) LED backlit Multi‑Touch display with IPS technology
Resolution (pixels)
2360-by-1640-pixel resolution at 264 ppi
See all specifications →
Apple Apple iPad Pro 13-inch (2024)
Apple Apple iPad Pro 13-inch (2024) specifications

Experience unprecedented power with the Apple iPad Pro 13-inch (2024), featuring the groundbreaking M4 chip for blazing fast performance and industry-leading AI capabilities. The revolutionary Tandem OLED Ultra Retina XDR display delivers 1000 nits of sustained brightness and perfect blacks for a cinematic viewing experience. Encased in an impossibly thin 5.1mm design, it offers Thunderbolt connectivity and an upgraded landscape camera system. Perfect for demanding creative professionals, it provides a flawless canvas when paired with the Apple Pencil Pro.

Size (inches)
11.09 x 8.48 x 0.206
Weight (pounds)
1.286
Resolution (pixels)
2752-by-2064-pixel resolution at 264 ppi
Features
Ultra Retina XDR display Tandem OLED ProMotion technology with adaptive refresh rates from 10Hz to 120Hz Wide color (P3) True Tone Fingerprint-resistant oleophobic coating Fully laminated Antireflective coating Nano-texture display glass SDR brightness: 1000 nits max XDR brightness: 1000 nits max full screen, 1600 nits peak (HDR content only) Supports Apple Pencil Pro Supports Apple Pencil (USB-C) Apple Pencil hover
See all specifications →
Apple Apple iPad Pro 11-inch (M5) 2025
Apple Apple iPad Pro 11-inch (M5) 2025 specifications

Unleash professional-grade performance on the go with the Apple iPad Pro 11-inch (M5) 2025, supercharged by the next-generation Apple M5 silicon. Experience staggering visuals on the 11-inch Tandem OLED Ultra Retina XDR display, offering 120Hz ProMotion and flawless color accuracy for creative work. It boasts ultra-fast Thunderbolt 4 connectivity, Wi-Fi 7, and advanced 12MP cameras with LiDAR for immersive AR applications. Paired with the Apple Pencil Pro and Magic Keyboard, it instantly transforms into a versatile, high-end workstation that fits effortlessly into your bag.

Size (inches)
9.83 x 6.99 x 0.21
Weight (pounds)
0.98
Resolution (pixels)
2420-by-1668-pixel resolution at 264 ppi
Features
Ultra Retina XDR display Tandem OLED ProMotion technology with adaptive refresh rates from 10Hz to 120Hz Wide color (P3) True Tone Fingerprint-resistant oleophobic coating Fully laminated Antireflective coating Nano-texture display glass option on 1TB and 2TB models SDR brightness: 1000 nits max XDR brightness: 1000 nits max full screen, 1600 nits peak (HDR content only) 1 nit minimum brightness 2,000,000:1 contrast ratio Supports Apple Pencil Pro Supports Apple Pencil (USB-C) Apple Pencil hover
See all specifications →
Apple Apple iPad Pro 13-inch (M5) 2025
Apple Apple iPad Pro 13-inch (M5) 2025 specifications

Push the boundaries of mobile computing with the Apple iPad Pro 13-inch (M5) 2025, featuring the revolutionary M5 chip with hardware-accelerated ray tracing and unparalleled AI processing. Its breathtaking 13-inch Tandem OLED Ultra Retina XDR display delivers extreme brightness and infinite contrast for true-to-life HDR workflows. Designed for professionals, it supports Wi-Fi 7, Thunderbolt connectivity, and the advanced Apple Pencil Pro for pixel-perfect precision. Encased in an impossibly thin aluminum chassis, it is the ultimate portable powerhouse for 3D rendering, video editing, and beyond.

Size (inches)
11.09 x 8.48 x 0.2
Weight (pounds)
1.28
Resolution (pixels)
2752-by-2064-pixel resolution at 264 ppi
Features
Ultra Retina XDR display Tandem OLED ProMotion technology with adaptive refresh rates from 10Hz to 120Hz Wide color (P3) True Tone Fingerprint-resistant oleophobic coating Fully laminated Antireflective coating Nano-texture display glass option on 1TB and 2TB models SDR brightness: 1000 nits max XDR brightness: 1000 nits max full screen, 1600 nits peak (HDR content only) 1 nit minimum brightness 2,000,000:1 contrast ratio Supports Apple Pencil Pro Supports Apple Pencil (USB-C) Apple Pencil hover
See all specifications →

Google specifications

Explore Google devices →
Google Google Pixel 12
Google Google Pixel 12 specifications

Upgrade to the Google Pixel 12, an advanced 5G AI smartphone. Experience seamless performance powered by the custom Google Tensor G7 processor. Enjoy a stunning 6.3-inch OLED Actua display and take flawless photos with the powerful 50MP dual-camera system. Built with an adaptive all-day battery and seamless Google Gemini AI integration, it is the perfect unlocked device for everyday brilliance.

Size (inches)
6.02 x 2.83 x 0.34
Weight (ounces)
6.95
Display size (inches)
6.3-inch (diagonal) OLED display
Resolution (pixels)
1080 x 2424 pixels
See all specifications →
Google Google Pixel 12 Pro
Google Google Pixel 12 Pro specifications

Discover the new Google Pixel 12 Pro, the ultimate 5G AI smartphone. Powered by the next-generation Google Tensor G7 chip and built-in Gemini AI, it delivers blazing-fast performance. Capture professional-grade photos with the advanced 50MP Pro Triple Camera system featuring 5x optical zoom. Complete with a stunning 6.3-inch Super Actua display, all-day adaptive battery, and a premium titanium frame. Upgrade to the smartest Android phone today.

Size (inches)
6.02 x 2.83 x 0.33
Weight (ounces)
7.3
Display size (inches)
6.31-inch LTPO OLED display
Resolution (pixels)
2856 x 1280 pixels
See all specifications →
Google Google Pixel 12 Pro XL
Google Google Pixel 12 Pro XL specifications

Go bigger with the Google Pixel 12 Pro XL, the ultimate large-screen 5G AI smartphone. It boasts a massive 6.8-inch OLED display and the lightning-fast Google Tensor G7 chip. Capture master-level photography using the 50MP Pro Triple Camera setup. Equipped with a massive 5500mAh battery for extended endurance and fast 67W charging, this unlocked flagship redefines premium power.

Size (inches)
6.43 x 3.07 x 0.34
Weight (ounces)
8.18
Display size (inches)
6.8-inch LTPO OLED Super Actua display
Resolution (pixels)
2992 x 1344 pixels
See all specifications →
Google Google Pixel 12 Pro Fold
Google Google Pixel 12 Pro Fold specifications

Transform your mobile experience with the Google Pixel 12 Pro Fold. This unlocked 5G AI foldable smartphone unfolds into a gorgeous 8-inch dual-UTG inner OLED display for next-level multitasking and entertainment. Powered by the Google Tensor G7 chip, it features flexible triple-camera photography, advanced Gemini AI, and a highly durable folding hinge design.

Size (inches) - Unfolded
6.24 x 5.64 x 0.16
Size (inches) - Folded
6.24 x 2.87 x 0.35
Weight (ounces)
7.58
Main Display
8.0-inch Foldable LTPO OLED, 120Hz
See all specifications →

Samsung specifications

Explore Samsung devices →
Samsung Samsung Galaxy Tab A11+ 5G
Samsung Samsung Galaxy Tab A11+ 5G specifications

Enjoy endless entertainment and connectivity with the Samsung Galaxy Tab A11+ 5G, featuring a vibrant, immersive display perfect for movies, gaming, and browsing. Powered by a responsive octa-core processor and lightning-fast 5G, you can stream high-definition content and download files without missing a beat. It features a long-lasting battery with fast charging capabilities, ensuring your tablet is always ready when you need it. Designed with a sleek, durable metal body and expandable storage, it is the perfect everyday multimedia hub for the whole family.

Size (inches)
6.64 x 10.12 x 0.27
Weight (ounces)
17
Display size (inches)
11
Resolution (pixels)
1920 x 1200 (WUXGA)
See all specifications →
Samsung Samsung Galaxy Tab S12+ 5G
Samsung Samsung Galaxy Tab S12+ 5G specifications

Redefine premium Android performance with the Samsung Galaxy Tab S12+ 5G, boasting a spectacular Dynamic AMOLED 2X display with a buttery-smooth 120Hz refresh rate. Powered by a flagship Snapdragon processor, it handles heavy multitasking, video editing, and demanding mobile games with absolute ease. The ultra-low latency S Pen is included for a flawless writing and drawing experience, backed by next-gen Galaxy AI tools. Featuring blazing-fast 5G connectivity, an ultra-slim design, and a massive battery, it is the ultimate creative and professional powerhouse.

Size (inches)
7.39 x 11.4 x 0.21
Weight (ounces)
19.61
Display size (inches)
12.6
Resolution (pixels)
2800 x 1752 (WQXGA+), 262 PPI
See all specifications →
Samsung Samsung Galaxy Tab S10 FE 5G
Samsung Samsung Galaxy Tab S10 FE 5G specifications

Experience seamless productivity and entertainment with the Samsung Galaxy Tab S10 FE 5G, featuring a vibrant 10.9-inch 90Hz display and a highly capable Exynos processor. This premium 5G Android tablet includes the versatile S Pen in the box, perfectly optimized for note-taking, digital art, and advanced Galaxy AI features. Built for everyday durability, it boasts an IP68 dust and water-resistant rating so you can confidently use it anywhere. Stay powered through intense work or study sessions with a massive 8,000mAh battery and lightning-fast 45W charging capabilities.

Size (inches)
10.01 x 6.53 x 0.24
Weight (ounces)
17.64
Display size (inches)
10.9
Resolution (pixels)
2304 x 1440
See all specifications →