Hi, Running python 3.3.4, pyramid 1.5b1, sqlalchemy 0.9.3.
A couple of months ago Mike helped to set up composite association proxies in my model ( https://groups.google.com/forum/#!searchin/sqlalchemy/composite$20association$20object/sqlalchemy/kxU-FaDGO2Q/b8ScnTXvPyIJ). I'm applying this pattern to a different project now, but running into problems when using a list comprehension to create a list of dict objects from the results of a query. I suspect the problem stems from (a) two association proxies using the same attribute mapped collection or (b) garbage collection creating stale association proxies... The basic model looks something like this: class User(Base): __tablename__ = 'users' # Columns id = Column(Integer, primary_key=True) name = Column(VARCHAR(50), nullable=False) # Relationships interests = association_proxy( 'user_interests', 'value', creator=lambda k, v: UserInterest(interest_name=k, value=v) ) ... class Occupation(Base): __tablename__ = 'occupations' # Columns id = Column(Numeric(8, 2), primary_key=True) title = Column(VARCHAR(150), nullable=False) # Relationships interests = association_proxy( 'occupation_interests', 'value', creator=lambda k, v: OccupationInterest(interest_name=k, value=v) ) ... class Interest(Base): __tablename__ = 'interests' # Columns id = Column(Integer, primary_key=True) name = Column(String, unique=True) # Composite association proxies linking the tables class UserInterest(Base): __tablename__ = 'user_interests' # Columns user_id = Column(ForeignKey('users.id'), primary_key=True) interest_id = Column(ForeignKey('interests.id'), primary_key=True) value = Column(Numeric(3, 2)) # Relationships user = relationship( 'User', backref=backref( 'user_interests', collection_class=attribute_mapped_collection('interest_name'), cascade='all, delete-orphan' ) ) interest = relationship('Interest') def __init__(self, interest_name, value): self._interest_name = interest_name self.value = value @property def interest_name(self): if self.interest is not None: return self.interest.name else: return self._interest_name ... class OccupationInterest(Base): __tablename__ = 'occupation_interests' # Columns occupation_id = Column(ForeignKey('occupations.id'), primary_key=True) interest_id = Column(ForeignKey('interests.id'), primary_key=True) value = Column(Numeric(3, 2)) # Relationships occupation = relationship( 'Occupation', backref=backref( 'occupation_interests', collection_class=attribute_mapped_collection('interest_name'), cascade='all, delete-orphan' ) ) interest = relationship('Interest') def __init__(self, interest_name, value): self._interest_name = interest_name self.value = value @property def interest_name(self): if self.interest is not None: return self.interest.name else: return self._interest_name ... This works fine. Objects of the Occupation class are rated against the same interest scale as objects of the User class, and I can get the objects' interest values with user.interests['Social'] or occupation.interests['Social']. If I query my database for a list of Occupation objects, I can use a list comprehension to create a list of dict objects without issues. However, when I added a method to calculate the euclidean distance between a User object and an Occupation object, I ran into KeyErrors. user = User.from_request(request) interests = DBSession.query(Interest).order_by(Interest.id).all() occupations = DBSession.query(Occupation).limit(10) def get_distance(occupation, user): d_max = Decimal(math.sqrt(6 ** 3)) d_squared = 0 for i in interests: d_squared += (user.interests[i.name] - occupation.interests[i.name]) ** 2 d = Decimal(math.sqrt(d_squared)) return (d_max - d) / d_max distances = [ dict( name=o.title, distance=get_distance(o, user), ) for o in occupations Traceback (most recent call last): File "/lib/python3.3/site-packages/pyramid_debugtoolbar/toolbar.py", line 172, in toolbar_tween response = _handler(request) File "/lib/python3.3/site-packages/pyramid_debugtoolbar/panels/performance.py", line 55, in resource_timer_handler result = handler(request) File "/lib/python3.3/site-packages/pyramid/tweens.py", line 21, in excview_tween response = handler(request) File "/lib/python3.3/site-packages/pyramid_tm/__init__.py", line 82, in tm_tween reraise(*exc_info) File "/lib/python3.3/site-packages/pyramid_tm/compat.py", line 13, in reraise raise value File "/lib/python3.3/site-packages/pyramid_tm/__init__.py", line 63, in tm_tween response = handler(request) File "/lib/python3.3/site-packages/pyramid/router.py", line 163, in handle_request response = view_callable(context, request) File "/lib/python3.3/site-packages/pyramid/config/views.py", line 329, in attr_view return view(context, request) File "/lib/python3.3/site-packages/pyramid/config/views.py", line 305, in predicate_wrapper return view(context, request) File "/lib/python3.3/site-packages/pyramid/config/views.py", line 245, in _secured_view return view(context, request) File "/lib/python3.3/site-packages/pyramid/config/views.py", line 355, in rendered_view result = view(context, request) File "/lib/python3.3/site-packages/pyramid/config/views.py", line 501, in _requestonly_view response = view(request) File "/projects/App/app/views.py", line 252, in results ) for o in occupations File "/projects/App/app/views.py", line 252, in <listcomp> ) for o in occupations File "/projects/App/app/views.py", line 240, in get_distance d_squared += (user.interests[i.name] - occupation.interests[i.name]) ** 2 File "/lib/python3.3/site-packages/sqlalchemy/ext/associationproxy.py", line 722, in __getitem__ return self._get(self.col[key]) KeyError: 'Social' Now, when I would previously get the interest values for an Occupation object (via o.interests where 'o' is an Occupation object) the result would look like this: {'Artistic': Decimal('4.33'), 'Enterprising': Decimal('1.67'), 'Social': Decimal('1.00'), 'Realistic': Decimal('5.00'), 'Investigative': Decimal('7.00'), 'Conventional': Decimal('4.33')} But when I open an interactive python shell in the Pyramid DebugToolbar, o.interests yields an empty object: {}. When I removed the list comprehension and calculated the 'distance' between a single User object and Occupation object, there were no issues. Is this error the result of both association proxies using the same attribute mapped collection ('interest_name')? Garbage collection during the get_distance method or list comprehension? I'd appreciate any suggestions -- thanks. -Brian -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
