How to correctly use SQL joins/subqueries in Sqlalchemy

Question

Say I have the following SQL code and I want to change it to Sqlalchemy:

SELECT amount FROM table1
JOIN table2 
  ON table2.id = table1.b_id
JOIN (SELECT id FROM table3 WHERE val1 = %s AND val2 = %s) inst
  ON inst.id = table1.i_id
WHERE
  val3 = %s

I've tried making a subquery for the SELECT id FROM table3 clause as follows:

subq = session.query(table3.id).filter(and_(table3.val1 == 'value', table3.val2 == 'value')).subquery()

And then putting everything together:

query = session.query(table1).join(table2).filter(table2.id == table1.b_id).\
join(subq).filter(table1.val3 == 'value')

When I ouput query.first().amount, this works for a few examples, but for some queries I'm getting no results when there should be something there, so I must be messing up somewhere. Any ideas where I'm going wrong? Thanks

What does the returned SQL statement look like? Can you given and example of when it does and doesn't work. — ACV
– ACV, Commented Oct 24, 2014 at 3:50

van · Accepted Answer · 2014-10-24 16:32:42Z

1

Query below should produce exactly the SQL you have. It is not much different from your, but removes some unnecessary things.
So if it does not work, then also your original SQL might not work. Therefore, I assume that your issue is not SQL but either data or the parameters for that query. And you can always print out the query itself by engine.echo = True.

val1, val2, val3 = 'value', 'value', 'value'  # @NOTE: specify filter values
subq = (session.query(table3.id)
        .filter(and_(table3.val1 == val1, table3.val2 == val2))
        ).subquery(name='inst')

quer = (
    session.query(table1.amount)  # @NOTE: select only one column
    .join(table2)  # @NOTE: no need for filter(...)
    .join(subq)
    .filter(table1.val3 == val3)
).first()

print(quer and quer.amount)

answered Oct 24, 2014 at 16:32

van

77.6k13 gold badges174 silver badges179 bronze badges

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

Collectives™ on Stack Overflow

How to correctly use SQL joins/subqueries in Sqlalchemy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related