SqlAlchemy的断关联查询

模型基础

viewonly很重要, 避免多个重复的子集相同字段的问题

relationship其实也是可以给模型动态赋予的, 这里不做多尝试

from sqlalchemy import Column, Integer, ForeignKey, create_engine, ForeignKeyConstraint, Table, VARCHAR
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, joinedload, subqueryload, backref, scoped_session, mapper, \
    selectinload, contains_eager, load_only, aliased, remote, foreign
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine

engine = create_engine('sqlite:///test.db')
Base = declarative_base()
Session = sessionmaker(engine)
Scoped_session = scoped_session(Session)

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer)
    age = Column(Integer)
    name = Column(VARCHAR)
    parent = relationship("Parent", primaryjoin='Child.parent_id==foreign(Parent.id)', uselist=False, viewonly=True)
    detail = relationship("ChildDetail", primaryjoin='Child.id==foreign(ChildDetail.parent_id)', uselist=True)

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", primaryjoin='Parent.id==foreign(Child.parent_id)', uselist=True)
    # children = relationship("Child", primaryjoin=id==foreign(Child.parent_id), uselist=True)
    detail = relationship("ParentDetail", primaryjoin='Parent.id==foreign(ParentDetail.parent_id)', uselist=True)
    groups = relationship("Group", secondary=association_table, back_populates="parents")


class ChildDetail(Base):
    __tablename__ = 'child_detail'

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer)
    parent = relationship("Child", primaryjoin='ChildDetail.parent_id==foreign(Child.id)', uselist=False)

其中primaryjoin的参数就相当于模型的绑定, 其中使用到foreign确定要绑定对方表的哪个字段

当然 from sqlalchemy.orm import foreign => 使用foreign包使用变量来绑定也是可以的

相对来说这种情况要处理模型在代码块里的先后顺序, 字符串则不存在此问题

一共提供了以下load方法:

  • lazy loading - available via lazy='select' or the lazyload() option, this is the form of loading that emits a SELECT statement at attribute access time to lazily load a related reference on a single object at a time. Lazy loading is detailed at Lazy Loading.
  • joined loading - available via lazy='joined' or the joinedload() option, this form of loading applies a JOIN to the given SELECT statement so that related rows are loaded in the same result set. Joined eager loading is detailed at Joined Eager Loading.
  • subquery loading - available via lazy='subquery' or the subqueryload() option, this form of loading emits a second SELECT statement which re-states the original query embedded inside of a subquery, then JOINs that subquery to the related table to be loaded to load all members of related collections / scalar references at once. Subquery eager loading is detailed at Subquery Eager Loading.
  • select IN loading - available via lazy='selectin' or the selectinload() option, this form of loading emits a second (or more) SELECT statement which assembles the primary key identifiers of the parent objects into an IN clause, so that all members of related collections / scalar references are loaded at once by primary key. Select IN loading is detailed at Select IN loading.
  • raise loading - available via lazy='raise', lazy='raise_on_sql', or the raiseload() option, this form of loading is triggered at the same time a lazy load would normally occur, except it raises an ORM exception in order to guard against the application making unwanted lazy loads. An introduction to raise loading is at Preventing unwanted lazy loads using raiseload.
  • no loading - available via lazy='noload', or the noload() option; this loading style turns the attribute into an empty attribute (None or []) that will never load or have any loading effect. This seldom-used strategy behaves somewhat like an eager loader when objects are loaded in that an empty attribute or collection is placed, but for expired objects relies upon the default value of the attribute being returned on access; the net effect is the same except for whether or not the attribute name appears in the InstanceState.unloaded collection. noload may be useful for implementing a “write-only” attribute but this usage is not currently tested or formally supported.

通常joinedload在使用上会更多, 采取一次性加载

query1-2

 """
 query1和query2是等价的, 简单用法
 """
 query1 = session.query(Parent).options(joinedload(Parent.children).joinedload(Child.detail))
 query2 = session.query(Parent).options(joinedload(Parent.children).options(joinedload(Child.detail)))

query3

stmt1 = session.query(Child).filter(Child.id > 1).outerjoin(Child.detail).options(joinedload(Child.detail)).subquery()
ua1 = aliased(Child, stmt1)
query3 = session.query(Parent).options(joinedload(Parent.children.of_type(ua1)))
"""
query3里of_type和stmt+aliased配合的在sql语句中是子查询过滤Child.id > 1, 但是结果无法达到预期, 
原因是outerjoin(Child.detail)或者options(joinedload(Child.detail))的SQL结果看来并没出现在主select的结果字段里,
当没有outerjoin(Child.detail)时, 打印stmt1可以发现, options(joinedload(Child.detail))并没有生效,
此时改为 => session.query(Parent).options(joinedload(Parent.children.of_type(ua1).options(joinedload(Child.detail)))), 
或者 => session.query(Parent).options(joinedload(Parent.children.of_type(ua1).joinedload(Child.detail))),
则会报出异常: Attribute "Child.detail" does not link from element "aliased(Child)",
在此希望有大佬能告知问题所在和解决办法
SQL输出:
SELECT
    parent.id AS parent_id,
    anon_1.id AS anon_1_id,
    anon_1.parent_id AS anon_1_parent_id,
    anon_1.age AS anon_1_age,
    anon_1.name AS anon_1_name 
FROM
    parent
    LEFT OUTER JOIN (
    SELECT
        child.id AS id,
        child.parent_id AS parent_id,
        child.age AS age,
        child.name AS name,
        child.id = child_detail.parent_id AS anon_2 
    FROM
        child
        LEFT OUTER JOIN child_detail ON child.id = child_detail.parent_id 
    WHERE
        child.id > ? 
    ) AS anon_1 ON parent.id = anon_1.parent_id;
"""

