Vai al contenuto
code-to-code

Apro l'indice…

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

Il database nell'API2. psycopgp. 15

psycopg

Il SQL che già conosci, mandato da Python

  • 15 minuti di lettura

Il SQL lo hai già scritto in psql: tu digiti la domanda, PostgreSQL risponde. Adesso la domanda la scrive il programma, e la risposta la riceve il programma.

Il ponte tra Python e PostgreSQL si chiama psycopg. Lo hai già nel venv: sta in requirements.txt, e pip install -r requirements.txt lo ha messo lì. Non c'è altro da imparare che questo: una stringa di SQL va dall'altra parte, una risposta torna indietro. Tutto il capitolo sta in quel viaggio.

Il disegno

 il tuo programma                         PostgreSQL
┌──────────────────┐                   ┌──────────────────┐
│  conn ───────────┼── la connessione ─┼─▶ db_psycopg     │
│                  │                   │                  │
│  execute(query)  │──── la domanda ──▶│   tabelle        │
│        │         │                   │                  │
│  il vassoio ◀────┼──── le righe ─────┼───               │
│  fetchone()      │                   └──────────────────┘
│  fetchall()      │
└──────────────────┘

Quattro pezzi, e li ritrovi in ogni riga di codice del capitolo:

PezzoCos'èNel codice
La connessioneLa strada aperta verso un database, come psql -d db_psycopgconn, aperta da connetti()
La domandaUna stringa di SQL, con i buchi %s per i valori"SELECT * FROM treni WHERE codice = %s"
Il vassoioDove PostgreSQL appoggia le righe della rispostaquello che torna da conn.execute(...)
Il ritiroPrendi le righe dal vassoio, una o tuttefetchone(), fetchall()

execute ordina, fetch ritira. Come al ristorante: ordinare non è ancora mangiare.

connetti(), riga per riga

Nello zip della palestra c'è palestra/database.py, e dentro c'è questa funzione:

Python
def connetti() -> psycopg.Connection:
    return psycopg.connect(dbname=NOME_DB, row_factory=dict_row)
  • psycopg.connect(...) apre la strada.
  • dbname=NOME_DB dice quale database. NOME_DB vale db_psycopg (leggi più sotto come, e perché).
  • Non c'è un host, non c'è una porta, non c'è un utente, non c'è una password. Quando mancano, psycopg fa come psql -d: parla con PostgreSQL sul tuo computer, attraverso il socket, con il tuo utente. PostgreSQL, sul tuo computer, si fida di te. È per questo che in psql non ti ha mai chiesto una password.
  • row_factory=dict_row decide che forma ha una riga quando arriva. Senza, è una tupla. Con dict_row, un dizionario con i nomi delle colonne.

Provato, la stessa riga, senza e con dict_row:

Python
import psycopg
from database import NOME_DB, connetti

with psycopg.connect(dbname=NOME_DB) as conn:
    print(conn.execute("SELECT codice, vagoni FROM treni WHERE id = 3").fetchone())

with connetti() as conn:
    print(conn.execute("SELECT codice, vagoni FROM treni WHERE id = 3").fetchone())
('M14', 3)
{'codice': 'M14', 'vagoni': 3}

Con il dizionario scrivi treno["codice"], non treno[0]: sai cosa stai leggendo. E un dizionario lo conosci già, e FastAPI lo trasforma in JSON da solo.

Il with: commit e rollback

connetti() apre la strada. Ma c'è un'altra regola di PostgreSQL che conosci già dal libro SQL: le scritture (INSERT, UPDATE, DELETE) restano provvisorie finché qualcuno non dice «confermo». Quel «confermo» si chiama commit.

Lo scrivi con il with:

Python
with connetti() as conn:
    conn.execute("UPDATE treni SET km = 0 WHERE codice = %s", ("M14",))

Quando il blocco with finisce, succede una cosa o l'altra:

Come finisce il bloccoCosa fa il withRisultato
Tutto benecommit, poi chiude la connessioneLe modifiche restano
Un errore dentro il bloccorollback, poi chiude la connessioneLe modifiche spariscono, come se non fossero mai esistite

