Look-ups over the mapped instrument tables.
Three questions can be asked, and between them they close the loop from an instrument identity to a broker's own raw row and back:
broker_tokensgoes from an identity to every broker's token for it, which is what placing an order or requesting a quote needs.identitygoes the other way, from a broker's tokens back to the unified identities, which is what reading a position or holding back from a broker needs.raw_rowgoes from a broker token to that broker's full raw row, which is the escape hatch for anything the mapped tables do not carry.
The third exists because a broker's own quote endpoint sometimes needs an identifier the mapped tables have no column for. Because broker_token is each broker's own join key into its raw table, that row is always one lookup away.
Placing an order is not such a case. The broker_symbol column is a display name for several brokers rather than a tradeable ticker, so unified.broker_mappings carries order_symbol beside it, filled from each broker's own tradeable column for the six brokers that order by symbol and left empty for the four that order by token alone. An order therefore reads a column rather than reconstructing a ticker from a raw row.
Two further look-ups exist for the case where a broker reports a position without a token at all. identity_by_isin identifies an instrument from its ISIN, which is the only identifier a Groww holding carries, and identity_by_symbol identifies one from its ticker.
Every method takes an as_of_date and uses the latest mapping on or before it, so a lookup for a past date sees what was mapped then rather than what is mapped now.
A lookup that lands on the current mapping date is answered through the MappingCache the resolver holds, which keeps it out of the database; one for an earlier date goes straight to Postgres and caches nothing.
The resolver is handed a cache rather than building one. The memory tier only pays for itself when a process has exactly one of it, so a resolver that made its own would give a process two sets of dictionaries, double the memory and halve the hit rate.
Classes:
| Name | Description |
|---|---|
MappingResolver |
Every look-up over the mapped instrument tables, cached where the date allows it and read from Postgres where it does not. |
Functions:
| Name | Description |
|---|---|
exchange_order |
The sort key that puts a preferred exchange's identity first among an ISIN's answers. |
Every look-up over the mapped instrument tables, cached where the date allows it and read from Postgres where it does not.
Attributes:
| Name | Type | Description |
|---|---|---|
mapping_cache |
MappingCache
|
The cache a request for the current date is answered from. |
engine |
Engine
|
The SQLAlchemy engine over TimescaleDB. |
Build the resolver around a cache.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
mapping_cache
|
MappingCache | None
|
The cache to answer current-date requests from. None builds one, which is right for a command line tool and wrong for anything sharing a process with another resolver. |
None
|
Returns:
| Type | Description |
|---|---|
None
|
This function returns nothing. |
Example programs
"""Finds every broker's token for INFY on today's mapping and on an earlier one, and turns a token back into an identity.
A `MappingResolver` is handed a mapping cache rather than building one, and takes its database engine from that cache. When the date asked about falls on the cache's current mapping date, the cache supplies the date and the identities, and only the broker rows are read from the database. An earlier date is answered from the database alone, using the latest mapping on or before it.
Both the cache and the engine here are small stand-ins. The cache says the current mapping date is 2026-09-28 and knows one Zerodha token. The engine answers the resolver's queries from rows held in memory: INFY was mapped at three brokers on 2026-09-28 and at two on 2026-08-14. Each stand-in counts what it is asked, so the output shows which answers came from the cache and which from the database. No Redis or PostgreSQL is needed.
Notice that asking about 2026-08-20 lands on the 2026-08-14 mapping, because nothing was mapped between those dates, and that the identity for today comes from the cache without a database query.
Run it from the project root:
python examples/stock_brokers/instruments/mapping/utilities/resolution/MappingResolver/example_1_tokens_for_an_instrument.py
"""
import datetime
import decimal
import types
from stock_brokers.instruments.mapping.base import instrument_id
from stock_brokers.instruments.mapping.utilities.resolution import (
MappingResolver,
)
class StandInResult:
"""A stand-in for a SQLAlchemy result holding fixed rows.
Attributes:
rows (list): The rows.
value (object): The single value `scalar` returns.
"""
def __init__(self, rows, value=None):
"""Holds the rows and the scalar value.
Args:
rows (list): The rows.
value (object): The single value `scalar` returns.
Returns:
None: This method returns nothing.
"""
self.rows = rows
self.value = value
def all(self):
"""Every row.
Returns:
list: The rows.
"""
return self.rows
def one(self):
"""The only row.
Returns:
types.SimpleNamespace: The first row.
"""
return self.rows[0]
def scalar(self):
"""The single value of a one-column answer.
Returns:
object: The value.
"""
return self.value
class StandInEngine:
"""A stand-in engine answering the resolver's queries about one instrument from rows in memory.
Attributes:
broker_rows (dict): Mapping date to the instrument's broker rows on that date.
identity_rows (list): The rows a past-date identity query returns.
queries (int): How many queries were run.
"""
def __init__(self, broker_rows, identity_rows):
"""Holds the rows.
Args:
broker_rows (dict): Mapping date to the instrument's broker rows on that date.
identity_rows (list): The rows a past-date identity query returns.
Returns:
None: This method returns nothing.
"""
self.broker_rows = broker_rows
self.identity_rows = identity_rows
self.queries = 0
def connect(self):
"""Opens a connection.
Returns:
StandInEngine: This engine, which also acts as its own connection.
"""
return self
def __enter__(self):
"""Enters the connection block.
Returns:
StandInEngine: This engine.
"""
return self
def __exit__(self, error_type, error, traceback):
"""Leaves the connection block.
Args:
error_type (type | None): The exception type raised inside the block, if any.
error (BaseException | None): The exception raised inside the block, if any.
traceback (types.TracebackType | None): The traceback, if any.
Returns:
bool: False, so an exception is never swallowed.
"""
return False
def latest_on_or_before(self, as_of_date):
"""The latest mapping date on or before a date.
Args:
as_of_date (datetime.date): The date asked about.
Returns:
datetime.date | None: The date, or None when nothing was mapped by then.
"""
latest = None
for mapping_date in self.broker_rows:
if mapping_date <= as_of_date:
if latest is None or mapping_date > latest:
latest = mapping_date
return latest
def execute(self, statement, parameters):
"""Answers one query from the rows in memory.
Args:
statement (sqlalchemy.sql.elements.TextClause): The query.
parameters (dict): The query's parameters.
Returns:
StandInResult: The answer.
"""
self.queries += 1
sql = str(statement)
if 'max(mapping_date) AS mapping_date' in sql:
latest = self.latest_on_or_before(parameters['as_of_date'])
return StandInResult([
types.SimpleNamespace(
mapping_date=latest,
),
])
if 'max(mapping_date)' in sql:
return StandInResult([], self.latest_on_or_before(parameters['as_of_date']))
if sql.startswith('SELECT broker, broker_token'):
return StandInResult(self.broker_rows.get(parameters['mapping_date'], []))
return StandInResult(self.identity_rows)
class StandInMappingCache:
"""A stand-in for `MappingCache` that knows today's mapping date and one token's identity.
Attributes:
engine (StandInEngine): The engine the resolver borrows.
current_date (datetime.date): The current mapping date.
identities (dict): Broker name to a dict of token to identity.
lookups (int): How many identity look-ups were answered.
"""
def __init__(self, engine, current_date, identities):
"""Holds the engine, the date and the identities.
Args:
engine (StandInEngine): The engine the resolver borrows.
current_date (datetime.date): The current mapping date.
identities (dict): Broker name to a dict of token to identity.
Returns:
None: This method returns nothing.
"""
self.engine = engine
self.current_date = current_date
self.identities = identities
self.lookups = 0
def resolves_to_current_date(self, as_of_date):
"""The current mapping date when a request for this date lands on it.
Args:
as_of_date (datetime.date): The date asked about.
Returns:
datetime.date | None: The current mapping date, or None for an earlier date.
"""
if as_of_date >= self.current_date:
return self.current_date
return None
def identities_for_tokens(self, broker, broker_tokens, as_of_date, segments):
"""The identities of a broker's tokens on the current date.
Args:
broker (str): The broker name.
broker_tokens (list): The tokens, as text.
as_of_date (datetime.date): The date asked about.
segments (list | None): The segments to restrict to, which this stand-in ignores.
Returns:
dict: Token to identity, for the tokens it knows.
"""
self.lookups += 1
found = {}
known = self.identities.get(broker, {})
for broker_token in broker_tokens:
if broker_token in known:
found[broker_token] = known[broker_token]
return found
class TokensForAnInstrumentExample:
"""Looks INFY up on two dates and resolves a Zerodha token on two dates.
Attributes:
infy_id (str): INFY's instrument id on NSE.
engine (StandInEngine): The stand-in engine.
mapping_cache (StandInMappingCache): The stand-in cache.
resolver (MappingResolver): The resolver being shown.
"""
def __init__(self):
"""Builds the stand-ins and the resolver around them.
Returns:
None: This method returns nothing.
"""
self.infy_id = instrument_id(
'nse',
'nse_equities',
'security',
{
'symbol': 'INFY',
},
)
today = datetime.date(2026, 9, 28)
earlier = datetime.date(2026, 8, 14)
self.engine = StandInEngine(
{
today: [
self.broker_row('dhan', '1594', 'INFY', None),
self.broker_row('fyers', '10100000001594', 'NSE:INFY-EQ', 'NSE:INFY-EQ'),
self.broker_row('zerodha', '408065', 'INFY', 'INFY'),
],
earlier: [
self.broker_row('dhan', '1594', 'INFY', None),
self.broker_row('zerodha', '408065', 'INFY', 'INFY'),
],
},
[
types.SimpleNamespace(
broker_token='408065',
mapping_date=earlier,
instrument_id=self.infy_id,
exchange='nse',
segment='nse_equities',
shape='security',
symbol='INFY',
underlying_symbol=None,
expiry_date=None,
strike_price=None,
option_type=None,
),
],
)
self.mapping_cache = StandInMappingCache(
self.engine,
today,
{
'zerodha': {
'408065': {
'instrument_id': self.infy_id,
'exchange': 'nse',
'segment': 'nse_equities',
'symbol': 'INFY',
'mapping_date': today,
},
},
},
)
self.resolver = MappingResolver(self.mapping_cache)
def broker_row(self, broker, broker_token, broker_symbol, order_symbol):
"""Builds one broker mapping row with a lot of 1 and a tick of 0.05.
Args:
broker (str): The broker name.
broker_token (str): The broker's token.
broker_symbol (str): The broker's display symbol.
order_symbol (str | None): The symbol an order is sent with, or None for a broker that orders by token.
Returns:
types.SimpleNamespace: The row.
"""
return types.SimpleNamespace(
broker=broker,
broker_token=broker_token,
broker_symbol=broker_symbol,
order_symbol=order_symbol,
lot_size=decimal.Decimal('1'),
tick_size=decimal.Decimal('0.05'),
)
def print_tokens(self, label, as_of_date):
"""Looks INFY up for one date and prints the answer and the queries it cost.
Args:
label (str): What the look-up is.
as_of_date (datetime.date): The date asked about.
Returns:
None: This method returns nothing.
"""
queries_before = self.engine.queries
mapping_date, rows = self.resolver.broker_tokens(
'nse',
'nse_equities',
'security',
{
'symbol': 'INFY',
},
as_of_date,
)
print(f'{label}: mapping of {mapping_date}, {self.engine.queries - queries_before} database queries')
for row in rows:
print(f' {row["broker"]}: token {row["broker_token"]}, order symbol {row["order_symbol"]}, lot {row["lot_size"]}, tick {row["tick_size"]}')
def run(self):
"""Prints the mapping dates, the broker tokens and the identities.
Returns:
None: This method returns nothing.
"""
print(f'INFY instrument id: {self.infy_id}')
print(f'2026-09-30 resolves to {self.resolver.mapping_date_for(datetime.date(2026, 9, 30))}')
print(f'2026-08-20 resolves to {self.resolver.mapping_date_for(datetime.date(2026, 8, 20))}')
self.print_tokens('Today', datetime.date(2026, 9, 30))
self.print_tokens('On 2026-08-20', datetime.date(2026, 8, 20))
rows = self.resolver.broker_rows_on_date(self.infy_id, datetime.date(2026, 8, 14))
print(f'Brokers mapped on 2026-08-14 exactly: {len(rows)}')
queries_before = self.engine.queries
today_identity = self.resolver.identity('zerodha', [408065], datetime.date(2026, 9, 30))
print(f'Zerodha 408065 today: {today_identity["408065"]["symbol"]}, cache look-ups {self.mapping_cache.lookups}, database queries {self.engine.queries - queries_before}')
earlier_identity = self.resolver.identity('zerodha', ['408065'], datetime.date(2026, 8, 20))
print(f'Zerodha 408065 on 2026-08-20: {earlier_identity["408065"]["symbol"]} from the mapping of {earlier_identity["408065"]["mapping_date"]}')
print(f'No tokens asked for: {self.resolver.identity("zerodha", [], datetime.date(2026, 9, 30))}')
if __name__ == '__main__':
TokensForAnInstrumentExample().run()
It prints:
INFY instrument id: f310e1b0-e082-5b16-808a-25f741abb9de
2026-09-30 resolves to 2026-09-28
2026-08-20 resolves to 2026-08-14
Today: mapping of 2026-09-28, 1 database queries
dhan: token 1594, order symbol None, lot 1, tick 0.05
fyers: token 10100000001594, order symbol NSE:INFY-EQ, lot 1, tick 0.05
zerodha: token 408065, order symbol INFY, lot 1, tick 0.05
On 2026-08-20: mapping of 2026-08-14, 2 database queries
dhan: token 1594, order symbol None, lot 1, tick 0.05
zerodha: token 408065, order symbol INFY, lot 1, tick 0.05
Brokers mapped on 2026-08-14 exactly: 2
Zerodha 408065 today: INFY, cache look-ups 1, database queries 0
Zerodha 408065 on 2026-08-20: INFY from the mapping of 2026-08-14
No tokens asked for: {}
"""Identifies holdings that arrive without a broker token, by ISIN and by ticker, and lists every instrument a stored token has pointed at.
A Groww holding carries only an ISIN and a trading symbol. `identity_by_isin` looks the ISIN up in the raw files of the seven brokers that publish one, takes each broker's own token from there, and resolves that token like any other. A dual-listed share has an NSE and a BSE identity, so the answer is a list, ordered here with NSE first because that is the preferred exchange; the same instrument found through a second broker is not repeated. `identity_by_symbol` searches the master table by ticker within given segments and never picks a winner. `identity_spans` lists, for a price series stored under a broker token, every instrument that token has meant over all mapping dates; asking it to match on a column other than `broker_token` or `order_symbol` raises `ValueError`.
The mapping cache and the engine are small stand-ins answering from rows in memory, so no Redis or PostgreSQL is needed. The rows are made up but use real identifiers: INFY's ISIN INE009A01021, Dhan's NSE token 1594 and BSE token 500209, and Fyers' token for NSE INFY.
Run it from the project root:
python examples/stock_brokers/instruments/mapping/utilities/resolution/MappingResolver/example_2_holdings_without_a_token.py
"""
import datetime
import types
from stock_brokers.instruments.mapping.base import instrument_id
from stock_brokers.instruments.mapping.utilities.resolution import (
MappingResolver,
)
class StandInResult:
"""A stand-in for a SQLAlchemy result holding fixed rows.
Attributes:
rows (list): The rows.
value (object): The single value `scalar` returns.
"""
def __init__(self, rows, value=None):
"""Holds the rows and the scalar value.
Args:
rows (list): The rows.
value (object): The single value `scalar` returns.
Returns:
None: This method returns nothing.
"""
self.rows = rows
self.value = value
def all(self):
"""Every row.
Returns:
list: The rows.
"""
return self.rows
def one(self):
"""The only row.
Returns:
types.SimpleNamespace: The first row.
"""
return self.rows[0]
def scalar(self):
"""The single value of a one-column answer.
Returns:
object: The value.
"""
return self.value
class StandInEngine:
"""A stand-in engine answering the ISIN, ticker and span queries from rows in memory.
Attributes:
raw_isin_rows (dict): Broker name to the rows its raw file gives for the ISIN query.
symbol_rows (list): The rows the ticker query returns.
span_rows (list): The rows the span query returns.
brokers_searched (list): The raw files the ISIN look-up read, in order.
"""
def __init__(self, raw_isin_rows, symbol_rows, span_rows):
"""Holds the rows.
Args:
raw_isin_rows (dict): Broker name to the rows its raw file gives for the ISIN query.
symbol_rows (list): The rows the ticker query returns.
span_rows (list): The rows the span query returns.
Returns:
None: This method returns nothing.
"""
self.raw_isin_rows = raw_isin_rows
self.symbol_rows = symbol_rows
self.span_rows = span_rows
self.brokers_searched = []
def connect(self):
"""Opens a connection.
Returns:
StandInEngine: This engine, which also acts as its own connection.
"""
return self
def __enter__(self):
"""Enters the connection block.
Returns:
StandInEngine: This engine.
"""
return self
def __exit__(self, error_type, error, traceback):
"""Leaves the connection block.
Args:
error_type (type | None): The exception type raised inside the block, if any.
error (BaseException | None): The exception raised inside the block, if any.
traceback (types.TracebackType | None): The traceback, if any.
Returns:
bool: False, so an exception is never swallowed.
"""
return False
def execute(self, statement, parameters):
"""Answers one query from the rows in memory.
Args:
statement (sqlalchemy.sql.elements.TextClause): The query.
parameters (dict): The query's parameters.
Returns:
StandInResult: The answer.
"""
sql = str(statement)
if '.instruments WHERE download_date' in sql:
broker = sql.split('FROM ')[1].split('.instruments')[0]
self.brokers_searched.append(broker)
return StandInResult(self.raw_isin_rows.get(broker, []))
if 'first_mapping_date' in sql:
return StandInResult(self.span_rows)
return StandInResult(self.symbol_rows)
class StandInMappingCache:
"""A stand-in for `MappingCache` that knows today's mapping date and a few tokens' identities.
Attributes:
engine (StandInEngine): The engine the resolver borrows.
current_date (datetime.date): The current mapping date.
identities (dict): Broker name to a dict of token to identity.
"""
def __init__(self, engine, current_date, identities):
"""Holds the engine, the date and the identities.
Args:
engine (StandInEngine): The engine the resolver borrows.
current_date (datetime.date): The current mapping date.
identities (dict): Broker name to a dict of token to identity.
Returns:
None: This method returns nothing.
"""
self.engine = engine
self.current_date = current_date
self.identities = identities
def resolves_to_current_date(self, as_of_date):
"""The current mapping date when a request for this date lands on it.
Args:
as_of_date (datetime.date): The date asked about.
Returns:
datetime.date | None: The current mapping date, or None for an earlier date.
"""
if as_of_date >= self.current_date:
return self.current_date
return None
def identities_for_tokens(self, broker, broker_tokens, as_of_date, segments):
"""The identities of a broker's tokens on the current date.
Args:
broker (str): The broker name.
broker_tokens (list): The tokens, as text.
as_of_date (datetime.date): The date asked about.
segments (list | None): The segments to restrict to, which this stand-in ignores.
Returns:
dict: Token to identity, for the tokens it knows.
"""
found = {}
known = self.identities.get(broker, {})
for broker_token in broker_tokens:
if broker_token in known:
found[broker_token] = known[broker_token]
return found
class HoldingsWithoutATokenExample:
"""Resolves an ISIN and two tickers, and lists a token's spans.
Attributes:
engine (StandInEngine): The stand-in engine.
resolver (MappingResolver): The resolver being shown.
today (datetime.date): The current mapping date.
"""
def __init__(self):
"""Builds the stand-ins and the resolver around them.
Returns:
None: This method returns nothing.
"""
self.today = datetime.date(2026, 9, 28)
nse_infy = self.identity('nse', 'INFY')
bse_infy = self.identity('bse', 'INFY')
nse_tcs = self.identity('nse', 'TCS')
self.engine = StandInEngine(
{
'dhan': [
self.raw_row('1594'),
self.raw_row('500209'),
],
'fyers': [
self.raw_row('10100000001594'),
],
},
[
self.master_row(nse_infy),
self.master_row(nse_tcs),
],
[
self.span_row(nse_infy, datetime.date(2025, 1, 2), self.today, 431),
],
)
mapping_cache = StandInMappingCache(
self.engine,
self.today,
{
'dhan': {
'1594': nse_infy,
'500209': bse_infy,
},
'fyers': {
'10100000001594': nse_infy,
},
},
)
self.resolver = MappingResolver(mapping_cache)
def identity(self, exchange, symbol):
"""Builds the identity of one listed share on today's mapping.
Args:
exchange (str): The exchange, "nse" or "bse".
symbol (str): The share's symbol.
Returns:
dict: The identity.
"""
segment = f'{exchange}_equities'
return {
'instrument_id': instrument_id(
exchange,
segment,
'security',
{
'symbol': symbol,
},
),
'exchange': exchange,
'segment': segment,
'shape': 'security',
'symbol': symbol,
'underlying_symbol': None,
'expiry_date': None,
'strike_price': None,
'option_type': None,
'mapping_date': self.today,
}
def raw_row(self, broker_token):
"""Builds one raw-file row carrying INFY's ISIN.
Args:
broker_token (str): The broker's token in that row.
Returns:
types.SimpleNamespace: The row.
"""
return types.SimpleNamespace(
broker_token=broker_token,
isin_code='INE009A01021',
)
def master_row(self, identity):
"""Builds one master-table row from an identity.
Args:
identity (dict): The identity.
Returns:
types.SimpleNamespace: The row.
"""
return types.SimpleNamespace(
instrument_id=identity['instrument_id'],
exchange=identity['exchange'],
segment=identity['segment'],
shape=identity['shape'],
symbol=identity['symbol'],
underlying_symbol=None,
expiry_date=None,
strike_price=None,
option_type=None,
)
def span_row(self, identity, first_mapping_date, last_mapping_date, mapping_dates):
"""Builds one span row for Flattrade's token 1594.
Args:
identity (dict): The instrument the token pointed at.
first_mapping_date (datetime.date): The first date it did.
last_mapping_date (datetime.date): The last date it did.
mapping_dates (int): On how many mapping dates it did.
Returns:
types.SimpleNamespace: The row.
"""
return types.SimpleNamespace(
broker_token='1594',
instrument_id=identity['instrument_id'],
exchange=identity['exchange'],
segment=identity['segment'],
shape=identity['shape'],
symbol=identity['symbol'],
first_mapping_date=first_mapping_date,
last_mapping_date=last_mapping_date,
mapping_dates=mapping_dates,
)
def run(self):
"""Prints the ISIN answer, the ticker answer, the spans and the refused match column.
Returns:
None: This method returns nothing.
"""
by_isin = self.resolver.identity_by_isin(
[
' ine009a01021 ',
],
self.today,
preferred_exchange='nse',
)
print(f'Raw files searched: {self.engine.brokers_searched}')
for found in by_isin['INE009A01021']:
print(f' INE009A01021 -> {found["exchange"]} {found["symbol"]}, found through {found["evidence_broker"]} token {found["evidence_token"]}')
by_symbol = self.resolver.identity_by_symbol(
'nse',
[
'nse_equities',
],
[
'infy',
'tcs',
'NOSUCHSHARE',
],
self.today,
)
for symbol in by_symbol:
print(f'Ticker {symbol}: {len(by_symbol[symbol])} match in {by_symbol[symbol][0]["segment"]}')
spans = self.resolver.identity_spans(
'flattrade',
[
1594,
],
segments=[
'nse_equities',
],
)
for span in spans['1594']:
print(f'Flattrade 1594 meant {span["symbol"]} from {span["first_mapping_date"]} to {span["last_mapping_date"]} on {span["mapping_dates"]} dates')
try:
self.resolver.identity_spans(
'flattrade',
[
'1594',
],
match_on='isin',
)
except ValueError as error:
print(f'ValueError: {error}')
if __name__ == '__main__':
HoldingsWithoutATokenExample().run()
It prints:
Raw files searched: ['dhan', 'kotak', 'groww', 'stoxkart', 'fyers', 'wisdom_capital', 'indmoney']
INE009A01021 -> nse INFY, found through dhan token 1594
INE009A01021 -> bse INFY, found through dhan token 500209
Ticker INFY: 1 match in nse_equities
Ticker TCS: 1 match in nse_equities
Flattrade 1594 meant INFY from 2025-01-02 to 2026-09-28 on 431 dates
ValueError: cannot match broker_mappings on 'isin'
"""Fetches a broker's full raw instrument row for one of its tokens, from the latest snapshot on or before a date.
The mapped tables keep only a token, the symbols, the lot size and the tick size. When a caller needs something else from a broker's own file, `raw_row` reads the whole row from that broker's `instruments` table, using the token column the broker is keyed on, such as Zerodha's `instrument_token`.
`raw_row` reads through pandas, which needs a real SQLAlchemy connection, so this program gives the resolver an in-memory SQLite database instead of a small stand-in. SQLite has no schemas, so a second in-memory database is attached under the name `zerodha` to make the table `zerodha.instruments` exist; it holds two days of Zerodha's row for INFY, with the last price changed between them. Python's default way of passing a date to SQLite is deprecated, so the program registers `datetime.date.isoformat` as the adapter. A tiny stand-in cache only carries the engine, and no PostgreSQL or Redis is reached.
Notice that asking about 2026-09-27 returns that day's row even though a later one exists, that an unknown token gives None, and that a broker outside the mapped ten raises `ValueError` before any query, because the broker name becomes part of the table name.
Run it from the project root:
python examples/stock_brokers/instruments/mapping/utilities/resolution/MappingResolver/example_3_a_brokers_raw_row.py
"""
import datetime
import sqlite3
import sqlalchemy
from stock_brokers.instruments.mapping.utilities.resolution import (
MappingResolver,
)
class EngineOnlyMappingCache:
"""A stand-in for `MappingCache` that only lends its engine, which is all `raw_row` uses.
Attributes:
engine (sqlalchemy.engine.Engine): The in-memory SQLite engine.
"""
def __init__(self, engine):
"""Holds the engine.
Args:
engine (sqlalchemy.engine.Engine): The in-memory SQLite engine.
Returns:
None: This method returns nothing.
"""
self.engine = engine
class BrokersRawRowExample:
"""Fills an in-memory Zerodha snapshot and reads rows back through the resolver.
Attributes:
engine (sqlalchemy.engine.Engine): The in-memory SQLite engine.
resolver (MappingResolver): The resolver being shown.
"""
def __init__(self):
"""Builds the in-memory database, fills it and builds the resolver.
Returns:
None: This method returns nothing.
"""
sqlite3.register_adapter(datetime.date, datetime.date.isoformat)
self.engine = sqlalchemy.create_engine('sqlite://')
with self.engine.begin() as connection:
connection.exec_driver_sql("ATTACH DATABASE ':memory:' AS zerodha")
connection.exec_driver_sql(
'CREATE TABLE zerodha.instruments ('
'instrument_token TEXT, exchange_token TEXT, tradingsymbol TEXT, name TEXT, '
'last_price TEXT, tick_size TEXT, lot_size TEXT, exchange TEXT, download_date TEXT)'
)
connection.exec_driver_sql(
'INSERT INTO zerodha.instruments VALUES '
"('408065', '1594', 'INFY', 'INFOSYS', '1532.4', '0.1', '1', 'NSE', '2026-09-27'), "
"('408065', '1594', 'INFY', 'INFOSYS', '1547.9', '0.1', '1', 'NSE', '2026-09-28')"
)
self.resolver = MappingResolver(EngineOnlyMappingCache(self.engine))
def run(self):
"""Prints the row for two dates, the answer for an unknown token and the refusal of an unknown broker.
Returns:
None: This method returns nothing.
"""
row = self.resolver.raw_row('zerodha', 408065, datetime.date(2026, 9, 27))
print(f'As of 2026-09-27: {row}')
row = self.resolver.raw_row('zerodha', '408065', datetime.date(2026, 9, 30))
print(f'As of 2026-09-30: last price {row["last_price"]} from {row["download_date"]}')
print(f'Unknown token: {self.resolver.raw_row("zerodha", "999999", datetime.date(2026, 9, 30))}')
try:
self.resolver.raw_row('upstox', '408065', datetime.date(2026, 9, 30))
except ValueError as error:
print(f'ValueError: {error}')
if __name__ == '__main__':
BrokersRawRowExample().run()
It prints:
As of 2026-09-27: {'instrument_token': '408065', 'exchange_token': '1594', 'tradingsymbol': 'INFY', 'name': 'INFOSYS', 'last_price': '1532.4', 'tick_size': '0.1', 'lot_size': '1', 'exchange': 'NSE', 'download_date': '2026-09-27'}
As of 2026-09-30: last price 1547.9 from 2026-09-28
Unknown token: None
ValueError: 'upstox' is not one of the mapped brokers: ['dhan', 'kotak', 'groww', 'stoxkart', 'fyers', 'wisdom_capital', 'indmoney', 'flattrade', 'shoonya', 'zerodha']
Methods:
| Name | Description |
|---|---|
mapping_date_for |
The mapping date a request for one as-of date resolves to. |
broker_tokens |
Find every broker's token for one instrument identity. |
broker_rows_on_date |
Read one instrument's broker rows for one exact mapping date. |
identity |
Find the unified identity behind each of one broker's tokens. |
identity_spans |
Find every instrument each of one broker's tokens has pointed at, over every mapping date. |
identity_by_isin |
Find the unified identities behind ISIN codes, by way of the brokers whose raw files carry an ISIN. |
identity_by_symbol |
Find the unified identities behind trading symbols, within given segments. |
raw_row |
Fetch a broker's full raw instrument row for one of its own tokens. |
Source code in stock_brokers/instruments/mapping/utilities/resolution.py
The mapping date a request for one as-of date resolves to.
The current date comes from the cache, which reads it from a small Redis key rather than computing it, because a max over the whole hypertable costs about 1.2 seconds and has no selective filter of its own. Only a request for an earlier date pays that max, and it pays a filtered one.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
as_of_date
|
date
|
The date the caller asked about. |
required |
Returns:
| Type | Description |
|---|---|
date | None
|
The latest mapping date on or before as_of_date, or None when nothing was mapped by then. |
Example usage
print(f'2026-09-30 resolves to {self.resolver.mapping_date_for(datetime.date(2026, 9, 30))}')
print(f'2026-08-20 resolves to {self.resolver.mapping_date_for(datetime.date(2026, 8, 20))}')
Source code in stock_brokers/instruments/mapping/utilities/resolution.py
Find every broker's token for one instrument identity.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
exchange
|
str
|
Canonical lowercase exchange name, for example "nse". |
required |
segment
|
str
|
Exchange-prefixed segment value, for example "nse_equities". |
required |
shape
|
str
|
One of "security", "future", or "option". |
required |
identity
|
dict
|
The identity fields for the shape, for example {"symbol": "RELIANCE"}. |
required |
as_of_date
|
date
|
Use the latest mapping on or before this date. |
required |
Returns:
| Type | Description |
|---|---|
tuple
|
A (mapping_date, rows) pair, where mapping_date is the date the answer came from (datetime.date, or None when the instrument was never mapped) and rows is a list of dicts with keys "broker", "broker_token", "broker_symbol", "order_symbol", "lot_size", and "tick_size". |
Example usage
mapping_date, rows = self.resolver.broker_tokens(
'nse',
'nse_equities',
'security',
{
'symbol': 'INFY',
},
as_of_date,
)
Source code in stock_brokers/instruments/mapping/utilities/resolution.py
Read one instrument's broker rows for one exact mapping date.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
computed_id
|
str
|
The instrument id. |
required |
mapping_date
|
date
|
The mapping date to read. |
required |
Returns:
| Type | Description |
|---|---|
list[dict]
|
One dict per broker, with keys "broker", "broker_token", "broker_symbol", "order_symbol", "lot_size", and "tick_size", ordered by broker. |
Example usage
rows = self.resolver.broker_rows_on_date(self.infy_id, datetime.date(2026, 8, 14))
Source code in stock_brokers/instruments/mapping/utilities/resolution.py
Find the unified identity behind each of one broker's tokens.
A token can point at more than one instrument, which happens when a broker's file reuses it, so the most recent mapping wins and the symbol breaks any remaining tie. That keeps the answer stable from one call to the next rather than depending on row order.
The tie-break alone is not enough when the caller already knows what kind of instrument it is holding, because the winning symbol can be the wrong one. Zerodha's BSE token 128224004 carries both ITC in bse_equities and a spurious ISIN-shaped row in bse_fixed_income, and the alphabetical tie-break prefers the latter. Passing the segments a holding can possibly be in removes the twin from consideration rather than hoping the tie-break lands on the right row.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
broker
|
str
|
The broker name, for example "zerodha". |
required |
broker_tokens
|
list[str]
|
The broker's own tokens to resolve. |
required |
as_of_date
|
date
|
Use the latest mapping on or before this date. |
required |
segments
|
list[str] | None
|
Canonical segment names to restrict the answer to, for example ["nse_equities", "bse_equities"], or None to accept any segment. |
None
|
Returns:
| Type | Description |
|---|---|
dict
|
Mapping of broker token to a dict with keys "instrument_id", "exchange", "segment", "shape", "symbol", "underlying_symbol", "expiry_date", "strike_price", "option_type", and "mapping_date". Tokens with no mapping, and tokens whose only mappings fall outside the requested segments, are absent from the result. |
Example usage
today_identity = self.resolver.identity('zerodha', [408065], datetime.date(2026, 9, 30))
earlier_identity = self.resolver.identity('zerodha', ['408065'], datetime.date(2026, 8, 20))
print(f'No tokens asked for: {self.resolver.identity("zerodha", [], datetime.date(2026, 9, 30))}')
Source code in stock_brokers/instruments/mapping/utilities/resolution.py
Find every instrument each of one broker's tokens has pointed at, over every mapping date.
identity answers for one date, which suits a live position and does not suit a stored price series. A series holds bars from years before the first mapping date, and its token can have pointed at different instruments across the mapping dates there are - a symbol change, a move from EQ to BE, a day on which the broker's file filed a row under the wrong segment. So this returns all of them, each with the span of dates it was seen on, and leaves the choice between them to the caller, who knows what the series is.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
broker
|
str
|
The broker name, for example "flattrade". |
required |
broker_tokens
|
list[str]
|
The values to look up. |
required |
segments
|
list[str] | None
|
Canonical segment names to restrict the answer to, or None to accept any segment. |
None
|
match_on
|
str
|
The broker_mappings column the values are compared with, "broker_token" or "order_symbol". Fyers stores its price series under its ticker, which the mapping keeps as order_symbol. |
'broker_token'
|
Returns:
| Type | Description |
|---|---|
dict
|
Mapping of each value to a list of dicts with keys "instrument_id", "exchange", "segment", "shape", "symbol", "first_mapping_date", "last_mapping_date" and "mapping_dates", ordered by first_mapping_date. Values with no mapping in the requested segments are absent. |
Example usage
spans = self.resolver.identity_spans(
'flattrade',
[
1594,
],
segments=[
'nse_equities',
],
)
self.resolver.identity_spans(
'flattrade',
[
'1594',
],
match_on='isin',
)
Source code in stock_brokers/instruments/mapping/utilities/resolution.py
Find the unified identities behind ISIN codes, by way of the brokers whose raw files carry an ISIN.
This is load-bearing rather than a fallback. Groww's holdings rows publish no broker token at all, only an ISIN and a trading symbol, so an ISIN is the only identifier a Groww holding arrives with. Seven brokers publish an ISIN column, and looking one up in any of their raw files yields that broker's own token, which resolves like any other token.
The token taken from each raw file is the broker's join key into unified.broker_mappings, which is not always the column an ISIN sits beside. Fyers is the case that matters: its ISIN sits with scrip_code while its stored token is fytoken, so taking the exchange-assigned security id here would silently find nothing.
An answer is a list rather than one identity, because a dual listed security legitimately has both an NSE and a BSE identity and neither is wrong. The list is ordered with the preferred exchange first so that a caller with a preference does not have to re-sort it.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
isin_codes
|
list[str]
|
The ISIN codes to resolve. |
required |
as_of_date
|
date
|
Use the latest mapping on or before this date. |
required |
preferred_exchange
|
str | None
|
Canonical lowercase exchange name to order first, for example "nse", or None to leave the order to the exchange name and symbol. |
None
|
Returns:
| Type | Description |
|---|---|
dict
|
Mapping of ISIN code to a list of dicts, each holding the same keys identity returns plus "evidence_broker" and "evidence_token", naming the broker whose raw file carried the ISIN and the token it supplied. ISIN codes nothing maps are absent from the result. |
Example usage
by_isin = self.resolver.identity_by_isin(
[
' ine009a01021 ',
],
self.today,
preferred_exchange='nse',
)
Source code in stock_brokers/instruments/mapping/utilities/resolution.py
Find the unified identities behind trading symbols, within given segments.
A ticker is a weaker identifier than a token or an ISIN, so this returns a list per symbol and never picks a winner. An ambiguous match is something the caller has to see rather than something to resolve quietly, because a misfiled position is worse than an unidentified one.
Callers should restrict this to equities and exchange traded funds. A ticker match on a derivative can land on the wrong expiry or strike, where nothing about the answer looks wrong.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
exchange
|
str
|
Canonical lowercase exchange name, for example "nse". |
required |
segments
|
list[str]
|
Canonical segment names to search within, for example ["nse_equities"]. |
required |
symbols
|
list[str]
|
The trading symbols to resolve, matched case-insensitively. |
required |
as_of_date
|
date
|
Use the mapping that was current on or before this date. |
required |
Returns:
| Type | Description |
|---|---|
dict
|
Mapping of the upper-cased symbol to a list of dicts holding the same keys identity returns. Symbols nothing matches are absent from the result. |
Example usage
by_symbol = self.resolver.identity_by_symbol(
'nse',
[
'nse_equities',
],
[
'infy',
'tcs',
'NOSUCHSHARE',
],
self.today,
)
Source code in stock_brokers/instruments/mapping/utilities/resolution.py
Fetch a broker's full raw instrument row for one of its own tokens.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
broker
|
str
|
The broker name, which is also the schema its raw instruments table lives in. |
required |
broker_token
|
str
|
The broker's own token, as stored in unified.broker_mappings. |
required |
as_of_date
|
date
|
Use the latest downloaded snapshot on or before this date. |
required |
Returns:
| Type | Description |
|---|---|
dict | None
|
Every column of the raw row, or None when that broker's file has no such token on or before the date. |
Raises:
| Type | Description |
|---|---|
ValueError
|
If the broker name is not one of the mapped brokers, since the name is interpolated into the table name. |
Example usage
row = self.resolver.raw_row('zerodha', 408065, datetime.date(2026, 9, 27))
row = self.resolver.raw_row('zerodha', '408065', datetime.date(2026, 9, 30))
self.resolver.raw_row('upstox', '408065', datetime.date(2026, 9, 30))
Source code in stock_brokers/instruments/mapping/utilities/resolution.py
The sort key that puts a preferred exchange's identity first among an ISIN's answers.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
found
|
dict
|
One resolved identity. |
required |
preferred_exchange
|
str | None
|
The exchange to order first, or None for no preference. |
required |
Returns:
| Type | Description |
|---|---|
tuple
|
A (preference, exchange, symbol) triple, where preference is 0 for the preferred exchange and 1 for every other. |