load.py spreadsheets phase: fall back to partial path match when exact mission name not found

[ksalamy]

Problem
The --spreadsheets phase of load.py uses an exact match to look up missions in the database (~line 1827):

mission = Mission.objects.get(name=f"{parent_dir}/{row['Mission']}")

This means the spreadsheet entry must exactly match the full DB path. For example, if the spreadsheet has:
2026/20260716d1

...but the DB stores:
2026/OctopusGarden_LASS_2026/20260716d1/multibeam
2026/OctopusGarden_LASS_2026/20260716d1/lidar

...the script logs Not found in database and skips the row entirely, so no metadata (vehicle name, quality, region, etc.) is ever populated.

This was discovered with the July 2026 Octopus Garden / LASS surveys. Jenny had added them to the survey tally spreadsheet using the short form, but none of the metadata loaded.

Proposed Fix
In update_db_from_df(), after the Mission.DoesNotExist exception, add a fallback partial-path match:

⚠️
⚠️ Superseded — see correction in comments below.
⚠️

except Mission.DoesNotExist:
    # Fallback: try partial path match
    # e.g. spreadsheet has "20260716d1", DB has "2026/OctopusGarden_LASS_2026/20260716d1/multibeam"
    candidates = Mission.objects.filter(name__contains=f"/{row['Mission']}/")
    if candidates.count() == 1:
        mission = candidates.first()
        self.logger.info(f"Partial path match for {row['Mission']}: {mission.name}")
    elif candidates.count() > 1:
        self.logger.warning(
            f"Ambiguous partial match for {row['Mission']}: {[m.name for m in candidates]} — skipping"
        )
        continue
    else:
        self.logger.warning(f"Not found in database: {parent_dir}/{row['Mission']}")
        continue

Notes
The fallback should only trigger when the exact match fails, preserving current behaviour for well-formed entries.
If multiple candidates match (e.g. both multibeam and lidar subdirectories), the script should warn and skip rather than silently pick one — or alternatively apply the metadata update to all candidates.
This should also fix similar mismatches for other missions buried in project subdirectories.

Workaround (until fix is merged)
Jenny updates the survey tally spreadsheet to use the full DB path (e.g. 2026/OctopusGarden_LASS_2026/20260716d1/multibeam), one row per subdirectory.

[ksalamy]

Correction to proposed fix
After further investigation, the "Ambiguous match → skip" logic in the original proposed fix is wrong for this use case.

For LASS dives (and any other missions with sensor subdirectories), multiple matches are expected and correct — each dive has both a /multibeam and a /lidar entry in the DB, and both should receive the same metadata from a single spreadsheet row.

Corrected fix for update_db_from_df():
The cleanest fix is to resolve the mission lookup into a list first, then loop the existing field-update code over it — so nothing else in the function needs to change:

try:
    count += 1
    # Get the Mission object(s) for this row
    if row["Mission"].startswith(f"{parent_dir}/"):
        missions_to_update = [Mission.objects.get(name=row["Mission"])]
    else:
        try:
            missions_to_update = [Mission.objects.get(name=f"{parent_dir}/{row['Mission']}")]
        except Mission.DoesNotExist:
            # Fallback: partial path match for missions with sensor subdirectories
            # e.g. spreadsheet has "20260716d1", DB has:
            #   "2026/20260716d1/multibeam"
            #   "2026/20260716d1/lidar"
            candidates = list(
                Mission.objects.filter(name__contains=f"/{row['Mission']}/")
            )
            if candidates:
                self.logger.info(
                    f"Partial path match for {row['Mission']}: "
                    f"updating {[m.name for m in candidates]}"
                )
                missions_to_update = candidates
            else:
                self.logger.warning(
                    f"Not found in database: {parent_dir}/{row['Mission']}"
                )
                continue
    for mission in missions_to_update:
        mission.route_file = row["Route"]
        mission.region_name = row["Location"]
        mission.vehicle_name = row["Vehicle"]
        # ... rest of existing field-update code unchanged ...
        mission.save()
        saved_count += 1
        self.logger.info(f"Updated fields for {mission.name = }")
        # ... citations and quality_categories unchanged ...
except Mission.DoesNotExist:
    # This now only triggers for the startswith(parent_dir) branch above
    self.logger.warning(f"Not found in database: {parent_dir}/{row['Mission']}")
except KeyError as e:
    self.logger.warning(f"KeyError: {e}")

Root cause confirmed
Verified on production (Aug 4, 2026):

• XLSX has: 20260716d1 (one row per dive, no sensor suffix), Vehicle=LASS
• DB has: 2026/20260716d1/multibeam and 2026/20260716d1/lidar (two entries per dive)
• Script tries: exact match for 2026/20260716d1 → DoesNotExist → skipped
• All 8 July 2026 LASS dives (16 DB records total) had vehicle_name=None and empty region_name as a result

One-time workaround applied (Aug 4, 2026)
Manually updated all 16 records directly via Django shell with the correct vehicle_name and region_name values from the XLSX. This is a one-time fix; the permanent solution is this issue.

[MBARIMike]

I'm working on a fix for production and testing on my local dev machine for complete spreadsheet processing. I see that the code was getting the Quality_category* row value, but I see now that the column name is now Quality_category. Not sure if the asterisk is important, but the code will be changed to look for both.

[MBARIMike]

These PRs have been pulled to production and then executed: ... scripts/load.py --spreadsheets -v.