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

Question

What is the correct way to combine two queries into the same table data in SQLAlchemy?

ie I have a data class, 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 corresponding 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 make this simple? ... or am I fundamentally wrong about it (I'm pretty new to SQLAlchemy)?

Answer 1

Use 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 

Answer 2

You can use aliases to establish a relationship between the same tables.

Your request may look like

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