Vai al contenuto
code-to-code

Apro l'indice…

↑ ↓ per scegliere, Invio per aprire, Esc per chiudere

Il database nell'API3. Le rotte con psycopgp. 67

Le rotte con psycopg, le soluzioni

Il ragionamento, il codice, le trappole

Per ogni esercizio, sempre lo stesso ordine: in italiano → il codice → le trappole. Per i più difficili, anche il passo per passo.

La tua soluzione è diversa ma i test sono verdi? Probabilmente va bene: il SQL si può scrivere in più modi. Confronta il ragionamento, non le singole parole. E leggi comunque le trappole: alcune fanno passare qualche test e poi si rompono su un altro.

Le soluzioni complete

La stessa cartella, con il codice al posto di pass. Aprila dopo averci provato: i test sono gli stessi.

db-rotte-con-psycopg-soluzioni.zip21 KB

La palestra

1 · La scheda della stazione

In italiano. Chiedo al database la stazione con quell'id e ritiro una riga sola. Se non c'è niente (None), il 404. Altrimenti la restituisco.

Python
@app.get("/stazioni/{stazione_id}")
def leggi_stazione(stazione_id: int) -> Stazione:
    with connetti() as conn:
        stazione = conn.execute("SELECT * FROM stazioni WHERE id = %s", (stazione_id,)).fetchone()
    if stazione is None:
        raise HTTPException(status_code=404, detail="Stazione non trovata")
    return stazione

L'if sta fuori dal with: la connessione si chiude subito, la decisione si prende dopo.

Le trappole.

Il codice sbagliatoCosa succedeI test rossi
niente if, solo return stazioneCon l'id 99 la rotta restituisce None: ResponseValidationError, il client riceve un 500 invece del 404test_stazione_che_non_c_e_da_404
(stazione_id) al posto di (stazione_id,)TypeError: object of type 'int' has no len(): la tupla la fa la virgola, le parentesi da sole non bastanotutti e quattro
.fetchall() al posto di .fetchone()Torna una lista con un dizionario dentro: ResponseValidationError: Input should be a valid dictionary or object to extract fields from. Anche con l'id 99 (lista vuota, non None: l'if non scatta)tutti e quattro

La prima è la più comune. Il database non dice «non trovato»: dice «nessuna riga». Il 404 è tuo.


2 · Le segnalazioni da chiudere

In italiano.

  1. Se risolta è None, nessun filtro: tutte le segnalazioni, in ordine di id.
  2. Altrimenti solo quelle con risolta uguale al valore chiesto, sempre in ordine di id.
  3. fetchall() dà la lista (vuota se nessuna passa il filtro).
Python
@app.get("/segnalazioni")
def lista_segnalazioni(risolta: bool | None = None) -> list[Segnalazione]:
    with connetti() as conn:
        if risolta is None:
            segnalazioni = conn.execute("SELECT * FROM segnalazioni ORDER BY id").fetchall()
        else:
            segnalazioni = conn.execute(
                "SELECT * FROM segnalazioni WHERE risolta = %s ORDER BY id",
                (risolta,),
            ).fetchall()
    return segnalazioni

Il disegno.

inizio GET /segnalazioni se risolta is None sì: SELECT di tutte, ORDER BY id no: SELECT WHERE risolta = %s passo fetchall() restituisci segnalazioniGET /segnalazionirisolta is NoneSELECT WHERE risolta = %sSELECT di tutte, ORDER BY idfetchall()restituisci segnalazioninosì
Il filtro sulle segnalazioni

Le trappole.

Il codice sbagliatoCosa succedeI test rossi
sempre WHERE risolta = %s, senza il ramo NoneSenza filtro risolta vale None, e risolta = NULL non è mai vero: la lista è vuotatest_segnalazioni_senza_filtro_sono_tutte, test_segnalazioni_in_ordine_di_id_anche_dopo_una_modifica
sempre la query senza WHEREIl filtro ?risolta=false viene ignorato: tornano tutte e novetest_segnalazioni_non_risolte, test_segnalazioni_risolte, test_segnalazioni_nessuna_aperta_da_lista_vuota
niente ORDER BY idIl database non promette nessun ordine: dopo una modifica alla riga 1, la riga 1 compare in fondo. La lista parte da 2test_segnalazioni_in_ordine_di_id_anche_dopo_una_modifica

