如何使用Python/SQLAlchemy在同一张表上连接两个查询？

Question

如何在SQLAlchemy中正确地将两个查询连接到同一张数据表上？

比如，我定义了一个数据类，类似于以下内容：

class DataMeasurement(Base):
    __tablename__ = 'DataMeasurement'
    id = Column(Integer, Sequence('data_measurement_id_seq'), primary_key=True)
    data_source = Column(String)
    timestamp = Column(DateTime)
    sensor_output = Column(Float)

我希望将下面两个查询连接起来，其中时间戳匹配：

q1 = self.session.query(DataMeasurement).filter_by(data_source='Sensor1').order_by(DataMeasurement.timestamp)
q2 = self.session.query(DataMeasurement).filter_by(data_source='Sensor2').order_by(DataMeasurement.timestamp)
# ...and now what?

有没有简单的方法来做到这一点？还是我对此的方式根本有缺陷（我对SQLAlchemy非常新）？

- Jon Cage

Answer 1

使用子查询：

subq = self.session.query(DataMeasurement).\
    filter_by(data_source='Sensor1').subquery()
q = self.session.query(
    DataMeasurement.timestamp,
    # Use labels to distinguish between identically named columns.
    # This is optional.
    subq.c.sensor_output.label('output1'),
    DataMeasurement.sensor_output.label('output2')
).filter(
    (DataMeasurement.data_source == 'Sensor2') &
    (DataMeasurement.timestamp == subq.c.timestamp)
)

# Simply get a list of named tuples.
print q.all()
# Or access each column using properties.
for row in q:
    print row.timestamp, row.output1, row.output2

您还可以获得DataMeasurement对象作为结果：

subq = self.session.query(DataMeasurement).\
    filter_by(data_source='Sensor1').subquery()
# Use alias to associate mapped class to a subquery.
dmalias = aliased(DataMeasurement, subq)
q = self.session.query(dmalias, DataMeasurement).filter(
    (DataMeasurement.data_source == 'Sensor2') &
    (DataMeasurement.timestamp == dmalias.timestamp)
)

# For each row you get a tuple containing two DataMeasurement objects.
for dm1, dm2 in q:
    print dm1.timestamp, dm1.sensor_output, dm2.sensor_output

- Audrius Kažukauskas

---

那肯定会返回正确的信息。只有一个问题，我如何访问查询结果的属性？是否有一种方法可以返回两个 DataMeasurement 对象，还是我只能为每行匹配的命名元组获得一个？ - Jon Cage

是的，你可以得到 DataMeasurement 对象作为结果，我会更新我的答案来说明这一点。 - Audrius Kažukauskas

在第二个示例中，您如何访问它们（作为DataMeasurement对象）？即，您如何区分它们？“q...”？ - Jon Cage

对于每一行，您将获得两个DataMeasurement对象的元组。我扩展了第二个示例来演示这一点。 - Audrius Kažukauskas

Answer 2

你可以使用别名来建立同一张表之间的关系。

你的查询可能会像这样：

adalias1 = aliased(DataMeasurement)
adalias2 = aliased(DataMeasurement)
q1 = self.session.query(DataMeasurement).\
        filter(
               and_(
                    adalias1.data_source in ('Sensor1', 'Sensor2'), 
                    adalias1.timestamp == adalias2.timestamp
                   )
              )

- Nilesh

---

我尝试了你的建议，但从生成的SQL语句来看，adalias1.data_source in ('Sensor1', 'Sensor2')被翻译成了0。如果我删除AND，查询将以这个结束：WHERE "DataMeasurement_1".timestamp = "DataMeasurement_2".timestamp，但是如果包括AND，它看起来像这样：WHERE 0 AND "DataMeasurement_1".timestamp = "DataMeasurement_2".timestamp。 - Jon Cage

我将 in adalias1.data_source in ('Sensor1', 'Sensor2') 更改为 in_(['Sensor1', 'Sensor2'])，这似乎已经修复了 0 的问题，使其成为了一个正确的 SQL 术语，但是现在好像没有过滤任何结果了（例如查询结果包括其他传感器）？ - Jon Cage