SqlAlchemy - 如何使用模型类而不是物理表名定义外键列 [英] SqlAlchemy - How to define a foreign key column using model class instead of physical table name

问题描述

我想使用模型类来定义一个外键列.

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.

通常我们通过物理表名定义作为外键的列，例如这里

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'))

短语 ForeignKey('author.id') 有助于将列 author_id 定义为外键列 - 它指的是 author where author是表名.

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.

我想使用模型类名，即

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

但是这段代码出错了

无法确定关系 XX.YYY 上父/子表之间的连接条件 - 没有链接这些表的外键.确保引用列与 ForeignKey 或 ForeignKeyConstraint 相关联，或指定primaryjoin"表达式.

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?

推荐答案

简要

• 来自作者以外的模特
  author_id = db.Column(db.Integer, db.ForeignKey(Author.__table__.c.id))

  来自作者模型本身，即自引用 - 只需列出列名
  author_id = db.Column(db.Integer, db.ForeignKey(id))

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

  不能使用字符串值
  author_id = db.Column(db.Integer, db.ForeignKey('Author.id'))

  ForeignKey 首先接受列参数可以是 Column 类型或 schema_name.table_name.column_name 或 table_name.column_name 格式的字符串.您在声明性模型中定义的列将变为 InstumentedAttribute 对象.这就是 db.ForeignKey(Author.id) 导致错误的原因.您可以通过模型的 __table__ 属性访问实际列:

  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']))
  

  或

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

  如果您需要定义自引用外键，您可以简单地传递列的名称.虽然模型的声明尚未完成，但它仍然具有 Column 类型:

  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.

  这篇关于SqlAlchemy - 如何使用模型类而不是物理表名定义外键列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！