Thanks for links, I found `aliased` in docs but not in tutorials, I got errors using it but probably I was using it in the wrong way, now it's clearer.
On Dec 16, 2:39 am, Michael Bayer <[email protected]> wrote: > 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 [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group > > athttp://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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
