文章/答案/技术大牛

发布

社区首页 >问答首页 >SQLAlchemy:连接负载和子负载行为的区别

问SQLAlchemy:连接负载和子负载行为的区别
EN

Stack Overflow用户

提问于 2017-12-19 23:13:46

回答 1查看 1.8K关注 0票数 1

我发现SQLAlchemy的接合载荷和子负载之间有一个惊人的区别，特别是它们如何处理多态。在使用subqueryload时，我无法在subqueryload中包含的关系的子类上急切地加载关系，而joinedload似乎可以很好地处理这个问题。

例如，假设我有一个User类，它有-许多Shape对象。Rectangle是一个形状，并且说它有-一个与它相关的Widget。在查询User类时，我希望能够构造我的查询选项，以便热切地获取用户的所有矩形小部件，并且我希望将这些形状作为子查询(而不是连接加载)来查询，以减少返回结果集中的重复数据量。我可以通过连接加载shapes关系来让它工作，但我不能让它作为子查询工作。有什么想法吗？

例如，在代码中：

import logging
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
    backref,
    joinedload,
    relationship,
    scoped_session,
    sessionmaker,
    subqueryload,
    with_polymorphic)
from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    ForeignKey,
    MetaData,
    String)


logging.basicConfig()
logger = logging.getLogger(__name__)
logger.setLevel(logging.INFO)


Base = declarative_base()


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)


class Shape(Base):
    __tablename__ = "shapes"
    __mapper_args__ = {"polymorphic_on": "type"}

    id = Column(Integer, primary_key=True)
    type = Column(String, nullable=False)
    owner_id = Column(Integer, ForeignKey("users.id"))
    owner = relationship("User", backref=backref("shapes"), cascade="all")


class Rectangle(Shape):
    __tablename__ = "shapes_rectangles"
    __mapper_args__ = {"polymorphic_identity": "RECT"}

    id = Column(Integer, ForeignKey("shapes.id"), primary_key=True)
    length = Column(Integer)
    width = Column(Integer)
    widget_id = Column(Integer, ForeignKey("widgets.id"))
    widget = relationship("Widget", cascade="all")


class Widget(Base):
    __tablename__ = "widgets"

    id = Column(Integer, primary_key=True)


engine = create_engine("sqlite://", echo=True)
metadata = MetaData()
with engine.begin() as connection:
    metadata = Base.metadata
    metadata.create_all(connection)

session = scoped_session(sessionmaker(bind=engine))

user = User()
widget = Widget()
rectangle = Rectangle(owner=user, widget=widget, length=10, width=5)
session.add_all([user, widget, rectangle])
session.commit()

shapes_load = subqueryload(
    User.shapes.of_type(
        with_polymorphic(Shape, "*", aliased=True)))

session.remove()
query = session.query(User) .options(shapes_load.joinedload(Rectangle.widget))
logger.info("##########subqueryload begin")
user = query.one()
logger.info("##########accessing widget")
user.shapes[0].widget
logger.info("##########subqueryload end")

shapes_load = joinedload(
    User.shapes.of_type(
        with_polymorphic(Shape, "*", aliased=True)))

session.remove()
query = session.query(User).options(shapes_load.joinedload(Rectangle.widget))
logger.info("##########joinedload begin")
user = query.one()
logger.info("##########accessing widget")
user.shapes[0].widget
logger.info("##########joinedload end")

相关产出：

