mirror of
https://github.com/mblanke/Gov_Travel_App.git
synced 2026-03-01 14:10:22 -05:00
mblanke
15094ac94b
- Implemented Python scraper using BeautifulSoup and pandas to automatically collect travel rates from official NJC website - Added currency extraction from table titles (supports EUR, USD, AUD, CAD, ARS, etc.) - Added country extraction from table titles for international rates - Flatten pandas MultiIndex columns for cleaner data structure - Default to CAD for domestic Canadian sources (accommodations and domestic tables) - Created SQLite database schema (raw_tables, rate_entries, exchange_rates, accommodations) - Successfully scraped 92 tables with 17,205 rate entries covering 25 international cities - Added migration script to convert scraped data to Node.js database format - Updated .gitignore for Python files (.venv/, __pycache__, *.pyc, *.sqlite3) - Fixed city validation and currency conversion in main app - Added comprehensive debug and verification scripts This replaces manual JSON maintenance with automated data collection from official government source.
62 lines
1.5 KiB
Python
62 lines
1.5 KiB
Python
"""Test currency extraction step by step"""
|
|
import sqlite3
|
|
import re
|
|
|
|
def _extract_currency_from_title(title):
|
|
"""Extract currency code from table title like 'Albania - Currency: Euro (EUR)'"""
|
|
if not title:
|
|
return None
|
|
# Pattern: "Currency: [Name] ([CODE])"
|
|
match = re.search(r"Currency:\s*[^(]+\(([A-Z]{3})\)", title)
|
|
if match:
|
|
return match.group(1)
|
|
return None
|
|
|
|
conn = sqlite3.connect('data/travel_rates_scraped.sqlite3')
|
|
cursor = conn.cursor()
|
|
|
|
print("Testing currency extraction from stored titles:\n")
|
|
|
|
# Get Argentina table title
|
|
cursor.execute("""
|
|
SELECT title
|
|
FROM raw_tables
|
|
WHERE title LIKE '%Argentina%'
|
|
""")
|
|
row = cursor.fetchone()
|
|
if row:
|
|
title = row[0]
|
|
print(f"Argentina Title: {title}")
|
|
currency = _extract_currency_from_title(title)
|
|
print(f"Extracted Currency: {currency}")
|
|
|
|
# Get Albania table title
|
|
cursor.execute("""
|
|
SELECT title
|
|
FROM raw_tables
|
|
WHERE title LIKE '%Albania%'
|
|
""")
|
|
row = cursor.fetchone()
|
|
if row:
|
|
title = row[0]
|
|
print(f"\nAlbania Title: {title}")
|
|
currency = _extract_currency_from_title(title)
|
|
print(f"Extracted Currency: {currency}")
|
|
|
|
# Check what entries we actually have
|
|
cursor.execute("""
|
|
SELECT COUNT(*)
|
|
FROM rate_entries
|
|
WHERE currency IS NOT NULL
|
|
""")
|
|
print(f"\nTotal entries with currency: {cursor.fetchone()[0]}")
|
|
|
|
cursor.execute("""
|
|
SELECT COUNT(*)
|
|
FROM rate_entries
|
|
WHERE currency IS NULL
|
|
""")
|
|
print(f"Total entries WITHOUT currency: {cursor.fetchone()[0]}")
|
|
|
|
conn.close()
|