Vai al contenuto
code-to-code

Apro l'indice…

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

Il database nell'API2. psycopgp. 22

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:

  1. Prevedi: leggi il codice e scrivi su un foglio cosa stampa. Prima di aprire la risposta.
  2. In italiano: cosa fa il programma, a parole.
  3. Passo per passo: cosa succede a ogni riga.
  4. 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:

  1. crea un file palestra/prova.py (ogni esempio sostituisce il precedente);
  2. incolla il codice;
  3. 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:

Terminale
psql -d db_psycopg -f linea_menta.sql

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

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

  1. Apro la connessione.
  2. Ordino: i treni con più di quattro vagoni, in ordine di id. execute mi dà il vassoio.
  3. Ritiro una riga, poi tutte le altre, poi di nuovo tutte, poi di nuovo una.

Passo per passo.

RigaCosa c'è nel vassoio primaCosa ritiroCosa resta
fetchone()M16, M20M16M20
fetchall()M20[M20]niente
fetchall()niente[]niente
fetchone()nienteNoneniente

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:

Python
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:

Python
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}
None
treno = {"id": 3, "codice": "M14", "vagoni": 3, "km": 5200, "in_servizio": True, "stazione_id": 3}trenodict"id"3"codice""M14""vagoni"3"km"5200"in_servizio"True"stazione_id"3
La riga del M14 che torna dal database

In italiano.

  1. La funzione riceve un codice.
  2. Ordina «tutte le colonne del treno con questo codice». Il codice non è scritto nella stringa: c'è un buco, %s.
  3. Il valore del buco arriva a parte, in una tupla di un elemento.
  4. fetchone() ritira la riga: un dizionario, oppure None.

Passo per passo.

ChiamataCosa va nel bucoCosa 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:

Python
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:

Terminale
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:

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

VersioneChi fa il commitCosa vede psql
conn = connetti()nessuno5200: la modifica sparisce
with connetti() as conn:il with, all'uscita del blocco0: 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.

Python
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:

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

  1. Dentro lo stesso with metto due scritture.
  2. La prima va bene: il M10 esce dal servizio, ma ancora provvisoriamente.
  3. La seconda rompe un vincolo: il database risponde con un errore.
  4. L'errore esce dal blocco with. Il with se ne accorge e fa il rollback: annulla anche la prima scrittura.
  5. Il try/except fuori dal blocco prende l'errore e lo stampa (come nel libro Errori e debugger).

Il disegno.

inizio with connetti() as conn passo UPDATE: M10 fuori servizio passo INSERT: M26 con 0 vagoni se CheckViolation sì: passo rollback: M10 torna in servizio mostra print(errore) no: passo commit: tutto salvatowith connetti() as connUPDATE: M10 fuori servizioINSERT: M26 con 0 vagoniCheckViolationcommit: tutto salvatorollback: M10 torna inserviziomostra print(errore)nosì
Due scritture nello stesso with

Passo per passo.

PassoCosa succedeStato provvisorio
1UPDATE del M10M10 fuori servizio
2INSERT del M26 con 0 vagoni: errore—
3il with fa il rollbackM10 di nuovo in servizio
4il except stampa il messaggioniente è 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.

Python
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 (km a 0, in_servizio a True, stazione_id a None: il M28 è al deposito).
nuovo = {"id": 10} completo = {"id": 11, "codice": "M28", "vagoni": 6, "km": 0, "in_servizio": True, "stazione_id": None}nuovocompletodict"id"10dict"id"11"codice""M28""vagoni"6"km"0"in_servizio"True"stazione_id"None
Le due righe che tornano da RETURNING

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.

QueryCosa scriveCosa ritira fetchone()
INSERT … RETURNING idM26, 4 vagoni{'id': 10}
INSERT … RETURNING *M28, 6 vagonitutta 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.

Python
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 legge Alessio come 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 è diventata WHERE 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 cercatof-string%s
Lucia Ferri1 persona1 persona
Gino D'AlessioSyntaxErrorB-11
x' OR '1'='112 persone: tutte0 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.

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

Terminale
python palestra/prova.py
PGPASSWORD=s3greta python palestra/prova.py
MENTA_DB=db_che_non_esiste python palestra/prova.py
Prevedi 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_DB cambia 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.

  1. os.environ.get("MENTA_DB", "db_psycopg") cerca la variabile: se la trova usa quella, altrimenti il valore di riserva.
  2. La variabile messa prima del comando (NOME=valore comando) vale solo per quel comando.
  3. 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.