Bases de datos

En resumen: Una base de datos guarda los datos en tablas y los encuentra rápido con una consulta SQL; en Python hablas con ella a través de sqlite3, y los parámetros con ? protegen contra la inyección SQL.

Problema

Un catálogo en un archivo

El catálogo de la tienda es un archivo CSV, como en el módulo de archivos. Mientras hay cien productos, funciona. Luego hay cien mil, y aparecen problemas:

• para encontrar un producto, lees el archivo entero;
• «todo lo que cuesta menos de 5 €, ordenado por precio» es un bucle, un if y una ordenación, escritos a mano;
• dos cajas escriben en el archivo a la vez, y un registro se pierde o el archivo se estropea;
• se va la luz en mitad de una escritura, y desaparece la mitad del archivo.

Por eso los datos de casi cualquier programa real viven en una base de datos: un programa cuyo trabajo es guardar datos, encontrarlos rápido y no perderlos nunca.

La idea

Tablas y SQL

Una base de datos relacional guarda los datos en tablas. Una tabla tiene columnas (nombre, tipo) y filas (una por cada producto). La columna id es la clave primaria: su valor es distinto en cada fila, así que con ella se encuentra exactamente una.

Con la base de datos se habla en SQL. Los cinco comandos más comunes:

SQL

CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    price DECIMAL(8, 2)
);

INSERT INTO products VALUES (1, 'Café', 7.50);
SELECT name, price FROM products WHERE price < 5 ORDER BY price;
UPDATE products SET price = 6.90 WHERE id = 1;
DELETE FROM products WHERE id = 3;

• CREATE TABLE crea una tabla, INSERT añade una fila, UPDATE cambia filas y DELETE las borra.
• SELECT lee: qué columnas, de qué tabla, con qué condición (WHERE) y en qué orden (ORDER BY).

Tú dices qué quieres, y la base de datos decide cómo encontrarlo rápido.

Más precisamente, para curiosos

• Bases de datos relacionales conocidas son PostgreSQL, MySQL, Oracle y SQL Server. Funcionan como servidores aparte. Aquí usamos SQLite: una base de datos pequeña que vive en un archivo o entera en la memoria del programa. El módulo sqlite3 está en la biblioteca estándar de Python. La dirección ":memory:" es una base de datos en memoria: cada ejecución empieza con una vacía.
• El texto en SQL va entre comillas simples: 'Café'. En PostgreSQL y MySQL, DECIMAL(8, 2) es un número exacto con 2 decimales, adecuado para dinero. SQLite no guarda números decimales exactos: ahí 7.50 es un número de coma flotante normal.

Míralo

sqlite3: conexión, consulta, resultado

El módulo sqlite3 sigue el estándar de Python para bases de datos (DB-API, PEP 249): PostgreSQL y MySQL tienen los mismos connect, execute, commit y close. La conexión es una Connection, y el resultado de una consulta es un cursor. La función setup crea la tabla y añade tres productos:

def setup(conn):
    conn.execute("CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, price DECIMAL(8, 2))")
    conn.execute("INSERT INTO products VALUES (1, 'Café', 7.50), (2, 'Té', 3.20), (3, 'Chocolate', 2.40)")
    conn.commit()

Leemos las filas y convertimos cada una en un objeto de la dataclass Product, con los campos id, name y price:

@dataclass
class Product:
    id: int
    name: str
    price: float

conn = sqlite3.connect(":memory:")
setup(conn)
products = []
for product_id, name, price in conn.execute("SELECT id, name, price FROM products ORDER BY price"):
    products.append(Product(product_id, name, price))
for p in products:
    print(p)
conn.close()

Salida

Product(id=3, name='Chocolate', price=2.4)
Product(id=2, name='Té', price=3.2)
Product(id=1, name='Café', price=7.5)

• sqlite3.connect(dirección) abre la conexión. La dirección dice qué base de datos: aquí ":memory:", o un nombre de archivo como "shop.db".
• conn.execute(sql) envía cualquier comando y devuelve un cursor. Un for sobre el cursor te da las filas una a una, cada una como una tupla de las columnas en el orden del SELECT. Aquí la tupla se desempaqueta en tres variables.
• Antes de INSERT, UPDATE y DELETE, sqlite3 empieza una transacción por su cuenta, y conn.commit() guarda los cambios. Sin él, los cambios en una base de datos en un archivo se pierden al cerrarla.
• conn.close() cierra la conexión. (with conn: no la cierra: crea una transacción, como verás más abajo.)
• Los errores son excepciones de sqlite3.Error, por ejemplo OperationalError para un SQL incorrecto e IntegrityError para una regla de la tabla que no se cumple. Python no tiene excepciones comprobadas.

