How can I join two queries on the same table with python / sqlalchemy?

Question

What's the right way to join two queries on the same table data in SQLAlchemy?

i.e I have a data class defined something like this:

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)

...and I would like to join the following two queries where there are matching timestamps:

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?

Is there a way to do this simply? ...or am I going about this in a fundamentally flawed way (I'm rather new to SQLAlchemy)?

Answer 1

Use a subquery:

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

You can also get results as DataMeasurement objects:

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