Saqib Ali Saqib Ali - 1 month ago 34
Python Question

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?

Answer

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.