Skip to content

README.md

Latest commit

 

History

History
242 lines (174 loc) · 10.2 KB

README.md

File metadata and controls

242 lines (174 loc) · 10.2 KB
Raw

YT-PAO - YouTube Playlist Analyzer & Organizer

YT-PAO analyzes YouTube playlists and produces reports in multiple formats. It started as a terminal tool and now includes a simple web frontend and a FastAPI backend. This repository contains both the original CLI utilities and the web UI + API, plus Docker configuration for running the whole stack.

Features

  • Analyze playlists and extract titles, URLs, durations, uploaders, view counts, and availability status.
  • Produce reports in cmd, txt, json, csv, html or save directly to a MySQL database.
  • Generate multiple report formats in a single run, for example mySQL and html together.
  • In the web frontend, export the currently selected MySQL report snapshot to csv, sql, txt, json, or html without triggering a new backend generation run.
  • The frontend export controls live in a collapsible panel in the playlist timeline so they do not cover the full page.
  • Playlists can be disabled from the dashboard; disabled playlists are hidden and a later CLI/frontend import of the same playlist URL re-enables them.
  • When a playlist has more than five reports, the timeline shows a compact mini chart of video counts from the first report to the latest one.
  • Work modes: all, available, unavailable.
  • CLI utilities for one-off reports and a web interface for browsing playlists and reports.

Current status

  • Backend: FastAPI application (api.py) — serves API endpoints and static files.
  • Frontend: React + Vite in frontend/ (development with Vite, production build served with Nginx in Docker).
  • Database: MySQL (optional in docker-compose, or use an external MySQL instance via environment variables).
  • Docker: Dockerfile, frontend/Dockerfile and docker-compose.yml included for easy local deployment.

Requirements

Python 3.11+ for the backend and Node.js (recommended 18+) for the frontend development. Install Python dependencies with:

pip install -r requirements.txt

For frontend development:

cd frontend
npm install

Docker and Docker Compose are recommended to run the full stack locally.

Installation (quick)

git clone https://github.com/your-repo/YT-PAO.git
cd YT-PAO
pip install -r requirements.txt

Handling YouTube Anti-Bot Protections (Limits & Missing Uploaders)

Due to aggressive anti-bot protections, YouTube limits unauthenticated API requests to a maximum of 100 videos per playlist and aggressively removes metadata like Uploaders and View Counts (showing as Unknown or 0).

To bypass this and fetch full playlists (e.g., 1000+ videos), you must authenticate yt-dlp using a valid browser session cookie.

How to set up cookies.txt:

  1. Open your browser and log in to YouTube (a secondary/dummy account is fine).
  2. Play any video for about 5-10 seconds. This is a crucial step to generate required "PO Tokens" that prove you are not a bot.
  3. Use a browser extension like "Get cookies.txt LOCALLY" (available for Chrome/Firefox) while on the YouTube tab.
  4. Export the cookies and save the file exactly as cookies.txt in the root directory of this project (next to main.py).
  5. Restart your Docker containers. The docker-compose.yml file is configured to mount this file directly into the backend container.

Note: Cookies expire over time or when you manually log out of that session in your browser. If you start seeing 100-video limits again, simply repeat the process to refresh your cookies.txt.

Usage

CLI (original terminal mode):

python main.py --playlistLink <playlist_link> --resultFormats <cmd|txt|json|csv|html|mySQL> [<cmd|txt|json|csv|html|mySQL> ...] --listMode <all|available|unavailable>

You can pass more than one format in the same invocation:

python main.py --playlistLink <playlist_link> --resultFormats mySQL html --listMode all

If you still use the legacy single-format flag, --resultFormat, it is treated as a compatibility alias for one format.

When multiple formats are requested, each format is processed independently. A failure in one format does not stop the others, and the CLI prints a per-format result.

Thumbnail repair (repair missing thumbnail files from database):

# Scan database and re-download missing thumbnail files
python main.py --repair-thumbnails

# In Docker:
docker exec yt-pao-backend-1 python main.py --repair-thumbnails

This is useful when thumbnail files are missing from disk but still referenced in the database. The repair operation will:

  • Scan all stored thumbnails in the database
  • Check if files physically exist on disk
  • Re-download missing files from their original source URL
  • Update SHA256 hashes in the database

Web API (development):

python -m uvicorn api:app --reload --port 8000
# API examples: http://localhost:8000/api/playlists

The playlist report endpoint accepts a JSON body with a formats array, for example:

{ "formats": ["mySQL", "html"] }

The web UI exposes the same multi-format selection before starting report generation.

The playlist detail page also includes a local export panel. It uses the currently selected timeline snapshot from MySQL and lets you download the report in csv, sql, txt, json, or html format. This export is client-side and does not start a new report job.

