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

Question

In brief

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

My google search on this topic is not helpful so I asked here.

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?

Answer 1

In brief

• From model other than Author
  author_id = db.Column(db.Integer, db.ForeignKey(Author.__table__.c.id))

• From Author model itself i.e. self-referential - just list the column name
  author_id = db.Column(db.Integer, db.ForeignKey(id))

• CANNOT use string value
  author_id = db.Column(db.Integer, db.ForeignKey('Author.id'))

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.