使用SQLAlchemy添加多对多关系

Question

前提：在公共注释（字符串）中搜索预定列表中的项目实例。一个注释中可以有多个列表匹配项。在

我试图使用多对多的结构来跟踪这一点。在

我使用SQLAlchemy（python3.5）创建了以下数据库结构

reddit_assoc = Table('reddit_assoc', Base.metadata,
    Column('comment_id', Integer, ForeignKey('reddit_comments.comment_id')),
    Column('character_id', Integer, ForeignKey('characters.character_id'))
    )

class characters(Base):
    __tablename__ ='characters'

    character_id = Column(VARCHAR(20),primary_key=True)
    name = Column(VARCHAR(3072))
    added = Column('added', DateTime, default=datetime.datetime.now())
    reddit_mentions = relationship('reddit_comments', secondary='reddit_assoc', back_populates='character_mentions')

class reddit_comments(Base):
    __tablename__ = 'reddit_comments'
    comment_id = Column(VARCHAR(50), primary_key=True)
    comment_author = Column(VARCHAR(300))
    comment_created = Column(VARCHAR(300))
    link_id = Column(VARCHAR(50))
    subreddit_id = Column(VARCHAR(50))
    character_mentions = relationship('characters', secondary='reddit_assoc', back_populates='reddit_comments')

然后用下面的方法找到匹配项

^{pr2}$

问题：查看上述片段中的注释，我不明白如何将可能多个字符匹配的关系从comment['comment_body']字符串正确添加到reddit_assocassocation表中。有人能给我进一步的建议吗？在

Answer 1

在本例中使用的关系表现为一个列表。所以您需要将新创建的reddit注释添加到列表reddit_mentions。在

def char_counter(comment):
    Session = DBSession()
    reader = Session.query(characters).all()

    for char in reader:
        if char[0] in comment['comment_body'] or char[1] in comment['comment_body']:
            # We have a match. Add to database.
            rc = reddit_comments(#relevant information from comment#)
            Session.flush()  # to ensure you have primary key, although may not be needed
            char.reddit_mentions.append(rc)  # this will eventually fill your reddit_assoc table
            Session.add(char)

    # move this outside of loop        
    Session.commit()
    Session.close()