Tables, requêtes et pourquoi une base de données
Les transactions, cotations, positions et données de référence sont des tables : des lignes aux mêmes colonnes. Une base de données relationnelle les stocke sur disque, laisse plusieurs programmes lire et écrire en même temps, les garde cohérentes si quelque chose échoue en cours de route, et répond à des questions écrites en SQL sans que vous chargiez tout en mémoire. sqlite3 est fourni avec Python et garde toute une base dans un seul fichier, ce qui suffit pour la recherche ; PostgreSQL et les serveurs similaires font de même en production. Les instructions de base sont CREATE TABLE, INSERT, SELECT ... WHERE ... ORDER BY ... LIMIT, UPDATE et 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)]Des paramètres, pas du collage de chaînes
Ne construisez jamais une requête en collant une valeur dans le texte : f"... WHERE symbol = '{name}'" permet à une valeur comme x' OR '1'='1 de réécrire la requête (injection SQL), et des guillemets dans une valeur légitime la cassent. Mettez un espace réservé ? à l'endroit de la valeur et passez les valeurs séparément ; le pilote les envoie comme des données qui ne peuvent jamais être lues comme du SQL, et la base peut réutiliser le plan de requête. Les espaces réservés ne remplacent que des valeurs. Les noms de tables et de colonnes ne peuvent pas être des paramètres : si un nom vient de l'extérieur, vérifiez-le avec un motif strict (lettres, chiffres, tiret bas) ou une liste de noms autorisés avant de le mettre dans le texte.
# 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 nameAgrégats, jointures et index
GROUP BY regroupe les lignes qui partagent une clé et calcule SUM, AVG, COUNT, MIN ou MAX pour chaque groupe : la quantité totale échangée par symbole, par exemple. Une JOIN combine deux tables sur une colonne commune ; une INNER JOIN ne garde que les lignes ayant un partenaire des deux côtés, et une LEFT JOIN garde toutes les lignes de gauche. Le faire dans la base est bien plus rapide qu'une boucle Python. Pour de grandes tables, ajoutez un index sur les colonnes de filtre ou de jointure (CREATE INDEX idx_trades_symbol ON trades(symbol)) : la base saute alors aux lignes correspondantes au lieu de parcourir toute la table, et EXPLAIN QUERY PLAN montre ce qu'elle a choisi. Enveloppez les écritures liées dans une transaction pour qu'elles aient toutes lieu ou aucune.
# 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 afterNOT NULL, CHECK (price > 0), UNIQUE), car une règle imposée par la base vaut pour tous les programmes qui y écrivent. Stockez les horodatages dans un seul format et fuseau convenus (UTC), et gardez l'argent en centimes entiers ou en type décimal, comme vu pour les flottants. Et sauvegardez : une base de trading qu'on ne sait pas restaurer est un passif.- Une base relationnelle stocke des tables sur disque et répond à des requêtes SQL ;
sqlite3garde toute une base dans un fichier. - Passez les valeurs en paramètres
?, jamais collées dans le texte (injection SQL) ; vérifiez les noms de tables et colonnes avec un motif strict. - Utilisez
GROUP BY,JOINet les index pour laisser la base travailler ; ajoutez des contraintes, gardez des horodatages UTC et l'argent exact, et sauvegardez.
À suivre : Parquet et Arrow, des formats en colonnes pour de grandes données analytiques.
Lab : ce qui est noté
- Ne jamais coller de valeurs :
insert_statement25 pts - Composer une requête avec
build_select25 pts - Agréger par clé avec
group_sum25 pts - Rapprocher deux tables avec
inner_join25 pts