lg_agent.database_utils Namespace Reference

Functions
str	get_courseID_by_code (sqlite3.Connection conn, str course_code)
str	get_courseID_by_title (sqlite3.Connection conn, str course_title)
list	get_coops (sqlite3.Connection conn)
list	get_courseIDs_by_filters (sqlite3.Connection conn, schemas.CourseFilters filters)
dict	get_course_info_by_id (sqlite3.Connection conn, str course_id)
str	get_course_description_by_id (sqlite3.Connection conn, str course_id)
list	get_sectionIDs_by_filters (sqlite3.Connection conn, schemas.SectionFilters filters)
dict	get_section_info_by_id (sqlite3.Connection conn, str section_id)
list	get_upcoming_events (sqlite3.Connection conn)
list	get_event_dates_by_name (sqlite3.Connection conn, str event_name)
dict	get_student_basic_info (sqlite3.Connection conn, int student_id)
str	get_advisor_email (sqlite3.Connection conn, str advisor_name)
list	get_student_course_history (sqlite3.Connection conn, int student_id)
list	get_student_interests (sqlite3.Connection conn, int student_id)
list	get_student_tracked_sections (sqlite3.Connection conn, int student_id)
list	get_program_requirements_by_title (sqlite3.Connection conn, str program_title)
str	insert_student_interests (sqlite3.Connection conn, int student_id, list[str] interest)
str	insert_student_tracked_section (sqlite3.Connection conn, int student_id, str course_code, str section_number)
dict	get_data_with_hierarchy (sqlite3.Connection conn, str table, int targetID)
str	_format_hierarchy_node (dict node, int depth=0)
str	hierarchy_data_to_string (dict hierarchy_data)
str	get_data_with_hierarchy_string (sqlite3.Connection conn, str table, str targetID)
list	get_ids_by_field_value (sqlite3.Connection conn, str table, str field, str value)
list	get_ids_by_parent (sqlite3.Connection conn, str table, str targetID)
list	get_students_by_advisor (sqlite3.Connection conn, str advisorID)
str	async_get_courseID_by_code (aiosqlite.Connection conn, str course_code)
str	async_get_courseID_by_title (aiosqlite.Connection conn, str course_title)
dict	async_get_course_info_by_id (aiosqlite.Connection conn, str course_id)
list	async_get_coops (aiosqlite.Connection conn)
list	async_get_courseIDs_by_filters (aiosqlite.Connection conn, schemas.CourseFilters filters)
str	async_get_course_description_by_id (aiosqlite.Connection conn, str course_id)
list	async_get_sectionIDs_by_filters (aiosqlite.Connection conn, schemas.SectionFilters filters)
dict	async_get_section_info_by_id (aiosqlite.Connection conn, str section_id)
	async_get_upcoming_events (aiosqlite.Connection conn)
list	async_get_event_dates_by_name (aiosqlite.Connection conn, str event_name)
dict	async_get_student_basic_info (aiosqlite.Connection conn, int student_id)
list	async_get_student_course_history (aiosqlite.Connection conn, int student_id)
list	async_get_student_interests (aiosqlite.Connection conn, int student_id)
list	async_get_student_tracked_sections (aiosqlite.Connection conn, int student_id)
list	async_get_program_requirements_by_title (aiosqlite.Connection conn, str program_title)
str	async_insert_student_interests (aiosqlite.Connection conn, int student_id, list[str] interest)
str	async_insert_student_tracked_section (aiosqlite.Connection conn, int student_id, str course_code, str section_number)

Variables
	PARENT_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))

Detailed Description

Copyright 2026 Luca Silver

Contains various utility functions for interacting with the registration database.

