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. 49

Le rotte con psycopg

Lo stesso CRUD, ma i dati restano

  • 10 minuti di lettura

Nel CRUD su un dizionario ogni rotta faceva una mossa su un dizionario: get, una chiave nuova, un del. In psycopg hai imparato a mandare una domanda SQL al database e a ritirare la risposta.

Questo capitolo le mette insieme. Le rotte sono le stesse, i codici sono gli stessi (200, 201, 204, 404, 409). Cambia solo cosa c'è dentro il def: al posto della mossa sul dizionario, una query. E cambia dove stanno i dati: nel database, quindi se spegni il server non spariscono.

Il disegno

 il client                la tua API                         il database
┌──────────┐          ┌───────────────────┐              ┌──────────────┐
│  curl    │ ─ GET ─▶ │  la rotta (def)   │ ─ il SQL ──▶ │  PostgreSQL  │
│ /docs    │          │   with connetti() │              │  le tabelle  │
│ Postman  │ ◀─ 200 ─ │   fetchone()      │ ◀─ le righe ─│              │
└──────────┘  o 404   └───────────────────┘              └──────────────┘

La rotta fa tre cose, sempre nello stesso ordine: apre la connessione, manda la domanda, decide cosa rispondere guardando cosa è tornato. L'ultima è la sola che è tua: il database non sa niente di 200 e di 404.

Le cinque rotte, a confronto

RottaCon il dizionarioCon psycopgCosa ritiraSe la riga non c'è
GET /trenitreni.values()SELECT * FROM treni ORDER BY idfetchall()[]: 200 con la lista vuota
GET /treni/3treni.get(3)SELECT * FROM treni WHERE id = %sfetchone()None: 404
POST /trenitreni[nuovo_id] = trenoINSERT ... RETURNING *fetchone()non succede: la riga nuova c'è sempre
PUT /treni/3treni[3] = nuovoUPDATE ... WHERE id = %s RETURNING *fetchone()None: 404
DELETE /treni/3del treni[3]DELETE ... WHERE id = %s RETURNING idfetchone()None: 404

Guarda l'ultima colonna: tre rotte su cinque finiscono nello stesso punto, un None che diventa 404.

Il 404 sta in un None

fetchone() dà un dizionario se il database ha trovato la riga, None se non l'ha trovata. Nel dizionario era treni.get(3), e il None lo conoscevi già. La rotta di lettura è questa:

Python
@app.get("/treni/{treno_id}")
def leggi_treno(treno_id: int) -> Treno:
    with connetti() as conn:
        treno = conn.execute("SELECT * FROM treni WHERE id = %s", (treno_id,)).fetchone()
    if treno is None:
        raise HTTPException(status_code=404, detail="Treno non trovato")
    return treno

Il disegno di cosa può uscire dal vassoio:

SELECT * FROM treni WHERE id = %s
              │
              ▼
          fetchone()
         ╱          ╲
 un dizionario       None
       │               │
 return treno     raise HTTPException(404)
       │               │
  200 e il JSON    404 e il detail

Nota anche la posizione dell'if: fuori dal with. Dentro il with la query si fa; fuori, con la connessione già chiusa, si decide.

Scrivere: INSERT e RETURNING

Con il dizionario l'id lo sceglievi tu (max(...) + 1). Ora lo sceglie il database. Come fai a saperlo, per rimandarlo al client? Con RETURNING *, che hai visto in Scrivere in SQL: la riga appena scritta torna indietro, id compreso.

Python
@app.post("/treni", status_code=status.HTTP_201_CREATED)
def nuovo_treno(dati: TrenoNuovo) -> Treno:
    with connetti() as conn:
        treno = conn.execute(
            """
            INSERT INTO treni (codice, vagoni, km, in_servizio, stazione_id)
            VALUES (%s, %s, %s, %s, %s)
            RETURNING *
            """,
            (dati.codice, dati.vagoni, dati.km, dati.in_servizio, dati.stazione_id),
        ).fetchone()
    return treno

Tre cose da guardare:

  • I cinque %s e le cinque cose nella tupla vanno nello stesso ordine, e le colonne tra parentesi pure.
  • RETURNING * fa dell'INSERT una domanda che ha una risposta: per questo si può chiamare fetchone(). Senza, fetchone() si ferma con ProgrammingError: the last operation didn't produce records.
  • Il 201 sta nel decoratore, non nel def. Se lo dimentichi la rotta funziona e risponde 200: nessun errore, solo il codice sbagliato.

PUT e DELETE: RETURNING dice se la riga c'era

UPDATE e DELETE, da soli, su un id che non esiste non si lamentano: cambiano zero righe e basta. Lo senti in psql:

$ psql -d db_rotte_con_psycopg -c "DELETE FROM treni WHERE id = 99 RETURNING id"
 id 
