英文:
Python sqlalchemy get all ratings from one user
问题
我有一个问题。例如,我有两个表 - users 和 suggestions。
每个用户可以对这个建议给出一些评分,例如,有一个建议 "改进界面"。
用户 #1 给了这个建议一个评分为 10,而用户 #2 给了它一个评分为 3。
接下来的建议 "修复错误",只被用户 #1 评分,他给了它一个评分为 5。
我想做的是以某种方式获取每个用户给予所有建议的评分。这是否可能?我该如何做?
这是基本的代码,我不知道我的下一步应该是什么。
class Suggestion(Base):
__tablename__ = "suggestions"
s_id = Column("s_id", Integer, primary_key=True, autoincrement=True)
content = Column("content", String, nullable=False)
rating = Column("rating", Integer, nullable=False, default=0)
def __repr__(self):
return f"Suggestion #{self.id}: {self.content}"
class User(Base):
__tablename__ = "users"
user_id = Column("user_id", Integer, primary_key=True)
def __init__(self, user_id):
self.user_id = user_id
def __repr__(self):
return f"User #{self.user_id}"
英文:
I have a question. For example, I have two tables - users and suggestions.
Each user can give some rating to this suggestion, for example, there is a suggestion "Improve the interface".
User #1 gives this suggestion a rating of 10, and user #2 gives it a rating of 3.
The next suggestion, "Fix bugs", was rated only by user #1, who gave it a rating of 5.
What I want to do is to somehow get every rating that a user has given to all the suggestions. Is it possible to do this? How could I do it?
Here is the basic code, and I don't know what my next steps should be.
class Suggestion(Base):
__tablename__ = "suggestions"
s_id = Column("s_id", Integer, primary_key=True, autoincrement=True)
content = Column("content", String, nullable=False)
rating = Column("rating", Integer, nullable=False, default=0)
def __repr__(self):
return f"Suggestion #{self.id}: {self.content}"
class User(Base):
__tablename__ = "users"
user_id = Column("user_id", Integer, primary_key=True)
def __init__(self, user_id):
self.user_id = user_id
def __repr__(self):
return f"User #{self.user_id}"
答案1
得分: 3
这是一个多对多的关系(一个用户可以对多个建议进行评分,一个建议可以被多个用户评分),因此你可以使用一个用户和建议之间的关联表来建模。
请查看以下文档了解更多信息:
关联对象
这是一个示例实现。我正在使用 dataclass
集成来自动生成 __init__
和 __repr__
方法:
from __future__ import annotations
from sqlalchemy import ForeignKey, String, create_engine, select
from sqlalchemy.orm import Mapped, MappedAsDataclass, sessionmaker
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import relationship
class Base(MappedAsDataclass, DeclarativeBase):
pass
class User(Base):
__tablename__ = "user"
id: Mapped[int] = mapped_column(primary_key=True)
ratings: Mapped[list[Rating]] = relationship(init=False, repr=False)
class Rating(Base):
__tablename__ = "rating"
user_id: Mapped[int] = mapped_column(
ForeignKey("user.id"),
primary_key=True,
)
suggestion_id: Mapped[int] = mapped_column(
ForeignKey("suggestion.id"),
primary_key=True,
)
score: Mapped[int] = mapped_column(default=0)
suggestion: Mapped[Suggestion] = relationship(init=False, repr=False)
class Suggestion(Base):
__tablename__ = "suggestion"
id: Mapped[int] = mapped_column(primary_key=True, init=False)
content: Mapped[str] = mapped_column(String(255), nullable=False)
DATABASE_URL = "postgresql+psycopg2://test_username:test_password@localhost:5432/test"
engine = create_engine(
DATABASE_URL,
)
Session = sessionmaker(
bind=engine,
)
if __name__ == "__main__":
Base.metadata.create_all(engine)
# create initial users and suggestions
with Session.begin() as session:
session.add_all([
User(id=1),
User(id=2),
Suggestion(content="Improve the interface."),
Suggestion(content="Fix bugs."),
])
# users rating suggestions:
with Session() as session:
suggestion1_id = session.scalars(
select(Suggestion.id).filter_by(content="Improve the interface.")
).one()
suggestion2_id = session.scalars(
select(Suggestion.id).filter_by(content="Fix bugs.")
).one()
session.add_all([
Rating(user_id=1, suggestion_id=suggestion1_id, score=10),
Rating(user_id=2, suggestion_id=suggestion1_id, score=3),
Rating(user_id=1, suggestion_id=suggestion2_id, score=5),
])
session.commit()
# getting ratings for a user:
with Session() as session:
user1 = session.get(User, 1)
print(user1)
for rating in user1.ratings:
print(
f" rated `{rating.suggestion.content}`"
f" with a score of {rating.score}"
)
这是输出结果:
User(id=1)
rated `Improve the interface.` with a score of 10
rated `Fix bugs.` with a score of 5
这可以用多种方式完成,所以请阅读文档并选择你喜欢的方法。
英文:
This is a many-to-many relationship (a user can rate many suggestions, and a
suggestion can be rated by many users), so you can model this with an
association table between user and suggestion.
Please check the document on:
Association Object
for more information.
This is a sample implementation. I'm using the dataclass
integration to
automatically generate the __init__
and __repr__
method:
from __future__ import annotations
from sqlalchemy import ForeignKey, String, create_engine, select
from sqlalchemy.orm import Mapped, MappedAsDataclass, sessionmaker
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import relationship
class Base(MappedAsDataclass, DeclarativeBase):
pass
class User(Base):
__tablename__ = "user"
id: Mapped[int] = mapped_column(primary_key=True)
ratings: Mapped[list[Rating]] = relationship(init=False, repr=False)
class Rating(Base):
__tablename__ = "rating"
user_id: Mapped[int] = mapped_column(
ForeignKey("user.id"),
primary_key=True,
)
suggestion_id: Mapped[int] = mapped_column(
ForeignKey("suggestion.id"),
primary_key=True,
)
score: Mapped[int] = mapped_column(default=0)
suggestion: Mapped[Suggestion] = relationship(init=False, repr=False)
class Suggestion(Base):
__tablename__ = "suggestion"
id: Mapped[int] = mapped_column(primary_key=True, init=False)
content: Mapped[str] = mapped_column(String(255), nullable=False)
DATABASE_URL = "postgresql+psycopg2://test_username:test_password@localhost:5432/test"
engine = create_engine(
DATABASE_URL,
# echo=True,
)
Session = sessionmaker(
bind=engine,
)
if __name__ == "__main__":
Base.metadata.create_all(engine)
# create initial users and suggestions
with Session.begin() as session:
session.add_all([
User(id=1),
User(id=2),
Suggestion(content="Improve the interface."),
Suggestion(content="Fix bugs."),
])
# users rating suggestions:
with Session() as session:
suggestion1_id = session.scalars(
select(Suggestion.id).filter_by(content="Improve the interface.")
).one()
suggestion2_id = session.scalars(
select(Suggestion.id).filter_by(content="Fix bugs.")
).one()
session.add_all([
Rating(user_id=1, suggestion_id=suggestion1_id, score=10),
Rating(user_id=2, suggestion_id=suggestion1_id, score=3),
Rating(user_id=1, suggestion_id=suggestion2_id, score=5),
])
session.commit()
# getting ratings for a user:
with Session() as session:
user1 = session.get(User, 1)
print(user1)
for rating in user1.ratings:
print(
f" rated `{rating.suggestion.content}`"
f" with a score of {rating.score}"
)
This is the output:
User(id=1)
rated `Improve the interface.` with a score of 10
rated `Fix bugs.` with a score of 5
This can be done in numerous ways, so read the docs and pick one you like.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论