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" <[email protected]>
wrote:
> > -----Original Message-----
> > From: [email protected]
> > [mailto:[email protected]] 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 [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
-~----------~----~----~----~------~----~------~--~---
