SQLAlchemy中UNIQUE约束失败

Question

我有两个简单的类，用于SQLAlchemy：

papers2authors_table = Table('papers2authors', Base.metadata,
    Column('paper_id', Integer, ForeignKey('papers.id')),
    Column('author_id', Integer, ForeignKey('authors.id'))
)

class Paper(Base):
    __tablename__ = "papers"

    id = Column(Integer, primary_key=True)
    title = Column(String)
    handle = Column(String)

    authors = relationship("Author",
                    secondary="papers2authors",
                    backref="papers")

class Author(Base):
    __tablename__ = "authors"

    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)
    code = Column(String, unique=True)

然后我在其他地方运行初始化：

    engine = create_engine('sqlite:///' + REPECI_DB, echo=True)
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    self.s = session

接着我尝试往 papers 和 authors 中添加项目：

    paper = Paper()
    for line in lines: # the data is a sequence of lines "key: value" with few papers per file
        br = line.find(':')
        k = line[:br]
        v = line[br+1:].strip()

        if k == "Title":
            paper.title = v
        elif k == "Year":
            paper.year = v
        elif k == "Author-Name":
            try:
                self.s.begin_nested()
                author = Author(name=v)
            except IntegrityError:
                print("Duplicate author")
                self.s.rollback()
                author = self.s.query(Author).filter(Author.name==v).first()
            else:
                self.s.commit()
            paper.authors.append(author)
        elif k == "Handle": # this appears in the end of a paper's record
            paper.handle = v
            self.s.add(paper)
            self.s.commit()
            paper = Paper()

但是在添加作者的时候出现了问题。在往表里添加了一些作者后，我遇到了 (<class 'sqlalchemy.exc.IntegrityError'>, IntegrityError('(IntegrityError) UNIQUE constraint failed: authors.name',), None) 这个错误。与此同时，数据库里大约只有50个作者和一篇文章，而我处理的行中，作者的数量大约是文章的两倍。这意味着脚本根本没有添加这些作者。

我尝试按照这里的建议重写代码，但错误依然出现。

Answer 1

我找到了一种我不太喜欢的解决办法，不过它确实有效。把这个：

        try:
            self.s.begin_nested()
            author = Author(name=v)
        except IntegrityError:
            print("Duplicate author")
            self.s.rollback()
            author = self.s.query(Author).filter(Author.name==v).first()
        else:
            self.s.commit()
        paper.authors.append(author)

换成这个：

        author = self.s.query(Author).filter(Author.name==v).first()
        if author is None:
            author = Author(name=v)

        paper.authors.append(author)