python - 使用 SQLAlchemy 添加多对多关系

Question

前提:在公共(public)评论(字符串)中搜索预定列表中的项目实例。一条评论中可以有多个列表匹配。

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

我使用SQLAlchemy (Python 3.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')

并使用以下内容来查找匹配项

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.
            Session.merge(reddit_comments(#relevant information from comment#))
            #How do I add to the Many to Many here?
            Session.commit()
        Session.close()

问题:查看上面代码片段中的评论，我不明白如何从评论['comment_body'中添加潜在的多个字符匹配的关系] 字符串正确插入 reddit_assoc 关联表中。有人可以进一步建议吗？

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()