conn = sqlite3.connect(":memory:")
setup(conn)
products = []
for product_id, name, price in conn.execute("SELECT id, name, price FROM products ORDER BY price"):
    products.append(Product(product_id, name, price))
for p in products:
    print(p)
conn.close()

Paso a paso, en palabras

1. Una conexión con la base de datos en memoria. setup crea la tabla products y añade 3 filas.
2. Una lista vacía, donde irán los objetos.
3. SELECT con ORDER BY price: conn.execute devuelve un cursor sobre las filas, de la más barata a la más cara.
4. El bucle toma la fila 1 como una tupla y la desempaqueta en tres variables, y Product(...) crea con ellas un objeto: Chocolate.
5. El bucle toma la fila 2 como una tupla y la desempaqueta en tres variables, y Product(...) crea con ellas un objeto: Té.
6. El bucle toma la fila 3 como una tupla y la desempaqueta en tres variables, y Product(...) crea con ellas un objeto: Café.
7. El cursor no tiene más filas y el bucle se detiene.
8. La lista tiene objetos normales. Al imprimirlos se ve la dataclass con sus campos.

Más precisamente, para curiosos

• Con una base de datos real en un servidor, la dirección es otra y hay un usuario y una contraseña. Además necesitas el driver (controlador) de la base de datos como dependencia en el proyecto (para MySQL: el paquete mysql-connector-python):

import mysql.connector

conn = mysql.connector.connect(host="localhost", port=3306, database="shop", user="shop_app", password="secreto")
print("¡Conectado a la base de datos!")
conn.close()

• La contraseña no se escribe en el código. Se lee de la configuración o de una variable de entorno (os.environ).

• Aquí los precios son float para que sea breve. En una tienda real, el dinero se guarda exacto: como DECIMAL en PostgreSQL o MySQL (en Python llega como decimal.Decimal), y en SQLite como céntimos enteros.
• Con conn.row_factory = sqlite3.Row, también puedes leer cada fila por el nombre de la columna: row["name"].

Pruébalo

¿Qué se imprimirá?

Los productos son Café 7,50 €, Té 3,20 € y Chocolate 2,40 €. ¿Qué imprimirá el programa?

conn = sqlite3.connect(":memory:")
setup(conn)
for row in conn.execute("SELECT name FROM products WHERE price < 5 ORDER BY price DESC"):
    print(row[0])
conn.close()

Respuesta

Té
Chocolate

Café
Té
Chocolate

Chocolate
Té

Comprobar

Problema

El buscador que lo muestra todo

La web busca un producto por el nombre que escribe el usuario. La consulta se arma con una f-string:

def search(conn, name):
    sql = f"SELECT name, price FROM products WHERE name = '{name}'"
    for found, price in conn.execute(sql):
        print(f"  {found}: {price} €")

conn = sqlite3.connect(":memory:")
setup(conn)
print("Buscando: Té")
search(conn, "Té")
print("Buscando: x' OR '1'='1")
search(conn, "x' OR '1'='1")
conn.close()

Salida

Buscando: Té
  Té: 3.2 €
Buscando: x' OR '1'='1
  Café: 7.5 €
  Té: 3.2 €
  Chocolate: 2.4 €

Con «Té» funciona. Con x' OR '1'='1 la consulta se convierte en ... WHERE name = 'x' OR '1'='1'. La condición siempre es verdadera, y sale la tabla entera. La entrada del usuario ha pasado a formar parte del SQL. Esto es la inyección SQL (SQL injection), uno de los ataques más comunes contra sitios web. Con el mismo truco, alguien puede entrar sin contraseña o borrar una tabla.

La idea

Parámetros con ?

La solución es enviar la consulta y los valores por separado. La consulta tiene ? en el lugar de los valores, y tú se los pasas como una tupla, el segundo argumento de execute. (name,) es una tupla con un solo valor: la coma es obligatoria.

def search(conn, name):
    sql = "SELECT name, price FROM products WHERE name = ?"
    for found, price in conn.execute(sql, (name,)):
        print(f"  {found}: {price} €")

Salida

Buscando: Té
  Té: 3.2 €
Buscando: x' OR '1'='1

Ahora x' OR '1'='1 es solo un texto que se busca como nombre. No existe ese producto, y no sale nada. Escriba lo que escriba el usuario, no puede convertirse en SQL.

La regla es sencilla: un valor del usuario, de un archivo o de la red nunca se pega dentro del SQL. Usa siempre ?.

Pruébalo

Café con apóstrofo

La función cambia el nombre de un producto. Con el nombre Café 'Arábica' el programa se detiene con sqlite3.OperationalError: near "Arábica": syntax error. ¿En qué línea está el error?