----
(0 righe)

DELETE 0

Con RETURNING, le righe toccate tornano indietro: se ce n'è una, fetchone() la dà e la riga c'era. Se non ce n'è, fetchone() dà None e il 404 si decide come per la lettura. RETURNING è il modo di sapere se la riga c'era.

Python
@app.delete("/treni/{treno_id}", status_code=status.HTTP_204_NO_CONTENT)
def togli_treno(treno_id: int) -> None:
    with connetti() as conn:
        tolto = conn.execute("DELETE FROM treni WHERE id = %s RETURNING id", (treno_id,)).fetchone()
    if tolto is None:
        raise HTTPException(status_code=404, detail="Treno non trovato")

Il 204 vuol dire «fatto, e non ho niente da dirti»: il def è -> None e non c'è nessun return. Nel DELETE basta RETURNING id: non serve rimandare l'intera riga cancellata.

Nel PUT il codice è lo stesso del POST con un UPDATE ... SET ... WHERE id = %s RETURNING * al posto dell'INSERT. Il trucco da ricordare: i valori del SET sono cinque, il WHERE ne aggiunge un sesto, e l'id è l'ultimo della tupla, perché è l'ultimo %s della query.

Il database che dice di no

Il database ha delle regole (le hai scritte tu, nel CREATE TABLE): un codice non si ripete (UNIQUE), un treno con delle segnalazioni non si cancella (REFERENCES). Se la richiesta le rompe, psycopg alza un'eccezione. Ha un nome preciso:

Cosa tentiCosa risponde il databaseL'eccezione in PythonCosa rispondi tu
un codice che c'è giàduplicate key value violates unique constraintpsycopg.errors.UniqueViolation409
cancellare un treno con delle segnalazioniupdate or delete on table "treni" violates foreign key constraintpsycopg.errors.ForeignKeyViolation409

Se non fai niente, l'eccezione sale fino a FastAPI, che risponde 500. Ma non è un errore del server: è una richiesta che non si può accettare. Per questo si prende con try / except (lo hai visto in try, except, raise) e si trasforma in 409:

Python
    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")

Il try sta attorno al with: l'errore nasce dentro, e quando esce dal with la connessione ha già fatto il rollback. L'except lo prende fuori, a cose a posto.

La prova del nove

Con il dizionario, il riavvio azzerava tutto. Con il database no. Provalo:

  1. Accendi il server (fastapi dev palestra/centrale_psycopg.py).
  2. Con curl o con /docs, crea un treno nuovo.
  3. Ctrl+C: il server è spento.
  4. Riaccendilo, e chiedi quel treno.

Se c'è ancora, la scrittura è arrivata davvero sul disco. È la prova del nove, e la rifai ogni volta che scrivi una rotta di scrittura: la risposta 201 dice che la rotta è arrivata in fondo, non che il database ha salvato.

I test del capitolo fanno la stessa domanda in un altro modo: dopo una scrittura guardano dentro il database, non solo la risposta. Come funzionano lo racconta il capitolo sui test con il database. Per ora ti basta sapere che i test lavorano su un database loro, che prima di ogni test torna come nuovo: i tuoi dati non li toccano.

Lo schema da copiare

Tolti i nomi, ogni rotta del capitolo ha questa forma:

Python
def la_rotta(...):
    try:                                           # solo se il database può dire di no (409)
        with connetti() as conn:                   # 1. apri, e il with fa commit o rollback
            riga = conn.execute("...", (...,)).fetchone()   # 2. la domanda, con i buchi
    except psycopg.errors.UniqueViolation:         # 3. il no del database diventa 409
        raise HTTPException(status_code=409, detail="...")
    if riga is None:                               # 4. la riga non c'era: 404
        raise HTTPException(status_code=404, detail="...")
    return riga                                    # 5. il dizionario diventa il JSON

Nell'ultima riga riga è un dizionario, e a Pydantic basta. Con SQLAlchemy la tabella diventerà una classe, e le rotte cambieranno forma.

Ricorda

  • La rotta apre la connessione, manda la domanda, e decide cosa rispondere.
  • fetchone() dà un dizionario o None: il None è il 404, e lo scrivi tu con un if.
  • INSERT e UPDATE con RETURNING *, DELETE con RETURNING id: così sai se la riga c'era e ne ritiri una.
  • Nell'UPDATE l'id è l'ultimo %s, e nella tupla l'ultimo valore.
  • Il 201 e il 204 si scrivono nel decoratore. Il 204 non ha return.
  • Il no del database (UniqueViolation, ForeignKeyViolation) si prende con try attorno al with e diventa 409.
  • Dopo una scrittura, la prova del nove: spegni, riaccendi, c'è ancora?