Functions:
- `get_courseID_by_code`: Return the database course ID matching a course code.
- `get_courseID_by_title`: Return the database course ID for a given course title.
- `get_coops`: Return a list of course IDs for all 'Cooperative Work Experience' entries.
- `get_courseIDs_by_filters`: Return a list of course IDs that match the provided CourseFilters.
- `get_course_info_by_id`: Return course metadata for a given course ID.
- `get_course_description_by_id`: Return the textual description for a course identified by `course_id`.
- `get_sectionIDs_by_filters`: Return a list of section IDs that match the provided SectionFilters.
- `get_section_info_by_id`: Return section-level metadata for the provided section ID.
- `get_upcoming_events`: Return a list of upcoming events with their dates.
- `get_event_dates_by_name`: Return the dates for a specified event.
- `get_student_basic_info`: Return basic profile information for a given student ID, including their name, advisor, GPA, total credits, and programs of study.
- `get_student_course_history`: Return the course history for a given student ID, including course codes, titles, and grades.
- `get_student_interests`: Return a list of interests for a given student ID.
- `get_student_tracked_sections`: Return a list of section IDs that the student is currently tracking for openings.
- `get_program_requirements_by_title`: Return a list of course IDs that are requirements for a given program of study.
- `insert_student_interest`: Insert a new interest for a student.
- `insert_student_tracked_section`: Insert a new section to track for openings for a student.
- `get_data_with_hierarchy`: Return data from a specified table along with related data from parent and child tables to provide context for database entries.
- `_format_hierarchy_node`: Helper function to recursively format a hierarchy node and its children into a nested dictionary structure.
- `hierarchy_data_to_string`: Convert the nested dictionary structure returned by `get_data_with_hierarchy` into a readable string format for easier interpretation of hierarchical relationships in the database.
- `get_data_with_hierarchy_string`: Wrapper function that combines `get_data_with_hierarchy` and `hierarchy_data_to_string` to directly return the hierarchical data as a formatted string.
- `get_ids_by_field_value`: Return a list of IDs from a specified table where a given field matches a specified value.
- `get_ids_by_parent`: Return a list of IDs from a specified table where the ParentID matches a specified value.
- `get_students_by_advisor`: Return a list of student IDs for students who have a specified advisor.
Async Variants:
- `async_get_courseID_by_code`: Async variant of `get_courseID_by_code`.
- `async_get_courseID_by_title`: Async variant of `get_courseID_by_title`.
- `async_get_coops`: Async variant of `get_coops`.
- `async_get_courseIDs_by_filters`: Async variant of `get_courseIDs_by_filters`.
- `async_get_course_info_by_id`: Async variant of `get_course_info_by_id`.
- `async_get_course_description_by_id`: Async variant of `get_course_description_by_id`.
- `async_get_sectionIDs_by_filters`: Async variant of `get_sectionIDs_by_filters`.
- `async_get_section_info_by_id`: Async variant of `get_section_info_by_id`.
- `async_get_upcoming_events`: Async variant of `get_upcoming_events`.
- `async_get_event_dates_by_name`: Async variant of `get_event_dates_by_name`.
- `async_get_student_basic_info`: Async variant of `get_student_basic_info`.
- `async_get_student_course_history`: Async variant of `get_student_course_history`.
- `async_get_student_interests`: Async variant of `get_student_interests`.
- `async_get_student_tracked_sections`: Async variant of `get_student_tracked_sections`.
- `async_get_program_requirements_by_title`: Async variant of `get_program_requirements_by_title`.
- `async_insert_student_interest`: Async variant of `insert_student_interest`.
- `async_insert_student_tracked_section`: Async variant of `insert_student_tracked_section`.

Function Documentation

_format_hierarchy_node()

str _format_hierarchy_node ( dict node, int depth = 0 )

protected

Format a hierarchical node (from get_data_with_hierarchy) into a readable string.

This helper is used by `hierarchy_data_to_string` to pretty-print the nested
structure produced by `get_data_with_hierarchy`.

Args:
    node (dict): A dict representing a node in the hierarchy, with keys "entry" and "content".
    depth (int): Current depth in the hierarchy, used for indentation.

Returns:
    out (str): A formatted multi-line string representing the node and its children in a readable way.

async_get_coops()

list async_get_coops

(

aiosqlite.Connection

conn

)

Return a list of course IDs for all 'Cooperative Work Experience' entries (async).

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.

Returns:
    out (list[str]): List of course IDs (from `Courses.ID`) for all courses with the name "Cooperative Work Experience":
        Format of list items: Primary key values from `Courses.ID` column corresponding to rows where `Courses.Name` is "Cooperative Work Experience" (eg. ["12345", "67890"]).
        Note: If no courses are found with that name, returns an empty list.