Elige la línea con el error.

1def rename(conn, product_id, new_name):2 conn.execute(f"UPDATE products SET name = '{new_name}' WHERE id = {product_id}")3 conn.commit()

Comprobar

La idea

Transacción: todo o nada

Ana le transfiere 150 € a Carlos. Son dos consultas: sumarle a Carlos y restarle a Ana. La tabla tiene la condición CHECK (balance >= 0), así que la segunda consulta falla: Ana solo tiene 100 €.

def open_accounts(conn):
    conn.execute("CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(50), balance INT CONSTRAINT no_debt CHECK (balance >= 0))")
    conn.execute("INSERT INTO accounts VALUES (1, 'Ana', 100), (2, 'Carlos', 50)")
    conn.commit()


def move(conn, account_id, amount):
    conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, account_id))


def print_balances(conn):
    for owner, balance in conn.execute("SELECT owner, balance FROM accounts ORDER BY id"):
        print(f"  {owner}: {balance} €")

def transfer(conn, source, target, amount):
    move(conn, target, amount)
    conn.commit()
    move(conn, source, -amount)
    conn.commit()

conn = sqlite3.connect(":memory:")
open_accounts(conn)
try:
    transfer(conn, 1, 2, 150)
except sqlite3.Error:
    print("La transferencia de 150 € fue rechazada.")
print_balances(conn)
conn.close()

Salida

La transferencia de 150 € fue rechazada.
  Ana: 100 €
  Carlos: 200 €

La transferencia se rechazó, pero Carlos ya ha recibido 150 € de la nada: cada movimiento se guardó enseguida con commit(). Las dos consultas tienen que ser una transacción: o pasan las dos, o ninguna. En Python, esto lo hace with conn:.

def transfer(conn, source, target, amount):
    with conn:
        move(conn, target, amount)
        move(conn, source, -amount)

Salida

La transferencia de 150 € fue rechazada.
  Ana: 100 €
  Carlos: 50 €

• El primer UPDATE empieza una transacción: los cambios todavía no son definitivos.
• Si el bloque with conn: termina sin error, llama a conn.commit() y lo guarda todo de una vez.
• Si dentro hay una excepción, llama a conn.rollback() y lo deshace todo. La excepción sigue hacia arriba, para que también se entere quien llamó.
• open_accounts llama a commit() al final. Sin él, la creación de las cuentas estaría en la misma transacción, y rollback() también la desharía.

Más precisamente, para curiosos

• La excepción de la consulta que falla es un IntegrityError con el mensaje CHECK constraint failed: no_debt. no_debt es el nombre de la condición: en la tabla es balance INT CONSTRAINT no_debt CHECK (balance >= 0).

• Las bases de datos prometen cuatro cosas sobre las transacciones, conocidas como ACID: todo o nada (atomicidad, atomicity), los datos siguen siendo correctos según las reglas de las tablas (consistencia, consistency), las transacciones que se ejecutan a la vez no se estorban (aislamiento, isolation), y lo que se ha guardado no se pierde aunque se vaya la luz (durabilidad, durability).

La idea

ORM: objetos en lugar de filas

Con sqlite3, para cada clase escribes SQL y conviertes las filas en objetos a mano. Un ORM (object-relational mapping, mapeo objeto-relacional) lo hace por ti: describes la clase, y la biblioteca crea sola la tabla, las consultas y los objetos. La biblioteca estándar de Python no tiene ORM. El más conocido es SQLAlchemy (el paquete sqlalchemy), y los proyectos web con Django usan el ORM que trae Django.

models.py

from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column

class Base(DeclarativeBase):
    pass


class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    price: Mapped[float]

• Una subclase de Base (de DeclarativeBase) es una tabla, y __tablename__ le da el nombre.
• Cada campo con el tipo Mapped[...] es una columna. primary_key=True es la clave primaria: SQLAlchemy deja que la base de datos le dé un valor.
• mapped_column fija detalles de la columna: aquí, String(50). Un campo solo con Mapped[float] también se convierte en columna.
• El constructor se crea solo: Product(name="Café", price=7.50).

Guardar y leer pasa por la Session:

engine = create_engine("sqlite://")
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(Product(name="Café", price=7.50))
    session.add(Product(name="Té", price=3.20))
    session.commit()

    cheap = session.scalars(select(Product).where(Product.price < 5).order_by(Product.price))
    for p in cheap:
        print(f"{p.name}: {p.price} €")

Salida (con SQLAlchemy instalado)

Té: 3.2 €