INFO:__main__:##########subqueryload begin
2017-12-19 23:03:36,826 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2017-12-19 23:03:36,827 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id
FROM users
INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id
FROM users
2017-12-19 23:03:36,827 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
2017-12-19 23:03:36,829 INFO sqlalchemy.engine.base.Engine SELECT anon_1.shapes_id AS anon_1_shapes_id, anon_1.shapes_type AS anon_1_shapes_type, anon_1.shapes_owner_id AS anon
_1_shapes_owner_id, anon_1.shapes_rectangles_id AS anon_1_shapes_rectangles_id, anon_1.shapes_rectangles_length AS anon_1_shapes_rectangles_length, anon_1.shapes_rectangles_wid
th AS anon_1_shapes_rectangles_width, anon_1.shapes_rectangles_widget_id AS anon_1_shapes_rectangles_widget_id, anon_2.users_id AS anon_2_users_id
FROM (SELECT users.id AS users_id
FROM users) AS anon_2 JOIN (SELECT shapes.id AS shapes_id, shapes.type AS shapes_type, shapes.owner_id AS shapes_owner_id, shapes_rectangles.id AS shapes_rectangles_id, shapes_
rectangles.length AS shapes_rectangles_length, shapes_rectangles.width AS shapes_rectangles_width, shapes_rectangles.widget_id AS shapes_rectangles_widget_id
FROM shapes LEFT OUTER JOIN shapes_rectangles ON shapes.id = shapes_rectangles.id) AS anon_1 ON anon_2.users_id = anon_1.shapes_owner_id ORDER BY anon_2.users_id
INFO:sqlalchemy.engine.base.Engine:SELECT anon_1.shapes_id AS anon_1_shapes_id, anon_1.shapes_type AS anon_1_shapes_type, anon_1.shapes_owner_id AS anon_1_shapes_owner_id, anon
_1.shapes_rectangles_id AS anon_1_shapes_rectangles_id, anon_1.shapes_rectangles_length AS anon_1_shapes_rectangles_length, anon_1.shapes_rectangles_width AS anon_1_shapes_rect
angles_width, anon_1.shapes_rectangles_widget_id AS anon_1_shapes_rectangles_widget_id, anon_2.users_id AS anon_2_users_id
FROM (SELECT users.id AS users_id
FROM users) AS anon_2 JOIN (SELECT shapes.id AS shapes_id, shapes.type AS shapes_type, shapes.owner_id AS shapes_owner_id, shapes_rectangles.id AS shapes_rectangles_id, shapes_
rectangles.length AS shapes_rectangles_length, shapes_rectangles.width AS shapes_rectangles_width, shapes_rectangles.widget_id AS shapes_rectangles_widget_id
FROM shapes LEFT OUTER JOIN shapes_rectangles ON shapes.id = shapes_rectangles.id) AS anon_1 ON anon_2.users_id = anon_1.shapes_owner_id ORDER BY anon_2.users_id
2017-12-19 23:03:36,829 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
INFO:__main__:##########accessing widget
2017-12-19 23:03:36,831 INFO sqlalchemy.engine.base.Engine SELECT widgets.id AS widgets_id
FROM widgets
WHERE widgets.id = ?
INFO:sqlalchemy.engine.base.Engine:SELECT widgets.id AS widgets_id
FROM widgets
WHERE widgets.id = ?
2017-12-19 23:03:36,831 INFO sqlalchemy.engine.base.Engine (1,)
INFO:sqlalchemy.engine.base.Engine:(1,)
INFO:__main__:##########subqueryload end
2017-12-19 23:03:36,832 INFO sqlalchemy.engine.base.Engine ROLLBACK
INFO:sqlalchemy.engine.base.Engine:ROLLBACK
INFO:__main__:##########joinedload begin
2017-12-19 23:03:36,837 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2017-12-19 23:03:36,838 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, anon_1.shapes_id AS anon_1_shapes_id, anon_1.shapes_type AS anon_1_shapes_type, anon_1.s
hapes_owner_id AS anon_1_shapes_owner_id, widgets_1.id AS widgets_1_id, anon_1.shapes_rectangles_id AS anon_1_shapes_rectangles_id, anon_1.shapes_rectangles_length AS anon_1_sh
apes_rectangles_length, anon_1.shapes_rectangles_width AS anon_1_shapes_rectangles_width, anon_1.shapes_rectangles_widget_id AS anon_1_shapes_rectangles_widget_id
FROM users LEFT OUTER JOIN (SELECT shapes.id AS shapes_id, shapes.type AS shapes_type, shapes.owner_id AS shapes_owner_id, shapes_rectangles.id AS shapes_rectangles_id, shapes_
rectangles.length AS shapes_rectangles_length, shapes_rectangles.width AS shapes_rectangles_width, shapes_rectangles.widget_id AS shapes_rectangles_widget_id
FROM shapes LEFT OUTER JOIN shapes_rectangles ON shapes.id = shapes_rectangles.id) AS anon_1 ON users.id = anon_1.shapes_owner_id LEFT OUTER JOIN widgets AS widgets_1 ON widget
s_1.id = anon_1.shapes_rectangles_widget_id
INFO:sqlalchemy.engine.base.Engine:SELECT users.id AS users_id, anon_1.shapes_id AS anon_1_shapes_id, anon_1.shapes_type AS anon_1_shapes_type, anon_1.shapes_owner_id AS anon_1
_shapes_owner_id, widgets_1.id AS widgets_1_id, anon_1.shapes_rectangles_id AS anon_1_shapes_rectangles_id, anon_1.shapes_rectangles_length AS anon_1_shapes_rectangles_length,
anon_1.shapes_rectangles_width AS anon_1_shapes_rectangles_width, anon_1.shapes_rectangles_widget_id AS anon_1_shapes_rectangles_widget_id
FROM users LEFT OUTER JOIN (SELECT shapes.id AS shapes_id, shapes.type AS shapes_type, shapes.owner_id AS shapes_owner_id, shapes_rectangles.id AS shapes_rectangles_id, shapes_
rectangles.length AS shapes_rectangles_length, shapes_rectangles.width AS shapes_rectangles_width, shapes_rectangles.widget_id AS shapes_rectangles_widget_id
FROM shapes LEFT OUTER JOIN shapes_rectangles ON shapes.id = shapes_rectangles.id) AS anon_1 ON users.id = anon_1.shapes_owner_id LEFT OUTER JOIN widgets AS widgets_1 ON widget
s_1.id = anon_1.shapes_rectangles_widget_id
2017-12-19 23:03:36,839 INFO sqlalchemy.engine.base.Engine ()
INFO:sqlalchemy.engine.base.Engine:()
INFO:__main__:##########accessing widget
INFO:__main__:##########joinedload end