Il rollback è il motivo per cui il with è prezioso: se hai due scritture e la seconda fallisce, la prima non resta a metà. O vanno a buon fine tutte e due, o nessuna.

execute e fetch: ordinare e ritirare

Python
with connetti() as conn:
    vassoio = conn.execute("SELECT codice FROM treni WHERE vagoni > 4")
    treno = vassoio.fetchone()

conn.execute(...) manda la domanda e ti dà indietro il vassoio. Le righe sono lì sopra. Per averle, le ritiri:

RitiroCosa dàSe non ci sono righe
fetchone()una riga, come dizionarioNone
fetchall()tutte le righe, in una lista di dizionari[], la lista vuota

Scegli in base alla domanda. «Il treno con questo codice» è una cosa sola: fetchone(). «I treni con più di 4 vagoni» sono tante: fetchall().

Due cose da non confondere: None e [] non sono errori, sono risposte. «Non c'è nessun M99» è una risposta legittima. Decidere che cosa farne (in un'API, un 404) tocca alla rotta, nel capitolo dopo. Qui la funzione restituisce quello che trova, None compreso.

Il buco %s e la tupla

I valori non si scrivono dentro la stringa: si lasciano dei buchi, %s, e i valori si passano a parte, in una tupla.

Python
conn.execute("SELECT * FROM treni WHERE codice = %s", ("M14",))
  • Un %s per ogni valore, nello stesso ordine.
  • Niente apici intorno al %s: ci pensa psycopg a trattare il valore come testo, come numero, come date.
  • La tupla è il secondo argomento di execute.

La tupla la fa la virgola

Con due valori la tupla la conosci già: (5000, 4). Con un valore solo c'è un tranello, che è uno dei più famosi di Python (lo trovi in Insiemi e tuple):

Python
print(type((3)))
print(type((3,)))
print(type(("M12", 4)))
<class 'int'>
<class 'tuple'>
<class 'tuple'>

Le parentesi da sole non fanno la tupla: (3) è solo il numero 3, con le parentesi attorno come in un calcolo. È la virgola che fa la tupla. Quindi, con un valore solo, ci vuole la virgola alla fine: (codice,).

Python
conn.execute("... WHERE codice = %s", (codice))    # non è una tupla: è una stringa
conn.execute("... WHERE codice = %s", (codice,))   # una tupla di un elemento

Se te la dimentichi, psycopg 3.3 ti dice (provato):

TypeError: query parameters should be a sequence or a mapping, got str

«Mi aspettavo una sequenza di valori, e mi hai dato una stringa.» Se invece il valore è un numero: TypeError: object of type 'int' has no len(). E se i buchi e i valori non tornano: psycopg.ProgrammingError: the query has 2 placeholders but 1 parameters were passed. Tutti questi, e come si leggono, stanno nella pagina se qualcosa va storto.

RETURNING: scrivere e ritirare nello stesso colpo

Una INSERT normale non restituisce righe: scrive e basta. Ma spesso ti serve sapere che id ha preso la riga nuova. Nel libro SQL hai visto RETURNING (lo trovi in Scrivere): fa tornare la riga scritta, e con psycopg la ritiri come una SELECT.

Python
with connetti() as conn:
    nuovo = conn.execute(
        "INSERT INTO treni (codice, vagoni) VALUES (%s, %s) RETURNING id",
        ("M26", 4),
    ).fetchone()
{'id': 10}
  • RETURNING id ritorna solo l'id. RETURNING * ritorna tutta la riga, con anche i valori di partenza (km, in_servizio…).
  • Vale anche per UPDATE ... RETURNING *: ti dice cosa è stato cambiato, e se la riga non c'era, non torna niente: fetchone() dà None. È il modo più comodo per sapere se un id esisteva.

Bobby Tables: perché il %s non è una comodità

C'è un fumetto famoso (xkcd, numero 327). Una scuola scrive nel registro il nome di un alunno nuovo, e la madre lo ha chiamato Robert'); DROP TABLE Students;--. Il programma della scuola incolla il nome dentro la query. La query diventa due comandi, e il secondo cancella la tabella degli alunni. Da quella volta tutti lo chiamano Bobby Tables.

