How do I get rid of a circular dependency error while creating a database in sqlalchemy?

Question

I'm new at using sqlalchemy. How do I get rid of a circular dependency error for the tables shown below. Basically my goal is to create A question table with a one to one relationship "best answer" to answer and a one to many relationship "possible_answers" as well.

class Answer(Base):
    __tablename__ = 'answers'
    id = Column(Integer, primary_key=True)
    text = Column(String)

    question_id = Column(Integer, ForeignKey('questions.id'))

    def __init__(self, text, question_id):
        self.text = text

    def __repr__(self):
        return "<Answer '%s'>" % self.text

class Question(Base):
    __tablename__ = 'questions'

    id = Column(Integer, primary_key=True)
    text = Column(String)
    picture = Column(String)
    depth = Column(Integer)
    amount_of_tasks = Column(Integer)
    voting_threshold = Column(Integer)
    best_answer_id = Column(Integer, ForeignKey('answers.id'), nullable=True)

    possible_answers = relationship("Answer", post_update=True, primaryjoin = id==Answer.question_id)

    def __init__(self, text, picture, depth, amount_of_tasks):
        self.text = text
        self.picture = picture
        self.depth = depth
        self.amount_of_tasks = amount_of_tasks

    def __repr__(self):
        return "<Question, '%s', '%s', '%s', '%s'>" % (self.text, self.picture, self.depth, self.amount_of_tasks)

    def __repr__(self):
        return "<Answer '%s'>" % self.text

This is the error message: CircularDependencyError: Circular dependency detected. Cycles:

Answer 1

Apparently SQLAlchemy does not play well with circular dependencies. You might consider using an association table instead to represent the best answer...

from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy import Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

engine = create_engine('sqlite:///:memory:')
Base = declarative_base()


class Answer(Base):
    __tablename__ = 'answer'

    id = Column(Integer, primary_key=True)
    question_id = Column(Integer, ForeignKey('question.id'))
    text = Column(String)

    question = relationship('Question', backref='answers')

    def __repr__(self):
        return "<Answer '%s'>" % self.text


class Question(Base):
    __tablename__ = 'question'

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

    best_answer = relationship('Answer',
                               secondary=lambda: best_answer,
                               uselist=False)

    def __repr__(self):
        return "<Question, '%s'>" % (self.text)

best_answer = Table('best_answer', Base.metadata,
                    Column('question_id',
                           Integer,
                           ForeignKey('question.id'),
                           primary_key=True),
                    Column('answer_id',
                           Integer,
                           ForeignKey('answer.id'))
                    )


if __name__ == '__main__':

    session = sessionmaker(bind=engine)()
    Base.metadata.create_all(engine)

    question = Question(text='How good is SQLAlchemy?')

    somewhat = Answer(text='Somewhat good')
    very = Answer(text='Very good')
    excellent = Answer(text='Excellent!')

    question.answers.extend([somewhat, very, excellent])
    question.best_answer = excellent

    session.add(question)
    session.commit()

    question = session.query(Question).first()

    print(question.answers)
    print(question.best_answer)

Answer 2

Mark's solution works, but I wanted to find a way to do it without creating an additional table. After extensive searching, I finally found this example in the docs:

http://docs.sqlalchemy.org/en/latest/orm/relationship_persistence.html (the 2nd example)

The approach is to use primaryjoin [1] on both relationships in the Question model, and to add post_update=True on one of them. The post_update tells sqlalchemy to set best_answer_id as an additional UPDATE statement, getting around the circular dependency.

You also need foreign_keys specified on the question relationship in the Answer model.

Below is Mark's code modified to follow the linked example above. I tested it with sqlalchemy v1.1.9.

from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

engine = create_engine('sqlite:///:memory:')
Base = declarative_base()

class Answer(Base):
    __tablename__ = 'answer'
    id = Column(Integer, primary_key=True)
    text = Column(String)
    question_id = Column(Integer, ForeignKey('question.id'))
    question = relationship('Question', back_populates='answers', foreign_keys=[question_id])

    def __repr__(self):
        return "<Answer '%s'>" % self.text

class Question(Base):
    __tablename__ = 'question'
    id = Column(Integer, primary_key=True)
    text = Column(String)
    best_answer_id = Column(Integer, ForeignKey('answer.id'))
    answers     = relationship('Answer', primaryjoin= id==Answer.question_id)
    best_answer = relationship('Answer', primaryjoin= best_answer_id==Answer.id, post_update=True)

    def __repr__(self):
        return "<Question, '%s'>" % (self.text)

if __name__ == '__main__':

    session = sessionmaker(bind=engine)()
    Base.metadata.create_all(engine)

    question = Question(text='How good is SQLAlchemy?')

    somewhat = Answer(text='Somewhat good')
    very = Answer(text='Very good')
    excellent = Answer(text='Excellent!')

    question.answers.extend([somewhat, very, excellent])
    question.best_answer = excellent

    session.add(question)
    session.commit()

    question = session.query(Question).first()

    print(question.answers)
    print(question.best_answer)

[1] Interestingly, the "string format" for primaryjoin seems to cause an error -- but constructing the SQL expression with the overloaded operators on the column objects works.