import requests import time import sys import os from datetime import datetime if sys.platform == 'win32': sys.stdout.reconfigure(encoding='utf-8') from dotenv import load_dotenv load_dotenv(os.path.join(os.path.dirname(__file__), '../.env')) import mysql.connector sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..')) from keyword_tracker import is_keyword_done, mark_keyword_done def get_db_connection(): return mysql.connector.connect( 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()] # GREEK_KEYWORDS = [k.strip() for k in os.getenv('GREEK_KEYWORDS', '').split(',') if k.strip()] # ALL_KEYWORDS = KEYWORDS + GREEK_KEYWORDS def load_keywords_from_db(): """Fetch keywords from managed_keywords table for 'en' and 'el' languages.""" result = {'en': [], 'el': []} try: conn = get_db_connection() cursor = conn.cursor() cursor.execute( "SELECT language_code, keyword FROM managed_keywords WHERE language_code IN ('en', 'el')" ) for lang_code, keyword_cell in cursor.fetchall(): for kw in keyword_cell.split(','): kw = kw.strip() if kw: result[lang_code].append(kw) cursor.close() conn.close() except Exception as e: print(f" Warning: Could not load keywords from DB: {e}") return result _kw_by_lang = load_keywords_from_db() KEYWORDS = _kw_by_lang['en'] GREEK_KEYWORDS = _kw_by_lang['el'] ALL_KEYWORDS = KEYWORDS + GREEK_KEYWORDS print(f"EN keywords ({len(KEYWORDS)}): {KEYWORDS}") print(f"EL keywords ({len(GREEK_KEYWORDS)}): {GREEK_KEYWORDS}") print(f"Total : {len(ALL_KEYWORDS)}") # exit() API_URL = "https://cerpp.eprocurement.gov.gr/deliberationWebApi/deliberation/search" COMMENTS_API = "https://cerpp.eprocurement.gov.gr/deliberationWebApi/deliberationComment/deliberation/{}" DETAIL_BASE = "https://cerpp.eprocurement.gov.gr/deliberation/#/deliberation/public/view/{}" PAGE_SIZE = 10 SOURCE = 'cerpp' MAX_PAGES = int(os.getenv('CERPP_MAX_PAGES', 10)) def get_session(): session = requests.Session() session.headers.update({ 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/145.0.0.0 Safari/537.36', 'Accept': 'application/json, text/plain, */*', 'Content-Type': 'application/json', }) return session def fetch_page(session, keyword, page): payload = { "dateFrom": None, "dateTill": None, "page": page, "referenceNumber": "", "size": PAGE_SIZE, "title": keyword, } try: r = session.post(API_URL, json=payload, timeout=60) if r.status_code == 200: return r.json() else: print(f" HTTP {r.status_code}") return None except requests.RequestException as e: print(f" Request Error: {e}") return None def safe_date(val): if not val: return None try: s = str(val).strip()[:19] for fmt in ('%Y-%m-%dT%H:%M:%S', '%Y-%m-%d'): try: return datetime.strptime(s, fmt).date() except ValueError: continue except Exception: pass return None def safe_datetime(val): if not val: return None try: s = str(val).strip()[:19] for fmt in ('%Y-%m-%dT%H:%M:%S', '%Y-%m-%d'): try: return datetime.strptime(s, fmt) except ValueError: continue except Exception: pass return None def fetch_comments(session, source_id): try: r = session.get(COMMENTS_API.format(source_id), timeout=30) if r.status_code == 200: data = r.json() return data if isinstance(data, list) else [] return [] except requests.RequestException: return [] def save_comments(cursor, tender_id, comments): for c in comments: comment_id = str(c.get('comDbId', '')) if not comment_id: continue cursor.execute( "SELECT id FROM cerpp_comments WHERE comment_id = %s LIMIT 1", (comment_id,) ) if cursor.fetchone(): continue cursor.execute( """INSERT INTO cerpp_comments (tender_id, comment_id, commenter_name, commenter_email, posted_date, title, comment, is_rejected, reference_number) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)""", ( tender_id, comment_id, c.get('comName', ''), c.get('comEmail', ''), safe_datetime(c.get('postedDate')), c.get('comTitle', ''), c.get('comComment', ''), 1 if c.get('isRejected') else 0, c.get('referenceNumber', ''), ) ) def insert_tenders(items, keyword, session): if not items: return 0, 0 conn = get_db_connection() cursor = conn.cursor() inserted = 0 updated = 0 for item in items: source_id = str(item.get('dbid', '')) ref_num = item.get('referenceNumber', '') or '' title = item.get('title', '') or '' org = item.get('organization', '') or '' desc = item.get('description', '') or '' pub_date = safe_datetime(item.get('publicationDate')) end_date = safe_date(item.get('endDate')) closed = item.get('closed', 0) status = 'Closed' if closed else 'Open' url = DETAIL_BASE.format(source_id) try: cursor.execute( "SELECT id FROM tenders WHERE source = %s AND source_id = %s LIMIT 1", (SOURCE, source_id) ) row = cursor.fetchone() if row: tender_id = row[0] cursor.execute( """UPDATE tenders SET title = %s, organization = %s, description = %s, status = %s, closing_date = %s, publication_type = %s, keyword = %s, updated_at = NOW() WHERE id = %s""", (title, org, desc, status, end_date, pub_date, keyword, tender_id) ) updated += 1 else: cursor.execute( """INSERT INTO tenders (source, source_id, title, reference_number, url, organization, description, status, closing_date, publication_type, keyword, detail, created_at, updated_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 0, NOW(), NOW())""", (SOURCE, source_id, title, ref_num, url, org, desc, status, end_date, pub_date, keyword) ) tender_id = cursor.lastrowid inserted += 1 # Fetch and save comments for new records comments = fetch_comments(session, source_id) if comments: save_comments(cursor, tender_id, comments) time.sleep(0.3) # Upsert tender_details last_modified = safe_datetime(item.get('lastUpdateDate')) cursor.execute( "SELECT id FROM tender_details WHERE tender_id = %s LIMIT 1", (tender_id,) ) detail_row = cursor.fetchone() if detail_row: cursor.execute( """UPDATE tender_details SET publication_date = %s, submission_end = %s, last_modified = %s, updated_at = NOW() WHERE id = %s""", (pub_date, safe_datetime(item.get('endDate')), last_modified, detail_row[0]) ) else: cursor.execute( """INSERT INTO tender_details (tender_id, publication_date, submission_end, last_modified, created_at, updated_at) VALUES (%s, %s, %s, %s, NOW(), NOW())""", (tender_id, pub_date, safe_datetime(item.get('endDate')), last_modified) ) except Exception as e: print(f" DB Error ({source_id}): {e}") conn.rollback() continue conn.commit() cursor.close() conn.close() return inserted, updated def get_current_count(): conn = get_db_connection() cursor = conn.cursor() cursor.execute("SELECT COUNT(*) FROM tenders WHERE source = %s", (SOURCE,)) count = cursor.fetchone()[0] cursor.close() conn.close() return count def scrape_keyword(session, keyword): print(f"\n Keyword: \"{keyword}\"") page = 0 total_fetched = 0 total_inserted = 0 total_updated = 0 consecutive_errors = 0 while True: print(f" [Page {page + 1}] Fetching...", end=' ') data = fetch_page(session, keyword, page) if data is None: consecutive_errors += 1 print(f"Failed (#{consecutive_errors})") if consecutive_errors >= 3: print(" Too many errors, skipping keyword.") break time.sleep(2) continue items = data.get('content', []) count = len(items) if count == 0: print("No results.") break consecutive_errors = 0 inserted, updated = insert_tenders(items, keyword, session) total_fetched += count total_inserted += inserted total_updated += updated total_elements = data.get('totalElements', 0) print(f"Got {count} (New: {inserted}, Updated: {updated}) | Total: {total_elements:,}") if data.get('last', True): break if page + 1 >= MAX_PAGES: print(f" Reached max pages ({MAX_PAGES}) — stopping.") break page += 1 time.sleep(1) print(f" => Fetched: {total_fetched}, New: {total_inserted}, Updated: {total_updated}") return total_fetched, total_inserted, total_updated def scrape_tenders(): print("=" * 70) print("CERPP.EPROCUREMENT.GOV.GR - Keyword Tender Scraper") print("=" * 70) print(f"KEYWORDS : {len(KEYWORDS)}") print(f"GREEK_KEYWORDS: {len(GREEK_KEYWORDS)}") print(f"Total : {len(ALL_KEYWORDS)}") session = get_session() grand_fetched = 0 grand_inserted = 0 grand_updated = 0 tracker_conn = get_db_connection() for i, keyword in enumerate(ALL_KEYWORDS, 1): print(f"\n[{i}/{len(ALL_KEYWORDS)}]", end='') if is_keyword_done(tracker_conn, keyword, SOURCE): print(f" [SKIP] Already ran today: \"{keyword}\"") continue fetched, inserted, updated = scrape_keyword(session, keyword) grand_fetched += fetched grand_inserted += inserted grand_updated += updated mark_keyword_done(tracker_conn, keyword, SOURCE) print(f" [SAVED] Marked keyword done: \"{keyword}\"") time.sleep(1) tracker_conn.close() print("\n" + "=" * 70) print("Scraping Complete!") print("=" * 70) print(f" Keywords searched : {len(ALL_KEYWORDS)}") print(f" Total fetched : {grand_fetched:,}") print(f" New : {grand_inserted:,}") print(f" Updated : {grand_updated:,}") print(f" DB total (cerpp) : {get_current_count():,}") print("=" * 70) if __name__ == "__main__": try: scrape_tenders() except KeyboardInterrupt: print(f"\n\nInterrupted. DB total: {get_current_count():,}") except Exception as e: print(f"\nFatal Error: {e}") import traceback traceback.print_exc()