Spaces:
Sleeping
Sleeping
| import requests | |
| from bs4 import BeautifulSoup | |
| from datetime import datetime | |
| import pandas as pd | |
| import time | |
| from typing import List, Dict | |
| import threading | |
| from app.logger import get_app_logger | |
| logger = get_app_logger("scraper") | |
| # Global event to signal stopping of scraping tasks | |
| stop_event = threading.Event() | |
| BASE_URL = "https://komoditas.batangkab.go.id/statistik_komo" | |
| KOMODITAS_MAPPING = { | |
| 34: {"id": 1, "nama": "Bawang Merah"}, | |
| 35: {"id": 2, "nama": "Bawang Putih"}, | |
| 3: {"id": 3, "nama": "Beras IR-64 (KW Medium)"}, | |
| 46: {"id": 4, "nama": "Beras IR-64 (KW Premium)"}, | |
| 30: {"id": 5, "nama": "Cabe Merah Besar Keriting"}, | |
| 31: {"id": 6, "nama": "Cabe Merah Besar Teropong"}, | |
| 32: {"id": 7, "nama": "Cabe Rawit Merah"}, | |
| 33: {"id": 8, "nama": "Cabe Rawit Hijau"}, | |
| 11: {"id": 9, "nama": "Daging Ayam Ras"}, | |
| 12: {"id": 10, "nama": "Daging Ayam Kampung"}, | |
| 10: {"id": 11, "nama": "Daging Sapi Murni (Has)"}, | |
| 14: {"id": 12, "nama": "Telur Ayam Ras"}, | |
| 15: {"id": 13, "nama": "Telur Ayam Kampung"}, | |
| 63: {"id": 14, "nama": "Jeruk (1 kg)"}, | |
| 23: {"id": 15, "nama": "Jagung Pipilan Kering"}, | |
| 42: {"id": 16, "nama": "Kacang Hijau"}, | |
| 43: {"id": 17, "nama": "Kacang Tanah"}, | |
| 27: {"id": 18, "nama": "Kacang Kedelai Import"}, | |
| 28: {"id": 19, "nama": "Kacang Kedelai Lokal"}, | |
| 44: {"id": 20, "nama": "Ketela Pohon"}, | |
| 60: {"id": 21, "nama": "Pisang Ambon/Pisang Lokal (1 kg)"}, | |
| 48: {"id": 22, "nama": "Ikan Bandeng"}, | |
| 64: {"id": 23, "nama": "Ikan Kembung"}, | |
| 49: {"id": 24, "nama": "Ikan Asin Teri"}, | |
| 57: {"id": 25, "nama": "Udang Jerbung"}, | |
| 24: {"id": 26, "nama": "Tepung Terigu"}, | |
| 25: {"id": 27, "nama": "Tepung Terigu Segitiga Biru (KW medium)"}, | |
| 5: {"id": 28, "nama": "Gula Pasir Lokal (KW Medium)"}, | |
| 38: {"id": 29, "nama": "Garam Beryodium Bata"}, | |
| 39: {"id": 30, "nama": "Garam Beryodium Halus"}, | |
| 45: {"id": 31, "nama": "LPG 3 KG"}, | |
| 7: {"id": 32, "nama": "Minyak Goreng Bimoli Botol"}, | |
| 8: {"id": 33, "nama": "Minyak Goreng Curah (Tanpa Merk)"}, | |
| 41: {"id": 34, "nama": "Mie Instan Merk Indomie Kari Ayam"}, | |
| 18: {"id": 35, "nama": "Susu Kental Manis Merk Bendera"}, | |
| 19: {"id": 36, "nama": "Susu Kental Manis Merk Indomilk"}, | |
| 21: {"id": 37, "nama": "Susu Bubuk Merk Indomilk Coklat"}, | |
| 22: {"id": 38, "nama": "Susu Bubuk Merk Indomilk Full Cream"}, | |
| 61: {"id": 39, "nama": "Dancow Vanila (400 - 500 gram)"}, | |
| 62: {"id": 40, "nama": "Susu Balita (SGM atau sejenisnya 400 gram)"}, | |
| 50: {"id": 41, "nama": "Pupuk UREA"}, | |
| 51: {"id": 42, "nama": "Pupuk SP-36"}, | |
| 52: {"id": 43, "nama": "Pupuk ZA"}, | |
| 53: {"id": 44, "nama": "Pupuk KCL"}, | |
| 54: {"id": 45, "nama": "Semen Tiga Roda"}, | |
| 55: {"id": 46, "nama": "Semen Gresik"}, | |
| 56: {"id": 47, "nama": "Semen Holcim"}, | |
| 58: {"id": 48, "nama": "Tempe (1 kg)"}, | |
| 59: {"id": 49, "nama": "Tahu Mentah (1 kg)"} | |
| } | |
| BULAN_MAP = { | |
| "januari": "01", "februari": "02", "maret": "03", "april": "04", | |
| "mei": "05", "juni": "06", "juli": "07", "agustus": "08", | |
| "september": "09", "oktober": "10", "november": "11", "desember": "12" | |
| } | |
| NAMA_PASAR = { | |
| 1: "Pasar Batang", | |
| 2: "Pasar Bandar", | |
| 3: "Pasar Limpung" | |
| } | |
| def convert_tanggal_indo(tanggal_text: str) -> str: | |
| try: | |
| parts = tanggal_text.strip().lower().split() | |
| hari = parts[0].zfill(2) | |
| bulan = BULAN_MAP[parts[1]] | |
| tahun = parts[2] | |
| return f"{tahun}-{bulan}-{hari}" | |
| except: | |
| return None | |
| def clean_harga(value: str) -> float: | |
| """ | |
| Cleans price string into float. Handles Indonesian formats (e.g., 13.500,00 or 13500,00). | |
| """ | |
| if value is None: | |
| return 0.0 | |
| value = str(value).strip() | |
| if value in ("", "-", "0"): | |
| return 0.0 | |
| # Remove currency symbols and other non-numeric chars except comma and dot | |
| import re | |
| value = re.sub(r'[^\d,.]', '', value) | |
| # Handle Indonesian format: dots as thousands, comma as decimal | |
| # Or common web format: commas as thousands, dot as decimal | |
| # If both present, assume last one is decimal | |
| if "," in value and "." in value: | |
| if value.rfind(",") > value.rfind("."): | |
| # Dot is thousands, comma is decimal | |
| value = value.replace(".", "").replace(",", ".") | |
| else: | |
| # Comma is thousands, dot is decimal | |
| value = value.replace(",", "") | |
| elif "," in value: | |
| # Only comma: if it looks like decimal (e.g. 13500,00), replace with dot | |
| # If it looks like thousands (e.g. 13,500), remove it | |
| if len(value.split(",")[-1]) <= 2: # Likely decimal | |
| value = value.replace(",", ".") | |
| else: | |
| value = value.replace(",", "") | |
| try: | |
| return float(value) | |
| except (ValueError, TypeError): | |
| return 0.0 | |
| def is_summary_row(text: str) -> bool: | |
| text = text.lower() | |
| keywords = ["harga tertinggi", "harga rata", "harga terendah"] | |
| return any(k in text for k in keywords) | |
| def scraping_hari_ini(target_date: datetime = None) -> pd.DataFrame: | |
| if target_date is None: | |
| target_date = datetime.now() | |
| hasil = [] | |
| logger.info("=" * 50) | |
| logger.info(f"🕸️ Memulai proses scraping untuk tanggal {target_date.strftime('%Y-%m-%d')}") | |
| logger.info("=" * 50) | |
| stop_event.clear() | |
| for scraping_id, info in KOMODITAS_MAPPING.items(): | |
| database_id = info["id"] | |
| nama_komoditas = info["nama"] | |
| if stop_event.is_set(): | |
| logger.warning("🛑 Scraping dihentikan atas permintaan pengguna.") | |
| break | |
| params = { | |
| "id_komoditi": scraping_id, | |
| "bulan": target_date.month, | |
| "tahun": target_date.year | |
| } | |
| try: | |
| response = requests.get(BASE_URL, params=params, timeout=30) | |
| if response.status_code != 200: | |
| logger.warning(f"⚠️ Gagal mengambil data ID {scraping_id} (HTTP {response.status_code})") | |
| continue | |
| soup = BeautifulSoup(response.text, "html.parser") | |
| table = soup.find("table") | |
| if not table: | |
| continue | |
| rows = table.find_all("tr") | |
| found_data = False | |
| for row in rows[1:]: | |
| cols = row.find_all(["td", "th"]) | |
| data = [col.get_text(strip=True) for col in cols] | |
| if len(data) < 4 or is_summary_row(data[0]): | |
| continue | |
| tanggal = convert_tanggal_indo(data[0]) | |
| if not tanggal or tanggal != target_date.strftime("%Y-%m-%d"): | |
| continue | |
| found_data = True | |
| for pasar_id, harga in enumerate([clean_harga(data[1]), clean_harga(data[2]), clean_harga(data[3])], start=1): | |
| hasil.append({ | |
| "komoditas_id": database_id, | |
| "komoditas_nama": nama_komoditas, | |
| "pasar_id": pasar_id, | |
| "tanggal": tanggal, | |
| "harga": harga | |
| }) | |
| if found_data: | |
| logger.info(f"✅ Scraping sukses untuk {nama_komoditas} (ID {scraping_id} -> DB ID {database_id})") | |
| time.sleep(0.5) | |
| except Exception as e: | |
| logger.error(f"❌ Error scraping ID {scraping_id}: {e}") | |
| df = pd.DataFrame(hasil) | |
| logger.info(f"📊 Total baris hasil scraping: {len(df)}") | |
| return df | |
| def upload_to_mysql(df: pd.DataFrame, engine) -> Dict: | |
| pasar_berhasil = set() | |
| pasar_harga_0 = set() | |
| komoditas_berhasil = [] | |
| komoditas_harga_0 = [] | |
| stats = { | |
| "total_insert": 0, | |
| "total_update": 0, | |
| "total_skip": 0, | |
| "total_harga_0": 0, | |
| "pasar_berhasil": [], | |
| "pasar_harga_0": [], | |
| "komoditas_berhasil_count": 0, | |
| "komoditas_harga_0_count": 0 | |
| } | |
| logger.info("🚀 Memulai proses upload ke database MySQL...") | |
| if df.empty: | |
| logger.warning("⚠️ DataFrame kosong, tidak ada data yang diupload.") | |
| return stats | |
| with engine.begin() as conn: | |
| for _, row in df.iterrows(): | |
| if stop_event.is_set(): | |
| logger.warning("🛑 Upload database dihentikan atas permintaan pengguna.") | |
| break | |
| komoditas_id = int(row["komoditas_id"]) | |
| komoditas_nama = row["komoditas_nama"] | |
| pasar_id = int(row["pasar_id"]) | |
| tanggal = row["tanggal"] | |
| harga_baru = float(row["harga"]) | |
| pasar_nama = NAMA_PASAR.get(pasar_id, f"Pasar {pasar_id}") | |
| if harga_baru <= 0: | |
| stats["total_harga_0"] += 1 | |
| komoditas_harga_0.append(komoditas_id) | |
| pasar_harga_0.add(pasar_nama) | |
| continue | |
| check_query = """ | |
| SELECT id, harga FROM harga_harian | |
| WHERE komoditas_id = %s AND pasar_id = %s AND tanggal = %s LIMIT 1 | |
| """ | |
| result = conn.exec_driver_sql(check_query, (komoditas_id, pasar_id, tanggal)).fetchone() | |
| if result: | |
| existing_id, harga_lama = result[0], float(result[1]) | |
| if harga_lama <= 0 and harga_baru > 0: | |
| conn.exec_driver_sql("UPDATE harga_harian SET harga = %s WHERE id = %s", (harga_baru, existing_id)) | |
| stats["total_update"] += 1 | |
| komoditas_berhasil.append(komoditas_id) | |
| pasar_berhasil.add(pasar_nama) | |
| logger.info(f"✏️ Update sukses: {komoditas_nama} di {pasar_nama} -> Rp {harga_baru:,.0f}") | |
| else: | |
| stats["total_skip"] += 1 | |
| else: | |
| conn.exec_driver_sql( | |
| "INSERT INTO harga_harian (komoditas_id, pasar_id, tanggal, harga) VALUES (%s, %s, %s, %s)", | |
| (komoditas_id, pasar_id, tanggal, harga_baru) | |
| ) | |
| stats["total_insert"] += 1 | |
| komoditas_berhasil.append(komoditas_id) | |
| pasar_berhasil.add(pasar_nama) | |
| logger.info(f"📥 Insert sukses: {komoditas_nama} di {pasar_nama} -> Rp {harga_baru:,.0f}") | |
| stats["pasar_berhasil"] = sorted(list(pasar_berhasil)) | |
| stats["pasar_harga_0"] = sorted(list(pasar_harga_0)) | |
| stats["komoditas_berhasil_count"] = len(set(komoditas_berhasil)) | |
| stats["komoditas_harga_0_count"] = len(set(komoditas_harga_0)) | |
| logger.info("=" * 50) | |
| logger.info("📈 Ringkasan Upload Database:") | |
| logger.info(f" • Insert Baru : {stats['total_insert']}") | |
| logger.info(f" • Update Data : {stats['total_update']}") | |
| logger.info(f" • Diabaikan : {stats['total_skip']}") | |
| logger.info(f" • Harga Nol (0) : {stats['total_harga_0']}") | |
| logger.info(f" • Pasar Sukses : {', '.join(stats['pasar_berhasil']) or '-'}") | |
| logger.info("=" * 50) | |
| return stats |