async_get_course_description_by_id()

str async_get_course_description_by_id	(	aiosqlite.Connection	conn,
		str	course_id )

Return the textual description for a course identified by `course_id` (async).

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    course_id (str): Primary key value from Courses.ID (e.g. "12345").

Returns:
    out (str): Course description from Courses.Description column if found,
        "Course not found" if no course matches the ID.

async_get_course_info_by_id()

dict async_get_course_info_by_id	(	aiosqlite.Connection	conn,
		str	course_id )

Return course metadata for a given course ID (async).

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    course_id (str): Primary key value from Courses.ID (e.g. "12345").

Returns:
    out (dict): Mapping of column names to values for the matched course with keys:
          ID, Name, Department, Code, Credits, Requirements.
          Returns empty dict if no course found with that ID.

async_get_courseID_by_code()

str async_get_courseID_by_code	(	aiosqlite.Connection	conn,
		str	course_code )

Return the database course ID matching a course code (async).

Accepts course codes in either spaced format ("DPT NUM") or compact format ("DPTNUM").

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    course_code (str): Course code to look up:
        Format: "DPT NUM" | "DPTNUM" (eg. "MAT 101" or "MAT101").

Returns:
    out (str): The matching course ID as stored in the `Courses` table:
        Format if found: Primary key value from `Courses.ID` column corresponding to the provided course code. (eg. "12345")
        Note: If no course is found with that code, returns ``None``.

async_get_courseID_by_title()

str async_get_courseID_by_title	(	aiosqlite.Connection	conn,
		str	course_title )

Return the database course ID for a given course title (async).

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    course_title (str): Course title to look up:
        Format: Exact course title as stored in the `Courses.Name` column (eg. "Introduction to Computer Science").

Returns:
    out (str): The matching course ID:
        Format if found: Primary key value from `Courses.ID` column corresponding to the provided course title. (eg. "12345")
        Note: If no course is found with that title, returns ``None``.

Warnings: This is designed for a database with unique course titles (with the exception of "Cooperative Work Experience"). If you plan to use this for a database that may have multiple courses with the same title, consider implementing an alternative lookup method.

async_get_courseIDs_by_filters()

list async_get_courseIDs_by_filters	(	aiosqlite.Connection	conn,
		schemas.CourseFilters	filters )

Return a list of course IDs that match the provided CourseFilters (async).

Builds a parameterized SQL query from schemas.CourseFilters and returns matching
CoursesOffered.ID values.

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    filters (schemas.CourseFilters): CourseFilters with optional fields:
        - terms: List of DBTerm (year, season, optional number). Uses OR logic.
        - departments: List of dept abbreviations (e.g. ["PHY", "MAT"]). Uses OR.
        - course_codes: List of CodeCondition (condition, code). Uses AND.
        - credits: List of CreditCondition (condition, credits). Uses AND.
        - keywords: Search terms for Courses.Description. Uses OR.
        - prerequisites: Search terms for Courses.Requirements. Uses OR.

Returns:
    out (list[str]): Primary key values from CoursesOffered.ID matching filters
               (e.g. ["12345", "67890"]). Empty list if no matches.
               Returns ["No filters specified"] if filters is None.

Raises:
    ValueError: Invalid comparison operator in filters.
                "Invalid course code condition: {condition}" or
                "Invalid credit condition: {condition}".

async_get_event_dates_by_name()

list async_get_event_dates_by_name	(	aiosqlite.Connection	conn,
		str	event_name )

Return all future EventDates for an event identified by its name (async).

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    event_name (str): Exact event name as stored in Events.Name (e.g. "Spring Career Fair").

Returns:
    list[dict]: List of dicts with keys "Date", "StartTime", "EndTime", "Location"
                for future dates of the event. Returns ["Event not found"] if no event
                with that name exists, or empty list if event has no future dates.

async_get_program_requirements_by_title()

list async_get_program_requirements_by_title	(	aiosqlite.Connection	conn,
		str	program_title )

Return program requirement strings for a program identified by its Title (async).

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    program_title (str): Exact program title from ProgramsOfStudy.Title
                         (e.g. "Computer Information Systems").

