CODEWITHSUNDEEP
pythonsqlalchemy
Brian Yeh
Brian Yeh

Reputation: 3267

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

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:

Upvotes: 11

Views: 8127

Answers (4)

Daniel Waltrip
Daniel Waltrip

Reputation: 2610

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.

Upvotes: 6

wouter bolsterlee
wouter bolsterlee

Reputation: 4037

The proper way seems to be ForeignKeyConstraint(..., use_alter=True).

http://docs.sqlalchemy.org/en/latest/core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint.params.use_alter

Upvotes: 3

bawey
bawey

Reputation: 149

You could also sort of 'decorate' your models once they're initially defined. 

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

    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)

    Answer.question_id = Column(Integer, ForeignKey(Question.id))
    Question.possible_answers = relationship(Answer, post_update=True, primaryjoin=Question.id==Answer.question_id)

It's not too nice as the class definition starts to float around a little but it does the trick.

Upvotes: 1

Mark
Mark

Reputation: 722

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)

Upvotes: 7

Related Questions