How to join two queries in SQLAlchemy?

Question

In this example, I am using the sample MySQL classicmodels database.

So I have two queries:

products = session.query(Products)
orderdetails = session.query(OrderDetails)

Let's assume I cannot make any more queries to the database after this and I can only join these two queries from this point on.

I want to do an outer join on them to be able to do something like this:

for orderdetail, product in query:
    print product.productName, product.productCode, orderdetails.quantityOrdered

However, whenever I do an outerjoin on this, I can only seem to get a left join.

query = orderdetails.outerjoin(Products)

Code like this yields only orderdetails columns:

for q in query:
    # Only gives orderdetails columns
    print q

And doing something like this:

for orderdetails, product in query:
    print orderdetails, product

Gives me an error: TypeError: 'OrderDetails' object is not iterable.

What am I doing wrong? I just want columns from the Products table as well.

EDIT:

I have found my solution thanks to @univerio's answer. My real goal was to do a join on two existing queries and then do a SUM and COUNT operation on them.

SQLAlchemy basically just transforms a query object to a SQL statement. The with_entities function just changes the SELECT expression to whatever you pass to it. This is my updated solution, which includes unpacking and reading the join:

for productCode, numOrders, quantityOrdered in orderdetails.with_entities(
        OrderDetails.productCode, 
        func.count(OrderDetails.productCode),
        func.sum(OrderDetails.quantityOrdered)).group_by(OrderDetails.productCode):
    print productCode, numOrders, quantityOrdered

dain · Accepted Answer · 2019-03-04 11:03:53Z

5

You can overwrite the entity list with with_entities():

orderdetails.outerjoin(Products).with_entities(OrderDetails, Products)

edited Mar 4, 2019 at 11:03

dain

6,7491 gold badge45 silver badges49 bronze badges

answered Apr 4, 2016 at 15:53

univerio

20.7k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

joshualan Over a year ago

Yeah, I know there's a way to do it through session.query but is there a way to do it with the two session queries I have?

univerio Over a year ago

@joshualan I'm not sure I understand why you need to do that. No SQL is execute until you iterate through the query. Or did you want to join two subqueries? Or, you want to re-use an existing query because you have filters on it already?

joshualan Over a year ago

Yeah, I just wanted to re-use those existing queries. It doesn't seem like there's a way though.

joshualan Over a year ago

Thanks! This was exactly what I needed, I've updated my question with the answer.

Collectives™ on Stack Overflow

How to join two queries in SQLAlchemy?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related