How to create a field with a list of foreign keys in SQLAlchemy?
<p>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, <code>Actor</code>, and I want to create a new model, <code>Movie</code>, with the field <code>Movie.list_of_actors</code>:</p>

<pre><code>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'))
</code></pre>

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

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

<p>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: <a href="http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-many" rel="nofollow">http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-many</a> ? Either way, an example would be very helpful!</p>

<hr>

<p>Also, if it matters, I am using Postgres 9.5. I see from <a href="http://stackoverflow.com/questions/21932489/how-to-make-array-field-with-foreign-key-constraint-in-sqlalchemy">this post</a> there might be support for arrays in Postgres, so any thoughts on that could be helpful.</p>

<p>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.</p>

<p>An association table solution might resemble:</p>

<pre><code>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'))
</code></pre>

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

python postgresql sqlalchemy foreign-keys foreign-key-relationship