database — Quantstrip Docs

Direct SQL

run_query(sql_str, params=())

The single entry point for arbitrary SQL. Every other method on this class is built on top of it, and it is the method the Database / SQL page calls with the editor's query text.

Returns a pandas.DataFrame for result-set queries (SELECT), or the affected row count (int) for INSERT/UPDATE/DELETE. Runs in autocommit mode; no explicit commit required.

run_query executes whatever SQL you pass it, with no read-only guard. Prefer the typed methods below for writes wherever one exists — they enforce the right columns and conflict handling for you.

Orders

next_order_id()

Generates a strictly increasing, time-based order ID. Thread-safe and process-safe. Call once per order placed.

Returns int.

insert_order(order_id, strategy_id, broker_id, account, symbol, side, order_type, total_quantity, contract_id=None, limit_price=None, stop_price=None, trail_amount=None, instrument_type=None, parent_order_id=None, client_reference=None, supersedes_order_id=None, broker_ack_time=None, metadata=None, external_order_id=None)

Records a canonical order. Returns order_id.

get_order(order_id=None, strategy_id=None, symbol=None)

Fetches order rows with flexible filtering by any combination of the three arguments.

Returns a pandas.DataFrame.

insert_order_status(order_id, status, filled_quantity=None, remaining_quantity=None, avg_fill_price=None, last_fill_price=None, external_order_id=None, client_reference=None, parent_order_id=None, reason_held=None, cap_price=None, metadata=None, event_time=None)

Appends a new order-status event. Append-only — it does not overwrite prior status rows.

Executions & commissions

insert_execution(exec_id, order_id, strategy_id, broker_id, symbol, instrument_type, contract_id, side, quantity, price, exec_time, exchange=None, liquidity_flag=None, order_type=None, cum_qty=None, avg_price=None, is_liquidation=None, external_order_id=None, external_exec_id=None, open_close_indicator=None, holding_period_start=None, cost_basis=None, proceeds=None, net_cash=None, isin=None, cusip=None, description=None, metadata=None)

Inserts a canonical execution/fill record from individual arguments.

insert_commission(exec_id, amount, currency, fee_type, realized_pnl=None, tax_amount=None, metadata=None)

Inserts a commission/fee record tied to an execution. Duplicate inserts for the same exec_id are silently ignored (ON CONFLICT DO NOTHING).

Positions & strategy events

insert_position_event(event_time, strategy_id, broker_id, exec_id, order_id, symbol, position, avg_price, trade_quantity, trade_price, event_type, contract_id=None, group_label=None, metadata=None)

Inserts a position snapshot resulting from a fill — the running position and average price after this trade, classified with an event_type (OPEN_LONG, PARTIAL_CLOSE, FLIP, etc.). Duplicate inserts for the same exec_id are silently ignored.

get_last_position_event(strategy_id, symbol)

The most recent position snapshot for a strategy/symbol, required to compute the next one. See the position-state-machine example on the IBKR template page.

Returns a dict, or None if the strategy has no history for that symbol yet.

get_last_strategy_event(strategy_id, symbol=None)

The most recent strategy_event row for a strategy (optionally scoped to a symbol) — this is what the Trade Operations Strategy Events tab and Add Event panel are built on.

Returns a dict, or None.

Reference data

get_brokers()

All brokers in the database.

Returns a pandas.DataFrame (broker_id, name, full_name).

get_strategies()

All strategies in the database.

Returns a pandas.DataFrame (strategy_id, name, description).

A strategy or broker referenced by insert_order/insert_execution must already exist in these reference tables — create them from the Trade Operations Static Data tab first.