> -----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
-~----------~----~----~----~------~----~------~--~---
