Bases de datos SQL

Tablas, consultas y por qué una base de datos

Las operaciones, cotizaciones, posiciones y datos de referencia son tablas: filas con las mismas columnas. Una base de datos relacional las guarda en disco, deja que muchos programas lean y escriban a la vez, las mantiene coherentes si algo falla a mitad y responde a preguntas escritas en SQL sin que cargues todo en memoria. sqlite3 viene con Python y guarda toda una base en un archivo, lo que basta para investigar; PostgreSQL y servidores similares hacen lo mismo en producción. Las sentencias básicas son CREATE TABLE, INSERT, SELECT ... WHERE ... ORDER BY ... LIMIT, UPDATE y DELETE.

# A database in memory with sqlite3: create, insert, query
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE trades (symbol TEXT, price REAL, qty INTEGER)")
db.executemany("INSERT INTO trades (symbol, price, qty) VALUES (?, ?, ?)",
               [("AAPL", 184.25, 100), ("MSFT", 410.5, 50), ("AAPL", 184.5, 25)])

rows = db.execute("SELECT symbol, price FROM trades WHERE price > ? ORDER BY price DESC LIMIT 2", (184.3,)).fetchall()
print(rows)                                          # [('MSFT', 410.5), ('AAPL', 184.5)]

Parámetros, no pegar cadenas

Nunca construyas una consulta pegando un valor en el texto: f"... WHERE symbol = '{name}'" permite que un valor como x' OR '1'='1 reescriba la consulta (inyección SQL), y las comillas en un valor legítimo la rompen. Pon un marcador ? donde va el valor y pasa los valores aparte; el controlador los envía como datos que nunca pueden leerse como SQL, y la base puede reutilizar el plan de consulta. Los marcadores solo representan valores. Los nombres de tablas y columnas no pueden ser parámetros, así que si un nombre viene de fuera, compruébalo con un patrón estricto (letras, dígitos y guion bajo) o una lista de nombres permitidos antes de ponerlo en el texto.

# A pasted value rewrites the query; a placeholder keeps it data
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (name TEXT, secret TEXT)")
db.executemany("INSERT INTO users VALUES (?, ?)", [("alice", "a1"), ("bob", "b2")])

evil = "x' OR '1'='1"
pasted = db.execute(f"SELECT secret FROM users WHERE name = '{evil}'").fetchall()
safe = db.execute("SELECT secret FROM users WHERE name = ?", (evil,)).fetchall()
print(pasted)                                        # [('a1',), ('b2',)]  the injected condition matched every row
print(safe)                                          # []                  the same text is just an unknown name

Agregados, uniones e índices

GROUP BY agrupa las filas que comparten una clave y calcula SUM, AVG, COUNT, MIN o MAX para cada grupo: la cantidad total negociada por símbolo, por ejemplo. Un JOIN combina dos tablas por una columna común; un INNER JOIN conserva solo las filas con pareja en ambos lados y un LEFT JOIN conserva todas las de la izquierda. Hacerlo en la base es mucho más rápido que un bucle en Python. Para tablas grandes añade un índice en las columnas por las que filtras o unes (CREATE INDEX idx_trades_symbol ON trades(symbol)): la base salta a las filas coincidentes en vez de recorrer toda la tabla, y EXPLAIN QUERY PLAN muestra lo que eligió. Envuelve las escrituras relacionadas en una transacción para que ocurran todas o ninguna.

# GROUP BY and JOIN done by the database
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE trades (symbol TEXT, qty INTEGER)")
db.execute("CREATE TABLE sectors (symbol TEXT, sector TEXT)")
db.executemany("INSERT INTO trades VALUES (?, ?)", [("AAPL", 100), ("MSFT", 50), ("AAPL", 25), ("XOM", 10)])
db.executemany("INSERT INTO sectors VALUES (?, ?)", [("AAPL", "tech"), ("MSFT", "tech")])

print(db.execute("SELECT symbol, SUM(qty) FROM trades GROUP BY symbol ORDER BY symbol").fetchall())
# [('AAPL', 125), ('MSFT', 50), ('XOM', 10)]
print(db.execute("SELECT s.sector, SUM(t.qty) FROM trades t JOIN sectors s ON t.symbol = s.symbol GROUP BY s.sector").fetchall())
# [('tech', 175)]   XOM has no sector, so the inner join drops it

# An index turns a full scan into a lookup; the query plan shows which one the database uses
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE ticks (symbol TEXT, ts INTEGER, price REAL)")
db.executemany("INSERT INTO ticks VALUES (?, ?, ?)", [("S%d" % (i % 50), i, 100.0 + i % 7) for i in range(5000)])

query = "EXPLAIN QUERY PLAN SELECT price FROM ticks WHERE symbol = 'S7'"
before = " ".join(row[-1] for row in db.execute(query))
db.execute("CREATE INDEX idx_ticks_symbol ON ticks(symbol)")
after = " ".join(row[-1] for row in db.execute(query))
print("SCAN" in before, "INDEX" in after)            # True True   a full scan before the index, an index search after

• Una base relacional guarda tablas en disco y responde consultas SQL; sqlite3 guarda toda una base en un archivo.
• Pasa los valores como parámetros ?, nunca pegados en el texto (inyección SQL); comprueba los nombres de tablas y columnas con un patrón estricto.
• Usa GROUP BY, JOIN e índices para que la base haga el trabajo; añade restricciones, mantén marcas de tiempo UTC y dinero exacto, y haz copias de seguridad.

A continuación: Parquet y Arrow, formatos columnares para datos analíticos grandes.

Lab: qué se califica

• Nunca pegar valores: insert_statement25 pts
• Componer una consulta con build_select25 pts
• Agregar por clave con group_sum25 pts
• Emparejar dos tablas con inner_join25 pts