Python sqlalchemy获取一个用户的所有评分

huangapple go评论86阅读模式
英文:

Python sqlalchemy get all ratings from one user

问题

我有一个问题。例如,我有两个表 - userssuggestions

每个用户可以对这个建议给出一些评分,例如,有一个建议 "改进界面"。

用户 #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.

huangapple
  • 本文由 发表于 2023年7月17日 18:36:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/76703629.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定