Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

python - 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.

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...