Questo si chiama SQL injection: un valore che doveva essere solo dati diventa comandi.

Succede quando i valori si incollano dentro la stringa, con una f-string:

Python
query = f"SELECT * FROM personale WHERE nome = '{nome}'"

Provato sul database della Linea Menta. Tre nomi di fila: uno normale, uno con l'apostrofo, e uno che è Bobby Tables in versione corta:

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
  • Con Lucia Ferri va tutto bene: una persona.
  • Con Gino D'Alessio l'apostrofo del cognome chiude la stringa prima del tempo: il SQL non è più SQL. Questo non è un attacco, è un cognome vero.
  • Con x' OR '1'='1 il nome non è più un nome: è un pezzo di condizione che dice «oppure uno è uguale a uno», sempre vera. La ricerca trova tutte e 12 le persone.

Con il buco, la stessa ricerca:

Python
conn.execute("SELECT * FROM personale WHERE nome = %s", (nome,))
0
['B-11']

Zero persone per x' OR '1'='1 (nessuno si chiama così) e Gino D'Alessio trovato, apostrofo compreso. Il valore viaggia separato dalla domanda: PostgreSQL lo prende come un dato, mai come un comando.

Nota che il %s funziona per i valori. I nomi delle tabelle e delle colonne non possono essere buchi: FROM %s non esiste. Quelli si scrivono a mano nella query, e vengono dal tuo codice, mai da un utente.

La password non va nel codice

Il tuo database non chiede la password. Il database della Linea Menta, il giorno che sarà su un altro computer, la chiederà. E tu non la scrivi nel codice: un file di codice finisce su GitHub, sul computer di un collega, in uno zip. La password va fuori dal codice, in una variabile d'ambiente: un'etichetta che il terminale attacca al programma quando lo lancia.

Terminale
PGPASSWORD=s3greta python palestra/prova.py

Dentro Python la leggi con os.environ:

Python
import os

os.environ.get("PGPASSWORD", "(non c'è)")

os.environ.get funziona come il get dei dizionari: un valore di riserva se la variabile manca. Provato:

Database: db_psycopg
PGPASSWORD: (non c'è)
{'quanti': 9}

Database: db_psycopg
PGPASSWORD: s3greta
{'quanti': 9}

Il database dei test funziona con lo stesso trucco. In database.py, il nome del database non è scritto nella query: è questo.

Python
NOME_DB = os.environ.get("MENTA_DB", "db_psycopg")

Se c'è la variabile MENTA_DB vale quella (i test la mettono su db_psycopg_test, ed è così che non toccano i tuoi dati). Se non c'è, vale db_psycopg. Lo stesso programma, due database, senza cambiare una riga.

Psycopg conosce da solo alcune di queste variabili: PGPASSWORD, PGHOST, PGPORT, PGUSER. Se le trova, le usa, e tu non scrivi niente. Un database lontano si descrive spesso con un indirizzo solo, tutto in una stringa:

postgresql://utente:password@host:5432/nome_del_database

Anche quella è un posto dove la password non deve stare nel codice: la costruisci mettendo insieme i pezzi letti dall'ambiente. È l'esercizio 7 della palestra.

Ricorda

  • psycopg manda una stringa di SQL a PostgreSQL e ti restituisce le righe.
  • connetti() apre la strada senza host né password: il tuo utente, il socket, come psql -d.
  • Il with fa il commit se va tutto bene e il rollback se c'è un errore. Senza with, la scrittura sparisce senza errori.
  • execute ordina, fetch ritira. fetchone() dà un dizionario o None, fetchall() una lista, anche vuota.
  • I valori vanno nei buchi %s, e i valori vanno in una tupla. La tupla la fa la virgola: (codice,).
  • RETURNING ti restituisce la riga scritta, e con UPDATE ti dice se la riga c'era.
  • Mai una f-string con dei valori dentro: l'apostrofo di Gino D'Alessio la rompe, Bobby Tables la sfrutta.
  • La password sta fuori dal codice, in una variabile d'ambiente.