与association_proxy的双向关系

Question

我有两个模型，分别是 Word（单词）和 Sentence（句子），它们之间是双向的多对多关系。为了存储一些额外的信息，我还创建了一个关联对象 WordInSentence。

class WordInSentence(Base):
    __tablename__ = "word_in_sentence"
    word_id = Column(Integer, ForeignKey('word.id'),
        primary_key=True)
    sentence_id = Column(Integer, ForeignKey('sentence.id'),
        primary_key=True)
    space_after = Column(String)
    tag = Column(String)
    position = Column(Integer)

    word = relationship("Word",
        backref=backref("word_sentences", lazy="dynamic"))
    sentence = relationship("Sentence",
        backref=backref("sentence_words", lazy="dynamic"))

class Sentence(Base):
    text = Column(Text, index = True)

    words = association_proxy("sentence_words", "word",
        creator=lambda word: WordInSentence(word=word))

class Word(Base):

    word = Column(String, index = True)
    sentences = association_proxy("word_sentences", "sentence",
        creator=lambda sent: WordInSentence(sentence=sent))

    def __repr__(self):
        return "<Word: " + str(self.word) + ">"

我想要能够实现这样的功能：

w = Word()
s = Sentence()
w.sentences = [s]

但是，我遇到了这样的错误：

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/plasma/project/venv/lib/python2.7/site-packages/sqlalchemy/ext/associationproxy.py", line 274, in __set__
    proxy.clear()
  File "/home/plasma/project/venv/lib/python2.7/site-packages/sqlalchemy/ext/associationproxy.py", line 629, in clear
    del self.col[0:len(self.col)]
TypeError: object of type 'AppenderBaseQuery' has no len()

我还在文档中注意到 这个例子，但我不太确定怎么才能让它变成双向的，并且是一个列表。

Answer 1

你看到的错误信息 TypeError: object of type 'AppenderBaseQuery' has no len() 是因为你的关系设置成了 lazy="dynamic"。如果你仔细查看这个对象，它其实只是一个SQL查询。这就是为什么你不能直接遍历它——它必须先执行才能得到结果。

你可以通过使用所有查询都可以用的标准函数来执行这个查询，比如在这个对象上调用 filter(<conditions>)，或者如果你想要获取所有结果，可以使用 all()。

如果你不想每次访问时都多执行一次动态查询，还有一个选择——如果关系中的子项数量不多的话，可以把 lazy 设置改为 'select'。这样的话，关联查询会和父对象的查询同时进行，这对于子项数量很大的情况来说不太实际，但对于数量较少的情况是可以接受的。这样你就可以像预期那样遍历它了。

Answer 2

希望这个代码片段能对你有所帮助。

我只是调整了一下创建的顺序，并添加了引擎和会话。

这是可以正常工作的代码。

from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm.session import Session
from sqlalchemy.sql.schema import Column, ForeignKey
from sqlalchemy.sql.sqltypes import Integer, String, Float, Date

Base = declarative_base()

class Sentence(Base):
    __tablename__ = 'sentence'

    id = Column(Integer, primary_key=True)
    text = Column(String, index = True)

    sentence_words = relationship('WordInSentence')
    words = association_proxy("sentence_words", "word")

class Word(Base):
    __tablename__ = 'word'

    id = Column(Integer, primary_key=True)
    word = Column(String, index = True)

    word_sentences = relationship('WordInSentence')
    sentences = association_proxy("word_sentences", "sentence")

    def __repr__(self):
        return "<Word: " + str(self.word) + ">"


class WordInSentence(Base):
    __tablename__ = "word_in_sentence"

    word_id = Column(Integer, ForeignKey(Word.id),
        primary_key=True)
    sentence_id = Column(Integer, ForeignKey(Sentence.id),
        primary_key=True)
    space_after = Column(String)
    tag = Column(String)
    position = Column(Integer)

    word = relationship(Word)
        #backref=backref("word_sentences", lazy="dynamic"))
    sentence = relationship(Sentence)
        #backref=backref("sentence_words", lazy="dynamic"))


engine = create_engine('sqlite://')
Base.metadata.create_all(engine)

session = Session(engine)

测试：

>>> 
>>> w = Word(word='something new')
>>> w.sentences = [Sentence(text='there is somethine new')]
>>> session.add(w)
>>> session.commit()
>>> session.query(WordInSentence).one().word_id
1
>>> session.query(Word).one().word
'something new'
>>> session.query(Sentence).one().text
'there is somethine new'
>>>