Custom Management Commands

Relevant source files

The following files were used as context for generating this wiki page:

• finder/management/commands/load_rooms_excel.py
• finder/management/commands/load_timetable.py
• finder/models.py
• generate_sample_data.py
• README.md
• finder/views.py

The Room Finder application includes custom Django management commands to facilitate database population from external Excel files. These commands are essential for initial setup and bulk data loading, providing a command-line interface to import room and class schedule information. They serve as a robust method for populating the database, complementing the UI-based upload functionality.

The primary purpose of these commands is to parse structured data from .xlsx files and map it to the corresponding Django models, Room and ClassSchedule. This automates the process of setting up the application with a large, realistic dataset.

Sources: README.md:120-122, finder/management/commands/load_rooms_excel.py, finder/management/commands/load_timetable.py

load_rooms_excel Command

This command is responsible for loading room inventory into the database from an Excel file. It clears all existing room data before importing new records to ensure a fresh state.

Sources: finder/management/commands/load_rooms_excel.py:7-10

Execution Flow

The command follows a clear sequence to process the rooms.xlsx file. It performs validation at each step to ensure data integrity.

This diagram illustrates the step-by-step process of the load_rooms_excel command, from initiation to completion, including error handling and data validation.

Sources: finder/management/commands/load_rooms_excel.py:10-50

Expected Data Format (rooms.xlsx)

The command expects the Excel file to be located at sample_data/rooms.xlsx and contain the following columns:

Column	Type	Description	Example
Room	String	The unique name or number of the room.	HH101
Building	String	The name of the building where the room is located.	Hephaestus Hall
capacity	Integer	The seating capacity of the room.	30
amenities	JSON String	A JSON-formatted list of amenities available in the room.	["Projector", "Whiteboard"]
photo_url	URL String	A URL to a photo of the room.	https://images.unsplash.com/...

Sources: finder/management/commands/load_rooms_excel.py:18-21, generate_sample_data.py:40-41

Data Model Interaction

This command directly interacts with the Room model. For each valid row in the Excel file, it creates or updates a Room instance. The update_or_create method uses the name and building fields to uniquely identify a room.

# finder/management/commands/load_rooms_excel.py:41-48
Room.objects.update_or_create(
    name=room_name,
    building=building,
    defaults={
        "capacity": capacity,
        "amenities": amenities,
        "photo_url": photo_url
    }
)

The Room model is defined with a unique constraint on the combination of name and building.

Sources: finder/models.py:6-26, finder/management/commands/load_rooms_excel.py:41-48

load_timetable Command

This command loads class schedules from sample_data/timetable.xlsx. It depends on the Room data already being present in the database, as it links each schedule to a specific room. The command clears all existing ClassSchedule entries before loading new ones.

Sources: finder/management/commands/load_timetable.py:11-12

Execution Flow

The process involves complex parsing, especially for the Day/Time field, and requires looking up foreign key relationships.

This diagram shows the workflow for loading timetable data, highlighting the regular expression parsing and database lookups required.

Sources: finder/management/commands/load_timetable.py:11-128

Expected Data Format (timetable.xlsx)

The command expects the sample_data/timetable.xlsx file with the following structure:

Column	Type	Description	Example
Course	String	The course code.	CS101
Subject	String	The full subject name.	Intro to Programming
Day/Time	String	A formatted string for the schedule.	Mon 08:00-09:30
Room	String	The name of the room. Must match a Room record.	HH101
Building	String	The building of the room. Must match a Room record.	Hephaestus Hall
Lecturer	String	The name of the lecturer.	Dr. Smith

Sources: finder/management/commands/load_timetable.py:24-31, generate_sample_data.py:68-69

Day/Time Parsing

A key feature of this command is its flexible parsing of the Day/Time column using a regular expression. This allows it to handle different time formats.

• Regex: (?P<day>\w+)\s+(?P<start>\d{1,2}:\d{2}\s*(?:AM|PM)?)[-–](?P<end>\d{1,2}:\d{2}\s*(?:AM|PM)?)
• Supported Formats: Mon 08:00-09:30, Tuesday 8:00 AM-9:30 AM
• Day Mapping: The command also maps full day names (e.g., "Monday") to their three-letter abbreviations (e.g., "Mon") to match the choices defined in the ClassSchedule model.

Sources: finder/management/commands/load_timetable.py:46-52, finder/management/commands/load_timetable.py:54-64

Data Model Relationships

The management commands populate two core models, Room and ClassSchedule, which are linked by a one-to-many relationship. Each ClassSchedule must belong to a Room.

This ER diagram shows that a Room can have many ClassSchedule entries, but each ClassSchedule is associated with exactly one Room.

Sources: finder/models.py

Usage

To execute the commands, run the following from the project's root directory in your shell. It is recommended to run load_rooms_excel before load_timetable to ensure all rooms exist before schedules are assigned to them.

1. Load Rooms:

   python manage.py load_rooms_excel
   

2. Load Timetable:

   python manage.py load_timetable
   

These commands expect the .xlsx files to be present in the sample_data/ directory. A helper script, generate_sample_data.py, is provided to create these files with realistic data.

Sources: README.md:162-164, generate_sample_data.py

Summary

The custom management commands load_rooms_excel and load_timetable are critical utilities for the Room Finder project. They provide a reliable and scriptable way to populate the database from standardized Excel files, handling data validation, parsing, and relationship mapping. This system is fundamental for setting up the application, testing, and managing bulk data imports efficiently.