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:
| Pezzo | Cos'è | Nel codice |
|---|---|---|
| La connessione | La strada aperta verso un database, come psql -d db_psycopg | conn, aperta da connetti() |
| La domanda | Una stringa di SQL, con i buchi %s per i valori | "SELECT * FROM treni WHERE codice = %s" |
| Il vassoio | Dove PostgreSQL appoggia le righe della risposta | quello che torna da conn.execute(...) |
| Il ritiro | Prendi le righe dal vassoio, una o tutte | fetchone(), 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:
def connetti() -> psycopg.Connection:
return psycopg.connect(dbname=NOME_DB, row_factory=dict_row)psycopg.connect(...)apre la strada.dbname=NOME_DBdice quale database.NOME_DBvaledb_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 inpsqlnon ti ha mai chiesto una password. row_factory=dict_rowdecide che forma ha una riga quando arriva. Senza, è una tupla. Condict_row, un dizionario con i nomi delle colonne.
Provato, la stessa riga, senza e con dict_row:
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:
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 blocco | Cosa fa il with | Risultato |
|---|---|---|
| Tutto bene | commit, poi chiude la connessione | Le modifiche restano |
| Un errore dentro il blocco | rollback, poi chiude la connessione | Le 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
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:
| Ritiro | Cosa dà | Se non ci sono righe |
|---|---|---|
fetchone() | una riga, come dizionario | None |
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.
conn.execute("SELECT * FROM treni WHERE codice = %s", ("M14",))- Un
%sper 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):
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,).
conn.execute("... WHERE codice = %s", (codice)) # non è una tupla: è una stringa
conn.execute("... WHERE codice = %s", (codice,)) # una tupla di un elementoSe 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.
with connetti() as conn:
nuovo = conn.execute(
"INSERT INTO treni (codice, vagoni) VALUES (%s, %s) RETURNING id",
("M26", 4),
).fetchone(){'id': 10}RETURNING idritorna 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:
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'='1il 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:
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.
PGPASSWORD=s3greta python palestra/prova.pyDentro Python la leggi con os.environ:
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.
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_databaseAnche 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, comepsql -d.- Il
withfa il commit se va tutto bene e il rollback se c'è un errore. Senzawith, la scrittura sparisce senza errori. executeordina,fetchritira.fetchone()dà un dizionario oNone,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,). RETURNINGti restituisce la riga scritta, e conUPDATEti 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.