SqlAlchemy - defining a foreign key column using a model class instead of the name of a physical table

Question

Briefly

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

My google search on this topic does not help, so I asked here.

In full

Usually we define a column that is a foreign key through the physical name of the table, for example. here

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

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

I want to use the class name of the model ie

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

But this code gets an error

Failed to determine join condition between parent / child tables in relation to XX.YYY - there are no foreign keys connecting these tables. Make sure the column references are linked to ForeignKey or ForeignKeyConstraint or specify the expression 'primaryjoin'.

How can we get to it?

Answer 1

Briefly

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

• From the author’s 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 information

ForeignKey takes a column as the first argument, which can be of type Column or a string in the format schema_name.table_name.column_name or table_name.column_name . The columns that you define in the declarative model refer to InstumentedAttribute objects. This is why db.ForeignKey(Author.id) leads to an error. You can access the actual column using the __table__ attribute of the 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 a foreign key for a self-referencing, you can simply pass the column name. While the model declaration is not yet complete, it still has a Column type:

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

Note that you CANNOT define a foreign key as follows:

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

You need to specify the physical name of the table, the name of the mapping class will not work.