Vai al contenuto
code-to-code

Apro l'indice…

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

Il database nell'API2. psycopgp. 31

psycopg, la palestra

Otto esercizi da scaricare, con i test

  • 90–120 minuti di lavoro

Otto esercizi corti, dal ★ al ★★★, ambientati sull'ufficio del personale e sull'officina della Linea Menta. Ognuno è una funzione da completare, con il suo file di test: quando è tutto verde, hai finito. Non c'è nessuna API: solo Python, psycopg e il database.

Gli esercizi da scaricare

Una cartella con i file da completare e i test già pronti. Come si usa: la guida in cinque passi.

db-psycopg.zip21 KB

Prima di iniziare

Scompatta lo zip e segui il README.md che c'è dentro: venv, pip install, il tuo database con createdb db_psycopg e psql -d db_psycopg -f linea_menta.sql, poi code .. PostgreSQL deve essere acceso (Mac: brew services start postgresql@16, WSL: sudo service postgresql start). Se è la prima volta, la pagina Come si usa un esercizio lo fa con te, un comando alla volta; le basi di psql sono in PostgreSQL e psql.

I database sono due, e non devi crearne uno tu:

DatabaseDi chi èCosa ci succede
db_psycopgTuoLo guardi con psql, ci lanci i file con python palestra/…. Quello che scrivi resta
db_psycopg_testDei testconftest.py lo crea la prima volta e lo riporta come nuovo prima di ogni test

I test non toccano i tuoi dati, e non dipendono da cosa è successo nel test prima.

Ogni esercizio, sempre gli stessi quattro passi:

  1. Apri il file in palestra/. In alto: la storia, cosa fai, come provi.
  2. Al posto di pass scrivi il codice. La docstring ha gli esempi di cosa deve uscire.
  3. pytest palestra/test_<nome>.py -v, oppure la provetta di VS Code.
  4. Rosso? Leggi l'ultima riga del messaggio: i test dicono in italiano cosa manca. Bloccato da 15 minuti? Apri il primo aiutino, non la soluzione.

Questo è il primo test, ancora con pass:

palestra/test_persona_per_badge.py::test_una_persona_che_c_e FAILED                          [ 25%]
palestra/test_persona_per_badge.py::test_chi_gira_su_tutta_la_linea_ha_la_stazione_none FAILED [ 50%]
palestra/test_persona_per_badge.py::test_un_badge_che_non_c_e_da_none PASSED                 [ 75%]
palestra/test_persona_per_badge.py::test_il_badge_arriva_dal_parametro FAILED                [100%]

Uno è già verde: con pass la funzione restituisce None, ed è proprio la risposta giusta per un badge che non c'è. È normale: contano tutti e quattro.

#EsercizioCosa si imparaDifficoltà
1Il collega per badge%s, la tupla, fetchone(), None★
2Le segnalazioni aperte di un trenofetchall(), la lista vuota★
3I treni da mandare in officinail confine >=, la soglia come parametro★
4Una segnalazione nuovaINSERT … RETURNING *, il with★★
5Chiudere una segnalazioneUPDATE … RETURNING *, None★★
6Ritirare un trenodue scritture, un solo with, il rollback★★★
7L'indirizzo del database, senza la passwordos.environ, il valore di riserva★★
8Cercare per nome, senza farsi fregarela f-string da buttare, Bobby Tables★★★

1 · Il collega per badge ★

palestra/persona_per_badge.py

Lucia Ferri, capotreno, trova un badge smarrito e vuole sapere di chi è. Se il badge non esiste, la risposta è None.

Python
persona_per_badge("B-01")   # {"id": 1, "badge": "B-01", "nome": "Lucia Ferri", "ruolo": "capotreno", "stazione_id": 1}
persona_per_badge("B-99")   # None

Come provi: pytest palestra/test_persona_per_badge.py -v

