How to select specific columns of multi-column join in sqlalchemy?

Question

We are testing the possibility to implement SQLAlchemy to handle our database work. In some instances I need to join a database to a clone of itself (with potentially different data, of course).

An example of the SQL I need to replicate is as follows:

SELECT lt.name, lt.date, lt.type
FROM dbA.dbo.TableName as lt
LEFT JOIN dbB.dbo.TableName as rt
    ON lt.name = rt.name
    AND lt.date = rt.date
WHERE rt.type is NULL

So far I have tried using the join object but I can't get it to not spit the entire join out. I have also tried various .join() methods based on the tutorial here: http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html and I keep getting an AttributeError: "mapper" or not what I'm looking for.

The issues I'm running into is that I need to not only join on multiple fields, but I can't have any foreign key relationships built into the objects or tables.

docs.sqlalchemy.org/en/rel_1_1/core/…

Kijewski
– Kijewski

2015-10-05 17:36:50 +00:00
Commented Oct 5, 2015 at 17:36 — Kijewski
– Kijewski, Commented Oct 5, 2015 at 17:36

Axe · Accepted Answer · 2019-11-18 13:08:51Z

1

Thanks to Kay's like I think I figured out the solution.

It looks like it can be solved by:

session.query(dbA_TableName).outerjoin(
   dbB_TableName,
   and_(dbA_TableName.name == dbB_TableName.name",
   dbA_TableName.date == dbB_TableName.date")
).filter("dbB_TableName.type is NULL")`

edited Nov 18, 2019 at 13:08

Axe

6,3553 gold badges34 silver badges39 bronze badges

answered Oct 6, 2015 at 14:57

Ryan

8010 bronze badges

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

Collectives™ on Stack Overflow

How to select specific columns of multi-column join 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