Returns:
    out (list[str]): Requirement strings for the program: 
        Format for options:
            "Department: {Dept}, Course Number: {Code}, Title: {Name}"
            "Any {Elective} course"
            Multiple options separated by " OR ".
            Returns ["Program not found"] if no program exists, or
            ["No requirements found"] if program has no requirements.

async_get_section_info_by_id()

dict async_get_section_info_by_id	(	aiosqlite.Connection	conn,
		str	section_id )

Return section-level metadata for the provided section ID (async).

Get section metadata combined from Sections, CoursesOffered, and Courses tables.

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    section_id (str): Primary key value from Sections.ID (e.g. "12345").

Returns:
    out (dict): Mapping of column names to values with keys: ID, Department, Code, Name,
          SectionNum, Instructor, Method, Location, MaxSeats, SeatsLeft.
          Returns empty dict if section not found.

async_get_sectionIDs_by_filters()

list async_get_sectionIDs_by_filters	(	aiosqlite.Connection	conn,
		schemas.SectionFilters	filters )

Return a list of section IDs that match the provided SectionFilters (async).

Builds a parameterized SQL query from schemas.SectionFilters and returns matching
Sections.ID values.

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    filters (schemas.SectionFilters): SectionFilters with optional fields:
        - terms: List of DBTerm (year, season, optional number). Uses OR.
        - course_codes: Spaced or compact format (e.g. ["CSC 101", "MAT125"]). OR.
        - instructors: List of names from Sections.Instructor. Uses OR.
        - teaching_methods: List of methods (e.g. ["In-Person", "Online"]). OR.
        - enrollment_capacity: List of EnrollmentCondition for MaxSeats. AND.
        - enrollment: List of EnrollmentCondition for SeatsLeft. AND.
        - locations: List of location keywords from Sections.Location. AND.
        - meet_times: List of DBMeetTime (day, start_time, end_time). OR.

Returns:
    out (list[str]): Primary key values from Sections.ID matching filters
               (e.g. ["12345", "67890"]). Empty list if no matches.
               Returns ["No filters specified"] if filters is None.

Raises:
    ValueError: Invalid comparison operator in capacity/enrollment filters.
                "Invalid enrollment capacity condition: {condition}" or
                "Invalid enrollment condition: {condition}".

async_get_student_basic_info()

dict async_get_student_basic_info	(	aiosqlite.Connection	conn,
		int	student_id )

Return basic profile information for a student (async).

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    student_id (int): Numeric ID from Students.ID (e.g. 12345).

Returns:
    out (dict): Profile information with keys: Name, Email, Advisor, GPA, CreditsEarned,
          ProgramsOfStudy. Returns empty dict if student not found.
          Note: Advisor is "No advisor assigned" if not set. ProgramsOfStudy is
          ["No program of study"] if student has no programs.

async_get_student_course_history()

list async_get_student_course_history	(	aiosqlite.Connection	conn,
		int	student_id )

Return a student's course history as a list of course entries (async).

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    student_id (int): Numeric ID from Students.ID (e.g. 12345).

Returns:
    out (list[dict]): List of dicts with keys "CourseCode", "Name", "Grade" for each course.
                Returns ["No courses taken"] if student has no course history.

async_get_student_interests()

list async_get_student_interests	(	aiosqlite.Connection	conn,
		int	student_id )

Return a list of interests for the given student (async).

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    student_id (int): Numeric ID from Students.ID (e.g. 12345).

Returns:
    out (list[str]): Interests from Interests.Interest for the student.
               Returns ["No interests specified"] if student has no interests.

async_get_student_tracked_sections()

list async_get_student_tracked_sections	(	aiosqlite.Connection	conn,
		int	student_id )

Return the list of sections a student is currently tracking (async).

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    student_id (int): Numeric ID from Students.ID (e.g. 12345).

Returns:
    out (list[dict]): List of tracked sections with keys "CourseCode", "SectionNumber", "Name".
                Returns ["No sections currently being tracked"] if student tracks no sections.

async_get_upcoming_events()

async_get_upcoming_events

(

aiosqlite.Connection

conn

)

Return a list of upcoming events that have at least one future EventDates row (async).

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.

Returns:
    out (list[dict]): List of dicts with keys "ID", "Name", "Description" for each
                upcoming event. Returns empty list if no upcoming events found.

