Skip to content

How to use JOIN and SELECT AS together in SQLAlchemy?

I have the two following tables.

Users Table

id name email
32 Hello
23 World

Sales Table

id SellerId CustomerId Amount
4 32 23 25

I want to join the tables in the following way to get this result. Here I want to only get the entries where customer id is equal to 23.

Id SellerId SellerName SellerEmail Amount
4 32 Hello. 25.

And also do another join this way. Here I want to only get the entries where seller id is equal to 32.

id CustomerId CustomerName CustomerEmail Amount
4 23 World 25.

My code for both the tables is

class Users(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String)
    email = Column(String, unique=True, index=True, nullable=False)

class Sales(Base):

    id = Column(Integer, primary_key=True, index=True)
    seller_id = Column(Integer, index=True)
    customer_id = Column(Integer, index=True)
    amount = Column(Integer, index=True)

I was able to achieve this using raw SQL with the following

SELECT (, name as SellerName, email as SellerEmail, Amount
FROM sales
LEFT JOIN user ON sales.SellerId =
WHERE CustomerId = 23

Please help with doing the same using SqlAlchemy Session.



query = (    
    .outerjoin(Users, == Sales.seller_id)
    .filter(Sales.customer_id == 23)
User contributions licensed under: CC BY-SA
4 People found this is helpful