Script to backup Metabase cards from a collection

backup.py

import os, json, re, pathlib, requests
from urllib.parse import urljoin

METABASE_HOST   = os.environ.get("METABASE_HOST",   "https://bounce.metabaseapp.com/")
API_KEY         = os.environ.get("METABASE_API_KEY")
TARGET_COLL_NAME= os.environ.get("METABASE_COLLECTION_NAME")
TARGET_COLL_ID  = os.environ.get("METABASE_COLLECTION_ID")
OUTPUT_DIR      = os.environ.get("OUTPUT_DIR", "./metabase_export")

session = requests.Session()
headers = {"Content-Type": "application/json"}
if API_KEY:
    headers["X-API-KEY"] = API_KEY
else:
    raise SystemExit("Please set METABASE_API_KEY or add X-Metabase-Session in the code.")

def get_json(path, params=None):
    r = session.get(urljoin(METABASE_HOST, path), headers=headers, params=params, timeout=60)
    r.raise_for_status()
    return r.json()

def sanitize(name):
    return re.sub(r"[^a-zA-Z0-9_.-]+", "_", name).strip("_") or "unnamed"

pathlib.Path(OUTPUT_DIR).mkdir(parents=True, exist_ok=True)

def find_collection_id_by_name(name):
    res = get_json("/api/search", params={"q": name})
    for item in res.get("data", []):
        if item.get("model") == "collection" and item.get("name") == name:
            return item.get("id")
    raise RuntimeError(f'Collection named "{name}" not found. Provide METABASE_COLLECTION_ID to skip name lookup.')

if not TARGET_COLL_ID:
    if not TARGET_COLL_NAME:
        raise SystemExit("Set METABASE_COLLECTION_NAME or METABASE_COLLECTION_ID.")
    TARGET_COLL_ID = find_collection_id_by_name(TARGET_COLL_NAME)

cards = get_json(f"/api/collection/{TARGET_COLL_ID}/items", params={"models": "card"})['data']

print(f"Found {len(cards)} questions in collection {TARGET_COLL_ID}")

for it in cards:
    card_id = it["id"]
    card = get_json(f"/api/card/{card_id}")
    card_name = sanitize(card.get("name") or f"card_{card_id}")

    # Write full card JSON (includes dataset_query and metadata)
    with open(pathlib.Path(OUTPUT_DIR, f"{card_id}_{card_name}.json"), "w", encoding="utf-8") as f:
        json.dump(card, f, ensure_ascii=False, indent=2)

    # If native SQL, extract the SQL string
    dq = (card.get("dataset_query") or {})
    native = dq.get("native") or {}
    sql = native.get("query")
    if sql:
        with open(pathlib.Path(OUTPUT_DIR, f"{card_id}_{card_name}.sql"), "w", encoding="utf-8") as f:
            f.write(sql)

print(f"Export complete → {OUTPUT_DIR}")