The app footer and exported HTML reports display the program version, which is read automatically from Git using git describe --tags --dirty --always and falls back to the short commit hash when tags are not available.

The dashboard now supports soft-delete by setting a playlist as disabled. This hides the playlist from the main list without removing its history, and re-importing the same playlist URL through the CLI or frontend sets it back to enabled automatically.

Frontend (dev):

cd frontend
npm run dev
# Vite dev server: http://localhost:5173

Docker (recommended for local deploy)

Copy .env.example to .env and edit if you want to use an external DB or change defaults.

If your MySQL server is outside Docker, set DB_HOST to that server's address. If it runs on the host machine, use host.docker.internal on Docker Desktop/Windows or the host's IP/DNS name on Linux.

cp .env.example .env
docker compose up --build

The backend container runs by default as the host user with UID/GID 1000:1000. This prevents Permission denied errors when writing to mounted volumes. Override UID and GID in .env when needed.

To run without the bundled MySQL, set DB_HOST in .env to your DB host and start only backend and frontend:

DB_HOST=1.2.3.4
docker compose up --build backend frontend

Configuration

The backend configuration is loaded from environment variables (including .env) via pydantic-settings.

Use these variables for database configuration:

  • DB_HOST
  • DB_PORT
  • DB_USER
  • DB_PASSWORD
  • DB_NAME

Copy .env.example to .env and adjust values for your environment.

When using Docker, UID and GID can be set in .env to override the default backend container user (1000:1000).

File structure (high level)

  • api.py — FastAPI app and API endpoints
  • main.py — original CLI entrypoint and helpers
  • mySQL_manager.py — database utilities
  • frontend/ — React + Vite frontend
  • web_template/ — HTML templates used by CLI HTML output
  • frontend/src/utils/reportExporters.js — client-side export helpers for the selected playlist snapshot
  • docker-compose.yml, Dockerfile, frontend/Dockerfile — docker configuration
  • requirements.txt — Python dependencies

Report styling

The HTML report style now follows the dark, card-based frontend look and is shared between the CLI and the frontend export helper. Both outputs use the same overall visual language so exported HTML looks consistent with the playlist detail page.

Comparing Playlists / Detecting Missing Videos

YT-PAO can compare latest reports for playlists stored in MySQL to identify missing videos between playlists. The SQL example below demonstrates the query pattern used to compute differences between two playlist snapshots.

WITH params AS (
   SELECT 2 AS p1, 19 AS p2 -- Replace 2 and 19 with your playlist IDs
),
last_reports AS (
   SELECT r.playlist_id, r.report_id
   FROM ytp_reports r
   INNER JOIN (
      SELECT playlist_id, MAX(report_date) AS max_date
      FROM ytp_reports, params
      WHERE playlist_id IN (SELECT p1 FROM params UNION SELECT p2 FROM params)
      GROUP BY playlist_id
   ) latest
     ON r.playlist_id = latest.playlist_id
    AND r.report_date = latest.max_date
),
videos_in_playlists AS (
   SELECT rd.video_id, v.video_title, v.video_url, r.playlist_id
   FROM ytp_report_details rd
   JOIN ytp_reports r ON rd.report_id = r.report_id
   JOIN ytp_videos v ON rd.video_id = v.video_id
   WHERE r.report_id IN (SELECT report_id FROM last_reports)
)

-- Videos in Playlist p1 missing from p2
SELECT 
   v1.video_id,
   v1.video_title,
   v1.video_url,
   (SELECT p1 FROM params) AS playlist_source
FROM (SELECT * FROM videos_in_playlists WHERE playlist_id = (SELECT p1 FROM params)) v1
LEFT JOIN (SELECT * FROM videos_in_playlists WHERE playlist_id = (SELECT p2 FROM params)) v2
      ON v1.video_id = v2.video_id
WHERE v2.video_id IS NULL

UNION

-- Videos in Playlist p2 missing from p1
SELECT 
   v2.video_id,
   v2.video_title,
   v2.video_url,
   (SELECT p2 FROM params) AS playlist_source
FROM (SELECT * FROM videos_in_playlists WHERE playlist_id = (SELECT p2 FROM params)) v2
LEFT JOIN (SELECT * FROM videos_in_playlists WHERE playlist_id = (SELECT p1 FROM params)) v1
      ON v2.video_id = v1.video_id
WHERE v1.video_id IS NULL;

Architecture

The core of this project is the Python/FastAPI backend, MySQL database design, and the data processing logic (which originally evolved from my custom CLI scripts). The React frontend was quickly scaffolded with the help of AI purely to visualize the backend data, as my main focus as a developer is backend engineering and infrastructure.

Authors

  • Kordight (Sebastian Legieziński) - GitHub