我有 sqlalchemy 模型:
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, and_
from sqlalchemy.orm import sessionmaker, relationship
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
Base = declarative_base()
class TopicToPizzaAssociation(Base):
__tablename__ = 'association'
pizza_id = Column(Integer, ForeignKey('pizza.id'), primary_key=True)
topic_id = Column(Integer, ForeignKey('topic.id'), primary_key=True)
topic = relationship("Topic")
pizza = relationship("Pizza")
class Pizza(Base):
__tablename__ = 'pizza'
id = Column(Integer, primary_key=True)
topics = relationship("TopicToPizzaAssociation")
def add_topics(self, topics):
used_topics = {t.topic.product for t in self.topics}
associations = []
for topic in topics:
if topic.product not in used_topics:
associations.append(TopicToPizzaAssociation(pizza=self, topic=topic))
used_topics.add(topic.product)
p1.topics.extend(associations)
class Topic(Base):
__tablename__ = 'topic'
id = Column(Integer, primary_key=True)
product = Column(String(), nullable=False)
我需要选择所有具有所需主题集的披萨对象:
t1 = Topic(product='t1')
t2 = Topic(product='t2')
t3 = Topic(product='t3')
session = Session()
session.add_all([t1, t2, t3])
p1 = Pizza()
p2 = Pizza()
p1.add_topics([t1, t2, t1])
p2.add_topics([t2, t3])
Base.metadata.create_all(engine)
session.add_all([p1, p2])
session.commit()
values = ['t1', 't2']
topics = session.query(Topic.id).filter(Topic.product.in_(values))
pizza = session.query(Pizza).filter(Pizza.topics.any(TopicToPizzaAssociation.topic_id.in_(
topics
))).all()
这将返回具有其中一个主题的所有披萨。如果我尝试用 all 替换 any,它不起作用。
我发现可以使用 JOIN 和 COUNT 进行查询,但我无法构建 sqlalchemy 查询。任何可能的解决方案都适合我。
请您参考如下方法:
首先,您可以阅读大量有关 SQLAlchemy 关系的资料 in the docs .
您的代码与 Association Object 非常匹配模式是(来自文档):
...used when your association table contains additional columns beyond those which are foreign keys to the left and right tables
即,如果 Pizza 和 Topic 之间的个别关系有特定的内容,您将根据关联中外键之间的关系存储该信息 table 。这是文档给出的示例:
class Association(Base):
__tablename__ = 'association'
left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)
right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)
extra_data = Column(String(50))
child = relationship("Child", back_populates="parents")
parent = relationship("Parent", back_populates="children")
class Parent(Base):
__tablename__ = 'left'
id = Column(Integer, primary_key=True)
children = relationship("Association", back_populates="parent")
class Child(Base):
__tablename__ = 'right'
id = Column(Integer, primary_key=True)
parents = relationship("Association", back_populates="child")
注意 Association 对象上定义的 extra_data 列。
在您的示例中,Association 中不需要 extra_data 类型字段,因此您可以简化 Pizza 和 Topic 之间关系的表达> 通过使用 Many to Many Pattern outlined in the docs .
我们可以从该模式中获得的主要好处是我们可以将 Pizza 类直接关联到 Topic 类。新模型看起来像这样:
class TopicToPizzaAssociation(Base):
__tablename__ = 'association'
pizza_id = Column(Integer, ForeignKey('pizza.id'), primary_key=True)
topic_id = Column(Integer, ForeignKey('topic.id'), primary_key=True)
class Pizza(Base):
__tablename__ = 'pizza'
id = Column(Integer, primary_key=True)
topics = relationship("Topic", secondary='association') # relationship is directly to Topic, not to the association table
def __repr__(self):
return f'pizza {self.id}'
class Topic(Base):
__tablename__ = 'topic'
id = Column(Integer, primary_key=True)
product = Column(String(), nullable=False)
def __repr__(self):
return self.product
与原始代码的区别是:
- 没有在
TopicToPizzaAssociation模型上定义任何关系。使用这种模式,我们可以直接将Pizza与Topic相关联,而无需在关联模型上建立关系。 - 为两个模型添加了
__repr__()方法,以便它们打印得更好。 - 从
Pizza中删除了add_topics方法(稍后会详细解释)。 - 为
Pizza.topics关系添加了secondary='association'参数。这告诉 sqlalchemy 与Topic的关系所需的外键路径是通过association表。
这是测试代码,我在里面放了一些注释:
t1 = Topic(product='t1')
t2 = Topic(product='t2')
t3 = Topic(product='t3')
session = Session()
session.add_all([t1, t2, t3])
p1 = Pizza()
p2 = Pizza()
p1.topics = [t1, t2] # not adding to the pizzas through a add_topics method
p2.topics = [t2, t3]
Base.metadata.create_all(engine)
session.add_all([p1, p2])
session.commit()
values = [t2, t1] # these aren't strings, but are the actual objects instantiated above
# using Pizza.topics.contains
print(session.query(Pizza).filter(*[Pizza.topics.contains(t) for t in values]).all()) # [pizza 1]
values = [t2, t3]
print(session.query(Pizza).filter(*[Pizza.topics.contains(t) for t in values]).all()) # [pizza 2]
values = [t2]
print(session.query(Pizza).filter(*[Pizza.topics.contains(t) for t in values]).all()) # [pizza 2, pizza 1]
所以这只会返回具有所有规定主题的比萨饼,而不是仅规定的主题。
我省略您的 add_topics 方法的原因是您使用该方法来检查添加到给定 Pizza 的重复 Topics。这很好,但是关联表的主键无论如何都不允许你为披萨添加重复的主题,所以我认为最好让数据库层管理它并只处理应用程序代码中发生的异常。
