On Dec 15, 2010, at 9:04 AM, neurino wrote:
> Hello I have 2 tables: data and acquisitions,
> - each Acquisition has many Data
> - each Data come from a different sensor
> - the single sensor is identified by the couple Acquisition.id_centr,
> Data.id_meas
>
> No I need a query with one colum for each sensor and a row for each
> Acquisition.datetime
>
> This is how I get it (in case of two sensors) with SQL:
>
>>>> q = curs.execute("""
> SELECT a.datetime, d1.value, d2.value
> FROM acquisitions AS a
> LEFT JOIN data AS d1
> ON a.id_acq=d1.id_acq
> AND a.id_centr=159
> AND d1.id_meas=1501
> LEFT JOIN data AS d2
> ON a.id_acq=d2.id_acq
> AND a.id_centr=320
> AND d2.id_meas=1551
> """)
>>>> for n, row in enumerate(q): print n, row
> ....:
> 0 (u'2010-09-02 12:05:00', 23.98, 25.67)
> 1 (u'2010-09-02 12:10:00', 23.77, 25.57)
> 2 (u'2010-09-02 12:15:00', 23.96, 25.57)
> 3 (u'2010-09-02 12:20:00', 24.78, 25.94)
> 4 (u'2010-09-02 12:25:00', 25.48, 26.27)
> 5 (u'2010-09-02 12:30:00', 25.91, 26.46)
> 6 (u'2010-09-02 12:35:00', 26.14, 26.62)
> 7 (u'2010-09-02 12:40:00', 26.32, 26.73)
> 8 (u'2010-09-02 12:45:00', 26.44, 26.80)
> 9 (u'2010-09-02 12:50:00', 26.55, 26.87)
> 10 (u'2010-09-02 12:55:00', 26.62, 26.92)
> 11 (u'2010-09-02 13:00:00', 26.67, 26.94)
> 12 (u'2010-09-02 13:05:00', 26.69, 26.94)
> 13 (u'2010-09-02 13:10:00', 26.71, 26.96)
> 14 (u'2010-09-02 13:15:00', 26.73, 26.98)
>
> But I can't get the same result with sqlalchemy, here's my mapping:
>
> data = Table('data', metadata,
> Column('id_data', Integer, primary_key=True),
> Column('id_meas', Integer, nullable=False),
> Column('id_acq', Integer, ForeignKey('acquisitions.id_acq'),
> nullable=False),
> Column('value', Float, nullable=False),
> )
>
> acquisitions = Table('acquisitions', metadata,
> Column('id_acq', Integer, primary_key=True),
> Column('id_centr', Integer, nullable=False),
> Column('datetime', DateTime, nullable=False),
> #acquisitions with same id_centr and datetime are duplicates
> UniqueConstraint('id_centr', 'datetime'),
> )
>
> orm.mapper(Data, data, properties={
> 'acquisitions': orm.relationship(Acquisition, backref='data'),
> })
> orm.mapper(Acquisition, acquisitions)
to create aliases during an ORM query you use the aliased() construct. There's
examples at:
http://www.sqlalchemy.org/docs/orm/tutorial.html#using-aliases
you'd also be using sqlalchemy.and_() to formulate those outerjoin() conditions.
>
> Any advice?
>
> Thanks for your support
> neurino
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
