SQLデータベース

テーブル、クエリ、そしてデータベースの理由

約定、気配、ポジション、参照データはテーブル、つまり同じ列を持つ行の集まりです。リレーショナルデータベースはそれらをディスクに保存し、多数のプログラムが同時に読み書きでき、途中で失敗しても整合性を保ち、すべてをメモリに読み込まなくても SQL で書いた問い合わせに答えます。sqlite3 は Python に付属し、データベース全体を1つのファイルに保持するので、リサーチには十分です。本番では PostgreSQL などのサーバーが同じ役割を果たします。基本の文は CREATE TABLE、INSERT、SELECT ... WHERE ... ORDER BY ... LIMIT、UPDATE、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)]

文字列の連結ではなくパラメータを

値をテキストに貼り付けてクエリを組み立ててはいけません。f"... WHERE symbol = '{name}'" では x' OR '1'='1 のような値でクエリが書き換えられ（SQL インジェクション）、正当な値に含まれる引用符でも壊れます。値の位置には ? のプレースホルダを置き、値は別に渡します。ドライバは値を SQL として読まれることのないデータとして送り、データベースはクエリプランを再利用できます。プレースホルダは値の代わりにしかなりません。テーブル名や列名はパラメータにできないので、外部から来る名前は、テキストに入れる前に厳格なパターン（英数字とアンダースコア）か許可リストで確認します。

# 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

集計、結合、インデックス

GROUP BY は同じキーを持つ行をまとめ、グループごとに SUM、AVG、COUNT、MIN、MAX を計算します。たとえば銘柄ごとの総約定数量です。JOIN は共通の列で2つのテーブルを結合します。INNER JOIN は両側に相手がある行だけを残し、LEFT JOIN は左の行をすべて残します。これをデータベース内で行うほうが、Python のループよりずっと速くなります。大きなテーブルでは、絞り込みや結合に使う列にインデックスを作ります（CREATE INDEX idx_trades_symbol ON trades(symbol)）。するとデータベースはテーブル全体を走査せず、一致する行へ直接ジャンプし、EXPLAIN QUERY PLAN で選択を確認できます。関連する書き込みはトランザクションで囲み、すべて成功するか、まったく行われないようにします。

# 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

• リレーショナルデータベースはテーブルをディスクに保存し SQL で答える。sqlite3 はデータベース全体を1ファイルに持つ
• 値は ? パラメータで渡し、テキストに貼り付けない（SQL インジェクション）。テーブル名・列名は厳格なパターンで確認する
• GROUP BY、JOIN、インデックスでデータベースに仕事をさせる。制約を付け、UTC のタイムスタンプと正確な金額を保ち、バックアップを取る

次は Parquet と Arrow です。大規模な分析データのための列指向フォーマットです。

ラボ：採点される内容

• 値を埋め込まない：insert_statement25点
• build_select でクエリを組み立てる25点
• group_sum でキーごとに集計する25点
• inner_join で2つの表を結合する25点