SQL 炼金术关联代理无法双向工作

Question

以SQL Alchemy文档中的多对多association_proxy为例，我尝试制作它，以便它可以双向工作。我的目标是建立模型，这样当我删除一个关键字时，它与用户的所有关联都将从关联表中清除。下面是我正在玩的代码：

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref
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')
    def __init__(self, name):
        self.name = name
    def __repr__(self):
        return 'User(%s)' % repr(self.name)
class Keyword(Base):
    __tablename__ = 'keyword'
    id = Column(Integer, primary_key=True)
    keyword = Column('keyword', String(64))
    users = association_proxy('keyword_users', 'user')
    def __init__(self, keyword):
        self.keyword = keyword
    def __repr__(self):
        return 'Keyword(%s)' % repr(self.keyword)
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)
    special_key = Column(String(50))
    user = relationship(User,
                        backref=backref('user_keywords',
                                        cascade='all, delete-orphan'))
    keyword = relationship(Keyword,
                           backref=backref('keyword_users',
                                           cascade ='all, delete-orphan'))
    def __init__(self, keyword=None, user=None, special_key=None):
        self.user = user
        self.keyword = keyword
        self.special_key = special_key
    def __repr__(self):
        return "UserKeyword({0}, {1}) Special: {2}".format(user.name,
                                                           keyword.keyword,
                                                           self.special_key)

测试：

from sqlalchemy import create_engine
engine = create_engine('sqlite://')
from sqlalchemy.orm import sessionmaker
session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)
s = session()
bob = User('Bob')
yay = Keyword('yay')
argh = Keyword('argh')
print("Everything: {0}, {1}, {2}".format(bob, yay, argh))
bob.keywords.append(yay)
print("{0} now has users {1}".format(yay, yay.users))
print("{0} now has users {1}".format(argh, argh.users))
argh.users.append(bob)

这是我运行它时看到的输出：

Players: User('Bob'), Keyword('yay'), Keyword('argh')
Keyword('yay') now has users [User('Bob')]
Keyword('argh') now has users []
Traceback (most recent call last):
  File "assoc_proxy.py", line 78, in <module>
    argh.users.append(bob)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/ext/associationproxy.py", line 595, in append
    item = self._create(value)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/ext/associationproxy.py", line 522, in _create
    return self.creator(value)
  File "<string>", line 4, in __init__
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/state.py", line 196, in _initialize_instance
    return manager.original_init(*mixed[1:], **kwargs)
  File "assoc_proxy.py", line 50, in __init__
    self.keyword = keyword
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 220, in __set__
    instance_dict(instance), value, None)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 780, in set
    value = self.fire_replace_event(state, dict_, value, old, initiator)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 801, in fire_replace_event
    value = fn(state, value, previous, initiator or self._replace_token)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 1101, in emit_backref_from_scalar_set_event
    child_impl = child_state.manager[key].impl
KeyError: 'keyword_users'

将断点放在orm/attributes.py的线1101上，我看到child_state.manager交替地携带keyword_users密钥而不携带。是否每个关联表只允许association_proxy一次？

Answer 1

我相信这是因为您没有一个名为keyword_users的表。它应该是"user_keyword"，这是关联表的名称。