L'ultima è la più subdola: senza modifiche, l'ordine sembra giusto e l'errore passa quasi tutti i test. Se l'ordine conta, lo chiedi.


3 · Un nuovo collega

In italiano.

  1. Apro la connessione.
  2. Scrivo la riga con i quattro valori nei quattro buchi.
  3. Con RETURNING * mi faccio dire la riga com'è stata salvata, con l'id che ha scelto il database.
  4. Ritiro quella riga e la restituisco. Il 201 lo dice il decoratore.
Python
@app.post("/personale", status_code=201)
def nuova_persona(dati: PersonaNuova) -> Persona:
    with connetti() as conn:
        persona = conn.execute(
            """
            INSERT INTO personale (badge, nome, ruolo, stazione_id)
            VALUES (%s, %s, %s, %s)
            RETURNING *
            """,
            (dati.badge, dati.nome, dati.ruolo, dati.stazione_id),
        ).fetchone()
    return persona

Un stazione_id assente diventa None in Python e NULL nel database, da solo. Anna Dell'Orto con l'apostrofo non crea problemi: il nome non sta nella query, sta nella tupla.

Le trappole.

Il codice sbagliatoCosa succedeI test rossi
@app.post("/personale"), senza status_code=201Funziona, ma risponde 200test_nuova_persona_risponde_201_con_l_id, test_nuova_persona_con_l_apostrofo_nel_nome
niente RETURNING *psycopg.ProgrammingError: the last operation didn't produce records (command status: INSERT 0 1): un INSERT senza RETURNING non ha righe da ritiraretutti e quattro
una f-string con i valori dentro la queryCon Anna Dell'Orto l'apostrofo chiude la stringa del SQL: psycopg.errors.SyntaxError: syntax error at or near "Orto"test_nuova_persona_senza_stazione_ha_null, test_nuova_persona_con_l_apostrofo_nel_nome
conn = connetti() senza withLa risposta è giusta (201, id 13) ma niente fa il commit: la riga non arriva nel databasetest_nuova_persona_e_nel_database, test_nuova_persona_senza_stazione_ha_null, test_nuova_persona_con_l_apostrofo_nel_nome

Nota l'ultima: il test della risposta (..._risponde_201_con_l_id) resta verde. Se i test guardassero solo la risposta, non si accorgerebbero di niente. Per questo i test di scrittura guardano dentro il database.

La f-string è il buco di sicurezza che si chiama SQL injection: un valore con un apostrofo diventa un pezzo di SQL. Il %s ci protegge: psycopg manda la query e i valori separati, e il valore resta sempre un valore.


4 · La segnalazione corretta

In italiano.

  1. Cambio le cinque colonne della riga che ha quell'id, con i valori del corpo.
  2. Chiedo di riavere la riga cambiata.
  3. Se fetchone() dà None, nessuna riga aveva quell'id: 404.
  4. Altrimenti restituisco la riga.
Python
@app.put("/segnalazioni/{segnalazione_id}")
def aggiorna_segnalazione(segnalazione_id: int, dati: SegnalazioneNuova) -> Segnalazione:
    with connetti() as conn:
        segnalazione = conn.execute(
            """
            UPDATE segnalazioni
            SET treno_id = %s, autore_id = %s, descrizione = %s, gravita = %s, risolta = %s
            WHERE id = %s
            RETURNING *
            """,
            (dati.treno_id, dati.autore_id, dati.descrizione, dati.gravita, dati.risolta, segnalazione_id),
        ).fetchone()
    if segnalazione is None:
        raise HTTPException(status_code=404, detail="Segnalazione non trovata")
    return segnalazione

Passo per passo. Cinque valori per il SET, uno per il WHERE. I buchi si riempiono nell'ordine in cui compaiono nella query:

BucoDoveValore nella tupla
1treno_id = %sdati.treno_id
2autore_id = %sdati.autore_id
3descrizione = %sdati.descrizione
4gravita = %sdati.gravita
5risolta = %sdati.risolta
6WHERE id = %ssegnalazione_id

Le trappole.

