CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemycircular-dependencyflask-migrate
Saqib Ali
Saqib Ali

Reputation: 12545

How to prevent this circular reference in Flask/SQLAlchemy Models?

I have the following four model classes in my Flask/SQLAlchemy application (a fifth class MyClassA is not shown here but is reference):

class MyClassF(db.Model):
    valid_fs = ['F1', 'F2']
    f_id = db.Column(db.Integer, nullable=False, primary_key=True)
    name = db.Column(db.Enum(*valid_fs, name='f_enum'), default='F1', nullable=False)

class MyClassD(db.Model):
    d_id = db.Column(db.Integer, nullable=False, primary_key=True)
    name = db.Column(db.String(128))
    v_collection = db.relationship('MyClassV', lazy='dynamic', backref=db.backref('d'), cascade='all, delete-orphan')

class MyClassV(db.Model):
    v_id = db.Column(db.Integer, nullable=False, primary_key=True)
    d_id = db.Column(db.Integer, db.ForeignKey(MyClassD.d_id), nullable=False)
    c_collection = db.relationship('MyClassC', lazy='dynamic', backref=db.backref('v'), cascade='all, delete-orphan')
    a_collection = db.relationship('MyClassA', lazy='dynamic', backref=db.backref('v'), cascade='all, delete-orphan')

class MyClassC(db.Model):
    c_id = db.Column(db.Integer, nullable=False, primary_key=True)
    v_id = db.Column(db.Integer, db.ForeignKey(MyClassV.v_id), nullable=False)
    f_id = db.Column(
        db.Integer,
        db.ForeignKey(MyClassF.f_id),
        nullable=False,
        #default=MyClassF.query.filter(MyClassF.name == "F1").one().f_id
    )

Creating this schema using the Flask-Migrate command db init, db migrate and db upgrade works just fine.

However, when I un-comment the line the definition of MyClassC.f_id and try it again (after removing the migrations directory), I get the following circular dependency error:

sqlalchemy.exc.InvalidRequestError: When initializing mapper Mapper|MyClassV|my_classV, expression 'MyClassC' failed to locate a name ("name 'MyClassC' is not defined"). If this is a class name, consider adding this relationship() to the class after both dependent classes have been defined.

All I'm trying to do is ensure that the default value of MyClassC.f_id is set by querying the MyClassF table. This check should occur at insertion time -- not when the database is being created. So I don't understand why I am getting this error now.

How can I use db.relationship() (or any other technique) to get around this circular dependency error while enforcing the database integrity rule I'm trying to implement?

Upvotes: 1

Views: 1063

Answers (1)

Bakuriu
Bakuriu

Reputation: 101919

I believe the default parameter is translated to the SQL DEFAULT constraint. AFAICT this constraint is not evaluated at insertion time, but when creating the table and the value is then used as a default (which means the default value is set once and forall at creation of the table and used for all rows that lack that column, it cannot dynamically change according to the contents of an other table).

However the documentation of SQLAlchemy mentions the fact that you can pass in a python function as default value and it will be called for each insert to obtain the default value to use, so you can do:

class MyClassC(db.Model):
    c_id = db.Column(db.Integer, nullable=False, primary_key=True)
    v_id = db.Column(db.Integer, db.ForeignKey(MyClassV.v_id), nullable=False)
    f_id = db.Column(
        db.Integer,
        db.ForeignKey(MyClassF.f_id),
        nullable=False,
        default=lambda: MyClassF.query.filter(MyClassF.name == "F1").one().f_id
    )

Note however that this will not use any database facility to provide the default value, it's SQLAlchemy first obtaining the default value and then manually inserting it in your queries.

Upvotes: 3

Related Questions