Aiutino 1
Ti serve una riga sola di SQL: SELECT * FROM personale WHERE badge = ..., con il buco al posto del badge. Lo trovi nella pagina di teoria, nella sezione «Il buco %s e la tupla».
Aiutino 2
Il parametro è uno solo: ricorda la virgola, (badge,). E restituisci quello che dà fetchone(): una riga o None.
Aiutino 3
La forma è with connetti() as conn:, e dentro return conn.execute(…, …).fetchone().

2 · Le segnalazioni aperte di un treno ★

palestra/segnalazioni_aperte.py

Ivo Brambati, il manutentore, prima di mettere le mani su un treno vuole l'elenco delle segnalazioni non ancora risolte, in ordine di id. Se non ce ne sono: una lista vuota.

Python
segnalazioni_aperte(2)   # [{"id": 2, ...}, {"id": 8, ...}]   le due del M12 non risolte
segnalazioni_aperte(8)   # []

Come provi: pytest palestra/test_segnalazioni_aperte.py -v

Aiutino 1
Le righe sono tante, non una: quale dei due ritiri, fetchone() o fetchall()?
Aiutino 2
Due condizioni nel WHERE, legate da AND. Una confronta il treno (con il buco), l'altra guarda la colonna risolta. Nel SQL i booleani si scrivono true e false.
Aiutino 3
La condizione sulla colonna risolta non ha bisogno di buchi: è sempre la stessa. Il buco serve solo per il numero del treno. Non dimenticare ORDER BY id.

3 · I treni da mandare in officina ★

palestra/treni_da_revisionare.py

Ernesto Pozzi, il capo deposito, manda in officina i treni che hanno fatto almeno una certa soglia di chilometri. Vuole il codice e i km, dal più stanco al meno stanco.

Python
treni_da_revisionare(5000)    # [{"codice": "M18", "km": 7100}, {"codice": "M14", "km": 5200}, {"codice": "M24", "km": 5000}]
treni_da_revisionare(99999)   # []

Come provi: pytest palestra/test_treni_da_revisionare.py -v

Aiutino 1
Guarda il M24: ha 5000 km giusti giusti, e con una soglia di 5000 ci deve essere. Quale confronto lo include, > o >=?
Aiutino 2
Non scrivere 5000 nella query: la soglia è un parametro della funzione, e va nel buco. Per l'ordine, ORDER BY km DESC.
Aiutino 3
Le colonne le scegli tu: SELECT codice, km. Non SELECT *.

4 · Una segnalazione nuova ★★

palestra/nuova_segnalazione.py

Un manutentore scrive una segnalazione di guasto. Il database le dà un id: chi chiama la funzione lo rivuole indietro, con tutta la riga.

Python
nuova_segnalazione(8, 2, "Specchietto storto", 1)
# {"id": 10, "treno_id": 8, "autore_id": 2, "descrizione": "Specchietto storto", "gravita": 1, "risolta": False}

Come provi: pytest palestra/test_nuova_segnalazione.py -v

Aiutino 1
INSERT INTO segnalazioni (treno_id, autore_id, descrizione, gravita) VALUES (…). Quattro colonne, quattro buchi. Le altre colonne (id, risolta) le riempie il database.
Aiutino 2
Per riavere la riga, in fondo alla query: RETURNING *. E poi fetchone().
Aiutino 3
Il test «è nel database» guarda dentro db_psycopg_test con una domanda sua. Se non la trova, qualcuno non ha fatto il commit: com'è che si fa, in questo capitolo?

5 · Chiudere una segnalazione ★★

palestra/chiudi_segnalazione.py

Il guasto è sistemato: Ivo chiude la segnalazione. Se il numero non esiste la risposta è None, e nessun'altra segnalazione deve cambiare.

Python
chiudi_segnalazione(3)    # {"id": 3, "treno_id": 5, "autore_id": 2, "descrizione": "Motore che scalda troppo", "gravita": 3, "risolta": True}
chiudi_segnalazione(99)   # None

Come provi: pytest palestra/test_chiudi_segnalazione.py -v

