-
Notifications
You must be signed in to change notification settings - Fork 65
Recording and analysis
PyLOB matches in memory and persists nothing by default. Attach a sink and the session becomes a SQLite database you can query afterwards: every event, every order, every trade, every balance movement and every commission.
This page is the record-then-inspect workflow end to end — what lands on disk, how to ask real research questions of it in SQL and pandas, and the two ways a careful person still loses data. Every query below was run against a session recorded by the script in Recording a session, and the output shown is that run's.
Contents
- Why record at all
- Attaching a sink
- Recording a session
- What lands on disk
- First look
- The trade tape
- Fill rates
- Order lifetimes
- Commission
- Per-trader PnL
- Taker and maker
- The closing ladder
- Reading the raw log
- The same questions in pandas
- Trusting the file
- Recovering a killed run
- The WAL sidecar trap
- Buffering
- What is not here
Sinkless is the default and the fast path: an engine with no sink builds no
event object at all. Attaching a SQLiteSink costs throughput and buys the
whole session back as history.
The trade-off is a per-run decision, not a per-project one. Run a parameter sweep sinkless — that is free rather than merely cheap — and attach a sink to the handful of runs you actually mean to inspect. See the README's Speed section for what recording costs.
from PyLOB import OrderBook
from PyLOB.sinks.sqlite import SQLiteSink
book = OrderBook(tick_size=0.01, sink=SQLiteSink("session.db"))
...
book.close()SQLiteSink lives in PyLOB.sinks.sqlite, not in PyLOB, so that
import PyLOB does not drag in sqlite3 for the majority of callers who never
attach one.
close() is not optional. It flushes the buffered tail and writes the
marker that says the session finished. Nothing is guaranteed on disk before
it. The sink is also a context manager, which is the safer form:
with SQLiteSink("session.db") as sink:
book = OrderBook(tick_size=0.01, sink=sink)
...
book.close()One database per session. seq is the log's primary key, so a sink pointed
at a file that already holds a session cannot write to it — the first flush
fails, and the whole batch is recorded as lost rather than half-merged into
somebody else's history. Use a fresh path per run.
The database every query on this page was run against:
"""record_session.py -- a small session, recorded, for the analytics guide."""
import random
from PyLOB import OrderBook
from PyLOB.sinks.sqlite import SQLiteSink
INSTRUMENT, CURRENCY = "FAKE", "USD"
TRADERS = range(1, 7)
def run(db_path, seed=7, n=400):
rng = random.Random(seed)
with SQLiteSink(db_path) as sink:
book = OrderBook(tick_size=0.01, sink=sink)
book.configure_instrument(INSTRUMENT, CURRENCY)
for tid in TRADERS:
book.configure_trader(
tid,
name="trader-%d" % tid,
commission_min=2.5,
commission_max_percnt=1.0,
commission_per_unit=0.01,
)
live = [] # order ids we might cancel or modify later
for _ in range(n):
tid = rng.choice(TRADERS)
roll = rng.random()
if roll < 0.10 and live:
idNum = rng.choice(live)
order = book.order(idNum)
if order is not None and order.resting:
book.cancelOrder(None, idNum)
live.remove(idNum)
continue
if roll < 0.18 and live:
idNum = rng.choice(live)
order = book.order(idNum)
if order is not None and order.resting:
if rng.random() < 0.5:
book.modifyOrder(idNum, dict(
side=order.side, qty=order.qty,
price=round(order.price + rng.choice((-0.05, 0.05)), 2)))
else:
# a pure quantity decrease -- keeps its place in the queue
book.modifyOrder(idNum, dict(
side=order.side,
qty=max(order.fulfilled + 1, order.qty - 1),
price=None))
continue
if roll < 0.28:
side = rng.choice(("bid", "ask"))
book.submit(tid, INSTRUMENT, side, "market", rng.randint(1, 8))
continue
side = rng.choice(("bid", "ask"))
mid = book.getLastPrice(INSTRUMENT) or 100.0
edge = rng.choice((0.01, 0.05, 0.10, 0.25, -0.05))
price = mid - edge if side == "bid" else mid + edge
order, _ = book.submit(
tid, INSTRUMENT, side, "limit", rng.randint(1, 10), round(price, 2))
if order.resting:
live.append(order.idNum)
book.close() # flushes the tail and writes the session_end marker
return db_path
if __name__ == "__main__":
import sys
run(sys.argv[1] if len(sys.argv) > 1 else "session.db")It is deterministic: same seed, same database, every time.
Two layers, written in the same transaction so they can never disagree.
event — the append-only log. One row per event: seq, kind,
timestamp, replayable, and the whole event as JSON in payload. This is
the source of truth. It is deliberately not normalised, so an event decodes
back field for field.
The projections — session, instrument, trader, orders, trade,
balance. These are a fold of the log: they add no information, but they turn
"reconstruct the state from 200,000 JSON rows" into a SELECT.
Two views — resting_order (what is still on the book, with the quantity
still available) and trader_commission (commission per trader per currency).
Two tables about the recording rather than the market — session_end
(written by close, and by nothing else) and event_loss (events the sink
accepted and could not write).
$ sqlite3 session.db .tables
balance instrument session trader
event orders session_end trader_commission
event_loss resting_order trade
The schema documents itself. Every table and column carries its comment inside
the CREATE statement, so this is the reference:
$ sqlite3 session.db .schema
A few column meanings that are worth knowing before you write your first query:
| Column | Meaning |
|---|---|
orders.status |
open, filled or cancelled. A market order with an unfilled remainder reads cancelled. |
orders.fulfilled, .value, .commission
|
Cumulative over the order's life, as of the last event to touch it. |
orders.currency |
The instrument's currency as configured when this order was accepted — not the instrument's current one. |
orders.accepted_ts, .last_ts
|
Engine-clock stamps. In a default session the clock advances by one per operation, so a difference is a count of operations, not seconds. |
trade.price |
The maker's limit, and the instrument's new last price. |
trade.*_commission_delta |
The increment charged for this trade. *_commission is the cumulative total. |
event.replayable |
events.is_replayable materialised at write time: configuration and caller commands are 1, engine consequences (fills, IOC cancels) are 0. |
SELECT (SELECT COUNT(*) FROM event) AS events,
(SELECT COUNT(*) FROM orders) AS orders,
(SELECT COUNT(*) FROM trade) AS trades,
(SELECT COUNT(*) FROM event_loss) AS losses,
(SELECT COUNT(*) FROM session_end) AS closed;events orders trades losses closed
554 326 163 0 1
losses = 0 and closed = 1 are the two you want. Anything else, go to
Trusting the file before you draw a conclusion.
SELECT s.tick_size, s.stream_version, i.symbol, i.currency, i.last_price
FROM session s, instrument i;tick_size stream_version symbol currency last_price
0.01 1 FAKE USD 99.9
And what the session was made of:
SELECT kind, COUNT(*) AS n FROM event GROUP BY kind ORDER BY n DESC;accepted 326
filled 163
cancelled 33
modified 24
trader_configured 6
instrument_configured 1
session_started 1
SELECT trade_id, timestamp, price, qty, taker_side, bid_tid, ask_tid
FROM trade ORDER BY seq LIMIT 5;trade_id timestamp price qty taker_side bid_tid ask_tid
1 8.0 99.95 3 ask 1 2
2 18.0 100.0 4 ask 5 3
3 18.0 100.0 4 ask 5 3
4 48.0 100.0 3 ask 5 4
5 48.0 100.0 5 ask 1 4
Trades 2 and 3 share a timestamp because one submission caused both: the engine clock advances per operation, not per trade.
SELECT COUNT(*) AS trades,
SUM(qty) AS volume,
SUM(price * qty) / SUM(qty) AS vwap,
MIN(price) AS low,
MAX(price) AS high
FROM trade;trades volume vwap low high
163 459 99.96762527233115 99.9 100.05
By order count, per trader, across every outcome:
SELECT tid,
COUNT(*) AS submitted,
SUM(status = 'filled') AS filled,
SUM(status = 'cancelled') AS cancelled,
SUM(status = 'open') AS still_resting
FROM orders GROUP BY tid ORDER BY tid;tid submitted filled cancelled still_resting
1 62 33 5 24
2 49 27 5 17
3 61 24 8 29
4 53 27 5 21
5 43 28 4 11
6 58 35 6 17
By quantity, which is usually the more honest measure, and restricted to limit orders — market orders would flatter every number, since they either fill or are cancelled immediately:
SELECT tid,
SUM(qty) AS qty_submitted,
SUM(fulfilled) AS qty_filled,
ROUND(1.0 * SUM(fulfilled) / SUM(qty), 3) AS fill_rate
FROM orders
WHERE order_type = 'limit'
GROUP BY tid ORDER BY fill_rate DESC;tid qty_submitted qty_filled fill_rate
5 171 104 0.608
6 275 160 0.582
1 292 157 0.538
4 224 114 0.509
2 215 97 0.451
3 322 105 0.326
Market orders deserve their own question, because "cancelled" means something different for them:
SELECT status, cancel_reason, COUNT(*) AS n,
SUM(qty) AS qty_asked, SUM(fulfilled) AS qty_got
FROM orders WHERE order_type = 'market'
GROUP BY status, cancel_reason;status cancel_reason n qty_asked qty_got
cancelled ioc_remainder 2 14 0
filled NULL 39 181 181
Two market orders in this session found an empty book and were cancelled in full. That is the immediate-or-cancel rule, not a failure.
SELECT status,
COUNT(*) AS n,
ROUND(AVG(last_ts - accepted_ts), 1) AS mean_life,
MIN(last_ts - accepted_ts) AS min_life,
MAX(last_ts - accepted_ts) AS max_life
FROM orders
WHERE order_type = 'limit'
GROUP BY status;status n mean_life min_life max_life
cancelled 31 67.8 1.0 275.0
filled 135 31.1 0.0 346.0
open 119 12.8 0.0 229.0
Remember what the unit is: engine-clock ticks, which in a default session
means operations elapsed, not seconds. A min_life of 0 is an order that
filled on arrival. For open orders last_ts - accepted_ts measures time
until the last event that touched them, not time on the book, since they are
still there.
Individual orders:
SELECT idNum, tid, side, price, qty,
accepted_ts, last_ts, last_ts - accepted_ts AS life
FROM orders
WHERE status = 'filled' AND order_type = 'limit'
ORDER BY life DESC LIMIT 5;idNum tid side price qty accepted_ts last_ts life
6 1 bid 99.9 7 7.0 353.0 346.0
34 5 bid 99.9 9 43.0 373.0 330.0
89 1 bid 99.9 9 108.0 379.0 271.0
48 4 bid 99.91 8 62.0 332.0 270.0
70 2 bid 99.92 1 87.0 317.0 230.0
The shipped view groups by trader and currency:
SELECT tid, currency, ROUND(commission, 4) AS commission
FROM trader_commission ORDER BY commission DESC;tid currency commission
6 USD 83.9957
1 USD 79.9966
5 USD 71.4982
4 USD 62.4973
2 USD 61.4953
3 USD 60.9993
It sums over orders, not trades, because the commission contract is a function of an order's cumulative fills rather than of each fill separately. Three independent routes to the same total, which is a useful thing to assert in a test:
SELECT (SELECT ROUND(SUM(commission), 6) FROM trader_commission) AS via_view,
(SELECT ROUND(SUM(commission), 6) FROM orders) AS via_orders,
(SELECT ROUND(SUM(bid_commission_delta + ask_commission_delta), 6)
FROM trade) AS via_trade_deltas;via_view via_orders via_trade_deltas
420.4824 420.4824 420.4824
Balances are recorded per (trader, symbol), where a symbol is an instrument
or a currency:
SELECT tid, symbol, ROUND(amount, 4) AS amount
FROM balance ORDER BY tid, symbol LIMIT 4;tid symbol amount
1 FAKE 16.0
1 USD -1679.7766
2 FAKE -4.0
2 USD 338.2447
The cash leg already has commission netted into it. Mark the inventory at the last trade price and you have PnL:
WITH mark AS (SELECT last_price AS px FROM instrument WHERE symbol = 'FAKE')
SELECT tid,
ROUND(SUM(CASE WHEN symbol = 'USD' THEN amount END), 2) AS cash,
ROUND(SUM(CASE WHEN symbol = 'FAKE' THEN amount END), 2) AS inventory,
ROUND(SUM(CASE WHEN symbol = 'USD' THEN amount
ELSE amount * (SELECT px FROM mark) END), 2) AS pnl
FROM balance GROUP BY tid ORDER BY pnl DESC;tid cash inventory pnl
3 4637.91 -47.0 -57.39
4 -1759.58 17.0 -61.28
2 338.24 -4.0 -61.36
5 -871.57 8.0 -72.37
1 -1679.78 16.0 -81.38
6 -1085.72 10.0 -86.72
Every trader lost money, which is what you would expect of six random traders paying commission to each other's counterparties. Separating the commission out shows why:
WITH mark AS (SELECT last_price AS px FROM instrument WHERE symbol = 'FAKE'),
net AS (
SELECT tid,
SUM(CASE WHEN symbol = 'USD' THEN amount
ELSE amount * (SELECT px FROM mark) END) AS pnl
FROM balance GROUP BY tid)
SELECT n.tid,
ROUND(n.pnl, 2) AS pnl_net,
ROUND(COALESCE(c.commission, 0), 2) AS commission,
ROUND(n.pnl + COALESCE(c.commission, 0), 2) AS pnl_before_commission
FROM net n LEFT JOIN trader_commission c ON c.tid = n.tid
ORDER BY pnl_net DESC;tid pnl_net commission pnl_before_commission
3 -57.39 61.0 3.61
4 -61.28 62.5 1.22
2 -61.36 61.5 0.14
5 -72.37 71.5 -0.87
1 -81.38 80.0 -1.38
6 -86.72 84.0 -2.72
Before costs the six are within a few dollars of flat, as random traders should be. Commission is the whole story.
A conservation check worth running on any session. Inventory is zero-sum — every share bought was sold by someone. Cash is not: it leaks by exactly the commission taken.
SELECT symbol, ROUND(SUM(amount), 6) AS total FROM balance GROUP BY symbol;
SELECT ROUND((SELECT SUM(amount) FROM balance WHERE symbol = 'USD'), 6) AS cash_total,
ROUND(-(SELECT SUM(commission) FROM trader_commission), 6) AS minus_commission;symbol total
FAKE 0.0
USD -420.4824
cash_total minus_commission
-420.4824 -420.4824
If inventory does not net to zero, or cash does not net to minus the commission, something is wrong with your analysis — or with the file, in which case see Trusting the file.
Every trade names both sides and says which one was the aggressor, so
liquidity provision is a UNION ALL away:
SELECT tid, SUM(taken) AS as_taker, SUM(made) AS as_maker
FROM (
SELECT bid_tid AS tid,
(taker_side = 'bid') AS taken, (taker_side = 'ask') AS made FROM trade
UNION ALL
SELECT ask_tid AS tid,
(taker_side = 'ask') AS taken, (taker_side = 'bid') AS made FROM trade
) GROUP BY tid ORDER BY tid;tid as_taker as_maker
1 29 40
2 25 14
3 28 21
4 16 30
5 22 31
6 43 27
resting_order is what was still on the book when the session ended, with the
quantity still available to trade:
SELECT side, price, SUM(available) AS volume, COUNT(*) AS orders
FROM resting_order
GROUP BY side, price
ORDER BY side, price DESC LIMIT 6;side price volume orders
ask 100.3 15 2
ask 100.27 3 1
ask 100.25 16 3
ask 100.24 11 2
ask 100.2 17 3
ask 100.19 24 5
SELECT (SELECT MAX(price) FROM resting_order WHERE side = 'bid') AS best_bid,
(SELECT MIN(price) FROM resting_order WHERE side = 'ask') AS best_ask;best_bid best_ask
99.9 99.95
This is the price ladder the engine has no depth() method for. In a
recording you get it in SQL.
The projections answer "what is the state". The log answers "what happened",
and SQLite's json_extract reads it directly.
Which orders needed the most fills:
SELECT idNum, COUNT(*) AS fills, SUM(qty) AS qty
FROM (SELECT bid_idNum AS idNum, qty FROM trade
UNION ALL
SELECT ask_idNum AS idNum, qty FROM trade)
GROUP BY idNum ORDER BY fills DESC, qty DESC LIMIT 3;idNum fills qty
34 5 9
88 5 7
4 4 10
And one of them, event by event:
SELECT seq, kind, timestamp, replayable,
COALESCE(json_extract(payload, '$.qty'), '') AS qty,
COALESCE(json_extract(payload, '$.price'), '') AS price
FROM event
WHERE json_extract(payload, '$.idNum') = 88
OR json_extract(payload, '$.bid_idNum') = 88
OR json_extract(payload, '$.ask_idNum') = 88
ORDER BY seq;seq kind timestamp replayable qty price
150 accepted 107.0 1 7
151 filled 107.0 0 1 100.02
152 filled 107.0 0 1 99.98
153 filled 107.0 0 1 99.97
154 filled 107.0 0 3 99.96
155 filled 107.0 0 1 99.95
That is a market ask for 7 sweeping five price levels in one operation — all
at timestamp 107, each fill at a different maker's limit, walking down the bid
side. accepted carries no price because a market order names none.
The priority rule, verified from the data. Modified records whether the
change cost the order its place:
SELECT json_extract(payload, '$.prev_price') AS prev_price,
json_extract(payload, '$.price') AS new_price,
json_extract(payload, '$.prev_qty') AS prev_qty,
json_extract(payload, '$.qty') AS new_qty,
json_extract(payload, '$.reprioritized') AS reprioritized
FROM event WHERE kind = 'modified' ORDER BY seq LIMIT 5;prev_price new_price prev_qty new_qty reprioritized
100.01 100.06 4 4 1
99.99 99.99 5 4 0
99.94 99.89 10 10 1
99.7 99.7 7 6 0
100.0 100.0 10 9 0
Price changed, reprioritized = 1. Quantity shrank, reprioritized = 0.
Why orders left the book:
SELECT json_extract(payload, '$.reason') AS reason, COUNT(*) AS n
FROM event WHERE kind = 'cancelled' GROUP BY reason;reason n
ioc_remainder 2
requested 31
And what fraction of the log is input rather than consequence:
SELECT replayable, kind, COUNT(*) AS n FROM event
GROUP BY replayable, kind ORDER BY replayable, n DESC;replayable kind n
0 filled 163
0 cancelled 2
1 accepted 326
1 cancelled 31
1 modified 24
1 trader_configured 6
1 session_started 1
1 instrument_configured 1
The 165 non-replayable rows are the 163 fills and the 2 IOC remainders — things the engine produced rather than things a caller asked for.
read_events decodes the log back into the event objects the engine emitted:
from PyLOB.sinks.sqlite import read_events
events = list(read_events("session.db"))
print(len(events), events[0])554 SessionStarted(seq=0, timestamp=0.0, tick_size=0.01, stream_version=1)
read_events(path, replayable_only=True) applies the replayable filter
using the library's own rule rather than a second copy of it. Round-tripping
is exact — the payload holds every field, so decode_event reconstructs an
event indistinguishable from the emitted one.
pandas is not a PyLOB dependency; sqlite3 alone does fine. But if you have
it:
import json
import sqlite3
import pandas as pd
conn = sqlite3.connect("session.db")
orders = pd.read_sql_query("SELECT * FROM orders", conn)
trades = pd.read_sql_query("SELECT * FROM trade", conn)
balance = pd.read_sql_query("SELECT * FROM balance", conn)
resting = pd.read_sql_query("SELECT * FROM resting_order", conn)Fill rate:
limit = orders[orders.order_type == "limit"]
fills = limit.groupby("tid")[["qty", "fulfilled"]].sum()
fills["fill_rate"] = fills.fulfilled / fills.qty
print(fills.sort_values("fill_rate", ascending=False).round(3)) qty fulfilled fill_rate
tid
5 171 104 0.608
6 275 160 0.582
1 292 157 0.538
4 224 114 0.509
2 215 97 0.451
3 322 105 0.326
Lifetimes:
life = limit.assign(life=limit.last_ts - limit.accepted_ts)
print(life.groupby("status").life.agg(["count", "mean", "median", "max"]).round(1)) count mean median max
status
cancelled 31 67.8 38.0 275.0
filled 135 31.1 9.0 346.0
open 119 12.8 0.0 229.0
PnL, and what commission cost:
trades["notional"] = trades.qty * trades.price
mark = trades.price.iloc[-1]
wide = balance.pivot(index="tid", columns="symbol", values="amount").fillna(0.0)
wide["pnl"] = wide["USD"] + wide["FAKE"] * mark
wide["commission"] = orders.groupby("tid").commission.sum()
wide["pnl_before_commission"] = wide.pnl + wide.commission
print(wide[["pnl", "commission", "pnl_before_commission"]]
.sort_values("pnl", ascending=False).round(2))symbol pnl commission pnl_before_commission
tid
3 -57.39 61.0 3.61
4 -61.28 62.5 1.22
2 -61.36 61.5 0.14
5 -72.37 71.5 -0.87
1 -81.38 80.0 -1.38
6 -86.72 84.0 -2.72
Gross flow per trader, and a cross-check against the recorded balances:
bought = trades.groupby("bid_tid").agg(bought=("qty", "sum"), spent=("notional", "sum"))
sold = trades.groupby("ask_tid").agg(sold=("qty", "sum"), received=("notional", "sum"))
flow = bought.rename_axis("tid").join(sold.rename_axis("tid"), how="outer").fillna(0.0)
flow["inventory"] = flow.bought - flow.sold
flow["cash"] = flow.received - flow.spent - orders.groupby("tid").commission.sum()
print(flow.round(2))
check = flow.join(wide[["FAKE", "USD"]])
print("inventory matches the balance table:",
bool(((check.inventory - check.FAKE).abs() < 1e-9).all()))
print("cash matches the balance table: ",
bool(((check.cash - check.USD).abs() < 1e-9).all())) bought spent sold received inventory cash
tid
1 104 10396.78 88 8797.00 16 -1679.78
2 58 5798.25 62 6197.99 -4 338.24
3 46 4598.40 93 9297.31 -47 4637.91
4 72 7196.79 55 5499.71 17 -1759.58
5 74 7397.42 66 6597.35 8 -871.57
6 105 10497.50 95 9495.78 10 -1085.72
inventory matches the balance table: True
cash matches the balance table: True
Taker and maker:
legs = pd.concat([
pd.DataFrame({"tid": trades.bid_tid,
"role": (trades.taker_side == "bid").map({True: "taker", False: "maker"})}),
pd.DataFrame({"tid": trades.ask_tid,
"role": (trades.taker_side == "ask").map({True: "taker", False: "maker"})}),
])
print(legs.value_counts().unstack(fill_value=0))role maker taker
tid
1 40 29
5 31 22
4 30 16
6 27 43
3 21 28
2 14 25
The raw log, and the priority rule across the whole session:
log = pd.read_sql_query(
"SELECT seq, kind, timestamp, replayable, payload FROM event ORDER BY seq", conn)
mods = pd.json_normalize(log.loc[log.kind == "modified", "payload"].map(json.loads))
mods["price_changed"] = mods.price != mods.prev_price
mods["qty_increased"] = mods.qty > mods.prev_qty
print(mods.groupby(["price_changed", "qty_increased"]).reprioritized.agg(["count", "all"])) count all
price_changed qty_increased
False False 16 False
True False 8 True
Sixteen pure quantity decreases, none reprioritized. Eight price changes, all reprioritized. The rule holds across every modify in the session.
A recording can be incomplete in ways that are not obvious from looking at it, so the library provides one function that decides:
from PyLOB.sinks.sqlite import check_log
check_log("session.db") # returns None, or raisesIt refuses a file for five reasons: a schema version it does not implement, any
event_loss row, a hole in seq, a stream version it does not implement, and
rows deleted since the session closed. A sixth condition — the session never
closed — raises IncompleteLogError, a subclass, because that one is suspect
rather than corrupt.
EventLogError something is wrong with the file
└── IncompleteLogError the file is a good prefix of a killed run
read_events calls check_log before yielding anything, so a strict read
cannot quietly run over a stream with a hole in it. Do not analyse a
recording without doing this. A session killed mid-run looks exactly like a
shorter session that finished.
A killed run is an ordinary thing to have. The events in the file are all genuinely good — every one of them was committed. What is unknown is only how many more there were meant to be.
Here is a session killed with SIGKILL after about a second and a half:
check_log("killed.db")IncompleteLogError: the log has no session_end row: the process that wrote it
was killed rather than closing it, so these 7168 event(s) ending at seq 7167
are a prefix of the session and not all of it. Whatever was still buffered went
with the process, and how much that was is not knowable from the file. The
events that are here were all committed: pass strict=False to read them
The marker is the tell: close() writes a session_end row as its last act,
and nothing else ever writes one. A crash cannot forge it.
To read the prefix deliberately:
try:
check_log(path)
except IncompleteLogError:
pass # a killed run; the prefix is good
events = read_events(path, strict=False)read 7168 events with strict=False
seq runs 0..7167 contiguous: True
strict=False logs a warning rather than raising, and gives you exactly the
events that were recorded. The projections are consistent with them, because
the log and the projections are written in the same transaction.
The one thing to keep in mind: the buffer takes the tail with it. With the
default buffer_size=512, a killed run loses up to 511 events that the engine
had emitted and the sink had not yet written. A short run can lose
everything — a session killed after a few hundred events, with nothing ever
flushed, leaves a file with zero events in it. If you expect to be killed, use
a smaller buffer.
SQLiteSink opens the database in WAL mode, which means a live or killed
session is three files, not one:
session.db the main database
session.db-wal committed data not yet folded into it
session.db-shm shared-memory index for the above
Copying, moving or archiving only the .db loses whatever is in the -wal.
This is the one verified way a careful researcher silently loses data, and it
has two faces — both reproduced below on real killed runs.
Face one: it looks like a schema error. If the session died early enough
that SQLite had not yet checkpointed anything, even the schema is still in the
-wal, and the lone .db is an almost-empty file:
killed process left: early.db 4096 bytes
early.db-wal 1371992 bytes
early.db-shm 32768 bytes
$ cp early.db somewhere-else.db # the mistake
check_log("somewhere-else.db")
EventLogError: database schema version 0 is not this module's version 3
That message sends you looking for a version-compatibility problem you do not have. The original, sidecars intact, reads perfectly well and reports the truth — an incomplete log of 136 events.
Face two, which is worse: it looks fine. If the session ran long enough that SQLite auto-checkpointed some of the WAL into the main file, the lone copy contains most of the data, is internally consistent, has no gaps, and reports exactly what a genuine killed run reports:
the full three-file set: 1600 events, 532 trades
the lone .db copy: 1288 events
check_log on the lone copy:
IncompleteLogError: the log has no session_end row: ... these 1288 event(s)
ending at seq 1287 are a prefix of the session ...
312 events gone, seq contiguous from 0, no event_loss row, and a message
indistinguishable from the one the intact file gives. Nothing in the file can
tell you those events ever existed. You would analyse a shorter session
believing it whole.
So:
-
Copy all three files together, or none.
cp session.db*rather thancp session.db. -
Or checkpoint first, which folds the WAL into the main file and leaves one file that is safe to copy alone:
conn = sqlite3.connect("session.db") conn.execute("PRAGMA wal_checkpoint(TRUNCATE)") conn.close()
-
Or use SQLite's own backup API, which handles it in one step and is the most robust of the three:
src = sqlite3.connect("session.db") dst = sqlite3.connect("archive.db") with dst: src.backup(dst) dst.close(); src.close()
One further subtlety worth knowing: opening a WAL database recovers the
sidecar into it. So a file you have already read with check_log or
read_events may be safe to copy alone when it would not have been a moment
earlier. Do not rely on that — it depends on whether anything opened the file,
which is not something you want your archive to depend on.
A session that closed cleanly has already checkpointed, so a .db from a
finished run generally travels alone safely. The trap is specific to sessions
that were killed, or that are still running.
SQLiteSink(path, buffer_size=N) sets how many events accumulate before a
write. It is a performance knob and nothing else: a sink with
buffer_size=1 and one with buffer_size=100000 produce byte-identical
databases. What it changes is how much a killed process loses, and how much
time the matching thread spends writing.
If a batch fails to write, the sink does not drop it whole: it rolls back and
re-attempts the events one at a time, then records whatever still failed in
event_loss — one row per contiguous run of missing seq, with the error that
caused it. So a single poison event costs one event rather than the batch
around it, and check_log refuses the file afterwards. An empty event_loss
is the only state that means nothing was lost.
Replay is not shipped. The event stream is designed to replay — that is
what is_replayable, STREAM_VERSION and the configuration events at the head
of every stream are for, and the design is documented in PyLOB/events.py —
but there is no replay() function in the library today. It exists in a change
proposal, not in the package you installed. If you write one yourself, read the
"Replay" section of the PyLOB.events module docstring first: the rule that
Filled and IOC-remainder Cancelled events are engine output and must not
be re-issued is the one people get wrong.
Sessions are not self-describing about their parameters. The recording carries the tick size, the instrument, the traders and their schedules — but nothing about your seed, your episode number or your strategy configuration. If you are recording a sweep, put those in the filename or in a table of your own.
Durability is WAL plus synchronous = NORMAL. A committed transaction
survives the process dying, which is the point. It does not promise to
survive an OS crash or power loss, either of which may take the most recent
commits. That is the right trade for analytics data the engine never reads
back, but it is not the same claim as "durable". WAL is also requested rather
than guaranteed — some filesystems refuse it — so the sink checks what it
actually got and exposes it as sink.journal_mode.