Search code examples
sqlalchemyfastapisqlmodel

columns from add_column not reflected in result in sqlmodel / sqlalchemy

I have an Inventory Model and Product Model, I wish to select the Product name only when it is needed. Inventory has product_id as a foreign key to Product.id

    inventories_stmt = select(Inventory)
    print("Statement Check", inventories_stmt)
    if product:
        inventories_stmt = (
            inventories_stmt.join(Product)
            .add_columns(
                Product.name
            )
    print("Statement Check", inventories_stmt)
    inventories = session.exec(batch_inventories).all()

The result is however does not contain Product.name, but the generated sql statement has Product.name in select and running it directly in sql client produces the required result.

Why does sqlmodel/sqlalchemy output removes Product.name? How to overcome it?

Solution

  • session.exec() in SQLModel is equivalent of session.execute().scalars() in SQLAlchemy. It returns only first object from the result row.

    Use session.execute() to get all data:

    for inventories, prod_name in session.execute(inventories_stmt).tuples().all():
    print(f"{inventories=}, {prod_name=}")