Access all column values of joined tables with SqlAlchemy

Question

Imagine one has two SQL tables

objects_stock
id | number

and

objects_prop
id | obj_id | color | weight

that should be joined on objects_stock.id=objects_prop.obj_id, hence the plain SQL-query reads

select * from objects_prop join objects_stock on objects_stock.id = objects_prop.obj_id;

How can this query be performed with SqlAlchemy such that all returned columns of this join are accessible? When I execute

query = session.query(ObjectsStock).join(ObjectsProp, ObjectsStock.id == ObjectsProp.obj_id)
results = query.all()

with ObjectsStock and ObjectsProp the appropriate mapped classes, the list results contains objects of type ObjectsStock - why is that? What would be the correct SqlAlchemy-query to get access to all fields corresponding to the columns of both tables?

Orangeman

Answer 1

Just in case someone encounters a similar problem: the best way I have found so far is listing the columns to fetch explicitly,

query = session.query(ObjectsStock.id, ObjectsStock.number, ObjectsProp.color, ObjectsProp.weight).\
   select_from(ObjectsStock).join(ObjectsProp, ObjectsStock.id == ObjectsProp.obj_id)
results = query.all()

Then one can iterate over the results and access the properties by their original column names, e.g.

for r in results:
    print(r.id, r.color, r.number)

Orangeman

Answer 2

A shorter way of achieving the result of @ctenar's answer is by unpacking the columns using the star operator:

query = (
    session
    .query(*ObjectsStock.__table__.columns, *ObjectsProp.__table__.columns)
    .select_from(ObjectsStock)
    .join(ObjectsProp, ObjectsStock.id == ObjectsProp.obj_id)
)
results = query.all()

This is useful if your tables have many columns.

Mood