On Sep 29, 2:00 pm, "Michael Bayer" <mike...@zzzcomputing.com> 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?
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
