* Conoscenza del computer >> Computer >  >> Hardware

Salvare commenti Facebook in MySQL: guida completa con Graph API e Python

Archiviare i commenti Facebook in un database MySQL richiede l'uso ufficiale della Graph API, una corretta gestione token, paginazione, rate limiting e uno schema DB normalizzato. Ecco la procedura end-to-end pronta per la produzione.

🏗️ 1. Prerequisiti e Setup Facebook Developer

  1. Crea App su developers.facebook.com → "My Apps" → "Create App" → Type "Business".
  2. Configura prodotto "Facebook Login" → Settings → Basic → aggiungi https://localhost (o tuo dominio) in "Valid OAuth Redirect URIs".
  3. Permessi necessari (App Review richiesta per uso produzione):
    • pages_read_engagement — leggere commenti su post di Pagine che gestisci.
    • pages_manage_metadata (opzionale) — per info pagina.
    • instagram_basic, instagram_manage_comments (se commenti IG).
  4. Genera Token:
    • User Access Token (short-lived 1h) → estendi a Long-Lived (60gg) via GET /oauth/access_token?grant_type=fb_exchange_token....
    • Page Access Token (non scade se utente admin e token long-lived valido) → GET /me/accounts con user token.
  5. Salva token in modo sicuro (env var, secret manager, DB cifrato). Mai in codice.

🗄️ 2. Schema MySQL normalizzato (InnoDB, utf8mb4)

CREATE DATABASE `fb_comments` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `fb_comments`;

