Adopting in an existing project#

This guide is for adding id-translation to an application you already have: one that already maps IDs in a handful of ad-hoc ways (hard-coded dicts, pandas.Series.map(), enum ladders, ad-hoc SQL JOINs, f-strings) and has accumulated enough of them to be worth consolidating.

Note

Starting a brand-new, organization-wide translation library? See the rsundqvist/id-translation-project Cookiecutter template.

A few terms first

  • source: a place labels are fetched from: a database table, a MemoryFetcher dataset, a CSV, and so on.

  • placeholder: a column a source provides (id, name, email, …). fmt strings reference these, e.g. fmt = "{name}".

  • name: overloaded. In translate(names=...) a name is the field that holds IDs (e.g. customer_id), not the label; the label is usually the name placeholder.

Is it worth it?#

id-translation competes with a dict, not with nothing. For a single, stable mapping in one place, a dict is the right answer; reach for this library only once translation has become a cross-cutting concern. Good signs you are past that line:

  • the same mapping is duplicated in more than one place (and has drifted, or will);

  • you resolve IDs one row at a time (an N+1 loop) over a large structure;

  • labels come from a mix of sources: a database, a static map, a CSV;

  • the label format is copy-pasted across modules.

Below that line, keep the dict.

1. List your sources#

Inventory the id -> label mappings scattered through your code, and give each a source name. For an order-reporting app that might be:

Source

Currently resolved by

Lives in

customers

one SQL query per row (an N+1)

your application database

countries

a hard-coded dict and a table

drifting between the two

statuses

an if/elif ladder

a few lines of Python

Pick a single source of truth per mapping now: the duplication is exactly what you are removing.

2. Point a fetcher at each source#

Each source is served by a Fetcher, configured in TOML so the what stays out of your code. One fetcher usually serves many sources.

Your application database. The built-in SqlFetcher takes a SQLAlchemy connection string (with optional ${VAR} environment-variable interpolation for secrets):

[fetching.SqlFetcher]
connection_string = 'postgresql://reporter:${DB_PASSWORD}@db.internal/app'
whitelist_tables = ["customers", "countries"]

Interpolation is a plain string substitution, so a password containing URL-reserved characters (@, :, /, ?) would corrupt the connection string. For those, drop the password from the URL and pass it through the dedicated password key instead, which is URL-escaped and substituted for {password} in the connection string:

[fetching.SqlFetcher]
connection_string = "postgresql://reporter:{password}@db.internal/app"
password = '${DB_PASSWORD}'

Bind the id placeholder to your primary key. Each source must supply an id placeholder, but your tables’ PKs are usually named customer_id, code, and so on. Map them explicitly with a per-source section:

[fetching.mapping.overrides.customers]
id = "customer_id"

[fetching.mapping.overrides.countries]
id = "code"

A column placed directly under [fetching.mapping.overrides] (no source) is a shared default applied to every source, so a uniform convention needs only one line; add per-source sections for the exceptions:

[fetching.mapping.overrides]
id = "code"            # default for every source

[fetching.mapping.overrides.orders]
id = "order_id"        # ...except this one

Heuristics can infer names like customer_id from a customers table, but an irregular PK like code matches nothing, so when retro-fitting an existing schema, explicit overrides are the reliable choice.

Note

Two configuration tables share the word overrides but do unrelated jobs. [fetching.mapping.overrides.<source>] (above) maps a placeholder to a column inside one source. [translator.mapping.overrides] maps a field name to a source: a persistent, config-file form of the names={field: source} dict in step 4:

[translator.mapping.overrides]       # field (name) -> source
created_by = "staff"

[fetching.mapping.overrides.staff]   # placeholder -> column
id = "staff_id"

To reuse an engine your application already manages, subclass SqlFetcher and override its create_engine() classmethod. connection_string is passed to it verbatim, so it can be any identifier (e.g. a per-environment database slug) rather than a literal URL:

from id_translation.fetching import SqlFetcher

class AppFetcher(SqlFetcher):
    @classmethod
    def create_engine(cls, connection_string, password, engine_kwargs):
        from myapp.db import get_engine  # your existing per-environment engine
        return get_engine(connection_string)

Reference a custom fetcher by its fully-qualified name, and feed create_engine the slug through connection_string. To choose the environment at runtime, interpolate an environment variable: ${VAR} works out of the box, with no metaconf.toml required (that file tunes interpolation and equivalence checks, both of which have working defaults):

[fetching.'myapp.translation.AppFetcher']
connection_string = '${APP_ENV:dev}'   # falls back to the 'dev' slug

Your factory (next step) sets APP_ENV before reading the config, so create_engine receives "dev" / "staging" / "prod" and maps it to the matching engine.

