python - 如何在 SQLAlchemy (ORM) 中为 3 个表建立多对多关系模型？

Question

我对 SQL 很陌生，对 SQL Alchemy 完全陌生，但有一个即将使用两者的项目。所以我想我写点东西让自己舒服点。由于宿醉，我决定写一些东西来跟踪酒精含量。

我有事件，用户参与并消费饮料 。这是我的三个基本表(有一个帮助表 gueSTList，用于 users 和 events 之间的 m:n 关系)。

drinks 始终向所有用户 列出所有事件 可用的饮料(无需映射任何内容)。 用户 是不时创建的，事件 也是如此。所有用户都可以加入所有事件，因此我使用gueSTList 表来映射这些事件。

现在进入问题的核心:我需要跟踪哪个用户在哪个时间喝了哪种饮料。我尝试用另一个表 shots 来解决这个问题(见下文)，但我不确定这是否是一个好的解决方案。

_{(来源:anyimg.com)}

在SQL Alchemy它可能看起来有点像这样(或者不是，但这是我到目前为止想出的)::

guestlist_table = Table('guestlist', Base.metadata,
    Column('event_id', Integer, ForeignKey('events.id')),
    Column('user_id', Integer, ForeignKey('users.id'))
)

class Event(Base):

  __tablename__ = 'events'

  id = Column(Integer, primary_key=True)
  name = Column(String(80), nullable=False)
  started = Column(DateTime, nullable=False, index=True,
    default=datetime.datetime.now
  )
  users = relationship("User", secondary=guestlist_table, backref="events")
  # ...

class User(Base):

  __tablename__ = 'users'

  id = Column(Integer, primary_key=True)
  name = Column(String(50), nullable=False, unique=True, index=True)
  birthdate = Column(Date, nullable=False)
  weight = Column(Integer, nullable=False)
  sex = Column(Boolean, nullable=False)
  # ...

class Drink(Base):

  __tablename__ = 'drinks'

  id = Column(Integer, primary_key=True)
  name = Column(String(50), nullable=False)
  volume = Column(Numeric(5,2), nullable=False)
  percent = Column(Numeric(5,2), nullable=False)
  # ...

class Shots(Base):

  __tablename__ = 'shots'

  id = Column(Integer, primary_key=True)
  at = Column(DateTime, nullable=False,
    default=datetime.datetime.now
  )
  user_id = Column(Integer, ForeignKey('users.id'), index=True)
  event_id = Column(Integer, ForeignKey('events.id'), index=True) 
  drink_id = Column(Integer, ForeignKey('drinks.id'))
  user = relationship("User", backref="shots")
  event = relationship("Event", backref="shots")
  drink = relationship("Drink", uselist=False) # one-to-one, no backref needed

我努力寻找一种好方法来构建一个将事件、用户和饮料映射在一起的表:我应该怎么做制定relationships我该如何query是吗？

问题是我觉得我忽略了一些东西。坦率地说，我完全不知道如何查询它？

以下是我大部分时间会进行的查询:

• 我至少需要获取事件中消耗的所有镜头(可能按用户排序)
• 有时我还需要特定用户的所有镜头(可能按事件排序)
• 还有很多计数:
  • 每次事件的拍摄次数
  • 每位用户的拍摄次数
  • 用户在一次事件中击倒的次数

shots 表是否是管理此问题的好方法？

Answer 1

我建议你画一个实体关系图，这样人们和你理解你的问题会更清楚。

回答你的问题:

I at least need to get all the shots consumed on a event (probably sorted by user)

要获取事件的所有镜头，您可以尝试

session.query(Shots).filter_by(event_id=event_id)

event_id 是你要查询的事件的id。要按用户存储，你可以试试

from sqlalchemy.sql.expression import desc, asc
session.query(Shots) \
    .filter_by(event_id=event_id) \
    .order_by(asc(Shots.user_id))

当然，你可能想按用户的属性排序，你可以加入用户表。

from sqlalchemy.sql.expression import desc, asc
session.query(Shots) \
    .filter_by(event_id=event_id) \
    .join(User) \
    .order_by(asc(User.name))

很简单。

I also sometimes need all shots for a specific user (probably sorted by event)

就像前面的例子

Number of shots per event

session.query(Shots) \
    .filter_by(event_id=event_id) \
    .count()

我没有运行它们，我只是把它们写在这里，但如果我没有打错，它们应该都能工作。