CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
Dirty Penguin
Dirty Penguin

Reputation: 4402

Flask-SQLAlchemy: there are no foreign keys linking these tables

I'm using Flask-SQLAlchemy with the following models.py:

class DomainRoot(db.Model):
    # Int/Float fields
    id      = db.Column(db.Integer, primary_key=True)

    # String fields
    domain  = db.Column(db.String(64), index=True, unique=True)

    # DateTime fields
    created = db.Column(db.DateTime)
    updated = db.Column(db.DateTime)

    # ForeignKey relationships
    domain_paths = db.relationship('DomainPath', backref='domain_root', lazy='dynamic')

    def __repr__(self):
        return '<DomainRoot %r>' % (self.domain)

class DomainPath(db.Model):
    # Int/Float fields
    id          = db.Column(db.Integer, primary_key=True)

    # String fields
    domain_path = db.Column(db.String(256), index=True, unique=True)

    # DateTime fields
    created     = db.Column(db.DateTime)
    updated     = db.Column(db.DateTime)

    # ForeignKey fields
    domainroot_id = db.Column(db.Integer, db.ForeignKey('domainroot.id'))

    def __repr__(self):
        return '<DomainPath %r>' % (self.domain_path)

When I try to query the DomainRoot object, I get an error:

InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not determine join condition between parent/child tables on relationship DomainRoot.domain_paths - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

As far as I can tell, I have added the proper foreign key fields.

What am I doing wrong here?

Upvotes: 3

Views: 10219

Answers (1)

davidism
davidism

Reputation: 127190

Flask-SQLAlchemy autogenerates the table name domain_root, with an underscore. You refer to domainroot without the underscore.

In general it's easier to define the fk and relationship in the same model, and avoid using string references.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy(app)

class DomainRoot(db.Model):
    id = db.Column(db.Integer, primary_key=True)

class DomainPath(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    root_id = db.Column(db.ForeignKey(DomainRoot.id))
    root = db.relationship(DomainRoot, backref='paths')

db.create_all()
db.session.add(DomainRoot(paths=[DomainPath(), DomainPath()]))
db.session.commit()
print(DomainRoot.query.get(1).paths)

[<__main__.DomainPath object at 0x7fc27f443f28>, <__main__.DomainPath object at 0x7fc27f443fd0>]

Upvotes: 6

Related Questions