import asyncio import os import re import sys from datetime import datetime from dotenv import load_dotenv import mysql.connector from playwright.async_api import async_playwright load_dotenv(os.path.join(os.path.dirname(__file__), '../.env')) sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..')) from keyword_tracker import is_keyword_done, mark_keyword_done if sys.platform == 'win32': sys.stdout.reconfigure(encoding='utf-8') # ─── Config ──────────────────────────────────────────────────────────────────── DB_CONFIG = { 'host': os.getenv('DB_HOST'), 'port': int(os.getenv('DB_PORT', 3306)), 'user': os.getenv('DB_USER'), 'password': os.getenv('DB_PASSWORD'), 'database': os.getenv('DB_NAME'), } KEYWORDS = [k.strip() for k in os.getenv('KEYWORDS', '').split(',') if k.strip()] PAGE_WAIT_MS = 4000 BETWEEN_PAGE_MS = 3000 MAX_PAGES = int(os.getenv('EUROPA_MAX_PAGES', 3)) # ─── Helpers ─────────────────────────────────────────────────────────────────── def build_url(keyword, page): from urllib.parse import quote ft = quote(keyword).replace('%20', '+') return ( f'https://ted.europa.eu/en/search/result?FT={ft}' f'&search-scope=ALL&scope=ALL&onlyLatestVersions=false' f'&sortColumn=publication-number&sortOrder=DESC&page={page}&simpleSearchRef=true' ) def parse_date(raw): if not raw: return None s = raw.strip() if not s: return None if re.match(r'^\d{4}-\d{2}-\d{2}', s): return s[:10] + ' 00:00:00' m = re.match(r'^(\d{1,2})[\/\.\-](\d{1,2})[\/\.\-](\d{4})(?:\s+(\d{1,2}:\d{2}:\d{2}))?', s) if m: dd, mm, yyyy, time_ = m.group(1), m.group(2), m.group(3), m.group(4) return f'{yyyy}-{mm.zfill(2)}-{dd.zfill(2)} {time_ or "00:00:00"}' return None def extract_title(description): idx = description.lower().find('place of') if idx > 0: return description[:idx].strip() return description.strip() or None def extract_timezone(deadline_text): if not deadline_text: return None m = re.search(r'\(UTC[+-]\d{2}:\d{2}\)', deadline_text) return m.group(0) if m else None def insert_tender(db, notice_number, description, country, publication_date, deadline, keyword): cursor = db.cursor() cursor.execute( 'SELECT id FROM tenders WHERE source = %s AND source_id = %s', ('europa', notice_number) ) if cursor.fetchone(): print(f' ⤷ Skip duplicate [{notice_number}]') cursor.close() return None title = extract_title(description) or notice_number url = f'https://ted.europa.eu/en/notice/-/detail/{notice_number}' try: cursor.execute( '''INSERT INTO tenders (source, source_id, title, description, url, keyword, detail, closing_date, created_at) VALUES (%s, %s, %s, %s, %s, %s, 0, %s, NOW())''', ('europa', notice_number, title, description, url, keyword, parse_date(deadline)) ) tender_id = cursor.lastrowid cursor.execute( '''INSERT INTO tender_details (tender_id, deadline_text, deadline, deadline_timezone, publication_date, contracting_org_country, created_at) VALUES (%s, %s, %s, %s, %s, %s, NOW())''', (tender_id, deadline or None, parse_date(deadline), extract_timezone(deadline), parse_date(publication_date), country or None) ) db.commit() print(f' ✔ Inserted id={tender_id} [{notice_number}] keyword="{keyword}"') return tender_id except Exception as e: db.rollback() raise e finally: cursor.close() # ─── Main ────────────────────────────────────────────────────────────────────── async def main(): if not KEYWORDS: print('No keywords found in KEYWORDS env variable.') return db = mysql.connector.connect(**DB_CONFIG) print('✔ Database connected') print(f' Keywords: {len(KEYWORDS)} loaded from .env') async with async_playwright() as p: browser = await p.chromium.launch(headless=True) context = await browser.new_context( user_agent=( 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 ' '(KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36' ) ) page = await context.new_page() for keyword in KEYWORDS: if is_keyword_done(db, keyword, 'europa'): print(f'\n [SKIP] Already ran today: "{keyword}"') continue print(f'\n{"═" * 60}') print(f' KEYWORD: "{keyword}"') print('═' * 60) current_page = 1 is_last_page = False while not is_last_page and current_page <= MAX_PAGES: url = build_url(keyword, current_page) print(f'\n ─── Page {current_page} ───') print(f' URL: {url}') try: await page.goto(url, wait_until='networkidle', timeout=60000) except Exception: print(' ⚠ Page load timed out – skipping page.') current_page += 1 await asyncio.sleep(BETWEEN_PAGE_MS / 1000) continue await asyncio.sleep(PAGE_WAIT_MS / 1000) try: await page.wait_for_selector( 'tbody.custom-react-classes-MuiTableBody-root', timeout=30000 ) except Exception: print(' ⚠ Table body not found – no results or end of pages.') break rows = await page.eval_on_selector_all( 'tbody.custom-react-classes-MuiTableBody-root tr', '''(trs) => trs.map(tr => { const tds = Array.from(tr.querySelectorAll('td')); return { notice_number: tds[1]?.innerText?.trim() || '', description: tds[2]?.innerText?.trim() || '', country: tds[3]?.innerText?.trim() || '', publication_date: tds[4]?.innerText?.trim() || '', deadline: tds[5]?.innerText?.trim() || '', }; })''' ) print(f' Found {len(rows)} row(s)') for row in rows: if not row.get('notice_number'): continue try: insert_tender( db, row['notice_number'], row.get('description', ''), row.get('country', ''), row.get('publication_date', ''), row.get('deadline', ''), keyword ) except Exception as e: print(f' ✖ DB error for [{row["notice_number"]}]: {e}') if current_page >= MAX_PAGES: print(f' ✔ Reached max pages ({MAX_PAGES}) — stopping.') is_last_page = True continue last_page_btn = await page.query_selector('button[aria-label="Go to the last page"]') if not last_page_btn: print(' ✔ No "last page" button – end of results.') is_last_page = True else: is_disabled = await last_page_btn.evaluate('btn => btn.disabled') if is_disabled: print(' ✔ "Last page" button disabled – end of results.') is_last_page = True else: current_page += 1 await asyncio.sleep(BETWEEN_PAGE_MS / 1000) mark_keyword_done(db, keyword, 'europa') print(f' [SAVED] Marked keyword done: "{keyword}"') print('\n═══ Scraping complete ═══') await browser.close() db.close() if __name__ == '__main__': asyncio.run(main())