psycopg, esercizi spiegati
Sette esempi, una riga alla volta
- 30 minuti di lettura
Sette esempi sui treni della Linea Menta, dal più semplice. Ognuno ha lo stesso ritmo:
- Prevedi: leggi il codice e scrivi su un foglio cosa stampa. Prima di aprire la risposta.
- In italiano: cosa fa il programma, a parole.
- Passo per passo: cosa succede a ogni riga.
- Cosa portiamo a casa: la cosa da ricordare.
Per provarli
Scompatta lo zip della palestra e segui il suo README.md, che ti fa creare il venv e il tuo database db_psycopg. Poi, per ogni esempio:
- crea un file
palestra/prova.py(ogni esempio sostituisce il precedente); - incolla il codice;
- dal terminale, nella cartella dello zip e con il venv acceso:
python palestra/prova.py.
Perché dentro palestra/? Perché from database import connetti funziona solo se database.py è nella stessa cartella del file.
Gli esempi scrivono nel tuo database. Per riportarlo com'era all'inizio:
psql -d db_psycopg -f linea_menta.sqlI treni, per ricordarteli (id, codice, vagoni, km):
id | codice | vagoni | km
----+--------+--------+------
1 | M10 | 4 | 1200
2 | M12 | 4 | 4800
3 | M14 | 3 | 5200
4 | M16 | 6 | 300
5 | M18 | 4 | 7100
6 | M20 | 6 | 0
7 | M22 | 3 | 2500
8 | M24 | 4 | 5000
9 | T28 | 2 | 900
(9 righe)A · Il vassoio
Ernesto Pozzi, il capo deposito, vuole i treni con più di quattro vagoni.
from database import connetti
with connetti() as conn:
vassoio = conn.execute("SELECT codice, vagoni FROM treni WHERE vagoni > 4 ORDER BY id")
print(vassoio.fetchone())
print(vassoio.fetchall())
print(vassoio.fetchall())
print(vassoio.fetchone())Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
{'codice': 'M16', 'vagoni': 6}
[{'codice': 'M20', 'vagoni': 6}]
[]
None- Riga 1:
fetchone()prende la prima riga del vassoio, il M16, come dizionario. - Riga 2:
fetchall()prende tutte quelle che restano. Il M16 l'hai già ritirato: resta solo il M20. Ed è una lista, anche se ha un elemento solo. - Riga 3: il vassoio è vuoto.
fetchall()dà la lista vuota, non un errore. - Riga 4: vassoio vuoto anche per
fetchone():None.
In italiano.
- Apro la connessione.
- Ordino: i treni con più di quattro vagoni, in ordine di id.
executemi dà il vassoio. - Ritiro una riga, poi tutte le altre, poi di nuovo tutte, poi di nuovo una.
Passo per passo.
| Riga | Cosa c'è nel vassoio prima | Cosa ritiro | Cosa resta |
|---|---|---|---|
fetchone() | M16, M20 | M16 | M20 |
fetchall() | M20 | [M20] | niente |
fetchall() | niente | [] | niente |
fetchone() | niente | None | niente |
Cosa portiamo a casa. execute non ti dà le righe: ti dà il vassoio. Le righe si ritirano una volta sola. fetchone() e fetchall() sul vassoio vuoto non sono errori: sono None e [].
B · Il buco e la virgola
Lucia Ferri e il capo deposito chiedono i treni per codice. Una funzione che lo fa:
from database import connetti
def treno_per_codice(codice):
with connetti() as conn:
return conn.execute("SELECT * FROM treni WHERE codice = %s", (codice)).fetchone()
print(treno_per_codice("M14"))Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
Questo programma si ferma. Ecco il traceback, accorciato (i file di psycopg sono tanti: leggi dal basso, come sempre):
Traceback (most recent call last):
File "~/corso/db-psycopg/palestra/prova.py", line 9, in <module>
print(treno_per_codice("M14"))
^^^^^^^^^^^^^^^^^^^^^^^
File "~/corso/db-psycopg/palestra/prova.py", line 6, in treno_per_codice
return conn.execute("SELECT * FROM treni WHERE codice = %s", (codice)).fetchone()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
…
TypeError: query parameters should be a sequence or a mapping, got str- L'ultima riga dice cosa: psycopg si aspettava una sequenza (una tupla, una lista) di valori, e ha ricevuto una stringa.
(codice)è la stringa"M14"con le parentesi attorno: le parentesi non fanno la tupla. La fa la virgola.
La versione giusta:
from database import connetti
def treno_per_codice(codice):
with connetti() as conn:
return conn.execute("SELECT * FROM treni WHERE codice = %s", (codice,)).fetchone()
print(treno_per_codice("M14"))
print(treno_per_codice("M99")){'id': 3, 'codice': 'M14', 'vagoni': 3, 'km': 5200, 'in_servizio': True, 'stazione_id': 3}
NoneIn italiano.
- La funzione riceve un codice.
- Ordina «tutte le colonne del treno con questo codice». Il codice non è scritto nella stringa: c'è un buco,
%s. - Il valore del buco arriva a parte, in una tupla di un elemento.
fetchone()ritira la riga: un dizionario, oppureNone.
Passo per passo.
| Chiamata | Cosa va nel buco | Cosa torna |
|---|---|---|
treno_per_codice("M14") | "M14" | il dizionario del M14 |
treno_per_codice("M99") | "M99" | None: nessun treno, nessuna riga, nessun errore |
Cosa portiamo a casa. Un valore solo: (codice,), con la virgola. Un treno che non c'è non rompe niente: la funzione risponde None.
C · Senza with, la scrittura sparisce
Il M14 ha fatto 5200 km: va in officina, e dopo la revisione si azzerano. Il primo tentativo:
from database import connetti
conn = connetti()
conn.execute("UPDATE treni SET km = 0 WHERE codice = %s", ("M14",))
print("Fatto, credo.")Poi, dal terminale, guardi il M14 con psql:
psql -d db_psycopg -c "SELECT codice, km FROM treni WHERE codice = 'M14'"Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
Fatto, credo.
codice | km
--------+------
M14 | 5200
(1 riga)- Il programma non dà nessun errore e dice «fatto».
- Ma i km del M14 sono ancora 5200. La modifica è rimasta provvisoria: nessuno ha detto «confermo», e quando il programma è finito, PostgreSQL l'ha buttata via.
La versione giusta:
from database import connetti
with connetti() as conn:
conn.execute("UPDATE treni SET km = 0 WHERE codice = %s", ("M14",))
print("Fatto.")Fatto.
codice | km
--------+----
M14 | 0
(1 riga)In italiano. Il with apre la connessione, esegue le scritture, e quando il blocco finisce fa il commit: «confermo». Poi chiude.
Passo per passo.
| Versione | Chi fa il commit | Cosa vede psql |
|---|---|---|
conn = connetti() | nessuno | 5200: la modifica sparisce |
with connetti() as conn: | il with, all'uscita del blocco | 0: la modifica resta |
Cosa portiamo a casa. Una scrittura senza with non dà errori e non si salva. L'unico modo di accorgersene è guardare nel database, come hai fatto con psql: lo faranno anche i test della palestra.
D · Due scritture, un errore
Il M10 va fuori servizio, e subito dopo si registra un treno nuovo, il M26. Ma il M26 è scritto male: zero vagoni. In tabella treni c'è un vincolo: vagoni deve essere maggiore di zero.
import psycopg
from database import connetti
try:
with connetti() as conn:
conn.execute("UPDATE treni SET in_servizio = false WHERE codice = %s", ("M10",))
conn.execute("INSERT INTO treni (codice, vagoni) VALUES (%s, %s)", ("M26", 0))
except psycopg.errors.CheckViolation as errore:
print("Il database ha detto di no:")
print(errore)Poi guardi i due treni:
psql -d db_psycopg -c "SELECT codice, in_servizio FROM treni WHERE codice IN ('M10', 'M26')"Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
Il database ha detto di no:
new row for relation "treni" violates check constraint "treni_vagoni_check"
DETAIL: Failing row contains (10, M26, 0, 0, t, null).
codice | in_servizio
--------+-------------
M10 | t
(1 riga)- Il database dice di no al M26:
CheckViolation, con il nome del vincolo (treni_vagoni_check) e la riga che l'ha rotto. - Il M10 è ancora in servizio (
t, true), e il M26 non c'è. La prima scrittura, quella che era andata bene, è stata annullata.
In italiano.
- Dentro lo stesso
withmetto due scritture. - La prima va bene: il M10 esce dal servizio, ma ancora provvisoriamente.
- La seconda rompe un vincolo: il database risponde con un errore.
- L'errore esce dal blocco
with. Ilwithse ne accorge e fa il rollback: annulla anche la prima scrittura. - Il
try/exceptfuori dal blocco prende l'errore e lo stampa (come nel libro Errori e debugger).
Il disegno.
Passo per passo.
| Passo | Cosa succede | Stato provvisorio |
|---|---|---|
| 1 | UPDATE del M10 | M10 fuori servizio |
| 2 | INSERT del M26 con 0 vagoni: errore | — |
| 3 | il with fa il rollback | M10 di nuovo in servizio |
| 4 | il except stampa il messaggio | niente è stato salvato |
Cosa portiamo a casa. O tutto, o niente. Il with è la differenza tra «un treno fuori servizio senza il suo motivo» e «niente è cambiato». Quando due scritture vanno insieme, stanno nello stesso with.
E · RETURNING
Si registrano due treni nuovi, e chi li registra vuole sapere che id hanno preso.
from database import connetti
with connetti() as conn:
nuovo = conn.execute(
"INSERT INTO treni (codice, vagoni) VALUES (%s, %s) RETURNING id",
("M26", 4),
).fetchone()
print(nuovo)
completo = conn.execute(
"INSERT INTO treni (codice, vagoni) VALUES (%s, %s) RETURNING *",
("M28", 6),
).fetchone()
print(completo)Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
{'id': 10}
{'id': 11, 'codice': 'M28', 'vagoni': 6, 'km': 0, 'in_servizio': True, 'stazione_id': None}- Con
RETURNING id, il vassoio contiene solo l'id nuovo: il 10 (i treni erano nove). - Con
RETURNING *contiene tutta la riga com'è nel database: l'id 11, e anche i valori che non hai scritto, quelli di partenza (kma 0,in_servizioaTrue,stazione_idaNone: il M28 è al deposito).
In italiano. Una INSERT scrive. RETURNING le dice di restituire anche quello che ha scritto. E quello che torna lo ritiri con fetchone(), perché è una riga sola.
Passo per passo.
| Query | Cosa scrive | Cosa ritira fetchone() |
|---|---|---|
INSERT … RETURNING id | M26, 4 vagoni | {'id': 10} |
INSERT … RETURNING * | M28, 6 vagoni | tutta la riga del M28, con l'id 11 |
Cosa portiamo a casa. Senza RETURNING il vassoio è vuoto, e fetchone() dà ProgrammingError: the last operation didn't produce records. Con RETURNING * hai la riga vera, non quella che credi di aver scritto: gli id e i valori di partenza li sceglie il database.
F · Bobby Tables
L'ufficio del personale cerca per nome. Due funzioni: una con la f-string, una con il buco.
import psycopg
from database import connetti
def cerca_con_f_string(nome):
with connetti() as conn:
query = f"SELECT * FROM personale WHERE nome = '{nome}'"
print(query)
return conn.execute(query).fetchall()
def cerca_con_il_buco(nome):
with connetti() as conn:
return conn.execute("SELECT * FROM personale WHERE nome = %s", (nome,)).fetchall()
print(len(cerca_con_f_string("Lucia Ferri")))
try:
cerca_con_f_string("Gino D'Alessio")
except psycopg.errors.SyntaxError as errore:
print("SyntaxError:", errore)
print(len(cerca_con_f_string("x' OR '1'='1")))
print()
print(len(cerca_con_il_buco("x' OR '1'='1")))
print([persona["badge"] for persona in cerca_con_il_buco("Gino D'Alessio")])Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
SELECT * FROM personale WHERE nome = 'Lucia Ferri'
1
SELECT * FROM personale WHERE nome = 'Gino D'Alessio'
SyntaxError: syntax error at or near "Alessio"
LINE 1: SELECT * FROM personale WHERE nome = 'Gino D'Alessio'
^
SELECT * FROM personale WHERE nome = 'x' OR '1'='1'
12
0
['B-11']- Con Lucia: una persona. La f-string funziona, finché il valore è tranquillo.
- Con Gino D'Alessio: l'apostrofo del cognome chiude la stringa prima del tempo. Guarda la query stampata: dopo
'Gino D'il SQL leggeAlessiocome una parola sua, e il database non capisce. La freccia^sotto la riga del messaggio indica proprio il punto. - Con
x' OR '1'='1: la query stampata è diventataWHERE nome = 'x' OR '1'='1'. La seconda metà è sempre vera, quindi la ricerca restituisce tutte e 12 le persone. Il nome si è trasformato in un pezzo di SQL. - Con il buco: il valore va a parte. Zero persone per il nome strano (nessuno si chiama così), e Gino trovato, apostrofo compreso: il suo badge è
B-11.
In italiano. La f-string costruisce un testo di SQL mettendoci dentro il valore: il database riceve già tutto impastato, e non può sapere dove finisce la query e dove cominciano i dati. Con %s, il database riceve la query e il valore in due pacchi separati: il valore resta un dato, qualunque cosa contenga.
Passo per passo.
| Nome cercato | f-string | %s |
|---|---|---|
Lucia Ferri | 1 persona | 1 persona |
Gino D'Alessio | SyntaxError | B-11 |
x' OR '1'='1 | 12 persone: tutte | 0 persone |
Cosa portiamo a casa. L'apostrofo di Gino D'Alessio non è un caso strano: è un cognome vero, e basta a rompere la f-string. Bobby Tables è lo stesso difetto, usato da un cattivo.
G · La password e il nome del database, dall'ambiente
database.py non ha scritto dentro il nome del database: lo chiede all'ambiente. Qualunque lettura da os.environ fa lo stesso.
import os
import psycopg
from database import NOME_DB, connetti
print("Database:", NOME_DB)
print("PGPASSWORD:", os.environ.get("PGPASSWORD", "(non c'è)"))
try:
with connetti() as conn:
print(conn.execute("SELECT count(*) AS quanti FROM treni").fetchone())
except psycopg.OperationalError as errore:
print("Errore:", errore)Lo lanci tre volte: normale, con una password, e con un nome di database che non esiste.
python palestra/prova.py
PGPASSWORD=s3greta python palestra/prova.py
MENTA_DB=db_che_non_esiste python palestra/prova.pyPrevedi Scrivi su un foglio cosa ti aspetti, poi apri.
Database: db_psycopg
PGPASSWORD: (non c'è)
{'quanti': 9}
Database: db_psycopg
PGPASSWORD: s3greta
{'quanti': 9}
Database: db_che_non_esiste
PGPASSWORD: (non c'è)
Errore: connection failed: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: database "db_che_non_esiste" does not exist- Primo lancio: nessuna variabile, vale il database di riserva e non c'è password. Il tuo PostgreSQL non la chiede: funziona.
- Secondo lancio: la password è comparsa solo per quel lancio. Nel file non c'è scritta da nessuna parte.
- Terzo lancio:
MENTA_DBcambia il database, senza toccare una riga di codice. Quel database non esiste, e l'errore lo dice con parole chiare:does not exist.
In italiano.
os.environ.get("MENTA_DB", "db_psycopg")cerca la variabile: se la trova usa quella, altrimenti il valore di riserva.- La variabile messa prima del comando (
NOME=valore comando) vale solo per quel comando. - La password si legge allo stesso modo. Poi tocca a psycopg: se trova
PGPASSWORD, la usa da solo.
Cosa portiamo a casa. Dove andare e con che chiave sono cose che cambiano da un computer all'altro: stanno nell'ambiente, non nel codice. Il codice resta uguale, e si può condividere senza regalare niente a nessuno.