async_insert_student_interests()

str async_insert_student_interests	(	aiosqlite.Connection	conn,
		int	student_id,
		list[str]	interest )

Insert one or more interest strings for a student (async).

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    student_id (int): Student's numeric ID from Students.ID (e.g. 12345).
    interest (list[str]): List of interest strings (e.g. ["Data Science", "Machine Learning"]).

Returns:
    out (str): Confirmation string "{counter} new interest(s) added".

async_insert_student_tracked_section()

str async_insert_student_tracked_section	(	aiosqlite.Connection	conn,
		int	student_id,
		str	course_code,
		str	section_number )

Add a tracked section for the student specified (async).

Args:
    conn (aiosqlite.Connection): Async connection to the registration database.
    student_id (int): The student's numeric ID:
        Format: Primary key value from `Students.ID` column (e.g. 12345).
    course_code (str): The course code of the section to be tracked:
        Format: Course code in either spaced or compact format (e.g. "CSC 101" or "CSC101").
    section_number (str): The section number of the section to be tracked:
        Format: Section number as stored in `Sections.SectionNum` (e.g. "1", "b1", "50", etc).

Returns:
    out (str): Confirmation string indicating the result of the operation:
        Possible return values:
            "Section added to tracked sections" - if the section was successfully added to the student's tracked sections.
            "Section not found" - if no section matches the provided course code and section number.
            "Section already being tracked" - if the student is already tracking a section with the same course code and section number.

get_advisor_email()

str get_advisor_email	(	sqlite3.Connection	conn,
		str	advisor_name )

Return the email address for an advisor identified by name.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    advisor_name (str): Exact name of the advisor as stored in Advisors.Name (e.g. "Dr. Smith").

Returns:
    out (str): The advisor's email address if found (e.g. "advisor@university.edu"), or "Advisor not found" if no advisor with that name exists.

get_coops()

list get_coops

(

sqlite3.Connection

conn

)

Return a list of course IDs for all 'Cooperative Work Experience' entries.

Args:
    cursor (sqlite3.Cursor): cursor object connected to the registration database.

Returns:
    out (list[str]): List of course IDs (from `Courses.ID`) for all courses with the name "Cooperative Work Experience":
        Format of list items: Primary key values from `Courses.ID` column corresponding to rows where `Courses.Name` is "Cooperative Work Experience" (eg. ["12345", "67890"]).
        Note: If no courses are found with that name, returns an empty list.

get_course_description_by_id()

str get_course_description_by_id	(	sqlite3.Connection	conn,
		str	course_id )

Return the textual description for a course identified by `course_id`.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    course_id (str): Primary key value from Courses.ID (e.g. "12345").

Returns:
    out (str): Course description from Courses.Description column if found,
        "Course not found" if no course matches the ID.

get_course_info_by_id()

dict get_course_info_by_id	(	sqlite3.Connection	conn,
		str	course_id )

Return course metadata for a given course ID.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    course_id (str): Primary key value from Courses.ID (e.g. "12345").

Returns:
    out (dict): Mapping of column names to values for the matched course with keys:
          ID, Name, Department, Code, Credits, Requirements.
          Returns empty dict if no course found with that ID.

get_courseID_by_code()

str get_courseID_by_code	(	sqlite3.Connection	conn,
		str	course_code )

Return the database course ID matching a course code.

Accepts course codes in either spaced format ("DPT NUM") or compact format ("DPTNUM").

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    course_code (str): Course code to look up:
        Format: "DPT NUM" | "DPTNUM" (eg. "MAT 101" or "MAT101").

Returns:
    out (str): The matching course ID as stored in the `Courses` table:
        Format if found: Primary key value from `Courses.ID` column corresponding to the provided course code. (eg. "12345")
        Note: If no course is found with that code, returns ``None``.

get_courseID_by_title()

str get_courseID_by_title	(	sqlite3.Connection	conn,
		str	course_title )

Return the database course ID for a given course title.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    course_title (str): Course title to look up:
        Format: Exact course title as stored in the `Courses.Name` column (eg. "Introduction to Computer Science").

Returns:
    out (str): The matching course ID:
        Format if found: Primary key value from `Courses.ID` column corresponding to the provided course title. (eg. "12345")
        Note: If no course is found with that title, returns ``None``.

