Python 如何在 SqlAlchemy 中执行“左外连接”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26142304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to execute "left outer join" in SqlAlchemy
提问by Roosh
I need to execute this query::
我需要执行这个查询::
select field11, field12
from Table_1 t1
left outer join Table_2 t2 ON t2.tbl1_id = t1.tbl1_id
where t2.tbl2_id is null
I had these classes in python:
我在 python 中有这些类：
class Table1(Base):
....
class Table2(Base):
table_id = Column(
Integer,
ForeignKey('Table1.id', ondelete='CASCADE'),
)
....
How do I get to the above from the below?
我如何从下面到达上面？
采纳答案by van
q = session.query(Table1.field1, Table1.field2)\
.outerjoin(Table2)\ # use in case you have relationship defined
# .outerjoin(Table2, Table1.id == Table2.table_id)\ # use if you do not have relationship defined
.filter(Table2.tbl2_id == None)
should do it, assuming that field1and field2are from Table1, and that you define a relationship:
应该这样做，假设field1和field2来自Table1，并且您定义了一个关系：
class Table2(Base):
# ...
table1 = relationship(Table1, backref="table2s")
回答by maciek
You can also do that using SQLAlchemy Core only:
您也可以仅使用 SQLAlchemy Core 执行此操作：
session.execute(
select(['field11', 'field12'])
.select_from(
Table1.outerjoin(Table2, Table1.tbl1_id == Table2.tbl1_id))
.where(Table2.tbl2_id.is_(None))
)
PS .outerjoin(table, condition)is equivalent to .join(table, condition, isouter=True).
PS.outerjoin(table, condition)相当于.join(table, condition, isouter=True)。
