Bancos de dados SQL

Tabelas, consultas e por que um banco de dados

Negócios, cotações, posições e dados de referência são tabelas: linhas com as mesmas colunas. Um banco de dados relacional os guarda em disco, deixa muitos programas lerem e escreverem ao mesmo tempo, mantém tudo consistente se algo falhar no meio e responde a perguntas escritas em SQL sem que você carregue tudo na memória. O sqlite3 vem com o Python e guarda um banco inteiro em um arquivo, o que basta para pesquisa; o PostgreSQL e servidores semelhantes fazem o mesmo em produção. As instruções básicas são CREATE TABLE, INSERT, SELECT ... WHERE ... ORDER BY ... LIMIT, UPDATE e 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, não colagem de strings

Nunca monte uma consulta colando um valor no texto: f"... WHERE symbol = '{name}'" deixa um valor como x' OR '1'='1 reescrever a consulta (injeção de SQL), e aspas dentro de um valor legítimo a quebram. Coloque um marcador ? onde vai o valor e passe os valores separadamente; o driver os envia como dados que nunca podem ser lidos como SQL, e o banco pode reutilizar o plano de consulta. Marcadores representam só valores. Nomes de tabelas e colunas não podem ser parâmetros, então se um nome vem de fora, verifique-o com um padrão estrito (letras, dígitos e sublinhado) ou uma lista de nomes permitidos antes de pô-lo no 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

Agregações, junções e índices

GROUP BY agrupa linhas que compartilham uma chave e calcula SUM, AVG, COUNT, MIN ou MAX para cada grupo: a quantidade total negociada por símbolo, por exemplo. Um JOIN combina duas tabelas por uma coluna comum; um INNER JOIN mantém só as linhas com par nos dois lados e um LEFT JOIN mantém todas as da esquerda. Fazer isso no banco é muito mais rápido que um laço em Python. Para tabelas grandes adicione um índice nas colunas pelas quais filtra ou junta (CREATE INDEX idx_trades_symbol ON trades(symbol)): o banco salta para as linhas correspondentes em vez de varrer a tabela inteira, e EXPLAIN QUERY PLAN mostra o que escolheu. Envolva escritas relacionadas numa transação para que todas ocorram ou nenhuma.

# 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

• Um banco relacional guarda tabelas em disco e responde a consultas SQL; o sqlite3 guarda um banco inteiro em um arquivo.
• Passe os valores como parâmetros ?, nunca colados no texto (injeção de SQL); verifique nomes de tabelas e colunas com um padrão estrito.
• Use GROUP BY, JOIN e índices para o banco fazer o trabalho; adicione restrições, mantenha carimbos UTC e dinheiro exato, e faça backup.

A seguir: Parquet e Arrow, formatos colunares para grandes dados analíticos.

Lab: o que é avaliado

• Nunca colar valores: insert_statement25 pts
• Compor uma consulta com build_select25 pts
• Agregar por chave com group_sum25 pts
• Casar duas tabelas com inner_join25 pts