如何在 SQLALchemy 中执行左连接？

我有一个 SQL 查询，它在几个表上执行一系列左连接:

SELECT
<some attributes>
FROM table1 t1
INNER JOIN table2 t2
ON attr = 1 AND attr2 = 1
LEFT JOIN table3 t3
ON t1.Code = t2.Code AND t3.Date_ = t1.Date_
LEFT JOIN tabl4 t4
ON t4.Code = t1.code AND t4.Date_ = t1.Date_

到目前为止，我已经:

(sa.select([idc.c.Code])
.select_from(
t1.join(t2, and_(t1.c.attr == 1, t2.c.attr2 = 1))
.join(t3, t3.c.Code == t1.c.Code)))

但是我不知道怎么把连接变成 LEFT JOIN。

76416 次浏览
小开
最佳答案

isouter=True标志将产生一个与 LEFT JOIN相同的 LEFT OUTER JOIN。

用你的代码:

(sa.select([idc.c.Code])
.select_from(
t1.join(t2, and_(t1.c.attr == 1, t2.c.attr2 = 1))
.join(t3, t3.c.Code == t1.c.Code, isouter=True)))

陈述性例子:

session = scoped_session(sessionmaker())
session.query(Model).join(AnotherModel, AnotherModel.model_id == Model.id, isouter=True)
小开

以下是使用 isout 的方法:

select_from(db.join(Table1, Table2, isouter=True).join(Table3, isouter=True))
小开

选项1-LEFTJOIN 并从两个表中选择所有列

# Query the db
results = db.session.query(Table_1, Table_2).join(
Table_2, Table_2.column_name == Table_1.column_name,
isouter=True).all()
# Iterate results and do stuff
for result in results:
try:
# Use [0] for accesing table_1 columns (left table) and use [1] for accesing table_2 columns (right table)
print(result[0].column_name_x)
print(result[0].column_name_y)
print(result[1].column_name_x)
print(result[1].column_name_y)
except Exception as e:
print(str(e))

选项2-LEFTJOIN 并从两个表中选择某些列

# Query the db
results = db.session.query(Table_1.column_name_x, Table_1.column_name_y Table_2.column_name_z).join(Table_2, Table_2.column_name == Table_1.column_name, isouter=True).all()
# Iterate results and do stuff
for result in results:
try:
# Use dot notation for accesing column from any table
print(result.column_name_x)
print(result.column_name_y)
print(result.column_name_z)
except Exception as e:
print(str(e))

提交答案