Tables, queries and why a database
Trades, quotes, positions and reference data are tables: rows with the same columns. A relational database stores them on disk, lets many programs read and write at once, keeps them consistent when something fails halfway, and answers questions written in SQL without you loading everything into memory. sqlite3 ships with Python and keeps a whole database in one file, which is enough for research; PostgreSQL and similar servers do the same for production. The basic statements are CREATE TABLE, INSERT, SELECT ... WHERE ... ORDER BY ... LIMIT, UPDATE and 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)]Parameters, not string pasting
Never build a query by pasting a value into the text: f"... WHERE symbol = '{name}'" lets a value like x' OR '1'='1 rewrite the query (SQL injection), and quotes inside a legitimate value break it. Put a ? placeholder where the value goes and pass the values separately; the driver sends them as data that can never be read as SQL, and the database can reuse the query plan. Placeholders stand for values only. Table and column names cannot be parameters, so if a name comes from outside, check it against a strict pattern (letters, digits and underscore) or a list of allowed names before putting it in the text.
# 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 nameAggregates, joins and indexes
GROUP BY collapses rows that share a key and computes SUM, AVG, COUNT, MIN or MAX for each group: total traded quantity per symbol, say. A JOIN combines two tables on a shared column; an INNER JOIN keeps only the rows that have a partner on both sides, and a LEFT JOIN keeps every left row. Doing this in the database is far faster than looping in Python. For large tables add an index on the columns you filter or join by (CREATE INDEX idx_trades_symbol ON trades(symbol)): the database then jumps to the matching rows instead of scanning the whole table, and EXPLAIN QUERY PLAN shows which it chose. Wrap related writes in a transaction so they all happen or none do.
# 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), because a rule the database enforces holds for every program that writes to it. Store timestamps in one agreed format and time zone (UTC), and keep money in integer cents or a decimal type, as you learned for floats. And back it up: a trading database that cannot be restored is a liability.- A relational database stores tables on disk and answers SQL queries;
sqlite3keeps a whole database in one file. - Pass values as
?parameters, never pasted into the text (SQL injection); check table and column names against a strict pattern. - Use
GROUP BY,JOINand indexes to let the database do the work; add constraints, keep UTC timestamps and exact money, and back up.
Next up: Parquet and Arrow, columnar formats for large analytical data.
Lab · what's graded
- Never paste values:
insert_statement25 pts - Compose a query with
build_select25 pts - Aggregate by key with
group_sum25 pts - Match two tables with
inner_join25 pts