POKETL

ETL pipeline for Pokémon TCG Pocket tournament data — scrape, transform, load into PostgreSQL.

Data source: LimitlessTCG

---

Overview

POKETL scrapes completed Pokémon TCG Pocket tournaments from LimitlessTCG, structures the raw HTML into JSON, and loads everything into a PostgreSQL database ready for analysis.

What gets loaded:

Table	Content
dwh_cards	Card catalog with stats scraped from limitlesstcg.com
wrk_decklists	Every card in every player's deck, with deck identity (deck_id) and enrichments (family_deck, main_set, newest_set)
wrk_tournaments	Tournament metadata + most recent card set used
wrk_players	Unique players across all tournaments
wrk_matches	Head-to-head results linked to deck IDs
wrk_results	Final standings per tournament

---

Project Structure

POKETL/
├── run.py                          # CLI entry point
├── scripts/
│   ├── utils.py                    # Shared DB connection, logger, DATA_DIR
│   ├── scraping_tournaments.py     # Async scraper → JSON files
│   ├── insert_wrk_cards.py         # Populate dwh_cards
│   ├── insert_wrk_decklists.py     # Populate wrk_decklists
│   ├── insert_wrk_tournaments.py   # Populate wrk_tournaments
│   ├── insert_wrk_players.py       # Populate wrk_players
│   ├── insert_wrk_matches.py       # Populate wrk_matches
│   └── insert_wrk_results.py       # Populate wrk_results
├── sample_output/                  # Generated JSON files (git-ignored)
├── .env.example                    # Environment variable template
├── .gitignore
└── requirements.txt

---

Prerequisites

• Python 3.10+
• PostgreSQL 14+

---

Setup

1. Clone

git clone https://github.com/Mekterz/POKETL.git
cd POKETL

2. Install dependencies

pip install -r requirements.txt

3. Configure environment

cp .env.example .env
# Edit .env with your PostgreSQL credentials

4. Create the database

CREATE DATABASE poketl;
CREATE USER poketl_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE poketl TO poketl_user;
\c poketl
GRANT ALL ON SCHEMA public TO poketl_user;

---

Usage

Scrape tournaments

python run.py scrape

Fetches all new completed tournaments from LimitlessTCG and saves them as JSON in sample_output/. Already-scraped tournaments are skipped automatically.

Run the full pipeline

python run.py insert

Run a single step

python run.py insert cards
python run.py insert decklists
python run.py insert tournaments
python run.py insert players
python run.py insert matches
python run.py insert results

Pipeline order

The steps have dependencies and must run in this order:

scrape → cards → decklists → tournaments → players → matches → results

Each insert script fully rebuilds its table on every run (full refresh).

---

Database Schema

dwh_cards          wrk_players
  card_id (PK)       player_id (PK)
  card_name          player_name
  card_set           player_country
  card_type
  card_subtype     wrk_tournaments
  card_hp            tournament_id (PK)
  card_illustrator   tournament_name
  evolution_n1       tournament_date
  stage_evo          tournament_organizer
  card_url           tournament_format
  card_img_url       tournament_nb_players
  evo_finale         newest_set

wrk_decklists                        wrk_matches
  deck_id (PK)                         match_id (PK)
  deck_instance_id                     tournament_id
  tournament_id (PK)                   player1_id / player2_id
  player_id (PK)                       player1_score / player2_score
  card_id (PK)                         player1_deck_id / player2_deck_id
  card_count
  family_deck       wrk_results
  main_set            tournament_id (PK)
  newest_set          player_id (PK)
                      placing

---

Configuration

All settings are read from .env:

Variable	Description	Default
POSTGRES_DB	Database name	—
POSTGRES_USER	Database user	—
POSTGRES_PASSWORD	Password	—
POSTGRES_HOST	Host	localhost
POSTGRES_PORT	Port	5432
DATA_DIR	Path to JSON output folder	./sample_output

---

License

MIT