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
- Crea App su developers.facebook.com → "My Apps" → "Create App" → Type "Business".
- Configura prodotto "Facebook Login" → Settings → Basic → aggiungi
https://localhost(o tuo dominio) in "Valid OAuth Redirect URIs". - 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).
- 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/accountscon user token.
- User Access Token (short-lived 1h) → estendi a Long-Lived (60gg) via
- 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
| Area | Azione concreta |
|---|---|
| Token Rotation | Job cron settimanale: rileva scadenza Page Token → rigenera via user long-lived token → aggiorna DB pages.access_token. |
| Rate Limiting | Graph 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 / Privacy | DPA 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 & DLQ | Commenti che falliscono upsert → tabella comments_dlq (payload JSON, error, retry_count) → retry manuale/automatico. |
| Monitoring | Prometheus metrics: fb_comments_fetched_total, fb_comments_upserted_total, fb_api_errors_total, fb_rate_limit_hits_total. Alert su error rate > 1%. |
| Backfill storico | Script 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, SELECTsu tabelle target. - [ ] Input validation:
comment_id,post_idregex^[0-9_]+$. - [ ] Nessun
print(token)/ log sensibili. - [ ] Test su App Facebook in Development Mode con utenti test.
- [ ] App Review completata per
pages_read_engagementprima 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.
- Video da MacBook non si carica su Facebook: cause e soluzioni complete
- Video non si carica su Facebook da MacBook: cause e soluzioni passo passo
Hardware
- Come impedire a Facebook di filtrare i commenti: mostra tutti in ordine cronologico
- PayPal è gratis? Costi, commissioni e cosa sapere prima di aprire un conto
- Come vedere i Reel visti di recente su Facebook (cronologia visualizzazioni)
- Come inviare un messaggio da una pagina Facebook
- Come cambiare dimensione e colore font nei post Facebook: estensioni e app terze
- Come cancellare i suggerimenti di ricerca su Facebook da qualsiasi dispositivo
- Come condividere un video TikTok su Facebook
- Messenger non funziona su iPad/iPhone? 7 fix (rete, cache, aggiornamento, reinstall, riavvio)