Warnings: This is designed for a database with unique course titles (with the exception of "Cooperative Work Experience"). If you plan to use this for a database that may have multiple courses with the same title, consider implementing an alternative lookup method.

get_courseIDs_by_filters()

list get_courseIDs_by_filters	(	sqlite3.Connection	conn,
		schemas.CourseFilters	filters )

Return a list of course IDs that match the provided CourseFilters.

Builds a parameterized SQL query from schemas.CourseFilters and returns matching
CoursesOffered.ID values.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    filters (schemas.CourseFilters): CourseFilters with optional fields:
        - terms: List of DBTerm (year, season, optional number). Uses OR logic.
        - departments: List of dept abbreviations (e.g. ["PHY", "MAT"]). Uses OR.
        - course_codes: List of CodeCondition (condition, code). Uses AND.
        - credits: List of CreditCondition (condition, credits). Uses AND.
        - keywords: Search terms for Courses.Description. Uses OR.
        - prerequisites: Search terms for Courses.Requirements. Uses OR.

Returns:
    out (list[str]): Primary key values from CoursesOffered.ID matching filters
               (e.g. ["12345", "67890"]). Empty list if no matches.
               Returns ["No filters specified"] if filters is None.

Raises:
    ValueError: Invalid comparison operator in filters.
                "Invalid course code condition: {condition}" or
                "Invalid credit condition: {condition}".

get_data_with_hierarchy()

dict get_data_with_hierarchy	(	sqlite3.Connection	conn,
		str	table,
		int	targetID )

Recursively gather an entry and all related rows that reference it.

Starting from the provided table and targetID, this function fetches the row's
non-ID fields and then discovers tables that declare a FOREIGN KEY referencing
the target table. For each related table, it recurses and includes that data
under the parent entry.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    table (str): The name of the table to start from (e.g. "Courses", "Students", etc).
    targetID (int): The ID of the row in the specified table to retrieve (e.g. 12345).

Returns:
    out (dict): A nested dictionary representing the target entry and all related entries that reference it, structured to indicate the relationships between the data:
        Format: {
            "entry": str,
                Format: A string combining the table name and target ID (e.g. "Courses: 12345").
            "content": dict,
                Format: A dictionary containing the fields and values of the target entry, as well as nested dictionaries for any related entries that reference it.
                The keys for the fields of the target entry are the column names from the table (excluding "ID" and "ParentID"), and the values are the corresponding field values from the database.
                For each related table that references the target entry, there is a key in the content dict with the name of that related table, and its value is a list of dicts representing each entry in that related table that references the target entry. Each of those dicts has the same structure as described here, allowing for recursive nesting to represent multiple levels of relationships.
        }

Warnings:
    This can produce very large results for highly-referenced tables (catalogs).

get_data_with_hierarchy_string()

str get_data_with_hierarchy_string	(	sqlite3.Connection	conn,
		str	table,
		str	targetID )

Helper that returns the hierarchical data for a target as a formatted string.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    table (str): The name of the table to start from (e.g. "Courses", "Students", etc).
    targetID (int): The ID of the row in the specified table to retrieve (e.g. 12345).

Returns:
    out (str): A formatted multi-line string representing the target entry and all related entries that reference it, structured to indicate the relationships between the data:
        Format: {
            "entry": str,
                Format: A string combining the table name and target ID (e.g. "Courses: 12345").
            "content": dict,
                Format: A dictionary containing the fields and values of the target entry, as well as nested dictionaries for any related entries that reference it.
                The keys for the fields of the target entry are the column names from the table (excluding "ID" and "ParentID"), and the values are the corresponding field values from the database.
                For each related table that references the target entry, there is a key in the content dict with the name of that related table, and its value is a list of dicts representing each entry in that related table that references the target entry. Each of those dicts has the same structure as described here, allowing for recursive nesting to represent multiple levels of relationships.
            }

Warnings:
    This can produce very large results for highly-referenced tables (catalogs).

get_event_dates_by_name()

list get_event_dates_by_name	(	sqlite3.Connection	conn,
		str	event_name )

