python - SQLAlchemy ORM 多个多对多关系
问题描述
我正在尝试制作一个电影数据库,我想要这些表:movies
、groups
(标签)genres
和directors
. 我想要一个 , 和 的列表groups
,genres
以及directors
在每个Movie
类中的列表movies
。例如:
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)
但是当我运行它时,我得到了这个错误:
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
是导致问题的原因,但我什至不确定关系是否正确完成。像我想要的那样建立关系的正确方法是什么。谢谢你的帮助。
解决方案
关键问题似乎是您的代码没有正确反映关系。您需要(多次)是一个多对多关系,它需要一个关联表。
下面的代码说明了电影/组关系需要更改的内容,您可以对您拥有的其他多对多关系重复相同的更改:
# 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)
...
现在下面应该工作:
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()
...
推荐阅读
- rust - 在 Vector 中查找匹配的 Enums 并在 Rust 中返回错误
- react-native-android - Proguard/Dexguard dontobfuscate React Native 中的特定图像文件夹
- reactjs - 如何在不指定 DateUtil(moment、datefns 等)的情况下覆盖标签命名
- mysql - Sql 查询为每个客户返回一行
- html - 如何在 Powershell 的 body 标记中将文本插入 HTML 文件
- spring-boot - 部署时 Maven 没有运行我的模拟测试
- android - 为什么在连接到 Databse 后我没有被重定向到 Android Studio 中的另一个活动
- amazon-web-services - AWS MSK:如何暂停?
- c# - Sparx Enterprise Architect (EA 14) 从 C# 的类模型生成代码,但它一直默认为 Java
- reactjs - 使用返回 API 调用但我的状态返回承诺的函数设置状态