SQLAlchemy ORM 多重多对多关系 [英] SQLAlchemy ORM multiple many to many relationships

问题描述

我正在尝试制作一个电影数据库，我想要表格:movies、groups(tags)、genres 和导演.我想要一个 Movie 类中的 groups、genres 和 directors 列表以及一个列表movies 中的每一个.例如:

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 the Movie class as well as a list of movies in each of those. e.g:

Movie:
    file: str
    id: str
    title: str
    etc: str
    groups: list of Group
    genres: list of Genre
    directors: list of Director
Group:
    group: str
    movies: list of Movies with this Group
Genre:
    genre: str
    movies: list of Movies with this Genre
etc...

我有这个代码:

from sqlalchemy import Column, String, Integer, Float, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()


class Movie(Base):
    __tablename__ = 'movies'

    file = Column(String)
    id = Column(String, primary_key=True)
    title = Column(String)
    year = Column(String)
    runtime = Column(Integer)
    rating = Column(Float)

    group_name = Column(String, ForeignKey("groups.group"))
    groups = relationship("Group", back_populates="movies")
    genre_name = Column(String, ForeignKey("genres.genre"))
    genres = relationship("Genre", back_populates="movies")
    director_name = Column(String, ForeignKey("directors.name"))
    directors = relationship("Director", back_populates="movies")

    @classmethod
    def from_dict(cls, info_dict: dict):
        info_dict = {key: val for key, val in info_dict.items() if key in vars(cls)}
        return cls(**info_dict)


class Group(Base):
    __tablename__ = "groups"

    group = Column(String, primary_key=True)
    movies = relationship("Movie", back_populates="groups")

    def __str__(self):
        return self.group

    def __repr__(self):
        return f"<{self.__class__.__qualname__}(name={self.group})>"

    def __eq__(self, other):
        if other.__class__ == self.__class__:
            return self.group == other.group
        elif isinstance(other, str):
            return self.group == other
        return False


class Genre(Base):
    __tablename__ = "genres"

    genre = Column(String, primary_key=True)
    movies = relationship("Movie", back_populates="genres")

    def __str__(self):
        return self.genre

    def __repr__(self):
        return f"<{self.__class__.__qualname__}(name={self.genre})>"


class Director(Base):
    __tablename__ = "directors"

    name = Column(String, primary_key=True)
    movies = relationship("Movie", back_populates="directors")

    def __str__(self):
        return self.name

    def __repr__(self):
        return f"<{self.__class__.__qualname__}(name={self.name})>"


if __name__ == '__main__':
    engine = create_engine(r"sqlite:///C:\path\to\movies.db")
    session = sessionmaker(bind=engine)()
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    info_dict = {
        "file": "",
        "id": "tt0848228",
        "title": "The Avengers",
        "year": "2012",
        "runtime": 143,
        "rating": 8.0,
        "groups": [Group(group='marvel')],
        "directors": [Director(name="Joss Whedon")]
    }
    movie = Movie.from_dict(info_dict)

但是当我运行它时，我得到这个错误:

But when I run it I get this error:

Traceback (most recent call last):
  File "C:/tools/movie_db/test.py", line 108, in <module>
    movie = Movie.from_dict(info_dict)
  File "C:/tools/movie_db/test.py", line 44, in from_dict
    return cls(**info_dict)
  File "<string>", line 4, in __init__
  File "C:\Users\Scott\.virtualenvs\movie_db-IqSCrsAm\lib\site-packages\sqlalchemy\orm\state.py", line 437, in _initialize_instance
    manager.dispatch.init_failure(self, args, kwargs)
  File "C:\Users\Scott\.virtualenvs\movie_db-IqSCrsAm\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "C:\Users\Scott\.virtualenvs\movie_db-IqSCrsAm\lib\site-packages\sqlalchemy\util\compat.py", line 198, in raise_
    raise exception
  File "C:\Users\Scott\.virtualenvs\movie_db-IqSCrsAm\lib\site-packages\sqlalchemy\orm\state.py", line 434, in _initialize_instance
    return manager.original_init(*mixed[1:], **kwargs)
  File "C:\Users\Scott\.virtualenvs\movie_db-IqSCrsAm\lib\site-packages\sqlalchemy\orm\decl_base.py", line 1132, in _declarative_constructor
    setattr(self, k, kwargs[k])
  File "C:\Users\Scott\.virtualenvs\media_organizer-IqSCrsAm\lib\site-packages\sqlalchemy\orm\attributes.py", line 431, in __set__
    self.impl.set(
  File "C:\Users\Scott\.virtualenvs\movie_db-IqSCrsAm\lib\site-packages\sqlalchemy\orm\attributes.py", line 1192, in set
    value = self.fire_replace_event(state, dict_, value, old, initiator)
  File "C:\Users\Scott\.virtualenvs\movie_db-IqSCrsAm\lib\site-packages\sqlalchemy\orm\attributes.py", line 1214, in fire_replace_event
    value = fn(
  File "C:\Users\Scott\.virtualenvs\movie_db-IqSCrsAm\lib\site-packages\sqlalchemy\orm\attributes.py", line 1643, in emit_backref_from_scalar_set_event
    instance_state(child),
AttributeError: 'list' object has no attribute '_sa_instance_state'

总的来说，我对 SQLAlchemy 和数据库非常陌生，所以我不确定我做错了什么.我知道 Group 的列表是导致问题的原因，但我什至不确定这些关系是否正确完成.建立像我想要的那种关系的正确方法是什么.感谢您的帮助.

I am very new to SQLAlchemy and databases in general so I'm not sure what I'm doing wrong. I understand that the list of Group is what is causing the problem but I'm not even sure if the relationships are done correctly. What is the correct way to do a relationship like the one I would like. Thanks for any help.

推荐答案

关键问题似乎是您的代码没有正确反映关系.您需要(多次)是 多对多关系，需要一个关联表.

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:
class Movie(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:
class Group(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()
...

这篇关于SQLAlchemy ORM 多重多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！