Small static maps. Use MemoryFetcher (no database, no file). The simplest form maps each ID straight to its label:

[fetching.MemoryFetcher.data.statuses]
P = "Pending"
S = "Shipped"
D = "Delivered"
C = "Cancelled"

This scalar form is ideal for string codes like these. For integer IDs, use the columnar id = [...] / name = [...] form instead; see Section: Fetching for why.

Tabular files. Point a PandasFetcher at a directory with one file per source; the reader is derived from the suffix. Every matching file becomes a source, so keep other data files elsewhere:

[fetching.PandasFetcher]
read_path_format = "data/{}.csv"   # data/customers.csv is the 'customers' source

3. One config, one entry point#

Wrap construction in a single factory so the rest of your app never sees the wiring:

import os
from pathlib import Path

from id_translation import Translator

_CONFIG = Path(__file__).parent / "config"

def create_translator(env: str = "dev") -> Translator:
    os.environ["APP_ENV"] = env  # interpolated into the fetcher's connection_string
    return Translator.from_config(
        _CONFIG / "main.toml",
        extra_fetchers=[_CONFIG / "fetching" / "database.toml"],
    )

from_config() accepts Path objects; extra_fetchers lets each fetcher live in its own file. A file declares one fetcher, along with its [fetching.mapping] overrides, so the database, the static maps and the tabular files from step 2 each get one. Multiple fetchers are composed automatically. Setting APP_ENV here is what makes the ${APP_ENV:dev} in the fetcher TOML resolve to the chosen environment: interpolation happens when from_config reads the file.

4. Replace the call sites#

Now collapse the scattered lookups into a single call. The names argument accepts a {field: source} dict that binds each ID-bearing field to the source that resolves it, bypassing name-to-source heuristics entirely, which is usually what you want when retro-fitting an existing schema:

translator = create_translator()
translator.translate(
    df,
    names={"customer_id": "customers", "country_id": "countries",
           "status_code": "statuses"},
    copy=False,  # Perform in-place translation.
)

This one call replaces the .map, the .apply ladder, the per-row SQL, and the manual f-string. Control the label shape with the fmt key under [translator], e.g. fmt = "{name}" for the label only. The fmt mini-language (optional [...] blocks, literal brackets) is documented in the Format docstring.

5. Let the defaults take over#

The steps above pin every name and placeholder by hand: the safe choice when retro-fitting a schema you do not control. Once it works, lean on the behaviour you opted out of: id-translation reads names off the translatable (e.g. DataFrame columns) and maps them to sources by heuristic, so a conventionally-named customer_id needs no names= entry. Scoring is off by default: enable it under [translator.mapping], e.g. score_function = "equality" with the like_database_table heuristic. Keep explicit overrides for the irregular cases only.

See also

The DataFrame you just translated is only one of the structures the dio framework handles; see the table of bundled integrations for the full set, from dicts and lists to polars and dask frames. The Translation IO system is extensible: write your own integration, or change how a built-in one behaves through the io_kwargs argument.

Q&A#

Questions that tend to come up mid-migration.

What do untranslated IDs look like?

Unknown IDs are rendered with a fallback Format: '<Failed: id=1>' by default. Configure it in the [unknown_ids] section, e.g. fmt = "Unknown", or fmt = "{id}" to leave IDs as-is. Missing IDs in a DataFrame are never fetched and do not count towards max_fails; see PandasIO for how they are rendered. A None in a builtin collection is an ordinary – unknown – ID.

How do I know the migration is complete?

Pass translate(df, max_fails=0.0) while validating; TooManyFailedTranslationsError is raised if any ID fails to resolve. Relax or drop the argument (default 1.0) once the configuration is trusted.

A column comes back untranslated. Why?

Names derived from the data (e.g. DataFrame columns, when names is not given) that fail to map to a source raise ScoringDisabledError under the default mapper. Once [translator.mapping] sets a score function, on_unmapped decides (skip silently by default), except for a name with a −∞ score for any source (e.g. from a filter): it is always skipped. Call translator.map(df) to inspect the name-to-source decision without translating, and use enable_verbose_debug_messages() to see why a match was rejected. The mapping primer explains the procedure.

When does fetching happen?

On every translate() call, for just the IDs being translated. Read-heavy services may prefer go_offline(): it stores a snapshot in memory, after which the instance stops fetching entirely and is safe to share between threads.

The IDs are UUIDs.

Set enable_uuid_heuristics = true under [translator]. IDs that are UUID in one place and strings in another (or strings in a different case) will then match anyway.

A column packs several flags into one ID.

Translate composite IDs such as bitmasks with a BitmaskTransformer, declared in config or passed in code through the transformers argument of Translator.