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 pymysqlDefining 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.pyThe 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
