Prepariamo l’ambiente per importare PokemonTGC tutti i SET, CARTE, IMMAGINI etc.. e importarle nel nostro DB

nano /opt/pokemon/backend/requirements.txt

che contiene già 4 righe, aggiungiamo in fondo:
requests

Salva ed esci e ricostruiamo nuovamente l'indice:
cd /opt/pokemon
docker-compose build --no-cache api
docker-compose up -d

Creare lo script di importazione

nano /opt/pokemon/imports/import_sets.py

Inserire:
import requests
import psycopg2

DB_HOST = "127.0.0.1"
DB_NAME = "pokemon"
DB_USER = "pokemon"
DB_PASS = "pokemon123"

url = "https://api.pokemontcg.io/v2/sets"

response = requests.get(url)
data = response.json()["data"]

conn = psycopg2.connect(
    host=DB_HOST,
    dbname=DB_NAME,
    user=DB_USER,
    password=DB_PASS
)

cur = conn.cursor()

for s in data:

    cur.execute("""
        INSERT INTO sets (
            id,
            name,
            series,
            release_date,
            total_cards
        )
        VALUES (%s,%s,%s,%s,%s)
        ON CONFLICT (id)
        DO NOTHING
    """, (
        s["id"],
        s["name"],
        s.get("series"),
        s.get("releaseDate"),
        s.get("total")
    ))

conn.commit()

print(f"Importati {len(data)} set")

cur.close()
conn.close()

Salva ed esci
Inizia adesso la fase di importazione dati vera..
apt install -y python3-requests python3-psycopg2
python3 /opt/pokemon/imports/import_sets.py

Mi aspetto una risposta del tipo:
Importati 174 set

Entra in PostgreSQL per una verifica rapida:
docker exec -it pokemon-postgres psql -U pokemon -d pokemon

al prompt pokemon=# digitare:
SELECT COUNT(*) FROM sets;

dovrebbe rispondere nuovamente 174 per uscire \q

nano /opt/pokemon/imports/import_cards.py

e inseriamo:
import requests
import psycopg2
import time

DB_HOST = "127.0.0.1"
DB_NAME = "pokemon"
DB_USER = "pokemon"
DB_PASS = "pokemon123"

conn = psycopg2.connect(
    host=DB_HOST,
    dbname=DB_NAME,
    user=DB_USER,
    password=DB_PASS
)

cur = conn.cursor()

page = 1
page_size = 100
total_imported = 0

while True:

    print(f"\nPagina {page}")

    while True:

        try:

            response = requests.get(
                "https://api.pokemontcg.io/v2/cards",
                params={
                    "page": page,
                    "pageSize": page_size
                },
                timeout=60
            )

            if response.status_code != 200:

                print(
                    f"Errore HTTP {response.status_code} sulla pagina {page}"
                )

                time.sleep(10)

                continue

            data = response.json()

            break

        except Exception as e:

            print(f"Errore: {e}")

            time.sleep(10)

    cards = data["data"]

    if not cards:

        print("\nImport completato")
        break

    for card in cards:

        cur.execute("""
            INSERT INTO cards (
                id,
                set_id,
                card_number,
                name,
                rarity,
                supertype,
                image_small,
                image_large
            )
            VALUES (%s,%s,%s,%s,%s,%s,%s,%s)
            ON CONFLICT (id)
            DO NOTHING
        """, (
            card["id"],
            card["set"]["id"],
            card.get("number"),
            card.get("name"),
            card.get("rarity"),
            card.get("supertype"),
            card.get("images", {}).get("small"),
            card.get("images", {}).get("large")
        ))

    conn.commit()

    total_imported += len(cards)

    print(f"Importate finora: {total_imported}")

    page += 1

    time.sleep(1)

print(f"\nTotale carte elaborate: {total_imported}")

cur.close()
conn.close()

Salva ed esci e lancia lo script (ho inserito un controllo di errore che non interrompe la copia, su ogni pagina da 100 carte riproverà finchè non riesce).
python3 /opt/pokemon/imports/import_cards.py

L'importazione durerà diverse ore, quando sarà conclusa verifichiamo entrando nel DB:
docker exec -it pokemon-postgres psql -U pokemon -d pokemon
SELECT COUNT(*) FROM cards;

Mi attendo una risposta del tipo:
 count
-------
 20479
(1 row)

Usciamo dal DB con \q

Adesso facciamo la prima funzione utile

nano /opt/pokemon/backend/app/main.py

Alla fine del file aggiungere in calce:
@app.get("/cards/search")
def search_cards(name: str):

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT
                    c.id,
                    c.name,
                    c.card_number,
                    c.rarity,
                    s.name AS set_name,
                    c.image_small
                FROM cards c
                JOIN sets s
                    ON s.id = c.set_id
                WHERE LOWER(c.name) LIKE LOWER(:search)
                ORDER BY c.name
                LIMIT 50
            """),
            {"search": f"%{name}%"}
        )

        return result.mappings().all()

Ricostruire il DB:
cd /opt/pokemon
docker-compose up -d --build

finita la ricompilazione dovrebbe dare nuovamente "Runnig" e "Started" Verifichiamo aprendo la pagina web http://vm ip:8000/cards/search?name=pikachu dovrebbe mostrare tutte le carte trovate con risultati del tipo:
[{"id":"smp-SM109","name":"Ash's Pikachu","card_number":"SM109","rarity":"Promo","set_name":"SM Black Star Promos","image_small":"https://images.pokemontcg.io/smp/SM109.png"}, .....

<—– Articolo Parte 3 | Articolo Parte 5 —–>