COTQuery Builder

COTQuery Builder

The COTQuery class provides a fluent interface for building SODA2 queries.

from cftc_cot import COTClient
client = COTClient()

df = (
    client.legacy()
    .market("Gold")
    .last_n_weeks(12)
    .select("report_date_as_yyyy_mm_dd", "noncomm_positions_long_all")
    .order_by_date(desc=True)
    .execute()
)

Methods

• .where(condition)
• .select(*columns)
• .order_by(column, desc=False)
• .limit(n)
• .offset(n)
• .order_by_date(desc=True)

Filtering

• .date_range(start, end) (YYYY-MM-DD)
• .date_after(date)
• .date_before(date)
• .last_n_weeks(n=52)
• .market(name, exact=False) (Case-insensitive)
• .markets_in(*names)
• .exchange(name, exact=False) — filter by exchange (the part after the final " - " in market_and_exchange_names). exact=True matches that trailing segment exactly; otherwise it is a substring match.

Dataset-Specific Helpers

• Legacy: .noncomm_long_gt(), .noncomm_short_gt(), .comm_long_gt(), .comm_short_gt()
• Disaggregated: .swap_dealers_long_gt(), .managed_money_long_gt(), .producer_merchant_short_gt()
• TFF: .dealer_long_gt(), .asset_manager_long_gt(), .leveraged_funds_long_gt()
• Universal: .long_positions_gt(), .short_positions_gt()

Execution

• .execute(): returns pd.DataFrame
• .count(): returns int
• .distinct_values(column): returns a list of the column's distinct values, honoring the current filters (used for market/exchange discovery within a window)
• .fetch_all_pages(): returns full pd.DataFrame
• .to_soda2(): returns query string

Reliability

COTQuery is usually created for you by COTClient, which passes in caching and the app token. When constructing it directly you can tune its resilience:

from cftc_cot.query import COTQuery

q = COTQuery("legacy", app_token="TOKEN", max_retries=3, backoff_base=1.0)

Parameter	Default	Description
cache	None	A COTCache instance for response caching.
cache_ttl	86400	Cache TTL in seconds.
max_retries	3	Attempts on transient API failures (HTTP 429/5xx, connection errors).
backoff_base	1.0	Base delay (seconds) for exponential backoff between retries.

• Retries: transient failures are retried with exponential backoff; once exhausted a COTConnectionError is raised. Genuine client errors (4xx other than 429) are not retried.
• Safe literals: values passed to .market(), .markets_in(), and the date filters are quote-escaped, so names containing apostrophes (e.g. "O'Brien") are handled safely.