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

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)?

Answers


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

You can use the aliases to make the relationship between same tables.

Your query might be like

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

Need Your Help

Entities Framework 6 alpha 2 - Async Patterns

c# asp.net-mvc asp.net-mvc-4 sql-azure entity-framework-6

I recently have a project with MVC 4, .NET 4.5 and using EF 5.0 (Database First) using the EF 5.0 DbContext Generator. I then upgraded through Nuget Manager to EF 6.0 alpha 2. I wanted to use the...

Do I need to define all the elements in an XML Object?

c# xml windows-phone-7

var block = (from query in data.Descendants("block")

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.