Return all future EventDates for an event identified by its name.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    event_name (str): Exact event name as stored in Events.Name (e.g. "Spring Career Fair").

Returns:
    list[dict]: List of dicts with keys "Date", "StartTime", "EndTime", "Location"
                for future dates of the event. Returns ["Event not found"] if no event
                with that name exists, or empty list if event has no future dates.

get_ids_by_field_value()

list get_ids_by_field_value	(	sqlite3.Connection	conn,
		str	table,
		str	field,
		str	value )

Return a list of IDs in `table` where `field` equals `value`.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    table (str): The name of the table to query (e.g. "Courses", "Students", etc).
    field (str): The name of the field/column to filter by (e.g. "Department", "AdvisorID", etc).
    value (str): The value to match in the specified field (e.g. "CSC", "Dr. Smith", etc).

Returns:
    out (list[str]): List of IDs (as strings) from the specified table where the specified field matches the provided value:
        Format of list items: Primary key values from the `ID` column of the specified table that match the condition (e.g. ["12345", "67890", etc]).

get_ids_by_parent()

list get_ids_by_parent	(	sqlite3.Connection	conn,
		str	table,
		str	targetID )

Return IDs from `table` whose `ParentID` equals `targetID`.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    table (str): The name of the table to query (e.g. "Courses", "Students", etc).
    targetID (str): The value to match in the `ParentID` field (e.g. "12345", "67890", etc).

Returns:
    out (list[str]): List of IDs (as strings) from the specified table where `ParentID` matches the provided targetID:
        Format of list items: Primary key values from the `ID` column of the specified table that match the condition (e.g. ["12345", "67890", etc]).

get_program_requirements_by_title()

list get_program_requirements_by_title	(	sqlite3.Connection	conn,
		str	program_title )

Return program requirement strings for a program identified by its Title.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    program_title (str): Exact program title from ProgramsOfStudy.Title
                         (e.g. "Computer Information Systems").

Returns:
    out (list[str]): Requirement strings for the program: 
        Format for options:
            "Department: {Dept}, Course Number: {Code}, Title: {Name}"
            "Any {Elective} course"
            Multiple options separated by " OR ".
            Returns ["Program not found"] if no program exists, or
            ["No requirements found"] if program has no requirements.

get_section_info_by_id()

dict get_section_info_by_id	(	sqlite3.Connection	conn,
		str	section_id )

Return section-level metadata for the provided section ID.

Get section metadata combined from Sections, CoursesOffered, and Courses tables.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    section_id (str): Primary key value from Sections.ID (e.g. "12345").

Returns:
    out (dict): Mapping of column names to values with keys: ID, Department, Code, Name,
          SectionNum, Instructor, Method, Location, MaxSeats, SeatsLeft.
          Returns empty dict if section not found.

get_sectionIDs_by_filters()

list get_sectionIDs_by_filters	(	sqlite3.Connection	conn,
		schemas.SectionFilters	filters )

Return a list of section IDs that match the provided SectionFilters.

Builds a parameterized SQL query from schemas.SectionFilters and returns matching
Sections.ID values.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    filters (schemas.SectionFilters): SectionFilters with optional fields:
        - terms: List of DBTerm (year, season, optional number). Uses OR.
        - course_codes: Spaced or compact format (e.g. ["CSC 101", "MAT125"]). OR.
        - instructors: List of names from Sections.Instructor. Uses OR.
        - teaching_methods: List of methods (e.g. ["In-Person", "Online"]). OR.
        - enrollment_capacity: List of EnrollmentCondition for MaxSeats. AND.
        - enrollment: List of EnrollmentCondition for SeatsLeft. AND.
        - locations: List of location strings from Sections.Location. OR.
        - meet_times: List of DBMeetTime (day, start_time, end_time). OR.

Returns:
    out (list[str]): Primary key values from Sections.ID matching filters
               (e.g. ["12345", "67890"]). Empty list if no matches.
               Returns ["No filters specified"] if filters is None.

Raises:
    ValueError: Invalid comparison operator in capacity/enrollment filters.
                "Invalid enrollment capacity condition: {condition}" or
                "Invalid enrollment condition: {condition}".

get_student_basic_info()

dict get_student_basic_info	(	sqlite3.Connection	conn,
		int	student_id )

