Skip to content

    Chapter 55, Backend

    PostgreSQL and SQL

    The database is, almost always, the most enduring part of a system. I teach SQL with SQLite, which comes with Python and requires no installation, and then I show what changes in PostgreSQL.

    Code for this chapter: backend/cap55_sql.py

    Why SQL, and why PostgreSQL

    SQL is the declarative language that describes what you want, and the database decides how to fetch. PostgreSQL is the relational database I choose by default: it is open, extremely reliable, has rich types (JSON, arrays, dates with timezone) and solid transactions. The concepts in this chapter apply to any relational database. SQLite is useful for learning and for quick tests, and I show where PostgreSQL differs.

    Tables and constraints

    Constraints (NOT NULL, UNIQUE, CHECK, REFERENCES) make the database reject invalid data, even if a bug in the code tries to write it. I always declare them, because the database is the last line of defense:

    backend/cap55_sql.pylinhas 10 a 28
    import sqlite3

conexao = sqlite3.connect(":memory:")
conexao.row_factory = sqlite3.Row
conexao.execute("PRAGMA foreign_keys = ON")
conexao.executescript("""
CREATE TABLE clientes (
    id INTEGER PRIMARY KEY,
    nome TEXT NOT NULL,
    cidade TEXT
);
CREATE TABLE pedidos (
    id INTEGER PRIMARY KEY,
    cliente_id INTEGER NOT NULL REFERENCES clientes(id),
    total_centavos INTEGER NOT NULL CHECK (total_centavos > 0)
);
INSERT INTO clientes (nome, cidade) VALUES ('Ana', 'Recife'), ('Bia', 'Natal'), ('Caio', 'Recife');
INSERT INTO pedidos (cliente_id, total_centavos) VALUES (1, 5000), (1, 2500), (2, 10000);
""")

    Monetary values are kept in whole cents (INTEGER) or in NUMERIC, never in FLOAT, for the same reason as the types chapter.

    Querying: SELECT, JOIN and aggregation

    backend/cap55_sql.pylinhas 33 a 42
    consulta = "SELECT nome FROM clientes WHERE cidade = 'Recife' ORDER BY nome"
print([linha["nome"] for linha in conexao.execute(consulta)])

juncao = """
SELECT c.nome, p.total_centavos
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
ORDER BY p.id
"""
print([tuple(linha) for linha in conexao.execute(juncao)])
    Output
    ['Ana', 'Caio']
[('Ana', 5000), ('Ana', 2500), ('Bia', 10000)]

    The JOIN (or INNER JOIN) only returns customers who have an order. The LEFT JOIN keeps all customers, and those without an order appear with NULL. The GROUP BY groups rows and the HAVING filters the groups (the WHERE filters rows before grouping):

    backend/cap55_sql.pylinhas 44 a 59
    agregacao = """
SELECT c.nome, COALESCE(SUM(p.total_centavos), 0) AS total
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
GROUP BY c.nome
ORDER BY c.nome
"""
print([tuple(linha) for linha in conexao.execute(agregacao)])

com_filtro = """
SELECT cliente_id, COUNT(*) AS quantidade
FROM pedidos
GROUP BY cliente_id
HAVING COUNT(*) > 1
"""
print([tuple(linha) for linha in conexao.execute(com_filtro)])
    Output
    [('Ana', 7500), ('Bia', 10000), ('Caio', 0)]
[(1, 2)]

    The COALESCE replaces Caio's NULL (without orders) with zero. Without the LEFT JOIN, he wouldn't even appear in the result.

    Parameters: never build SQL with text

    Building SQL with f-string or concatenation is the oldest and still most common vulnerability: SQL injection. User input becomes part of the command. The parameters send the value separate from the command, and the database never interprets it as SQL:

    backend/cap55_sql.pylinhas 64 a 67
    entrada = "x' OR '1'='1"
inseguro = conexao.execute(f"SELECT nome FROM clientes WHERE nome = '{entrada}'").fetchall()
seguro = conexao.execute("SELECT nome FROM clientes WHERE nome = ?", (entrada,)).fetchall()
print(len(inseguro), len(seguro))
    Output
    3 0

    The f-string version returned all the customers (3), because the input rewrote the condition. The parameter version searched for a literally strange name and found zero. The placeholder is ? in SQLite and %s in the psycopg, but the rule is the same in any driver.

    Transactions: all or nothing

    A transaction groups operations that need to happen together. Transferring money is the classic example: debiting from one account and crediting to another cannot be left halfway. The with conexao confirms (commit) if the block ends well and undoes (rollback) if an exception is raised:

    backend/cap55_sql.pylinhas 72 a 88
    conexao.execute("CREATE TABLE contas (id INTEGER PRIMARY KEY, saldo INTEGER NOT NULL CHECK (saldo >= 0))")
conexao.executemany("INSERT INTO contas VALUES (?, ?)", [(1, 100), (2, 50)])
conexao.commit()


def transferir(origem, destino, valor):
    with conexao:
        conexao.execute("UPDATE contas SET saldo = saldo - ? WHERE id = ?", (valor, origem))
        conexao.execute("UPDATE contas SET saldo = saldo + ? WHERE id = ?", (valor, destino))


