如何在 SQLAlchemy 中创建带有外键列表的字段? [英] How to create a field with a list of foreign keys in SQLAlchemy?

问题描述

我正在尝试在另一个模型的字段中存储模型列表.下面是一个简单的示例，我有一个现有模型 Actor，我想创建一个新模型 Movie，其字段为 Movie.list_of_actors:

I am trying to store a list of models within the field of another model. Here is a trivial example below, where I have an existing model, Actor, and I want to create a new model, Movie, with the field Movie.list_of_actors:

import uuid

from sqlalchemy import Boolean, Column, Integer, String, DateTime
from sqlalchemy.schema import ForeignKey
rom sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Actor(Base):
    __tablename__ = 'actors'

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String)
    nickname = Column(String)
    academy_awards = Column(Integer)


# This is my new model:
class Movie(Base):
    __tablename__ = 'movies'

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    title = Column(String)
    # How do I make this a list of foreign keys???
    list_of_actors = Column(UUID(as_uuid=True), ForeignKey('actors.id'))

我知道这可以通过多对多关系来完成，但有没有更简单的解决方案?请注意，我不需要查找 Actor 在哪个 Movie 中 - 我只想创建一个新的 Movie 模型并访问我的 Actor 的列表.理想情况下，我不希望在我的 Actor 模型中添加任何新字段.

I understand that this can be done with a many-to-many relationship, but is there a more simple solution? Note that I don't need to look up which Movie's an Actor is in - I just want to create a new Movie model and access the list of my Actor's. And ideally, I would prefer not to add any new fields to my Actor model.

我已经使用 关系 API，这里概述了使用 back_propagates 和 backref 的各种一对多/多对多组合:http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html 但我如果不创建完整的多对多实现，似乎无法实现我的外键列表.

I've gone through the tutorials using the relationships API, which outlines the various one-to-many/many-to-many combinations using back_propagates and backref here: http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html But I can't seem to implement my list of foreign keys without creating a full-blown many-to-many implementation.

但是如果多对多实现是唯一的方法，那么有没有一种方法可以实现它而不必创建关联表"?这里描述了关联表":http:///docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-many ?无论哪种方式，一个例子都会非常有帮助！

But if a many-to-many implementation is the only way to proceed, is there a way to implement it without having to create an "association table"? The "association table" is described here: http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-many ? Either way, an example would be very helpful!

另外，如果重要的话，我使用的是 Postgres 9.5.我从这篇文章看到可能是对 Postgres 中数组的支持，因此任何有关这方面的想法都会有所帮助.

Also, if it matters, I am using Postgres 9.5. I see from this post there might be support for arrays in Postgres, so any thoughts on that could be helpful.

更新

看起来这里唯一合理的方法是创建一个关联表，如下面选定的答案所示.我尝试使用 来自 SQLAlchemy 的 ARRAYPostgres 方言 但它似乎不支持外键.在上面的示例中，我使用了以下列:

It looks like the only reasonable approach here is to create an association table, as shown in the selected answer below. I tried using ARRAY from SQLAlchemy's Postgres Dialect but it doesn't seem to support Foreign Keys. In my example above, I used the following column:

list_of_actors = Column('actors', postgresql.ARRAY(ForeignKey('actors.id')))

但它给了我一个错误.似乎对带有外键的 Postgres ARRAY 的支持正在进行中，但仍然不完全存在.这是我找到的最新信息来源:http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/

but it gives me an error. It seems like support for Postgres ARRAY with Foreign Keys is in progress, but still isn't quite there. Here is the most up to date source of information that I found: http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/

推荐答案

如果您希望多个演员与一部电影相关联，并且许多电影与一个演员相关联，那么您需要多对多.这意味着您需要一个关联表.否则，您可以放弃规范化并使用 NoSQL 数据库.

If you want many actors to be associated to a movie, and many movies be associated to an actor, you want a many-to-many. This means you need an association table. Otherwise, you could chuck away normalisation and use a NoSQL database.

关联表解决方案可能类似于:

An association table solution might resemble:

class Actor(Base):
    __tablename__ = 'actors'

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String)
    nickname = Column(String)
    academy_awards = Column(Integer)

class Movie(Base):
    __tablename__ = 'movies'

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    title = Column(String)

    actors = relationship('ActorMovie', uselist=True, backref='movies')

class ActorMovie(Base):
    __tablename__ = 'actor_movies'

    actor_id = Column(UUID(as_uuid=True), ForeignKey('actors.id'))
    movie_id = Column(UUID(as_uuid=True), ForeignKey('movies.id'))

如果您不希望 ActorMovie 成为继承自 Base 的对象，您可以使用 sqlachlemy.schema.Table.

If you don't want ActorMovie to be an object inheriting from Base, you could use sqlachlemy.schema.Table.

这篇关于如何在 SQLAlchemy 中创建带有外键列表的字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！