relationships

python

sqlalchemy

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-01-20 22:36:58

我在使用with_polymorphic时发现了一个bug。此修补程序1修复了subqueryload查询，因此它在访问user.shapes[0].widget (这是预期的行为)时不会发出查询。

在SQLAlchemy版本1.3中，原始测试用例失败，例外情况如下：

ArgumentError: Attribute "Rectangle.widget" does not link from element "with_polymorphic(Shape, [])".

@@ -72,12 +72,13 @@ rectangle = Rectangle(owner=user, widget=widget, length=10, width=5)
 session.add_all([user, widget, rectangle])
 session.commit()

-shapes_load = subqueryload(
-    User.shapes.of_type(
-        with_polymorphic(Shape, "*", aliased=True)))
+polymorphic_shape = with_polymorphic(Shape, "*", aliased=True)
+shapes_load = subqueryload(User.shapes.of_type(polymorphic_shape))

 session.remove()
-query = session.query(User) .options(shapes_load.joinedload(Rectangle.widget))
+query = session.query(User).options(
+    shapes_load.joinedload(polymorphic_shape.Rectangle.widget)
+)
 logger.info("##########subqueryload begin")
 user = query.one()
 logger.info("##########accessing widget")
@@ -85,11 +86,13 @@ user.shapes[0].widget
 logger.info("##########subqueryload end")

 shapes_load = joinedload(
-    User.shapes.of_type(
-        with_polymorphic(Shape, "*", aliased=True)))
+    User.shapes.of_type(with_polymorphic(Shape, "*", aliased=True))
+)

 session.remove()
-query = session.query(User).options(shapes_load.joinedload(Rectangle.widget))
+query = session.query(User).options(
+    shapes_load.joinedload(polymorphic_shape.Rectangle.widget)
+)
 logger.info("##########joinedload begin")
 user = query.one()
 logger.info("##########accessing widget")

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/47896555

复制

相似问题

问SQLAlchemy:连接负载和子负载行为的区别
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQLAlchemy:连接负载和子负载行为的区别EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQLAlchemy:连接负载和子负载行为的区别
EN