CREATE TABLE `pages` (
  `page_id` VARCHAR(64) PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(128),
  `access_token` TEXT NOT NULL,           -- Page Access Token (cifrato a riposo)
  `token_expires_at` DATETIME NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `posts` (
  `post_id` VARCHAR(64) PRIMARY KEY,      -- es. "123456789_987654321"
  `page_id` VARCHAR(64) NOT NULL,
  `message` TEXT,
  `created_time` DATETIME NOT NULL,
  `permalink_url` VARCHAR(512),
  `insights_data` JSON,                   -- reach, impressions, etc. (opzionale)
  `fetched_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`page_id`) REFERENCES `pages`(`page_id`) ON DELETE CASCADE,
  INDEX `idx_page_created` (`page_id`, `created_time`)
) ENGINE=InnoDB;

CREATE TABLE `users` (
  `user_id` VARCHAR(64) PRIMARY KEY,
  `name` VARCHAR(255),
  `profile_url` VARCHAR(512),
  `first_seen_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `last_seen_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `comments` (
  `comment_id` VARCHAR(64) PRIMARY KEY,   -- es. "987654321_123456789"
  `post_id` VARCHAR(64) NOT NULL,
  `user_id` VARCHAR(64) NOT NULL,
  `message` TEXT NOT NULL,
  `created_time` DATETIME NOT NULL,
  `like_count` INT UNSIGNED DEFAULT 0,
  `reply_count` INT UNSIGNED DEFAULT 0,
  `parent_comment_id` VARCHAR(64) NULL,   -- per replies (threading)
  `is_hidden` BOOLEAN DEFAULT FALSE,
  `fetched_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`post_id`) REFERENCES `posts`(`post_id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE RESTRICT,
  FOREIGN KEY (`parent_comment_id`) REFERENCES `comments`(`comment_id`) ON DELETE SET NULL,
  INDEX `idx_post_created` (`post_id`, `created_time`),
  INDEX `idx_user_created` (`user_id`, `created_time`)
) ENGINE=InnoDB;

🐍 3. Python Producer: fetch + upsert idempotente

import os
import json
import time
import logging
import requests
import mysql.connector
from mysql.connector import pooling
from datetime import datetime
from typing import Generator, Dict, Any, Optional

# ─── Config ───
GRAPH_VERSION = "v20.0"
BASE_URL = f"https://graph.facebook.com/{GRAPH_VERSION}"
DB_CFG = {
    "host": os.getenv("MYSQL_HOST", "localhost"),
    "user": os.getenv("MYSQL_USER"),
    "password": os.getenv("MYSQL_PASS"),
    "database": "fb_comments",
    "charset": "utf8mb4",
    "autocommit": False,
}
POOL = pooling.MySQLConnectionPool(pool_name="fb_pool", pool_size=4, **DB_CFG)
SESSION = requests.Session()
SESSION.headers.update({"User-Agent": "FB-Comment-Archiver/1.0"})

# ─── Logging ───
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger(__name__)

# ─── Helpers ───
def get_conn():
    return POOL.get_connection()

def graph_get(url: str, params: Dict, token: str, max_retries: int = 5) -> Dict:
    params["access_token"] = token
    for attempt in range(1, max_retries + 1):
        r = SESSION.get(url, params=params, timeout=30)
        if r.status_code == 200:
            return r.json()
        # Rate limit / transient
        if r.status_code in (429, 500, 502, 503, 504):
            wait = min(2 ** attempt, 60)
            log.warning(f"Rate limit / server error {r.status_code}, retry {attempt}/{max_retries} in {wait}s")
            time.sleep(wait)
            continue
        # Auth / permission error
        if r.status_code in (400, 401, 403):
            log.error(f"Graph API error {r.status_code}: {r.text}")
            raise RuntimeError(f"Graph API {r.status_code}: {r.json().get('error',{}).get('message','Unknown')}")
        r.raise_for_status()
    raise RuntimeError(f"Max retries exceeded for {url}")

def fetch_comments_page(post_id: str, page_token: str, after: Optional[str] = None) -> Dict:
    params = {
        "fields": "id,message,created_time,from{id,name},like_count,parent{id}",
        "limit": 100,
        "order": "chronological",
    }
    if after:
        params["after"] = after
    url = f"{BASE_URL}/{post_id}/comments"
    return graph_get(url, params, page_token)

def upsert_user(conn, user_data: Dict):
    sql = """
        INSERT INTO `users` (`user_id`, `name`, `profile_url`)
        VALUES (%s, %s, %s)
        ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `profile_url`=VALUES(`profile_url`), `last_seen_at`=CURRENT_TIMESTAMP
    """
    cur = conn.cursor()
    cur.execute(sql, (user_data["id"], user_data.get("name"), f"https://facebook.com/{user_data['id']}"))
    cur.close()

def upsert_comment(conn, comment: Dict, post_id: str):
    sql = """
        INSERT INTO `comments`
        (`comment_id`, `post_id`, `user_id`, `message`, `created_time`, `like_count`, `reply_count`, `parent_comment_id`)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
            `message`=VALUES(`message`),
            `like_count`=VALUES(`like_count`),
            `reply_count`=VALUES(`reply_count`),
            `parent_comment_id`=VALUES(`parent_comment_id`)
    """
    parent_id = comment.get("parent", {}).get("id") if comment.get("parent") else None
    vals = (
        comment["id"], post_id, comment["from"]["id"], comment.get("message", ""),
        datetime.strptime(comment["created_time"], "%Y-%m-%dT%H:%M:%S%z").replace(tzinfo=None),
        comment.get("like_count", 0), 0, parent_id
    )
    cur = conn.cursor()
    cur.execute(sql, vals)
    cur.close()

# ─── Main Pipeline ───
def process_post(post_id: str, page_token: str):
    log.info(f"Processing post {post_id}")
    conn = get_conn()
    try:
        after = None
        total = 0
        while True:
            data = fetch_comments_page(post_id, page_token, after)
            comments = data.get("data", [])
            if not comments:
                break
            for c in comments:
                upsert_user(conn, c["from"])
                upsert_comment(conn, c, post_id)
                total += 1
            conn.commit()
            # Pagination
            paging = data.get("paging", {})
            cursors = paging.get("cursors", {})
            after = cursors.get("after")
            if not after:
                break
            # Respect rate limit: small delay between pages
            time.sleep(0.2)
        log.info(f"Post {post_id}: {total} comments upserted")
    except Exception as e:
        conn.rollback()
        log.exception(f"Failed post {post_id}: {e}")
        raise
    finally:
        conn.close()

if __name__ == "__main__":
    # Esempio: leggi post_id da env / arg / file
    PAGE_TOKEN = os.getenv("FB_PAGE_TOKEN")
    POST_IDS = os.getenv("FB_POST_IDS", "").split(",")
    if not PAGE_TOKEN or not POST_IDS[0]:
        raise SystemExit("Set FB_PAGE_TOKEN and FB_POST_IDS env vars")
    for pid in POST_IDS:
        process_post(pid.strip(), PAGE_TOKEN)

⚙️ 4. Produzione: aspetti critici da implementare

AreaAzione concreta
Token RotationJob cron settimanale: rileva scadenza Page Token → rigenera via user long-lived token → aggiorna DB pages.access_token.
Rate LimitingGraph API: 200 chiamate/ora/utente (app-level). Implementa token bucket o coda (Redis/RabbitMQ) + worker con backoff esponenziale. Monitora header x-app-usage, x-page-usage.
Delta API (near real-time)Per nuovi commenti: GET /{post_id}/comments?fields=...&since={last_fetched_ts} o Webhooks (commenti real-time) → endpoint HTTPS verificato → coda → worker.
GDPR / PrivacyDPA con Meta (Controller-Controller). Diritto cancellazione: job DELETE FROM comments WHERE user_id IN (SELECT user_id FROM gdpr_deletion_requests). Log accessi. Cifratura a riposo (TDE / column-level per token).
Error Handling & DLQCommenti che falliscono upsert → tabella comments_dlq (payload JSON, error, retry_count) → retry manuale/automatico.
MonitoringPrometheus metrics: fb_comments_fetched_total, fb_comments_upserted_total, fb_api_errors_total, fb_rate_limit_hits_total. Alert su error rate > 1%.
Backfill storicoScript separato con since/until o cursori before per recuperare commenti vecchi. Esegui in finestre low-traffic.

🔐 5. Checklist sicurezza prima del deploy

  • [ ] Token in secret manager (AWS Secrets Manager, HashiCorp Vault, Doppler, .env non in repo).
  • [ ] Connessione MySQL su TLS (CA verificata).
  • [ ] Principio minimo privilegio: DB user solo INSERT, UPDATE, SELECT su tabelle target.
  • [ ] Input validation: comment_id, post_id regex ^[0-9_]+$.
  • [ ] Nessun print(token) / log sensibili.
  • [ ] Test su App Facebook in Development Mode con utenti test.
  • [ ] App Review completata per pages_read_engagement prima di andare live.

In sintesi: usa Graph API ufficiale + Page Token + paginazione cursori + upsert idempotente + pool DB + rate limit handling + Delta/Webhook per tempo reale + GDPR by design. Il codice sopra è uno scheletro produttivo; adatta retry, logging, orchestrazione (Airflow, Prefect, cron) al tuo stack.

Hardware