Database per collezionisti gratuito Parte 5

Adesso creiamo la prima API che modifica il database

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

cancellate tutto e sostituite con:
from fastapi import FastAPI
from pydantic import BaseModel
from sqlalchemy import text

from app.database import engine

app = FastAPI(
    title="Pokemon Collection API"
)

class CollectionAdd(BaseModel):
    user_id: int
    card_id: str
    language: str
    condition: str
    variant: str
    graded: bool = False
    quantity: int = 1

@app.get("/")
def root():
    return {
        "app": "Pokemon Collection",
        "status": "running"
    }


@app.get("/health")
def health():
    return {
        "status": "ok"
    }


@app.get("/db")
def db_test():

    with engine.connect() as conn:

        result = conn.execute(
            text("SELECT COUNT(*) FROM users")
        )

        count = result.scalar()

    return {
        "users": count
    }

@app.get("/users")
def get_users():

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT
                    id,
                    username,
                    display_name,
                    is_admin
                FROM users
                ORDER BY id
            """)
        )

        rows = result.mappings().all()

    return rows


@app.get("/sets")
def get_sets():

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT *
                FROM sets
                ORDER BY name
            """)
        )

        rows = result.mappings().all()

    return rows

@app.get("/cards")
def get_cards():

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT *
                FROM cards
                ORDER BY name
                LIMIT 100
            """)
        )

        rows = result.mappings().all()

    return rows

@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()

@app.post("/collection/add")
def add_to_collection(item: CollectionAdd):

    with engine.connect() as conn:

        conn.execute(
            text("""
                INSERT INTO collection (
                    user_id,
                    card_id,
                    quantity,
                    language,
                    condition,
                    variant,
                    graded
                )
                VALUES (
                    :user_id,
                    :card_id,
                    :quantity,
                    :language,
                    :condition,
                    :variant,
                    :graded
                )
                ON CONFLICT (
                user_id,
                card_id,
                language,
                condition,
                variant,
                graded
                )
                DO UPDATE
                SET quantity = collection.quantity + EXCLUDED.quantity
            """),
            {
                "user_id": item.user_id,
                "card_id": item.card_id,
                "quantity": item.quantity,
                "language": item.language,
                "condition": item.condition,
                "variant": item.variant,
                "graded": item.graded
            }
        )

        conn.commit()

    return {
        "success": True,
        "card_id": item.card_id
    }

Salvare e uscire e 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/docs dovrebbe mostrare una pagina come la seguente

Creiamo la Collection basata sul multiutente

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

Aggiungere alla fine del file:
@app.get("/collection")
def get_collection(user_id: int):

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT
                    col.id,
                    c.name,
                    c.card_number,
                    s.name AS set_name,
                    col.quantity,
                    col.language,
                    col.condition,
                    col.variant,
                    col.graded
                FROM collection col
                JOIN cards c
                    ON c.id = col.card_id
                JOIN sets s
                    ON s.id = c.set_id
                WHERE col.user_id = :user_id
                ORDER BY c.name
            """),
            {"user_id": user_id}
        )

        return result.mappings().all()

Salvare e uscire e ricostruire il DB:
cd /opt/pokemon
docker-compose up -d --build

Aggiungere l’endpoint DELETE

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

Aggiungere alla fine del file:
@app.delete("/collection/{item_id}")
def delete_collection_item(item_id: int):

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                DELETE FROM collection
                WHERE id = :item_id
            """),
            {"item_id": item_id}
        )

        conn.commit()

    return {
        "success": True,
        "deleted_id": item_id
    }

Salvare e uscire e ricostruire il DB:
cd /opt/pokemon
docker-compose up -d --build

Aggiungi il modello Pydantic

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

Aggiungere al principio del file:
from fastapi.responses import FileResponse

Sotto class CollectionAdd(BaseModel): aggiungere:
class CollectionUpdate(BaseModel):
    quantity: int
    language: str
    condition: str
    variant: str
    graded: bool

Aggiungere alla fine del file:
@app.put("/collection/{item_id}")
def update_collection_item(
    item_id: int,
    item: CollectionUpdate
):

    with engine.connect() as conn:

        conn.execute(
            text("""
                UPDATE collection
                SET
                    quantity = :quantity,
                    language = :language,
                    condition = :condition,
                    variant = :variant,
                    graded = :graded
                WHERE id = :item_id
            """),
            {
                "item_id": item_id,
                "quantity": item.quantity,
                "language": item.language,
                "condition": item.condition,
                "variant": item.variant,
                "graded": item.graded
            }
        )

        conn.commit()

    return {
        "success": True,
        "updated_id": item_id
    }

@app.get("/stats")
def get_stats(user_id: int):

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT
                    COALESCE(SUM(quantity), 0) AS total_cards,
                    COUNT(*) AS unique_cards,
                    COUNT(DISTINCT c.set_id) AS sets_started
                FROM collection col
                JOIN cards c
                    ON c.id = col.card_id
                WHERE col.user_id = :user_id
            """),
            {"user_id": user_id}
        )

        stats = result.mappings().first()

        return stats

@app.get("/set/{set_id}/cards")
def get_set_cards(set_id: str):

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT
                    id,
                    name,
                    card_number,
                    rarity,
                    image_small
                FROM cards
                WHERE set_id = :set_id
                ORDER BY
                    CASE
                        WHEN card_number ~ '^[0-9]+$'
                        THEN card_number::integer
                    END NULLS LAST,
                    card_number
            """),
            {"set_id": set_id}
        )

        return result.mappings().all()

@app.get("/dashboard")
def dashboard():

    return FileResponse(
        "app/static/dashboard.html"
    )

Salvare e uscire e ricostruire il DB:
cd /opt/pokemon
docker-compose up -d --build

<—– Articolo Parte 4 | Articolo Parte 6 —–>