query4

stmt2 = session.query(Child).filter(Child.id > 1).subquery()
ua2 = aliased(Child, stmt2)
query4 = session.query(Parent).options(joinedload(Parent.children.of_type(ua2)), joinedload(Parent.children).joinedload(Child.detail))
"""
虽然query4, 得到了想要的, 但是显然要写2分joinedload(Parent.children), 有点奇怪? 虽然SQL是正确的
SQL输出:
SELECT
    parent.id AS parent_id,
    child_detail_1.id AS child_detail_1_id,
    child_detail_1.parent_id AS child_detail_1_parent_id,
    anon_1.id AS anon_1_id,
    anon_1.parent_id AS anon_1_parent_id,
    anon_1.age AS anon_1_age,
    anon_1.name AS anon_1_name 
FROM
    parent
    LEFT OUTER JOIN (
    SELECT
        child.id AS id,
        child.parent_id AS parent_id,
        child.age AS age,
        child.name AS name 
    FROM
        child 
    WHERE
        child.id > 1 
    ) AS anon_1 ON parent.id = anon_1.parent_id
    LEFT OUTER JOIN child_detail AS child_detail_1 ON anon_1.id = child_detail_1.parent_id;
"""

query5-6

"""
query5则是在join的on做Child.id > 1, 除了and_还有or_等其他操作
query6则是在query5的基础上增加了仅查字段
注意: 在query中joinedload(Parent.children.and_(Child.id >= 1), 
实际上在模型的primaryjoin中, primaryjoin=and_(foreign(Child.parent_id), id > 1)也可以获得同等效果, 一个是动态, 一个是写死
"""
query5 = session.query(Parent).options(joinedload(Parent.children.and_(Child.id > 1)).joinedload(Child.detail))
"""
SQL输出:
SELECT
    parent.id AS parent_id,
    child_detail_1.id AS child_detail_1_id,
    child_detail_1.parent_id AS child_detail_1_parent_id,
    child_1.id AS child_1_id,
    child_1.parent_id AS child_1_parent_id,
    child_1.age AS child_1_age,
    child_1.name AS child_1_name 
FROM
    parent
    LEFT OUTER JOIN child AS child_1 ON parent.id = child_1.parent_id AND child_1.id > 1
    LEFT OUTER JOIN child_detail AS child_detail_1 ON child_1.id = child_detail_1.parent_id AND child_1.id > 1;
"""
query6 = session.query(Parent).options(joinedload(Parent.children.and_(Child.id >= 1)).load_only(Child.id).joinedload(Child.detail))
"""
SQL输出:
SELECT
    parent.id AS parent_id,
    child_detail_1.id AS child_detail_1_id,
    child_detail_1.parent_id AS child_detail_1_parent_id,
    child_1.id AS child_1_id 
FROM
    parent
    LEFT OUTER JOIN child AS child_1 ON parent.id = child_1.parent_id 
    AND child_1.id >= ?
    LEFT OUTER JOIN child_detail AS child_detail_1 ON child_1.id = child_detail_1.parent_id 
    AND child_1.id >= ?
"""

query4-5-6都得到了想要的结果, 差别在于4是子查询,

目前没有测试过, 2个方法暂时未知哪个会比较好,

理论上query4的方式会更加灵活一点,

不过仔细看query5-6, and_影响到了两个LEFT OUTER JOIN, 他们最终都带上了child_1.id > 1

在sqlalchemy1.4版本2.0-style提供了with_loader_criteria

The with_loader_criteria() option is intended to add limiting criteria to a particular kind of entity in a query, globally, meaning it will apply to the entity as it appears in the SELECT query as well as within any subqueries, join conditions, and relationship loads, including both eager and lazy loaders, without the need for it to be specified in any particular part of the query. The rendering logic uses the same system used by single table inheritance to ensure a certain discriminator is applied to a table.

query7

from sqlalchemy.orm import with_loader_criteria
query7 = session.query(Parent).options(
    joinedload(Parent.children).joinedload(Child.detail), 
    with_loader_criteria(Child, Child.id > 1),
    with_loader_criteria(ChildDetail, ChildDetail.id > 1)
)
"""
SELECT
    parent.id AS parent_id,
    parent.name AS parent_name,
    child_detail_1.id AS child_detail_1_id,
    child_detail_1.parent_id AS child_detail_1_parent_id,
    child_1.id AS child_1_id,
    child_1.parent_id AS child_1_parent_id,
    child_1.age AS child_1_age,
    child_1.name AS child_1_name 
FROM
    parent
    LEFT OUTER JOIN child AS child_1 ON parent.id = child_1.parent_id 
    AND child_1.id > 1
    LEFT OUTER JOIN child_detail AS child_detail_1 ON child_1.id = child_detail_1.parent_id 
    AND child_detail_1.id > 1;
"""

如果本文对你有启发,或者对本文有疑问或者功能/方法建议,可以在下方做出评论,或者直接联系我,谢谢您的观看和支持!

添加新评论

本站现已启用评论投票,被点踩过多的评论将自动折叠。与本文无关评论请发留言板。请不要水评论,谢谢。

已有 0条评论