Return basic profile information for a student.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    student_id (int): Numeric ID from Students.ID (e.g. 12345).

Returns:
    out (dict): Profile information with keys: Name, Email, Advisor, GPA, CreditsEarned,
          ProgramsOfStudy. Returns empty dict if student not found.
          Note: Advisor is "No advisor assigned" if not set. ProgramsOfStudy is
          ["No program of study"] if student has no programs.

get_student_course_history()

list get_student_course_history	(	sqlite3.Connection	conn,
		int	student_id )

Return a student's course history as a list of course entries.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    student_id (int): Numeric ID from Students.ID (e.g. 12345).

Returns:
    out (list[dict]): List of dicts with keys "CourseCode", "Name", "Grade" for each course.
                Returns ["No courses taken"] if student has no course history.

get_student_interests()

list get_student_interests	(	sqlite3.Connection	conn,
		int	student_id )

Return a list of interests for the given student.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    student_id (int): Numeric ID from Students.ID (e.g. 12345).

Returns:
    out (list[str]): Interests from Interests.Interest for the student.
               Returns ["No interests specified"] if student has no interests.

get_student_tracked_sections()

list get_student_tracked_sections	(	sqlite3.Connection	conn,
		int	student_id )

Return the list of sections a student is currently tracking.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    student_id (int): Numeric ID from Students.ID (e.g. 12345).

Returns:
    out (list[dict]): List of tracked sections with keys "CourseCode", "SectionNumber", "Name".
                Returns ["No sections currently being tracked"] if student tracks no sections.

get_students_by_advisor()

list get_students_by_advisor	(	sqlite3.Connection	conn,
		str	advisorID )

Return a list of student IDs advised by the given advisor.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    advisorID (str): Advisor identifier used in `Students.AdvisorID`.

Returns:
    out (list[str]): List of student IDs (as strings) advised by the given advisor:
        Format of list items: Primary key values from the `ID` column of the `Students` table where `AdvisorID` matches the provided advisorID (e.g. ["12345", "67890", etc]).

get_upcoming_events()

list get_upcoming_events

(

sqlite3.Connection

conn

)

Return a list of upcoming events that have at least one future EventDates row.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.

Returns:
    out (list[dict]): List of dicts with keys "ID", "Name", "Description" for each
                upcoming event. Returns empty list if no upcoming events found.

hierarchy_data_to_string()

str hierarchy_data_to_string

(

dict

hierarchy_data

)

Convert a hierarchy data dict into a readable multi-line string.

Args:
    hierarchy_data (dict): A dict representing hierarchical data as produced by `get_data_with_hierarchy`.

Returns:
    out (str): A formatted multi-line string representing the hierarchy in a readable way.

insert_student_interests()

str insert_student_interests	(	sqlite3.Connection	conn,
		int	student_id,
		list[str]	interest )

Insert one or more interest strings for a student.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    student_id (int): Student's numeric ID from Students.ID (e.g. 12345).
    interest (list[str]): List of interest strings (e.g. ["Data Science", "Machine Learning"]).

Returns:
    out (str): Confirmation string "{counter} new interest(s) added".

insert_student_tracked_section()

str insert_student_tracked_section	(	sqlite3.Connection	conn,
		int	student_id,
		str	course_code,
		str	section_number )

Add a tracked section for the student specified.

Args:
    conn (sqlite3.Connection): Connection object connected to the registration database.
    student_id (int): The student's numeric ID:
        Format: Primary key value from `Students.ID` column (e.g. 12345).
    course_code (str): The course code of the section to be tracked:
        Format: Course code in either spaced or compact format (e.g. "CSC 101" or "CSC101").
    section_number (str): The section number of the section to be tracked:
        Format: Section number as stored in `Sections.SectionNum` (e.g. "1", "b1", "50", etc).

Returns:
    out (str): Confirmation string indicating the result of the operation:
        Possible return values:
            "Section added to tracked sections" - if the section was successfully added to the student's tracked sections.
            "Section not found" - if no section matches the provided course code and section number.
            "Section already being tracked" - if the student is already tracking a section with the same course code and section number.

Variable Documentation

PARENT_DIR

lg_agent.database_utils.PARENT_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))