SQLAlchemy中的正确外部联接

小编典典

SQLAlchemy中的正确外部联接

sql

我有两个表beard，moustache定义如下：

+--------+---------+------------+-------------+
| person | beardID | beardStyle | beardLength |
+--------+---------+------------+-------------+

+--------+-------------+----------------+
| person | moustacheID | moustacheStyle |
+--------+-------------+----------------+

我在PostgreSQL中创建了一个SQL查询，它将结合这两个表并生成以下结果：

+--------+---------+------------+-------------+-------------+----------------+
| person | beardID | beardStyle | beardLength | moustacheID | moustacheStyle |
+--------+---------+------------+-------------+-------------+----------------+
| bob    | 1       | rasputin   | 1           |             |                |
+--------+---------+------------+-------------+-------------+----------------+
| bob    | 2       | samson     | 12          |             |                |
+--------+---------+------------+-------------+-------------+----------------+
| bob    |         |            |             | 1           | fu manchu      |
+--------+---------+------------+-------------+-------------+----------------+

询问：

SELECT * FROM beards LEFT OUTER JOIN mustaches ON (false) WHERE  person = "bob"
UNION ALL
SELECT * FROM beards b RIGHT OUTER JOIN mustaches ON (false) WHERE  person = "bob"

但是我不能创建它的SQLAlchemy表示形式。我尝试了几种从实施from_statement到实施的方法，outerjoin但没有一种真正有效。有人可以帮我吗？

阅读 215

2021-03-17

共1个答案

小编典典

在@Francis
P的建议下，我想到了以下片段：

q1 = session.\
     query(beard.person.label('person'),
           beard.beardID.label('beardID'),
           beard.beardStyle.label('beardStyle'),
           sqlalchemy.sql.null().label('moustachID'),
           sqlalchemy.sql.null().label('moustachStyle'),
     ).\
     filter(beard.person == 'bob')

q2 = session.\
     query(moustache.person.label('person'),
           sqlalchemy.sql.null().label('beardID'), 
           sqlalchemy.sql.null().label('beardStyle'),
           moustache.moustachID,
           moustache.moustachStyle,
     ).\
     filter(moustache.person == 'bob')

result = q1.union(q2).all()

但是，这可行，但是您无法将其称为答案，因为它看起来像是骇客。这是RIGHT OUTER JOINsqlalchemy中应该存在的另一个原因。

2021-03-17