pythonsqlalchemy

SqlAlchemy - How to define a foreign key column using model class instead of physical table name

In brief

I want to use model class to define a foreign-key column.

In full

Normally we define column which is a foreign key via physical table name e.g. guided here

author_id = db.Column(db.Integer, db.ForeignKey('author.id'))

The phrase ForeignKey('author.id') helps define column author_id as foreign key column - it refers to talble author where author is the table name.

I want to use model class name i.e.

author_id = db.Column(db.Integer, db.ForeignKey(Author.id))

But this code gets error

Could not determine join condition between parent/child tables on relationship XX.YYY - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

How can we get to it?

Solution

  • In brief

    Full details

    ForeignKey accepts column as first argument which can be of type Column or string in format schema_name.table_name.column_name or table_name.column_name. Columns that you define in declarative model turn to InstumentedAttribute objects. That is why db.ForeignKey(Author.id) leads to an error. You can access actual column via __table__ attribute of a model:

    author_id = db.Column(db.Integer, db.ForeignKey(Author.__table__.c['id']))

    or

    author_id = db.Column(db.Integer, db.ForeignKey(Author.__table__.c.id))

    If you need to define self-referencing foreign key you can simply pass the name of column. While declaration of a model is not finished yet it still has Column type:

    id = db.Column(db.Integer, primary_key=True)
parent_id = db.Column(db.Integer, db.ForeignKey(id))

    Note that you CANNOT define foreign key this way:

    author_id = db.Column(db.Integer, db.ForeignKey('Author.id'))

    You need to specify physical table name, mapping class name won't work for it.