An ORM fetcher implementation

A Fetcher solution that uses SQLAlchemy ORM models.

Using ORM models enables rich formatting such as '{id}:{name} (parent={parent.name})'. A regular SqlFetcher will only have access to the raw parent_id column.

Warning

Note that f"{obj[key]}" and "{obj[key]}".format(obj=obj) are not equivalent.

While f-strings support arbitrary expressions in braces, the str.format()-method uses a mini-language with more limited indexing capabilities.

Implementation

Click here to see the full implementation of the MyOrmFetcher class.

Lazy-loaded relations

SQLAlchemy ORM relations are lazy-loaded by default. The placeholder_attributes property is a dict on the form {placeholder: {attribute, ...}}, containing information about attribute access and indexing operations that are required by the format.

The MyOrmFetcher is a naive implementation built to handle this. The placeholder object (e.g. an Actor instance) must be traversed to trigger loading of lazy relations before the ephemeral parent Session of the orm_object is closed.

Placeholder extraction.

def _to_record(
    self,
    orm_object: Any,
    placeholders: PlaceholdersTuple,
    placeholder_attributes: PlaceholderAttributes,
) -> tuple[Any, ...]:
    values = []
    for placeholder in placeholders:
        obj_attr = getattr(orm_object, placeholder)
        values.append(obj_attr)

        if self._preload_attributes:
            for path in placeholder_attributes.get(placeholder, ()):
                self._traverse(obj_attr, path.split("."))

    return tuple(values)

The ‘films’ placeholder will have path='[0].title'. The traversal logic is shown below.

Traversal logic for a single placeholder object.

@classmethod
def _traverse(cls, obj: Any, attr_path: list[str]) -> None:
    """Traverse attribute path, including indexing.

    .. note::

       The mini-language supports other primitives, e.g. None and float. For
       simplicity, we only cover the ``int`` case.

    .. warning::

       Does not handle chained indexing, e.g.  ``foo[0][1]`` will fail.

    Args:
        obj: An object to traverse.
        attr_path: Access path, e.g., ``["foo", "bar[123]", "baz"]``.
    """
    for attr in attr_path:
        key: int | str | None = None
        if attr[-1] == "]" and (idx := attr.find("[")) != -1:
            key = attr[idx + 1 : -1]  # E.g. "foo[123]" -> "123"
            if key.isdigit():
                key = int(key)
            name = attr[:idx]
        else:
            name = attr

        if name:
            obj = getattr(obj, name)
        if key is not None:
            obj = obj[key]

Configuration

Creating a Translator for the Sakila DVD Rental Database.

Using ORM models as sources.

orm_fetcher = MyOrmFetcher(
    engine=create_engine("postgresql://postgres:Sofia123!@localhost:5002/sakila"),
    models=MyOrmFetcher.from_base_model(SakilaBase),
    preload_attributes=True,
    mapper=Mapper(
        score_function=HeuristicScore("equality", heuristics=["smurf_columns"]),
        overrides={"stuff": "inventory"},
    ),
)
translator = Translator(orm_fetcher)
print(translator.fetcher)

The MyOrmFetcher.from_base_model() utility method was used to derive suitable model classes.

MyOrmFetcher(sources=['Actor', 'Inventory', 'Film', 'Customer', 'Staff', 'Rental'])

Click here for ORM models.

Translating

Let’s use a few overly-complicated Format specs to show the capabilities of the MyOrmFetcher class.

Translating Rental and Actor IDs.

print(translator.fetcher)

print("Rental=[20, 19]:")
for result in translator.translate(
    [20, 19],
    names="Rental",
    fmt=(
        "{staff.first_name} rented "
        "{inventory.film.title} ({stuff.film.release_year})"
        " to {customer.first_name} on {rental_date:%Y-%m-%d}."
    ),
):
    print("*", result)

print("Actor=[5, 11]:")
for result in translator.translate(
    [5, 11],
    names="Actor",
    fmt="{actor_id}: {first_name} {last_name} (first film={films[0].title!r})",
):
    print("*", result)

Output.

Rental=[20, 19]:
* Jon rented SCISSORHANDS SLUMS (2006) to ROBERTA on 2005-05-25.
* Mike rented HOLLOW JEOPARDY (2006) to RONNIE on 2005-05-25.

Actor=[5, 11]:
* 5: JOHNNY LOLLOBRIGIDA (first film='AMADEUS HOLY')
* 11: ZERO CAGE (first film='CANYON STOCK')

This concludes the example. Click here to download the complete example.