How to access column values in SQLAlchemy result list after a join a query

Question

I need to access colums of result query. I have these models

class Order(Base):
    __tablename__ = "orders"
    
    internal_id = Column(Integer, primary_key=True)
    total_cost = Column(Float, nullable=False)
    created_at = Column(TIMESTAMP(timezone=True), nullable=False, server_default=text("now()"))
    customer_id = Column(Integer, ForeignKey("customers.id", ondelete="CASCADE"), nullable=False)
    customer = relationship("Customer")


class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, nullable=False)
    internal_id = Column(Integer, nullable=False) 
    price = Column(Float, nullable=False)
    description = Column(String, nullable=False)
    order_id = Column(Integer, ForeignKey("orders.internal_id", ondelete="CASCADE"), nullable=False)
    order = relationship("Order")

Now I run this left join query that gives me all the columns from both tables

result = db.query(Order, Item).join(Item, Item.order_id == Order.internal_id, isouter=True).filter(Item.order_id == order_id).all()

I get back a list of tuples. How do I access a particular column of the result list? Doing something like this

for i in result:
    print(i.???) # NOW WHAT?

Getting AttributeError: Could not locate column in row for column anytime i try to fetch it by the name I declared.

this is the full function where I need to use it

@router.get("/{order_id}")
def get_orders(order_id: int, db: Session = Depends(get_db)):
    """ Get one order by id. """
    # select * from orders left join items on orders.internal_id = items.order_id where orders.internal_id = {order_id};
    result = db.query(Order, Item).join(Item, Item.order_id == Order.internal_id, isouter=True).filter(Item.order_id == order_id).all()
    for i in result:
        print(i.description) # whatever value i put here it errors out

This is the traceback

      ...      
print(i.description) # whatever value i put here it errors out
    AttributeError: Could not locate column in row for column 'description'

At least if I could somehow get the column names.. But i just cant get them. Trying keys(), _metadata.keys .. etc. Nothing works so far.

Can you please show us the exact code you tried and the resulting traceback? — Tim Roberts, Jul 16 '22 at 19:15

jorzel · Answer 1 · 2022-07-17T06:51:53.993

If additional implicite queries are not an issue for you, you can do something like this:

class Order(Base):
    __tablename__ = "orders"
    
    internal_id = Column(Integer, primary_key=True)
    total_cost = Column(Float, nullable=False)
    created_at = Column(TIMESTAMP(timezone=True), nullable=False, server_default=text("now()"))
    customer_id = Column(Integer, ForeignKey("customers.id", ondelete="CASCADE"), nullable=False)
    customer = relationship("Customer")
    items = relationship("Item", lazy="dynamic")

order = session.query(Order).join(Item, Order.internal_id == Item.order_id, isoutrr=True).filter(Order.internal_id == order_id).first()

if order:
    for i in order.items:
        print(i.description)
        print(order.total_cost)

However to avoid additional query when accessing items you can exploit contains_eager option:

from sqlalchemy.orm import contains_eager

order = session.query(Order).join(Item, Order.internal_id == Item.order_id, isoutrr=True).options(contains_eager("items").filter(Order.internal_id == order_id).all()

Here you have some examples: https://jorzel.hashnode.dev/an-orm-can-bite-you

I need to pass both class models to the query() becasue I basically need to do SELECT * FROM ORDERS JOIN ITEMS ON .. If i put only one class, it returns columns only from the one passed class. I need columns from both in the result — experimental, Jul 17 '22 at 08:03
But in my solution you have access to both models, see the loop. If you use `contains_eager` you should get columns from both tables in single query — jorzel, Jul 17 '22 at 09:30

score 0 · Answer 2 · answered Jul 17 '22 at 14:12

Ok, so acctualy the answer is quite simple. One just simply needs to use dot notation like i.Order.total_cost or whichever other field from the Order model

 result = db.query(Order, Item).join(Item, Item.order_id == Order.internal_id, isouter=True).filter(Item.order_id == order_id).all()
for i in result:
    print(i.Order.total_cost)
    print(i.Item.description)

How to access column values in SQLAlchemy result list after a join a query

2 Answers2

Linked