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