Aiutino 1
UPDATE segnalazioni SET risolta = true WHERE …. Nel libro SQL: cosa succede a un UPDATE senza WHERE?
Aiutino 2
Anche l'UPDATE ha il suo RETURNING *. Se nessuna riga ha quell'id, il vassoio è vuoto, e fetchone() dà None.
Aiutino 3
Il parametro è un numero solo: (segnalazione_id,), con la virgola.

6 · Ritirare un treno ★★★

palestra/ritira_treno.py

Per ritirare un treno servono due scritture: il treno esce dal servizio, e nasce la segnalazione che dice perché. Se la seconda salta, la prima non deve restare a metà. Se il codice del treno non esiste: None, e non si scrive niente.

Python
ritira_treno("M10", 2, 3, "Freno rotto")   # la segnalazione nuova: {"id": 10, "treno_id": 1, ...}
ritira_treno("M99", 2, 3, "Boh")           # None
ritira_treno("M10", 2, 7, "…")             # psycopg.errors.CheckViolation, e il M10 resta in servizio

Come provi: pytest palestra/test_ritira_treno.py -v

Aiutino 1
Un solo with connetti() as conn:, con due conn.execute dentro. Se l'errore esce dal blocco, il with fa il rollback: annulla anche la prima.
Aiutino 2
Per scrivere la segnalazione ti serve l'id del treno, che hai solo il codice. Fai tornare l'id dalla prima scrittura: UPDATE treni SET in_servizio = false WHERE codice = … RETURNING id.
Aiutino 3
Se quell'UPDATE non ha trovato il treno, fetchone() dà None: dopo l'if, return None. Altrimenti usa treno["id"] per la INSERT.

7 · L'indirizzo del database, senza la password ★★

palestra/stringa_di_connessione.py

Un giorno il database non sarà più sul tuo computer, e vorrà una password. La password non si scrive nel codice. Questa funzione costruisce l'indirizzo postgresql://utente:password@host:porta/database leggendo i pezzi dall'ambiente.

VariabileSe manca
PGUSER"menta"
PGHOST"localhost"
PGPORT"5432"
MENTA_DB"db_psycopg"
PGPASSWORDerrore: RuntimeError, e il messaggio nomina PGPASSWORD
Python
# con PGPASSWORD=s3greta e nient'altro:
stringa_di_connessione()   # "postgresql://menta:s3greta@localhost:5432/db_psycopg"

Come provi: pytest palestra/test_stringa_di_connessione.py -v. Il test cambia le variabili d'ambiente solo per sé: i tuoi file non li tocca.

Aiutino 1
os.environ.get("NOME", "ripiego") funziona come il get dei dizionari: ti dà il ripiego se la variabile manca.
Aiutino 2
La password non ha ripiego: os.environ.get("PGPASSWORD") dà None se manca. Dopo, un if e un raise RuntimeError(…).
Aiutino 3
L'indirizzo si costruisce con una f-string. Qui va bene: non è una query, è un indirizzo.

8 · Cercare per nome, senza farsi fregare ★★★

palestra/persone_per_nome.py

L'ufficio del personale cerca per nome. La funzione c'è già, scritta con una f-string, e funziona con i nomi tranquilli. Poi arriva Gino D'Alessio. E poi qualcuno prova Bobby Tables.

Tu non scrivi una funzione nuova: riscrivi la query con il buco e la tupla, al posto della f-string. Il resto resta com'è.

Python
persone_per_nome("Gino D'Alessio")   # [{"id": 10, "badge": "B-11", ...}]
persone_per_nome("x' OR '1'='1")     # []

Come provi: pytest palestra/test_persone_per_nome.py -v. Tre test sono rossi da subito: guarda il messaggio di ognuno.

Aiutino 1
Il nome dentro le virgolette non serve più: nome = %s, senza apici intorno al buco. Il valore lo mette psycopg, e lo tratta come testo.
Aiutino 2
Il secondo argomento di execute è la tupla: (nome,).
Aiutino 3
La riga with connetti() as conn: e fetchall() restano come sono. Cambia solo la riga della query e la chiamata a execute.