SQL-Datenbanken

Tabellen, Abfragen und warum eine Datenbank

Trades, Quotes, Positionen und Referenzdaten sind Tabellen: Zeilen mit denselben Spalten. Eine relationale Datenbank speichert sie auf der Platte, lässt viele Programme gleichzeitig lesen und schreiben, hält sie konsistent, wenn etwas mittendrin scheitert, und beantwortet in SQL formulierte Fragen, ohne dass Sie alles in den Speicher laden. sqlite3 gehört zu Python und hält eine ganze Datenbank in einer Datei, was für die Recherche reicht; PostgreSQL und ähnliche Server leisten dasselbe in der Produktion. Die Grundanweisungen sind CREATE TABLE, INSERT, SELECT ... WHERE ... ORDER BY ... LIMIT, UPDATE und 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)]

Parameter statt Textzusammenkleben

Bauen Sie nie eine Abfrage, indem Sie einen Wert in den Text kleben: f"... WHERE symbol = '{name}'" lässt einen Wert wie x' OR '1'='1 die Abfrage umschreiben (SQL-Injection), und Anführungszeichen in einem legitimen Wert zerstören sie. Setzen Sie einen Platzhalter ? an die Stelle des Werts und übergeben Sie die Werte getrennt; der Treiber sendet sie als Daten, die nie als SQL gelesen werden können, und die Datenbank kann den Abfrageplan wiederverwenden. Platzhalter stehen nur für Werte. Tabellen- und Spaltennamen können keine Parameter sein; kommt ein Name von außen, prüfen Sie ihn gegen ein strenges Muster (Buchstaben, Ziffern, Unterstrich) oder eine Liste erlaubter Namen, bevor Sie ihn in den Text setzen.

# 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

Aggregate, Joins und Indizes

GROUP BY fasst Zeilen mit gemeinsamem Schlüssel zusammen und berechnet SUM, AVG, COUNT, MIN oder MAX je Gruppe: etwa die gehandelte Gesamtmenge pro Symbol. Ein JOIN verbindet zwei Tabellen über eine gemeinsame Spalte; ein INNER JOIN behält nur Zeilen mit Partner auf beiden Seiten, ein LEFT JOIN behält alle linken Zeilen. Das in der Datenbank zu tun ist weit schneller als eine Python-Schleife. Bei großen Tabellen ergänzen Sie einen Index auf den Spalten, nach denen Sie filtern oder verbinden (CREATE INDEX idx_trades_symbol ON trades(symbol)): Die Datenbank springt dann zu den passenden Zeilen, statt die ganze Tabelle zu durchsuchen, und EXPLAIN QUERY PLAN zeigt ihre Wahl. Fassen Sie zusammengehörige Schreibvorgänge in einer Transaktion zusammen, damit alle oder keine stattfinden.

# 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

• Eine relationale Datenbank speichert Tabellen auf der Platte und beantwortet SQL-Abfragen; sqlite3 hält eine ganze Datenbank in einer Datei.
• Übergeben Sie Werte als ?-Parameter, nie in den Text geklebt (SQL-Injection); prüfen Sie Tabellen- und Spaltennamen gegen ein strenges Muster.
• Nutzen Sie GROUP BY, JOIN und Indizes, damit die Datenbank die Arbeit macht; ergänzen Sie Constraints, halten Sie UTC-Zeitstempel und exaktes Geld und sichern Sie.

Als Nächstes: Parquet und Arrow, spaltenorientierte Formate für große Analysedaten.

Lab: Was bewertet wird

• Nie Werte einkleben: insert_statement25 Pkt.
• Mit build_select eine Abfrage zusammensetzen25 Pkt.
• Mit group_sum nach Schlüssel aggregieren25 Pkt.
• Mit inner_join zwei Tabellen verbinden25 Pkt.