create_engine("sqlite://") dice qué base de datos: aquí, SQLite en memoria. create_all crea las tablas. session.add añade el objeto, y commit() lo guarda en una transacción, como en sqlite3. La consulta se construye con código: select(Product).where(Product.price < 5) habla de la clase y de su campo price, no de la tabla. session.scalars devuelve objetos ya hechos.

SQLAlchemy es un paquete, y el comprobador del sitio no lo tiene, así que este código no se ejecuta aquí. La salida viene de una ejecución con SQLAlchemy 2.1 instalado (pip install sqlalchemy).

Más precisamente, para curiosos

• Una consulta con select es código, así que es fácil armar las condiciones según lo que elige el usuario. Una función que busca por nombre:

def named(session, name):
    query = select(Product).where(Product.name == name)
    return session.scalars(query).all()

• Una transacción que se deshace si hay un error: session.begin() dentro de un with lo guarda todo al final, y con una excepción lo deshace todo:

with Session(engine) as session, session.begin():
    session.add(Product(name="Chocolate", price=2.40))
    # más cambios: con una excepción se deshace todo; si no, se guarda todo

• Con create_engine(dirección, echo=True), SQLAlchemy imprime el SQL que envía. Así ves que por debajo sigue siendo SQL.
• En código antiguo verás session.query(Product).filter(...). Es el estilo de SQLAlchemy 1.x, y select(...) es de la 2.0 en adelante.

Ejercicio

Una tienda con base de datos

Resuelve las tareas en tu editor, con sqlite3 y la base de datos ":memory:".1. Catálogo. Crea una tabla products y las funciones add, set_price, remove y show (list es el nombre de un tipo integrado), todas con ? menos show. Añade Café (7,50 €), Té (3,20 €) y Chocolate (2,40 €), imprímelos, cambia el precio del café a 6,90 €, borra el chocolate e imprime otra vez:

Salida

1. Café: 7.5 €
2. Té: 3.2 €
3. Chocolate: 2.4 €
---
1. Café: 6.9 €
2. Té: 3.2 €

2. Búsqueda por parte del nombre. Los productos son Café, Cacao, Té y Éclair. Una función find(conn, text) encuentra todos los productos cuyo nombre contiene el texto, sin importar mayúsculas y minúsculas. LOWER en SQLite solo cambia las letras de la A a la Z, así que É se queda como está. Dale a la base de datos una función propia: conn.create_function("lower_text", 1, str.lower), luego WHERE lower_text(name) LIKE ? con el valor f"%{text.lower()}%". Prueba «CA», «éclair» y un intento de inyección:

Salida

Buscando «CA»:
  Cacao: 4.8 €
  Café: 7.5 €
Buscando «éclair»:
  Éclair: 2.4 €
Buscando «' OR '1'='1»:
  no se encontraron productos

3. Una transferencia que falla a medias. Las cuentas son Ana (100 €) y Carlos (50 €). transfer(conn, source, target, amount) primero resta de source y luego suma a target. Si cursor.rowcount es 0 (no existe esa cuenta), lanza ValueError(f"No existe la cuenta {account_id}"). Todo va dentro de with conn:. Transfiere 30 € de la 1 a la 2, y luego 40 € de la 1 a la cuenta 99, que no existe:

Salida

Transferidos 30 € de 1 a 2
Rechazada: No existe la cuenta 99
  Ana: 70 €
  Carlos: 80 €

La segunda transferencia le quitó 40 € a Ana antes de fallar, pero with conn (con rollback()) se los devolvió.

Test

Ponte a prueba

1. ¿Qué devuelve conn.execute("SELECT ...")?

Un cursor: las filas del resultado, que recorres con forUna lista de objetos ProductEl número de filas cambiadas

2. ¿Por qué es peligroso f"... WHERE name = '{name}'"?

Porque el texto del usuario pasa a formar parte del SQL y puede cambiar la consultaNo es peligroso si name es un strPorque es lento

3. Una transferencia son dos consultas dentro de with conn:. La primera sale bien, la segunda lanza una excepción. ¿Qué hace with conn?

Guarda la primera y deshace la segundaRepite la segunda consultaDeshace todos los cambios desde el inicio de la transacción, también el primero (rollback)

4. ¿Qué hace un ORM como SQLAlchemy?

Solo hace más rápidas las consultas SQLSustituye a la base de datosConvierte objetos en filas y al revés, sin que escribas SQL para cada cosa

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: Datos en archivos · Listas: append, remove y sort · Lambdas · dataclass, match y patrones
• Continúa en: Inyecciones: cuando los datos se vuelven una orden · Monolito, monolito modular, servicios · Transactional Outbox · Una transacción entre servicios (Saga)
• Ejercicios sobre el tema: Programas en el mundo real