Wyatt Lee Baldwin wrote:
>
> On Sep 29, 2:00 pm, "Michael Bayer" <[email protected]> wrote:
>> Wyatt Lee Baldwin wrote:
>>
>> >> Anyway the primaryjoin here looks fine and does
>> >> represent the same thing you're getting in your route() @property.
>> >> It's
>> >> a simple many-to-one with an additional criterion. Nothing needs to
>> be
>> >> configured in the database as far as foreign keys, configuring it as
>> >> such
>> >> within the table metadata is all SQLA cares about.
>>
>> > I'm still not clear if/how I can replace the property with the
>> > relation. When I use the relation shown above, I get the big join.
>> > Sorry if I'm being daft.
>>
>> what big join ?
>
> When I define the route relation as above (2nd version) and access the
> route attribute of a Trip, I eventually get a MemoryError. I assume
> this is because of a join. When I use the property version, accessing
> route is speedy. Here is the SQL that's being generated from the
> relation (inside a `paster shell` session):
>
> In [14]: trip = sess.query(Trip).first()
>
> In [15]: trip.route_number
> Out[15]: 925
>
> In [16]: trip.route_begin_date
> Out[16]: datetime.date(2007, 5, 13)
>
> In [17]: trip.route
> 14:40:00,356 INFO [trimet.db.TriMetEngine.0x...2e8c] SELECT
> trans.route_def.route_number AS trans_route_def_route_number,
> trans.route_def.route_begin_date AS trans_route_def_route_be_1,
> trans.route_def.route_end_date AS trans_route_def_route_end_date,
> trans.route_def.public_route_description AS
> trans_route_def_public_r_2, trans.route_def.route_type AS
> trans_route_def_route_type, trans.route_def.route_sub_type AS
> trans_route_def_route_sub_type, trans.route_def.route_usage AS
> trans_route_def_route_usage, trans.route_def.route_sort_order AS
> trans_route_def_route_so_3
> FROM trans.route_def, trans.trip
> WHERE :param_1 = trans.route_def.route_number AND
> trans.trip.route_begin_date >= trans.route_def.route_begin_date AND
> trans.trip.route_begin_date <= trans.route_def.route_end_date ORDER BY
> route_sort_order
> 14:40:00,356 INFO [trimet.db.TriMetEngine.0x...2e8c] {'param_1': 925}
> 14:40:01,379 DEBUG [trimet.db.TriMetEngine.0x...2e8c] Col
> ('TRANS_ROUTE_DEF_ROUTE_NUMBER', 'TRANS_ROUTE_DEF_ROUTE_BE_1',
> 'TRANS_ROUTE_DEF_ROUTE_END_DATE', 'TRANS_ROUTE_DEF_PUBLIC_R_2',
> 'TRANS_ROUTE_DEF_ROUTE_TYPE', 'TRANS_ROUTE_DEF_ROUTE_SUB_TYPE',
> 'TRANS_ROUTE_DEF_ROUTE_USAGE', 'TRANS_ROUTE_DEF_ROUTE_SO_3')
>
> After a delay, with `engine.echo = True`, this starts spewing out
> thousands of trans.route_def records and eventually dies. Why is a
> literal value (:param_1 = 925) substituted for route_number while the
> column name (trans.trip.route_begin_date) is substituted for
> route_begin_date?
so, first you debug the join condition using
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.orm').setLevel(logging.INFO)
and then look for
INFO:sqlalchemy.orm.strategies.LazyLoader:Trip.route lazy loading clause
:param_1 = routedef.route_number AND trip.route_begin_date >=
routedef.route_begin_date AND trip.route_begin_date <=
routedef.route_end_date
which is wrong. then you ply it with arguments to convince it that the
comparisons are part of what it should be parameterizing.
the most direct way is:
remote_side=[RouteDef.route_number, RouteDef.route_begin_date,
RouteDef.route_end_date]
which will force it to figure out (local, remote) based on those columns
against the primaryjoin, to determine the "local remote pairs". you
should probably do it this way.
Another way is:
viewonly=True,
foreign_keys=[route_number, route_begin_date]
this one has it look for columns have dependent values based on what is
considered a foreign key. the "viewonly" flag means we don't have to
worry about populating those columns during flush, so it matches <= and >=
operators. then the "local remote pairs" is taken from that. not as
direct. But this relation() should probably have viewonly=True in any
case.
the ultimate way, which I haven't made very public as of yet, is to
totally spell out the local remote pairs. This doesn't play nicely with
declarative as of yet. I'm not sure yet what to do with this option
since it is the most explicit (so maybe less confusing) way, but then its
very verbose:
foreign_keys=[route_number, route_begin_date],
_local_remote_pairs = [
(route_number, RouteDef.__table__.c.route_number),
(route_begin_date, RouteDef.__table__.c.route_begin_date),
(route_begin_date, RouteDef.__table__.c.route_end_date)
]
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
