scraping.refresh_registration

  1# =============================================================================
  2#
  3# Copyright 2026 
  4#
  5# Author:   Noel Mensah
  6# GitHub:   https://github.com/LSilver17/CSC212---AI-Agent
  7# 
  8# =============================================================================
  9
 10
 11"""
 12@file refresh_registration.py
 13@brief Live scraper that refreshes QCC registration data from The Q portal.
 14
 15@details
 16This module is the primary live scraping tool for the QCC registration data pipeline.
 17It uses Playwright to automate a headless Chromium browser, iterates through all
 18departments on The Q portal, scrapes all course sections for the current term,
 19saves the results to registration_sections.json, and upserts them into registration.db.
 20
 21This script handles the full refresh cycle in three steps:
 22    1. Scrape all departments from The Q portal using Playwright
 23    2. Save results to registration_sections.json
 24    3. Upsert results into registration.db (insert new, update existing)
 25
 26For parsing a pre-saved HTML snapshot instead of live scraping,
 27see scrape_registration_sections.py.
 28
 29@date 2026
 30
 31@par Configuration
 32    - SEARCH_URL : URL of The Q course offerings search page
 33    - TERM       : Academic term label to select (e.g. "Spring 2026")
 34    - JSON_FILE  : Output path for the JSON file
 35    - DB_FILE    : Path to the SQLite database file
 36    - HEADLESS   : Run browser in headless mode (True/False)
 37
 38@par Dependencies
 39    - playwright (pip install playwright && playwright install chromium)
 40    - beautifulsoup4
 41    - sqlite3 (stdlib)
 42    - asyncio (stdlib)
 43
 44@par Usage
 45    python refresh_registration.py
 46"""
 47
 48import json
 49import re
 50import sqlite3
 51import asyncio
 52import os
 53from datetime import datetime
 54from bs4 import BeautifulSoup
 55from playwright.async_api import async_playwright
 56
 57## @brief URL of The Q portal's advanced course search page.
 58SEARCH_URL  = "https://theq.qcc.edu/ICS/Course_Offerings_and_Schedule.jnz?portlet=AddDrop_Courses&screen=Advanced+Course+Search&screenType=next"
 59
 60## @brief Academic term label to select from the portal dropdown.
 61TERM        = "Spring 2026"
 62
 63## @brief Output path for the scraped JSON file.
 64JSON_FILE   = "registration_sections.json"
 65
 66## @brief Path to the SQLite database file.
 67DB_FILE     = "registration.db"
 68
 69## @brief Whether to run the browser in headless (no UI) mode.
 70HEADLESS    = True
 71
 72
 73# ── Parsing helpers ───────────────────────────────────────
 74
 75def clean_text(text):
 76    """
 77    @brief Removes excess whitespace from a string.
 78
 79    @param text The raw string to clean.
 80    @return A normalized single-line string with no extra whitespace.
 81
 82    @par Example
 83    @code
 84    clean_text("  ACC   101  ") -> "ACC 101"
 85    @endcode
 86    """
 87    return " ".join(text.split()).strip()
 88
 89
 90def parse_seats(s):
 91    """
 92    @brief Parses a seat availability string into open and total seat counts.
 93
 94    @details
 95    Handles both standard slash "/" and Unicode division slash "∕" formats
 96    used by The Q portal (e.g. "1 / 24" or "1 ∕ 24").
 97
 98    @param s The raw seats string from the registration table.
 99    @return A dictionary with keys "open" (int or None) and "total" (int or None).
100
101    @par Example
102    @code
103    parse_seats("1 ∕ 24") -> {"open": 1, "total": 24}
104    parse_seats("")        -> {"open": None, "total": None}
105    @endcode
106    """
107    m = re.search("(\d+)\s*[/∕]\s*(\d+)", s)
108    return {"open": int(m.group(1)), "total": int(m.group(2))} if m else {"open": None, "total": None}
109
110
111def parse_details(s):
112    """
113    @brief Parses the details column into instructor, schedule, location, and method.
114
115    @details
116    The details column combines multiple fields into a slash-delimited string.
117    Expected format: "Instructor / Days Time; Location / Method"
118
119    @param s The raw details string from the registration table.
120    @return A dictionary with keys:
121            - "instructor" (str or None)
122            - "days_time" (str or None): e.g. "MW 08:00-09:15AM"
123            - "location" (str or None): e.g. "MAIN Campus, Room 312"
124            - "method" (str or None): e.g. "Lecture"
125    """
126    result = {"instructor": None, "days_time": None, "location": None, "method": None}
127    parts = [p.strip() for p in s.split("/")]
128    if len(parts) >= 1:
129        result["instructor"] = clean_text(parts[0])
130    if len(parts) >= 2:
131        result["days_time"] = clean_text(parts[1].split(";")[0])
132        if ";" in parts[1]:
133            result["location"] = clean_text(parts[1].split(";")[1])
134    if len(parts) >= 3:
135        result["method"] = clean_text(parts[2])
136    return result
137
138
139def parse_course_code(code):
140    """
141    @brief Splits a full course code string into department, number, and section.
142
143    @param code The full course code string (e.g. "ACC 101-01").
144    @return A dictionary with keys "department", "number", and "section",
145            all str or None if the format does not match.
146
147    @par Example
148    @code
149    parse_course_code("ACC 101-01") -> {"department": "ACC", "number": "101", "section": "01"}
150    @endcode
151    """
152    m = re.match("([A-Z]+)\s+(\d+)-(\w+)", code)
153    return {"department": m.group(1), "number": m.group(2), "section": m.group(3)} if m else {"department": None, "number": None, "section": None}
154
155
156def parse_html(html):
157    """
158    @brief Parses raw HTML from The Q portal and extracts course section data.
159
160    @details
161    Uses BeautifulSoup to find all table rows, identifies rows containing a
162    valid course code (matching "DEPT NUM-SECTION"), and extracts all course
163    fields using the confirmed 13-column layout of The Q portal.
164
165    Column layout (auto-detected by course code position):
166    - [idx+0] course_code
167    - [idx+1] name
168    - [idx+4] seats
169    - [idx+5] status
170    - [idx+6] details (instructor/schedule/method)
171    - [idx+7] credits
172    - [idx+8] begin_date
173    - [idx+9] end_date
174
175    @param html Raw HTML string from the browser page content.
176    @return A list of course section dictionaries with all extracted fields.
177    """
178    soup = BeautifulSoup(html, "html.parser")
179    courses = []
180    for row in soup.select("table tr"):
181        cols = row.find_all(["td", "th"])
182        if len(cols) < 4:
183            continue
184        values = [clean_text(col.get_text(" ", strip=True)) for col in cols]
185        joined = " | ".join(values).lower()
186        if "course code" in joined and "name" in joined:
187            continue
188        if all(not v for v in values):
189            continue
190        idx = next((i for i, v in enumerate(values) if re.match(r"[A-Z]{2,4}\s+\d{3}-\w+", v)), None)
191        if idx is None:
192            continue
193        try:
194            code_str = values[idx]
195            seats    = parse_seats(values[idx + 4])
196            details  = parse_details(values[idx + 6])
197            code_map = parse_course_code(code_str)
198            courses.append({
199                "course_code":   code_str,
200                "department":    code_map["department"],
201                "course_number": code_map["number"],
202                "section":       code_map["section"],
203                "name":          values[idx + 1],
204                "status":        values[idx + 5],
205                "seats_open":    seats["open"],
206                "seats_total":   seats["total"],
207                "credits":       values[idx + 7],
208                "instructor":    details["instructor"],
209                "days_time":     details["days_time"],
210                "location":      details["location"],
211                "method":        details["method"],
212                "begin_date":    values[idx + 8] if len(values) > idx + 8 else None,
213                "end_date":      values[idx + 9] if len(values) > idx + 9 else None,
214                "scraped_at":    datetime.now().isoformat(),
215            })
216        except IndexError:
217            continue
218    return courses
219
220
221# ── Scraper ───────────────────────────────────────────────
222
223async def scrape():
224    """
225    @brief Asynchronously scrapes all course sections from The Q portal.
226
227    @details
228    Launches a headless Chromium browser using Playwright, navigates to The Q
229    portal's course search page, retrieves all department options from the
230    dropdown, and iterates through each department to collect course sections.
231
232    Each department is attempted up to 3 times with a 2-second delay between
233    retries to handle intermittent portal timeouts.
234
235    @return A list of all course section dictionaries scraped across all departments.
236
237    @note Requires Playwright and Chromium to be installed:
238          pip install playwright && playwright install chromium
239    """
240    all_courses = []
241
242    async with async_playwright() as p:
243        browser = await p.chromium.launch(headless=HEADLESS)
244        page = await browser.new_page()
245
246        print("Loading QCC course search page...")
247        await page.goto(SEARCH_URL)
248        await page.wait_for_load_state("networkidle")
249
250        dept_select = await page.query_selector("#pg0_V_ddlDept")
251        if not dept_select:
252            print("❌ Could not find department dropdown. Check if page loaded correctly.")
253            await browser.close()
254            return []
255
256        dept_options = await dept_select.query_selector_all("option")
257        departments = []
258        for opt in dept_options:
259            value = await opt.get_attribute("value")
260            label = (await opt.inner_text()).strip()
261            if label.lower() != "all" and label:
262                departments.append((value, label))
263
264        print(f"Found {len(departments)} departments.\n")
265
266        for i, (value, label) in enumerate(departments):
267            print(f"[{i+1}/{len(departments)}] Scraping: {label}...")
268            for attempt in range(3):
269                try:
270                    await page.goto(SEARCH_URL, timeout=60000)
271                    await page.wait_for_load_state("networkidle", timeout=60000)
272                    await page.wait_for_selector("#pg0_V_ddlDept", timeout=60000)
273                    await page.select_option("#pg0_V_ddlTerm", label=TERM)
274                    await page.select_option("#pg0_V_ddlDept", value=value)
275                    await page.click("#pg0_V_btnSearch")
276                    await page.wait_for_load_state("networkidle", timeout=60000)
277                    courses = parse_html(await page.content())
278                    all_courses.extend(courses)
279                    print(f"  → {len(courses)} sections")
280                    break
281                except Exception as e:
282                    if attempt < 2:
283                        print(f"  ⚠ Attempt {attempt+1} failed, retrying...")
284                        await asyncio.sleep(2)
285                    else:
286                        print(f"  ✗ Failed after 3 attempts: {e}")
287
288        await browser.close()
289
290    return all_courses
291
292
293# ── Database update ───────────────────────────────────────
294
295def update_db(courses):
296    """
297    @brief Creates or updates registration.db with the scraped course data.
298
299    @details
300    Connects to (or creates) the SQLite database at DB_FILE. Creates the
301    courses table and indexes if they do not exist. Upserts each course
302    record — inserting new records and updating existing ones on conflict
303    with the unique course_code constraint.
304
305    The following indexes are created for query performance:
306    - idx_department  on courses(department)
307    - idx_course_code on courses(course_code)
308    - idx_status      on courses(status)
309    - idx_instructor  on courses(instructor)
310
311    @param courses A list of course section dictionaries to upsert.
312    @return A tuple (inserted, errors, total) where:
313            - inserted (int): number of records successfully processed
314            - errors (int): number of records that failed
315            - total (int): total records now in the database
316    """
317    if not os.path.exists(DB_FILE):
318        print(f"\n⚠️  {DB_FILE} not found — creating it now...")
319
320    conn = sqlite3.connect(DB_FILE)
321    cursor = conn.cursor()
322
323    cursor.executescript("""
324        CREATE TABLE IF NOT EXISTS courses (
325            id              INTEGER PRIMARY KEY AUTOINCREMENT,
326            course_code     TEXT NOT NULL,
327            department      TEXT,
328            course_number   TEXT,
329            section         TEXT,
330            name            TEXT,
331            status          TEXT,
332            seats_open      INTEGER,
333            seats_total     INTEGER,
334            credits         TEXT,
335            instructor      TEXT,
336            days_time       TEXT,
337            location        TEXT,
338            method          TEXT,
339            begin_date      TEXT,
340            end_date        TEXT,
341            scraped_at      TEXT,
342            UNIQUE(course_code)
343        );
344        CREATE INDEX IF NOT EXISTS idx_department  ON courses(department);
345        CREATE INDEX IF NOT EXISTS idx_course_code ON courses(course_code);
346        CREATE INDEX IF NOT EXISTS idx_status      ON courses(status);
347        CREATE INDEX IF NOT EXISTS idx_instructor  ON courses(instructor);
348    """)
349
350    inserted = errors = 0
351    for c in courses:
352        try:
353            cursor.execute("""
354                INSERT INTO courses (
355                    course_code, department, course_number, section,
356                    name, status, seats_open, seats_total, credits,
357                    instructor, days_time, location, method,
358                    begin_date, end_date, scraped_at
359                ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
360                ON CONFLICT(course_code) DO UPDATE SET
361                    status      = excluded.status,
362                    seats_open  = excluded.seats_open,
363                    seats_total = excluded.seats_total,
364                    instructor  = excluded.instructor,
365                    days_time   = excluded.days_time,
366                    location    = excluded.location,
367                    method      = excluded.method,
368                    begin_date  = excluded.begin_date,
369                    end_date    = excluded.end_date,
370                    scraped_at  = excluded.scraped_at
371            """, (
372                c.get("course_code"), c.get("department"), c.get("course_number"),
373                c.get("section"), c.get("name"), c.get("status"),
374                c.get("seats_open"), c.get("seats_total"), c.get("credits"),
375                c.get("instructor"), c.get("days_time"), c.get("location"),
376                c.get("method"), c.get("begin_date"), c.get("end_date"),
377                c.get("scraped_at"),
378            ))
379            inserted += 1
380        except sqlite3.Error as e:
381            print(f"  DB error for {c.get('course_code')}: {e}")
382            errors += 1
383
384    conn.commit()
385    total = conn.execute("SELECT COUNT(*) FROM courses").fetchone()[0]
386    conn.close()
387    return inserted, errors, total
388
389
390# ── Main ──────────────────────────────────────────────────
391
392async def main():
393    """
394    @brief Entry point — runs the full three-step registration refresh pipeline.
395
396    @details
397    Executes the pipeline in sequence:
398        1. Scrapes all departments from The Q portal via scrape()
399        2. Saves results to registration_sections.json
400        3. Upserts results into registration.db via update_db()
401
402    Prints a summary on completion including total sections scraped,
403    database record count, error count, and elapsed time.
404    """
405    start = datetime.now()
406    print("=" * 50)
407    print("  QCC Registration Refresh")
408    print(f"  {start.strftime('%Y-%m-%d %H:%M:%S')}")
409    print("=" * 50)
410
411    print("\n[1/3] Scraping QCC course offerings...")
412    courses = await scrape()
413    print(f"\n  Total scraped: {len(courses)} sections")
414
415    print("\n[2/3] Saving to JSON...")
416    with open(JSON_FILE, "w", encoding="utf-8") as f:
417        json.dump(courses, f, indent=2)
418    print(f"  Saved {len(courses)} sections to {JSON_FILE}")
419
420    print("\n[3/3] Updating database...")
421    inserted, errors, total = update_db(courses)
422    print(f"  Processed: {len(courses)} | Total in DB: {total} | Errors: {errors}")
423
424    elapsed = (datetime.now() - start).seconds
425    print(f"\n✅ Done in {elapsed}s — {total} courses in database.")
426    print("=" * 50)
427
428
429if __name__ == "__main__":
430    asyncio.run(main())