Re: [Sqlalchemy-tickets] [sqlalchemy] #2809: Strange effect with association proxy and event listener

[sqlalchemy <mi...@zz...>]

#2809: Strange effect with association proxy and event listener
-----------------------------------+------------------------------------
      Reporter:  schlamar          |      Owner:  zzzeek
          Type:  defect            |     Status:  reopened
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  (none)            |   Severity:  no triage selected yet
    Resolution:                    |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+------------------------------------
Changes (by schlamar):

 * status:  closed => reopened
 * resolution:  worksforme =>


Comment:

 Finally I was able to find a common test case for this issue. It looks
 like that the combination of association proxy + event listening on the
 associated attribute has the effect that you have an inconsistent state
 while in the event handler which causes any flush to fail.


 {{{
 #!python


 from sqlalchemy import (create_engine, event, inspect, Column, Integer,
                         ForeignKey, String)
 from sqlalchemy.orm import relationship, sessionmaker

 from sqlalchemy.ext.associationproxy import association_proxy
 from sqlalchemy.ext.declarative import declarative_base


 Base = declarative_base()


 class User(Base):
     __tablename__ = 'user'
     id = Column(Integer, primary_key=True)
     name = Column(String(64))
     keywords = association_proxy('user_keywords', 'keyword')

     user_keywords = relationship('UserKeyword', cascade='all, delete-
 orphan',
                                  backref='user')


 @event.listens_for(User.user_keywords, 'append')
 def keywords_added(user, value, initiator):
     session = inspect(user).session
     if session:
         session.flush()


 class UserKeyword(Base):
     __tablename__ = 'user_keyword'
     user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
     keyword_id = Column(Integer, ForeignKey('keyword.id'),
 primary_key=True)

     def __init__(self, keyword=None, **kwargs):
         super(UserKeyword, self).__init__(keyword=keyword, **kwargs)


 class Keyword(Base):
     __tablename__ = 'keyword'
     id = Column(Integer, primary_key=True)
     keyword = Column(String(64))

     user_keywords = relationship(
         'UserKeyword', cascade='all, delete-orphan',
         backref='keyword')


 engine = create_engine('sqlite:///:memory:')
 Base.metadata.create_all(engine)
 Session = sessionmaker(bind=engine)
 session = Session()

 user = User(name='xx')
 user.keywords.append(Keyword(keyword='x'))
 session.add(user)
 session.commit()

 session = Session()

 user = session.query(User).first()
 for kw in session.query(Keyword):
     user.keywords.append(kw)

 }}}

 Result:

 {{{
 #!pytb
 $ python sqla_2809.py
 Traceback (most recent call last):
   File "sqla_2809.py", line 63, in <module>
     user.keywords.append(kw)
   File
 "d:\Projekte\HACS\hacs\packages\sqlalchemy\ext\associationproxy.py", line
 568, in append
     self.col.append(item)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\collections.py",
 line 1057, in append
     item = __set(self, item, _sa_initiator)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\collections.py",
 line 1029, in __set
     item = getattr(executor, 'fire_append_event')(item, _sa_initiator)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\collections.py",
 line 733, in fire_append_event
     item, initiator)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\attributes.py", line
 910, in fire_append_event
     value = fn(state, value, initiator or self)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\events.py", line
 1496, in wrap
     orig_fn(target, value, *arg)
   File "sqla_2809.py", line 27, in keywords_added
     session.flush()
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\session.py", line
 1818, in flush
     self._flush(objects)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\session.py", line
 1936, in _flush
     transaction.rollback(_capture_exception=True)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\util\langhelpers.py",
 line 58, in __exit__
     compat.reraise(exc_type, exc_value, exc_tb)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\session.py", line
 1900, in _flush
     flush_context.execute()
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\unitofwork.py", line
 372, in execute
     rec.execute(self)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\unitofwork.py", line
 525, in execute
     uow
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\persistence.py",
 line 64, in save_obj
     table, insert)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\orm\persistence.py",
 line 569, in _emit_insert_statements
     execute(statement, params)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\engine\base.py", line
 662, in execute
     params)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\engine\base.py", line
 761, in _execute_clauseelement
     compiled_sql, distilled_params
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\engine\base.py", line
 874, in _execute_context
     context)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\engine\base.py", line
 1024, in _handle_dbapi_exception
     exc_info
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\util\compat.py", line
 195, in raise_from_cause
     reraise(type(exception), exception, tb=exc_tb)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\engine\base.py", line
 867, in _execute_context
     context)
   File "d:\Projekte\HACS\hacs\packages\sqlalchemy\engine\default.py", line
 324, in do_execute
     cursor.execute(statement, parameters)
 sqlalchemy.exc.IntegrityError: (IntegrityError) user_keyword.user_id may
 not be NULL u'INSERT INTO user_keyword (keyword
 _id) VALUES (?)' (1,)

 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2809#comment:4>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python