Ashish Yadav
Welcome to this comprehensive guide on building a Student Management System using SQLAlchemy and Python. This tutorial will take you from the basics to advanced features, allowing you to manage student data effectively. We will cover everything from setting up the database, creating models, and implementing CRUD operations to displaying data in a tabular format.
Table of Contents
Show
Prerequisites
Before we dive into the project, make sure you have the following:
- Basic knowledge of Python.
- Understanding of SQL and relational databases.
- Python installed on your machine.
- MySQL server set up.
- Required Python libraries installed (
sqlalchemy,pymysql,tabulate).
Setting Up the Project
Step 1: Install Required Libraries:
Open your terminal and install the required libraries using pip:
pip install sqlalchemy pymysql tabulateStep 2: Set Up MySQL Database
Ensure your MySQL server is running and create a database for this project. You can use the following commands in your MySQL client:
CREATE DATABASE DatabaseName;Step 3: Create the Project Structure
Organize your project files in a structured manner:
student_management_system/
├── main.py
└── models.pyDatabase Configuration and Models
Step 1: Database Configuration
In main.py, set up the database connection using SQLAlchemy:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
DATABASE_URL = "mysql+pymysql://USERNAME:PASSWORD@HOST/DATABASE"
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)Step 2: Define the Student Model
In models.py, define the Student model:
from sqlalchemy import Column, Integer, String, Date
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class Student(Base):
__tablename__ = 'students'
roll_no = Column(Integer, primary_key=True, index=True)
name = Column(String(50), nullable=False)
class_name = Column(String(50), nullable=False)
subjects = Column(String(200), nullable=False)
gender = Column(String(10), nullable=False)
dob = Column(Date, nullable=False)
mobile_no = Column(String(15), nullable=False)
address = Column(String(100), nullable=False)Step 3: Create Tables
Back in main.py, create the tables:
from models import Base, engine
Base.metadata.create_all(bind=engine)Implementing CRUD Operations
Add Student Function
In main.py, implement the function to add a student:
from models import Student
import datetime
def add_student(session, roll_no, name, class_name, subjects, gender, dob, mobile_no, address):
dob_date = datetime.datetime.strptime(dob, '%Y-%m-%d').date()
new_student = Student(roll_no=roll_no, name=name, class_name=class_name, subjects=subjects, gender=gender, dob=dob_date, mobile_no=mobile_no, address=address)
session.add(new_student)
session.commit()
print(f"Student {name} added successfully.")
Remove Student Function
def remove_student(session, roll_no):
student = session.query(Student).filter(Student.roll_no == roll_no).first()
if student:
session.delete(student)
session.commit()
print(f"Student {roll_no} removed successfully.")
else:
print(f"Student {roll_no} not found.")
Update Student Function
def update_student(session, roll_no, **kwargs):
student = session.query(Student).filter(Student.roll_no == roll_no).first()
if student:
for key, value in kwargs.items():
if hasattr(student, key):
if key == 'dob':
value = datetime.datetime.strptime(value, '%Y-%m-%d').date()
setattr(student, key, value)
session.commit()
print(f"Student {roll_no} updated successfully.")
else:
print(f"Student {roll_no} not found.")
Display Students Function
from tabulate import tabulate
def display_students(session):
students = session.query(Student).all()
if students:
table = [[student.roll_no, student.name, student.class_name, student.subjects, student.gender, student.dob, student.mobile_no, student.address] for student in students]
print(tabulate(table, headers=["Roll No", "Name", "Class", "Subjects", "Gender", "DOB", "Mobile No", "Address"], tablefmt="pretty"))
else:
print("No Students Found.")
Search Function
def search_student(session, search_term):
students = session.query(Student).filter((Student.name.ilike(f'%{search_term}%')) | (Student.roll_no == search_term)).all()
if students:
table = [[student.roll_no, student.name, student.class_name, student.subjects, student.gender, student.dob, student.mobile_no, student.address] for student in students]
print(tabulate(table, headers=["Roll No", "Name", "Class", "Subjects", "Gender", "DOB", "Mobile No", "Address"], tablefmt="pretty"))
else:
print("No Students Found.")
Validate Date Inputs
Implement input validation to ensure data integrity:
def validate_date(date_text):
try:
datetime.datetime.strptime(date_text, '%Y-%m-%d')
return True
except ValueError:
return FalseMain Program
Implement the main program loop to interact with the user:
def exit_program():
print("Exiting Program.")
exit()
def main():
session = SessionLocal()
while True:
print("\nStudent Management System")
print("1. Add Student")
print("2. Remove Student")
print("3. Update Student")
print("4. Display Students")
print("5. Exit Program")
print("6. Search Student")
choice = input("Enter your choice: ")
if choice == "1":
roll_no = int(input("Enter Roll No: "))
name = input("Enter Name: ")
class_name = input("Enter Class: ")
subjects = input("Enter Subjects (comma-separated): ")
gender = input("Enter Gender: ")
dob = input("Enter DOB (YYYY-MM-DD): ")
mobile_no = input("Enter Mobile No: ")
address = input("Enter Address: ")
add_student(session, roll_no, name, class_name, subjects, gender, dob, mobile_no, address)
elif choice == "2":
roll_no = int(input("Enter Roll No to remove: "))
remove_student(session, roll_no)
elif choice == "3":
roll_no = int(input("Enter Roll No to update: "))
fields = ['name', 'class_name', 'subjects', 'gender', 'dob', 'mobile_no', 'address']
kwargs = {}
for field in fields:
value = input(f"Enter new {field} (or press Enter to skip): ")
if value:
kwargs[field] = value
update_student(session, roll_no, **kwargs)
elif choice == "4":
display_students(session)
elif choice == "5":
exit_program()
elif choice == "6":
search_term = input("Enter name or roll number to search: ")
search_student(session, search_term)
else:
print("Invalid Choice. Please try again")
if __name__ == "__main__":
main()
Complete Code
For your convenience, here is the complete code for the project:
model.py
# models.py
from sqlalchemy import Column, Integer, String, Date
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class Student(Base):
__tablename__ = 'students'
roll_no = Column(Integer, primary_key=True, index=True)
name = Column(String(50), nullable=False)
class_name = Column(String(50), nullable=False)
subjects = Column(String(200), nullable=False)
gender = Column(String(10), nullable=False)
dob = Column(Date, nullable=False)
mobile_no = Column(String(15), nullable=False)
address = Column(String(100), nullable=False)
main.py
# main.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import datetime
from tabulate import tabulate
from models import Base, Student
DATABASE_URL = "mysql+pymysql://root:root@127.0.0.1:3306/db_testing"
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base.metadata.create_all(bind=engine)
def add_student(session, roll_no, name, class_name, subjects, gender, dob, mobile_no, address):
dob_date = datetime.datetime.strptime(dob, '%Y-%m-%d').date()
new_student = Student(roll_no=roll_no, name=name, class_name=class_name, subjects=subjects, gender=gender, dob=dob_date, mobile_no=mobile_no, address=address)
session.add(new_student)
session.commit()
print(f"Student {name} added successfully.")
def remove_student(session, roll_no):
student = session.query(Student).filter(Student.roll_no == roll_no).first()
if student:
session.delete(student)
session.commit()
print(f"Student {roll_no} removed successfully.")
else:
print(f"Student {roll_no} not found.")
def update_student(session, roll_no, **kwargs):
student = session.query(Student).filter(Student.roll_no == roll_no).first()
if student:
for key, value in kwargs.items():
if hasattr(student, key):
if key == 'dob':
value = datetime.datetime.strptime(value, '%Y-%m-%d').date()
setattr(student, key, value)
session.commit()
print(f"Student {roll_no} updated successfully.")
else:
print(f"Student {roll_no} not found.")
def display_students(session):
students = session.query(Student).all()
if students:
table = [[student.roll_no, student.name, student.class_name, student.subjects, student.gender, student.dob, student.mobile_no, student.address] for student in students]
print(tabulate(table, headers=["Roll No", "Name", "Class", "Subjects", "Gender", "DOB", "Mobile No", "Address"], tablefmt="pretty"))
else:
print("No Students Found.")
def search_student(session, search_term):
students = session.query(Student).filter((Student.name.ilike(f'%{search_term}%')) | (Student.roll_no == search_term)).all()
if students:
table = [[student.roll_no, student.name, student.class_name, student.subjects, student.gender, student.dob, student.mobile_no, student.address] for student in students]
print(tabulate(table, headers=["Roll No", "Name", "Class", "Subjects", "Gender", "DOB", "Mobile No", "Address"], tablefmt="pretty"))
else:
print("No Students Found.")
def validate_date(date_text):
try:
datetime.datetime.strptime(date_text, '%Y-%m-%d')
return True
except ValueError:
return False
def exit_program():
print("Exiting Program.")
exit()
def main():
session = SessionLocal()
while True:
print("\nStudent Management System")
print("1. Add Student")
print("2. Remove Student")
print("3. Update Student")
print("4. Display Students")
print("5. Exit Program")
print("6. Search Student")
choice = input("Enter your choice: ")
if choice == "1":
roll_no = int(input("Enter Roll No: "))
name = input("Enter Name: ")
class_name = input("Enter Class: ")
subjects = input("Enter Subjects (comma-separated): ")
gender = input("Enter Gender: ")
dob = input("Enter DOB (YYYY-MM-DD): ")
if not validate_date(dob):
print("Invalid date format. Please use YYYY-MM-DD.")
continue
mobile_no = input("Enter Mobile No: ")
address = input("Enter Address: ")
add_student(session, roll_no, name, class_name, subjects, gender, dob, mobile_no, address)
elif choice == "2":
roll_no = int(input("Enter Roll No to remove: "))
remove_student(session, roll_no)
elif choice == "3":
roll_no = int(input("Enter Roll No to update: "))
fields = ['name', 'class_name', 'subjects', 'gender', 'dob', 'mobile_no', 'address']
kwargs = {}
for field in fields:
value = input(f"Enter new {field} (or press Enter to skip): ")
if value:
if field == 'dob' and not validate_date(value):
print("Invalid date format. Please use YYYY-MM-DD.")
continue
kwargs[field] = value
update_student(session, roll_no, **kwargs)
elif choice == "4":
display_students(session)
elif choice == "5":
exit_program()
elif choice == "6":
search_term = input("Enter name or roll number to search: ")
search_student(session, search_term)
else:
print("Invalid Choice. Please try again")
if __name__ == "__main__":
main()
Conclusion:
Congratulations! You have successfully built a comprehensive Student Management System using SQLAlchemy and Python. This system allows you to add, remove, update, and display student records efficiently. You have also learned to implement advanced features like search functionality and input validation.
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
