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
| Rotta | Con il dizionario | Con psycopg | Cosa ritira | Se la riga non c'è |
|---|---|---|---|---|
GET /treni | treni.values() | SELECT * FROM treni ORDER BY id | fetchall() | []: 200 con la lista vuota |
GET /treni/3 | treni.get(3) | SELECT * FROM treni WHERE id = %s | fetchone() | None: 404 |
POST /treni | treni[nuovo_id] = treno | INSERT ... RETURNING * | fetchone() | non succede: la riga nuova c'è sempre |
PUT /treni/3 | treni[3] = nuovo | UPDATE ... WHERE id = %s RETURNING * | fetchone() | None: 404 |
DELETE /treni/3 | del treni[3] | DELETE ... WHERE id = %s RETURNING id | fetchone() | 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:
@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 trenoIl 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 detailNota 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.
@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 trenoTre cose da guardare:
- I cinque
%se le cinque cose nella tupla vanno nello stesso ordine, e le colonne tra parentesi pure. RETURNING *fa dell'INSERTuna domanda che ha una risposta: per questo si può chiamarefetchone(). Senza,fetchone()si ferma conProgrammingError: the last operation didn't produce records.- Il
201sta nel decoratore, non neldef. 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 0Con 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.
@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 tenti | Cosa risponde il database | L'eccezione in Python | Cosa rispondi tu |
|---|---|---|---|
| un codice che c'è già | duplicate key value violates unique constraint | psycopg.errors.UniqueViolation | 409 |
| cancellare un treno con delle segnalazioni | update or delete on table "treni" violates foreign key constraint | psycopg.errors.ForeignKeyViolation | 409 |
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:
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:
- Accendi il server (
fastapi dev palestra/centrale_psycopg.py). - Con
curlo con/docs, crea un treno nuovo. Ctrl+C: il server è spento.- 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:
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 JSONNell'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 oNone: ilNoneè il 404, e lo scrivi tu con unif.INSERTeUPDATEconRETURNING *,DELETEconRETURNING id: così sai se la riga c'era e ne ritiri una.- Nell'
UPDATEl'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 contryattorno alwithe diventa 409. - Dopo una scrittura, la prova del nove: spegni, riaccendi, c'è ancora?