transferir(1, 2, 30)
try:
    transferir(1, 2, 500)
except sqlite3.IntegrityError as erro:
    print("recusado:", erro)
print([tuple(linha) for linha in conexao.execute("SELECT id, saldo FROM contas ORDER BY id")])
    Output
    recusado: CHECK constraint failed: saldo >= 0
[(1, 70), (2, 80)]

    The second transfer failed on the debit (negative balance violates the CHECK), and the rollback ensured that nothing changed: the balances are 70 and 80, which sum to the same 150 as before. These guarantees (atomicity, consistency, isolation, and durability) are the ACID.

    Indexes and the query plan

    Without an index, searching for a column forces the database to read the entire table. An index is an auxiliary ordered structure that makes the search fast, at the cost of space and slightly slower writes. The command EXPLAIN shows how the database intends to execute the query:

    backend/cap55_sql.pylinhas 93 a 101
    def usa_indice(sql):
    plano = conexao.execute("EXPLAIN QUERY PLAN " + sql).fetchall()
    return any("INDEX" in linha["detail"] for linha in plano)


busca = "SELECT * FROM pedidos WHERE cliente_id = 1"
print("antes do índice:", usa_indice(busca))
conexao.execute("CREATE INDEX ix_pedidos_cliente ON pedidos (cliente_id)")
print("depois do índice:", usa_indice(busca))
    Output
    antes do índice: False
depois do índice: True

    Create indexes for the columns that appear in WHERE, in JOIN and in ORDER BY in the frequentqueries. Do not create one for each column: each index costs each INSERT.

    What changes in PostgreSQL

    SubjectSQLitePostgreSQL
    ServerA file, without a processA server, with users and network
    Auto keyINTEGER PRIMARY KEYINTEGER GENERATED ALWAYS AS IDENTITY
    Parameter marker?%s (in psycopg)
    TypesFlexible (weak typing)Strict, with JSONB, ARRAY, TIMESTAMPTZ
    ConcurrencyOne write at a timeMany simultaneous writes (MVCC)
    Return the created rowLimitedINSERT ... RETURNING

    With the driver psycopg (version 3), the connection is also a context manager: exiting the with without error confirms the transaction, and exiting with an exception undoes it. The blocks below run against a real PostgreSQL (the variable DATABASE_URL points to it):

    Com PostgreSQL (psycopg)
    import os

import psycopg
from psycopg.types.json import Jsonb

with psycopg.connect(os.environ["DATABASE_URL"]) as conexao:
    conexao.execute("DROP TABLE IF EXISTS produtos")
    conexao.execute("""
        CREATE TABLE produtos (
            id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            nome TEXT UNIQUE NOT NULL,
            preco_centavos INTEGER NOT NULL,
            atributos JSONB NOT NULL DEFAULT '{}'
        )
    """)
    cursor = conexao.execute(
        "INSERT INTO produtos (nome, preco_centavos, atributos) VALUES (%s, %s, %s) RETURNING id",
        ("caneta", 350, Jsonb({"cor": "azul"})),
    )
    print("id criado:", cursor.fetchone()[0])
    Output
    id criado: 1

    The RETURNING returns the key generated in the same operation, without a second query. The ON CONFLICT performs an upsert (inserts, or updates if the unique key already exists), and the operator ->> queries within the JSON:

    Upsert e consulta em JSONB
    import os

import psycopg

with psycopg.connect(os.environ["DATABASE_URL"]) as conexao:
    conexao.execute(
        """INSERT INTO produtos (nome, preco_centavos) VALUES (%s, %s)
           ON CONFLICT (nome) DO UPDATE SET preco_centavos = EXCLUDED.preco_centavos""",
        ("caneta", 400),
    )
    print(conexao.execute("SELECT nome, preco_centavos FROM produtos").fetchall())
    print(conexao.execute("SELECT nome FROM produtos WHERE atributos->>'cor' = %s", ("azul",)).fetchall())

with psycopg.connect(os.environ["DATABASE_URL"]) as conexao:
    try:
        conexao.execute("INSERT INTO produtos (nome, preco_centavos) VALUES (%s, %s)", ("caneta", 1))
    except psycopg.errors.UniqueViolation as erro:
        print("violação de unicidade:", erro.diag.constraint_name)
    conexao.rollback()
    conexao.execute("DROP TABLE produtos")
    Output
    [('caneta', 400)]
[('caneta',)]
violação de unicidade: produtos_nome_key

    Two transactions, two worlds

    In PostgreSQL, the default isolation (READ COMMITTED) means that each command sees what has already been confirmed by other transactions. To avoid the race of "read the balance, calculate, write", use SELECT ... FOR UPDATE, which locks the row until the end of the transaction, or a constraint in the database. Data concurrency is the database's problem, not Python's.

    Exercise 1

    Total per customer, including those who did not purchase

    Write total_por_cliente() that uses the above connection and returns a list of (nome, total) ordered by name, with zero for those who have no orders.