Michael Chambliss wrote:
> Hey Michael - thanks for the patient and helpful response. I played
> around with the from_statement() approach earlier today, but what I was
> able to derive seemed to follow the standard model of define table,
> define class, map table to class, execute query. That approach would be
> great assuming I can map to some composite result (IE,
> multi-table/function). Perhaps I need to dive further into this to
> determine how joins are handled and how the mapping should be defined
> for them. The original example I linked seemed to imply some mapping
> magic in that the "Customer" class wasn't defined but was mapped to the
> complex "Selectable". However, my research and attempts to do this
> mapping with from_statement() proved fruitless.
>
> In a theoretical example, say I have a CAR table that refers to both a
> CAR_TYPE table and CAR_ATTRIBUTES table. CAR_TYPE is simply an
> enumeration for a static list of types, and CAR_ATTRIBUTES is an
> arbitrarily long list of key,value attributes (color, weight, top speed,
> etc). So, ultimately, a "Car" is made up of these three.
>
> I'd want to bake all of these together, passing in a CAR.ID (primary
> key) to map to a Car instance. I prefer to live in SQL because I'm
> pretty good at it, and I need to reference, specifically, Oracle Spatial
> and Workspace functions. I do not, however, need to chain additional
> filters off of this, handle updates/inserts (at least at this point),
> etc. I'm literally just looking for a "cheap" way to map a row to an
> object and scoop up connection pooling, type handling, and other great
> things I'll probably learn about as I go.
Assuming you've configured Car, CarType and CarAttributes with mappers,
and associated them all together using relation(), and the general usage
is hypothetically along these lines:
sess.query(Car).from_statement("select car.*, car_attributes.*, car_type.*
from ....")
you can route the individual columns into attributes and collections on
each Car object using contains_eager(). the naming convention here is a
little tedious, but you want to go with raw SQL so here you go, assuming
each table had "id" and "name" columns, substitute the actual names..
sess.query(Car).from_statement("""
select car.id as car_id, car.name as car_name,
car_attributes.id as car_att_id, car_attributes.name as car_att_name,
car_type.id as car_type_id, car_type.name as car_type_name
from ....
""").options(
contains_eager(Car.type, alias='car_type'),
contains_eager(Car.attributes, alias='car_att')
).all()
havent tried it though.
--
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.
