Sqlalchemy Orm Multiple Many To Many Relationships

I am trying to make a movie database and I would like to have the tables: movies, groups(tags), genres and directors. I would like to have a list of groups, genres and directors in

Solution 1:

The key issue seems to be that your code does not reflect properly the relationships. What you need (multiple times) is a Many to Many relationship, which requires an association table.

The code below shoes what needs to change for the Movie/Group relationship, and you can repeat the same changes for other many-to-many relationships you have:

# define secondary table
user_group_table = Table(
    "movies_groups",
    Base.metadata,
    Column("movie_id", ForeignKey("movies.id"), primary_key=True),
    Column("group_group", ForeignKey("groups.group"), primary_key=True),
)

...

# changes to the Movies model:classMovie(Base):
    __tablename__ = 'movies'
    ...
    # REMOVE below row    # group_name = Column(String, ForeignKey("groups.group"))# CHANGE below as shown
    groups = relationship("Group", back_populates="movies", secondary=user_group_table)
    ...

# changes to the Group model:classGroup(Base):
    __tablename__ = 'groups'
    ...
    # CHANGE below as shown
    movies = relationship("Movie", back_populates="groups", secondary=user_group_table)
    ...

Now the below should work:

info_dict = {
    "file": "",
    "id": "tt0848228",
    "title": "The Avengers",
    "year": "2012",
    "runtime": 143,
    "rating": 8.0,
    "groups": [Group(group='marvel'), Group(group='no-fun')],
    # commented out for now: "directors": [Director(name="Joss Whedon")]
}
movie = Movie.from_dict(info_dict)
session.add(movie)
session.commit()
...