OK, thanks, it does work if you make an alias on the select like this: s = select([left.c.car_id, left.c.temperature.label('left_temperature'), right.c.temperature.label('right_temperature')], from_obj=tables, whereclause=and_(left.c.side == 'left', right.c.side == 'right')).alias('carside')
Thanks a lot! Now, is it really needed to translate my SQL query first to SA-speak or could I use the SQL directly in some way? (I have many old projects with SQL embedded in Perl scripts, so it would be someway easier is I can transfer the SQL directly). >From session.query() there is something like from_statement (SQL_string) but that won't work here I think. Again, many thanks, Jan. On Apr 17, 2:43 pm, "King Simon-NFHD78" <simon.k...@motorola.com> wrote: > > -----Original Message----- > > From: sqlalchemy@googlegroups.com > > [mailto:sqlalch...@googlegroups.com] On Behalf Of JanW > > Sent: 17 April 2009 13:18 > > To: sqlalchemy > > Subject: [sqlalchemy] mapping class against arbitrary SQL expression > > > Hi, > > > is there a way to map a class against an arbitrary SQL expression > > (read-only would be OK)? I can't find the correct way to define the > > selectable for the mapper. > > > Example: > > this table: > > carside_table = Table( > > 'carside', metadata, > > Column('id', Integer, primary_key=True), > > Column('car_id', Integer), > > Column('side', Text), > > Column('temperature', Float), > > ) > > > with a dummy class; > > class CarSide(object): > > pass > > > And I want to use this SQL expression to map the class: > > SELECT > > left.car_id > > left.temperature AS left_temperature > > right.temperature AS right_temperature > > FROM carside AS left > > JOIN carside AS right > > ON left.car_id=right.car_id > > WHERE > > left.side = "left" AND > > right.side = "right" > > ; > > > Many thanks, > > > Jan. > > I think the approach should look something like this: > > #----------------------------------------------- > > from sqlalchemy import * > from sqlalchemy import orm > > metadata = MetaData() > carside_table = Table( > 'carside', metadata, > Column('id', Integer, primary_key=True), > Column('car_id', Integer), > Column('side', Text), > Column('temperature', Float), > ) > > left = carside_table.alias('left') > right = carside_table.alias('right') > > tables = left.join(right, left.c.car_id == right.c.car_id) > > s = select([left.c.car_id, > left.c.temperature.label('left_temperature'), > right.c.temperature.label('right_temperature')], > from_obj=tables, > whereclause=and_(left.c.side == 'left', > right.c.side == 'right')) > > class CarSide(object): > pass > > orm.mapper(CarSide, s, primary_key=[s.c.car_id]) > > #----------------------------------------------- > > ...but it fails on the last line with the message "Mapper > Mapper|CarSide|%(3069523404 anon)s could not assemble any primary key > columns for mapped table '%(3069523404 anon)s'". I had hoped that > passing the primary_key parameter to mapper would have solved that, but > it doesn't. I'm not sure why. > > Simon --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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 -~----------~----~----~----~------~----~------~--~---