- Generated by
1.17.0
|
My Project
|
Functions | |
| aconnect () | |
| setup_database () | |
| create_triggers () | |
| populate_course_catalog (str json_file="course_catalog.json") | |
| populate_programs_catalog (str json_file="qcc_programs.json") | |
| add_new_term (str json_file="term_data.json") | |
| add_students_from_json (str json_file) | |
| add_advisors_from_json (str json_file) | |
| add_events_from_json (str json_file) | |
| reset_course_catalog () | |
| reset_programs_catalog () | |
| reset_students_and_advisors () | |
| reset_terms_and_courses () | |
| reset_events () | |
| reset_users () | |
| reset_all () | |
| parse_args (argv) | |
| run_operations (args) | |
| main () | |
Variables | |
| ROOT_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..')) | |
| JSONS_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', 'jsons')) | |
| str | EMAIL_TEST_MODE = "true" |
| TEST_EMAIL_ADDRESS = os.getenv("TEST_EMAIL_ADDRESS", "error") | |
Copyright 2026 Luca Silver
This module provides development tools for managing the SQLite database used by the advising application. It includes functions to set up the database schema, create necessary triggers, populate tables from JSON files, and reset specific groups of tables during development.
Functions:
- ``__connect()``: Internal function to establish a connection to the SQLite database with appropriate configuration.
- ``aconnect()``: Async variant of the database connection function using aiosqlite.
- ``setup_database()``: Creates the database schema with all required tables.
- ``create_triggers()``: Creates database triggers for logging section status changes and resetting student check fields.
- ``populate_course_catalog(json_file)``: Loads course data from a JSON file and populates the ``Courses`` table.
- ``populate_programs_catalog(json_file)``: Loads program of study data from a JSON file and populates the ``ProgramsOfStudy`` and related requirement tables.
- ``add_new_term(json_file)``: Inserts a new term and its course offerings, sections, and meet times from a JSON file.
- ``add_students_from_json(json_file)``: Loads student data from a JSON file and populates the ``Students`` table and courses taken table.
- ``add_advisors_from_json(json_file)``: Loads advisor data from a JSON file and populates the ``Advisors`` table.
- ``add_events_from_json(json_file)``: Loads event data from a JSON file and populates the ``Events`` and ``EventDates`` tables.
- ``parse_args(argv)``: Parses command-line arguments to specify which operations to run and which JSON files to use for population.
- ``run_operations(args)``: Executes a sequence of operations based on parsed command-line arguments, allowing for flexible setup and population of the database.
- ``main()``: Entry point for running the script from the command line, which parses arguments and runs the specified operations.
- Reset functions:
- ``reset_course_catalog()``: Drops course-related tables.
- ``reset_programs_catalog()``: Drops program-of-study related tables.
- ``reset_students_and_advisors()``: Drops the ``Students`` and ``Advisors`` tables.
- ``reset_terms_and_courses()``: Drops term- and offering-related tables.
- ``reset_events()``: Drops event-related tables.
- ``reset_users()``: Drops the ``Users`` table (cascades to related data).
- ``reset_all()``: Calls all reset functions in sequence to wipe the database.
Reset functions can be used in conjunction with ``setup_database()`` to update the schema and clear out old data before repopulating from JSON. Exercise caution when using reset functions as they permanently delete data.
| aconnect | ( | ) |
Async variant of __connect using aiosqlite. Yields an `aiosqlite.Connection` with foreign keys enabled and the same row_factory as the synchronous connector. Use `async with aconnect() as conn:` in async contexts.
| add_advisors_from_json | ( | str | json_file | ) |
Insert advisor accounts from a JSON file into the database.
Expected JSON structure (per advisor):
- ``Name``: advisor full name
- ``Email``: advisor email address
Behavior:
- Inserts a row into ``Advisors`` for each advisor, linked to a user account in ``Users``. If an advisor with the same name already exists, it is skipped and a message is printed.
Args:
json_file (str): Filename in the ``jsons`` directory to load.
| add_events_from_json | ( | str | json_file | ) |
Insert events and their dates from a JSON file into the database.
Expected JSON structure (per event):
- ``Title``: event title
- ``Description``: event description
- ``Dates``: list of date entries. Each entry may be either an ISO date string or an object with ``Date``, ``StartTime``, ``EndTime``, and optional ``Location``.
Behavior:
- Inserts a row into ``Events`` for each event and rows into ``EventDates`` for each associated date. If an event with the same title already exists, it is skipped and a message is printed.
Args:
json_file (str): Filename in the ``jsons`` directory to load.
| add_new_term | ( | str | json_file = "term_data.json" | ) |
Insert a new term and all of its course offerings, sections, and meet times from a JSON export.
The JSON must be located in the ``jsons`` directory and contain at least one term object with the keys: ``Year``, ``Season``, ``Num`` and a ``CoursesOffered`` list. Each course offering should include fields used below such as ``Department``, ``Code``, ``SectionNum``, ``Instructor``, ``StartDate``, ``EndDate``, ``Status``, ``MaxSeats``, ``SeatsLeft``, ``Method``, ``Location`` and ``MeetTimes``.
Behavior:
- Inserts a row into ``Terms`` and uses the inserted term ID as the ``ParentID`` for ``CoursesOffered`` rows.
- Skips course offerings when the base course cannot be found in ``Courses`` (logs a message and continues).
- Parses the ``MeetTimes`` string to split day initials and start/end times, converts AM/PM times to 24-hour format, and inserts one ``MeetTimes`` row per day.
Args:
json_file (str): Filename in the ``jsons`` directory to load. Defaults to ``term_data.json``.
| add_students_from_json | ( | str | json_file | ) |
Insert student accounts, course histories, and declared programs from a JSON file into the database.
Expected JSON structure (per student):
- ``ID``: numeric student identifier
- ``Name``: student full name
- ``Email``: student email address
- ``GPA``: floating point GPA
- ``CreditsEarned``: integer
- ``IntendedGraduationTerm``: string
- ``Advisor``: (optional) advisor name used to look up an Advisors row
- ``CoursesTaken``: list of objects with ``CourseCode`` (e.g. "CSC 101") and ``Grade``
- ``ProgramsOfStudy``: list of program title strings
Behavior:
- Inserts a row into ``Students`` for each student.
- For each ``CoursesTaken`` entry, resolves the course via :pyfunc:`lg_agent.database_utils.get_courseID_by_code` and inserts a ``CoursesTaken`` row. If a course cannot be found, the course is skipped and a message is printed.
- Associates programs with the student if the program title exists in ``ProgramsOfStudy``.
Args:
json_file (str): Filename in the ``jsons`` directory to load.
| create_triggers | ( | ) |
Create database triggers used by the application. The following triggers are created (if they do not already exist): - ``LogSectionStatusChange``: inserts a row in ``SectionStatusChanges`` when a section's ``Status`` column changes. - ``ResetCheckFields``: resets a student's ``LastEventCheck`` and ``LastSectionStatusCheck`` timestamps when their ``ParentID`` becomes NULL. - ``DeleteStudentData``: deletes related rows (relevant events, interests, tracked sections, student alerts) when a student's ``ParentID`` is set to NULL. - ``ResetEventCheckOnInterestChange`` (insert and update variants): reset a student's ``LastEventCheck`` when interests are inserted or updated.
| main | ( | ) |
Main entry point for the database development tools script.
This function parses command-line arguments and runs the specified database operations. It allows developers to easily set up the database schema, populate it with data from JSON files, and reset tables as needed during development. The operations are executed in a logical order based on the dependencies between them (e.g., resetting tables before setting up the schema, populating courses before programs, etc.).
Example usage:
- To reset all tables, set up the schema, populate courses and programs, add students and advisors, and add a new term:
python database_dev_tools.py --reset all --setup --populate_courses courses_data.json --populate_programs programs_data.json --add_students students_data.json --add_advisors advisors_data.json --add_term term_data.json
- To only reset the course catalog and populate it from a JSON file:
python database_dev_tools.py --reset course_catalog --populate_courses courses_data.json
- To set up the database schema without resetting or populating data:
python database_dev_tools.py --setup
- To add a new term without affecting existing data:
python database_dev_tools.py --add_term term_data.json
- To add events from a JSON file:
python database_dev_tools.py --add_events events_data.json
| parse_args | ( | argv | ) |
Parse command-line arguments for database development operations.
This function defines the command-line interface for running various database setup, population, and reset operations directly from the terminal. It uses the argparse library to handle arguments that specify which operations to perform and which JSON files to use for data population.
Returns:
argparse.Namespace: Parsed command-line arguments with attributes corresponding to the defined options.
| populate_course_catalog | ( | str | json_file = "course_catalog.json" | ) |
Load catalog of course from a JSON file and insert them into ``Courses``.
The JSON file is expected to be located in the repository's ``jsons`` directory. Each entry should contain at minimum the fields used below: ``course_code`` (format: "DPT NUM"), ``name``, ``description``, ``credits``, ``prerequisites``, and ``semesters_offered`` (e.g. "F/S/SU").
The function converts ``course_code`` into the ``Department`` and numeric ``Code`` columns, maps semester initials to full names (F -> Fall, S -> Spring, SU -> Summer, IN -> Winter), and inserts a row into the ``Courses`` table for each course. Operation is committed at the end.
Args:
json_file (str): Filename in the ``jsons`` directory to load. Defaults to ``course_catalog.json``.
Notes:
If ``semesters_offered`` is empty/falsey in the input, ``SemestersOffered`` is stored as ``NULL`` in the database.
| populate_programs_catalog | ( | str | json_file = "qcc_programs.json" | ) |
Load programs of study from JSON and populate ``ProgramsOfStudy`` and related requirement tables.
The input JSON (in the ``jsons`` directory) should contain program records with fields such as ``name``, ``description``, ``total_credits``, ``area_of_study``, and ``required_courses``. Each program is inserted into ``ProgramsOfStudy`` and program requirements are split into rows in ``ProgramRequiredCourses`` and ``ProgramRequiredCourseOptions``.
Args:
json_file (str): Filename in the ``jsons`` directory to load. Defaults to ``qcc_programs.json``.
Notes:
If a required course string ends with ``" OR"``, it is treated as an alternative to the previous requirement and inserted into the ``ProgramRequiredCourseOptions`` table.
| reset_all | ( | ) |
Reset all major database groups by dropping their tables.
This is a convenience wrapper that calls the individual reset functions in the following order: course catalog, programs catalog, terms and courses, events, and users. Use with extreme caution — this operation effectively wipes the application's data.
This can be used in conjunction with setup_database() to update the entirety of the database schema and clear out all data before repopulating from JSON.
Warnings:
This operation effectively wipes the application's data.
| reset_course_catalog | ( | ) |
Drop the ``Courses`` table if it exists.
This can be used in conjunction with setup_database() to update the course catalog schema or to clear out old course data before repopulating from JSON.
Warnings:
This permanently removes course catalog data.
| reset_events | ( | ) |
Drop the ``Events`` and ``EventDates`` tables if they exist.
This can be used in conjunction with setup_database() to update the events schema or to clear out old event data before repopulating.
Warnings:
This permanently removes event definitions and dates.
| reset_programs_catalog | ( | ) |
Drop program-of-study related tables: ``ProgramsOfStudy``, ``ProgramRequiredCourses``, and ``ProgramRequiredCourseOptions``.
This can be used in conjunction with setup_database() to update the programs catalog schema or to clear out old program and requirement data before repopulating from JSON.
Warnings:
This permanently removes programs and requirement data.
| reset_students_and_advisors | ( | ) |
Drop the ``Students`` and ``Advisors`` tables if they exist.
This can be used in conjunction with setup_database() to update the student/advisor schema or to clear out old student and advisor data before repopulating from JSON.
Warnings:
This permanently removes student and advisor records.
| reset_terms_and_courses | ( | ) |
Drop term- and offering-related tables: ``Terms``, ``CoursesOffered``, ``Sections``, and ``MeetTimes``.
This can be used in conjunction with setup_database() to update the term and course offering schema or to clear out old scheduling data before repopulating from JSON.
Warnings:
This permanently removes term offerings and section scheduling data.
| reset_users | ( | ) |
Drop the ``Users`` table if it exists.
This can be used in conjunction with setup_database() to update the user account schema or to clear out old user data before repopulating.
Warnings:
This permanently removes user accounts and all related data (interests, relevant events, tracked sections, and course opening alerts) due to the ON DELETE CASCADE foreign key constraints.
| run_operations | ( | args | ) |
Run database operations based on parsed command-line arguments.
This function takes the parsed arguments from parse_args() and executes the corresponding database operations in the appropriate order. It checks which operations were specified (reset, setup, populate, add) and calls the relevant functions defined in this module with the provided JSON filenames.
Args:
args (argparse.Namespace): Parsed command-line arguments with attributes corresponding to the defined options.
| setup_database | ( | ) |
Create the project's database schema. This function opens a connection using :pyfunc:`__connect` and creates all of the tables used by the project (courses, terms, sections, meet times, users, verification tokens, students, advisors, programs of study, program requirement tables, events and related tables). Each CREATE TABLE uses ``IF NOT EXISTS`` so the operation is idempotent. The function commits the schema changes and prints a confirmation message indicating which database file was initialized.
| str database_dev_tools.EMAIL_TEST_MODE = "true" |
| database_dev_tools.JSONS_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', 'jsons')) |
| database_dev_tools.ROOT_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..')) |
| database_dev_tools.TEST_EMAIL_ADDRESS = os.getenv("TEST_EMAIL_ADDRESS", "error") |
1.17.0