Il codice sbagliatoCosa succedeI test rossi
segnalazione_id per primo nella tuplaOgni valore finisce nel buco accanto. L'ultimo, dati.risolta (un booleano), arriva al WHERE id = ...: psycopg.errors.UndefinedFunction: operator does not exist: integer = booleantutti e cinque
niente if sul NoneCon l'id 99 la rotta restituisce None: ResponseValidationError, un 500test_correzione_di_una_segnalazione_che_non_c_e_da_404, test_correzione_che_non_c_e_non_cambia_niente
niente WHERE id = %s (e niente id nella tupla)L'UPDATE cambia tutte le segnalazioni, e RETURNING * ne riporta una a caso: risponde con la prima (id 1)test_correzione_risponde_con_la_segnalazione_nuova, test_correzione_non_tocca_le_altre, test_correzione_di_una_segnalazione_che_non_c_e_da_404, test_correzione_che_non_c_e_non_cambia_niente
niente RETURNING *ProgrammingError: the last operation didn't produce records (command status: UPDATE 1)tutti e cinque

Il WHERE dimenticato è il più pericoloso: non dà nessun errore. Il test test_correzione_non_tocca_le_altre esiste per questo: guarda le altre righe prima e dopo.


5 · Il treno che esce dall'elenco

In italiano.

  1. Provo a cancellare la riga con quell'id, e chiedo di riavere il suo id.
  2. Se il database dice di no perché ci sono segnalazioni che puntano a quel treno (ForeignKeyViolation), rispondo 409.
  3. Se la cancellazione è andata a buon fine ma non è tornato niente (None), quel treno non c'era: 404.
  4. Altrimenti niente: il 204 non ha corpo.
Python
@app.delete("/treni/{treno_id}", status_code=204)
def togli_treno(treno_id: int) -> None:
    try:
        with connetti() as conn:
            tolto = conn.execute("DELETE FROM treni WHERE id = %s RETURNING id", (treno_id,)).fetchone()
    except psycopg.errors.ForeignKeyViolation:
        raise HTTPException(status_code=409, detail="Il treno ha delle segnalazioni")
    if tolto is None:
        raise HTTPException(status_code=404, detail="Treno non trovato")

Il disegno.

inizio DELETE /treni/{treno_id} passo DELETE ... RETURNING id se ForeignKeyViolation sì: errore 409 Il treno ha delle segnalazioni se tolto is None sì: errore 404 Treno non trovato fine 204, senza corpoDELETE /treni/{treno_id}DELETE ... RETURNING idForeignKeyViolation409 Il treno ha dellesegnalazionitolto is None404 Treno non trovato204, senza corponosìnosì
La rotta che cancella un treno

Passo per passo. Quattro richieste, quattro strade:

RichiestaCosa dice il databaseCosa succedeRisposta
DELETE /treni/8 (M24, nessuna segnalazione)cancella la riga, riporta {"id": 8}tolto è un dizionario: nessun raise, la funzione finisce204, senza corpo
DELETE /treni/99zero righe cancellate, niente da riportaretolto è None404
DELETE /treni/2 (M12, tre segnalazioni)ForeignKeyViolation, e il with fa il rollbackl'except la prende409
DELETE /treni/9 due voltela seconda volta zero righetolto è None204, poi 404

Le trappole.

Il codice sbagliatoCosa succedeI test rossi
niente try / exceptCon il M12 l'eccezione sale fino a FastAPI: psycopg.errors.ForeignKeyViolation, e il client riceve un 500test_treno_con_segnalazioni_da_409, test_treno_con_segnalazioni_resta_dov_e
niente if tolto is NoneCancellare un treno che non c'è risponde 204: «fatto» per una cosa che non è successatest_treno_che_non_c_e_da_404, test_treno_tolto_due_volte_la_seconda_da_404
niente RETURNING idProgrammingError: the last operation didn't produce records (command status: DELETE 1)test_treno_tolto_risponde_204_senza_corpo, test_treno_tolto_non_c_e_piu_nel_database, test_treno_che_non_c_e_da_404, test_treno_tolto_due_volte_la_seconda_da_404
niente status_code=204 nel decoratoreLa rotta funziona, ma risponde 200test_treno_tolto_risponde_204_senza_corpo, test_treno_tolto_due_volte_la_seconda_da_404

Con RETURNING id e senza if, il 404 sparisce in silenzio: l'API risponde «fatto» ogni volta. È il caso in cui un test che chiede lo stesso DELETE due volte vale più di mille controlli a occhio.