class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key = True)
children = relationship("Child", lazy = 'joined')# set children to load lazily
session.query(Parent).options(lazyload(Parent.children)).all()
# set children to load eagerly with a join
session.query(Parent).options(joinedload(Parent.children)).all()session.query(Parent).options( joinedload(Parent.children).subqueryload(Child.subelements)).all()
session.query(Parent).options( lazyload(Parent.children).subqueryload(Child.subelements)).all()
stmt = select(Parent).options( lazyload(Parent.children).subqueryload(Child.subelements)) result = session.execute(stmt)
session.query(A).options(lazyload(A.bs.and_(B.id > 5)))Eager load reduces the number of queries. SQLAlchemy offers eager loading functions invoked via query options which give additional instructions to the Query. These options determine how to load various attributes via the Query.options() method.,The OUTER JOIN resulted in two rows, but it gives one instance of Customer back. This is because Query applies a “uniquing” strategy, based on object identity, to the returned entities. Joined eager loading can be applied without affecting the query results.,We want that Customer.invoices should load eagerly. The orm.subqueryload() option gives a second SELECT statement that fully loads the collections associated with the results just loaded. The name “subquery” causes the SELECT statement to be constructed directly via the Query re-used and embedded as a subquery into a SELECT against the related table.,The subqueryload() is more appropriate for loading related collections while joinedload() is better suited for many-to-one relationship.
We want that Customer.invoices should load eagerly. The orm.subqueryload() option gives a second SELECT statement that fully loads the collections associated with the results just loaded. The name “subquery” causes the SELECT statement to be constructed directly via the Query re-used and embedded as a subquery into a SELECT against the related table.
from sqlalchemy.orm
import subqueryload
c1 = session.query(Customer).options(subqueryload(Customer.invoices)).filter_by(name = 'Govind Pant').one()This results in the following two SQL expressions −
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers
WHERE customers.name = ?
('Govind Pant', )
SELECT invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount, anon_1.customers_id
AS anon_1_customers_id
FROM(
SELECT customers.id AS customers_id FROM customers WHERE customers.name = ? )
AS anon_1
JOIN invoices
ON anon_1.customers_id = invoices.custid
ORDER BY anon_1.customers_id, invoices.id 2018 - 06 - 25 18: 24: 47, 479
INFO sqlalchemy.engine.base.Engine('Govind Pant', )To access the data from two tables, we can use the below program −
print(c1.name, c1.address, c1.email)
for x in c1.invoices:
print("Invoice no : {}, Amount : {}".format(x.invno, x.amount))The other function is called orm.joinedload(). This emits a LEFT OUTER JOIN. Lead object as well as the related object or collection is loaded in one step.
from sqlalchemy.orm
import joinedload
c1 = session.query(Customer).options(joinedload(Customer.invoices)).filter_by(name = 'Govind Pant').one()This emits following expression giving same output as above −
SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email, invoices_1.id
AS invoices_1_id, invoices_1.custid
AS invoices_1_custid, invoices_1.invno
AS invoices_1_invno, invoices_1.amount
AS invoices_1_amount
FROM customers
LEFT OUTER JOIN invoices
AS invoices_1
ON customers.id = invoices_1.custid
WHERE customers.name = ? ORDER BY invoices_1.id('Govind Pant', )When using joined eager loading, if the query contains a modifier that impacts the rows returned externally to the joins, such as when using DISTINCT, LIMIT, OFFSET or equivalent, the completed statement is first wrapped inside a subquery, and the joins used specifically for joined eager loading are applied to the subquery. SQLAlchemy’s joined eager loading goes the extra mile, and then ten miles further, to absolutely ensure that it does not affect the end result of the query, only the way collections and related objects are loaded, no matter what the format of the query is.,When using the default lazy loading, if you load 100 objects, and then access a collection on each of them, a total of 101 SQL statements will be emitted, although each statement will typically be a simple SELECT without any joins.,The above query, linking A.bs via “outer” join and B.cs via “inner” join would render the joins as “a LEFT OUTER JOIN (b JOIN c)”. When using SQLite, this form of JOIN is translated to use full subqueries as this syntax is otherwise not directly supported.,In order to chain multiple eager joins together where some may be OUTER and others INNER, right-nested joins are used to link them:
sql>>> jack.addresses SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id FROM addresses WHERE ? = addresses.user_id [5] [<Address(u'jack@google.com')>, <Address(u'j25@yahoo.com')>]
sql >>> jack = session.query(User).\
...options(joinedload('addresses')).\
...filter_by(name = 'jack').all() #doctest: +NORMALIZE_WHITESPACE
SELECT addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address,
addresses_1.user_id AS addresses_1_user_id, users.id AS users_id, users.name AS users_name,
users.fullname AS users_fullname, users.password AS users_password
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
WHERE users.name = ? ['jack']sql >>> jack = session.query(User).\
...options(subqueryload('addresses')).\
...filter_by(name = 'jack').all()
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE users.name = ?
('jack', )
SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address,
addresses.user_id AS addresses_user_id, anon_1.users_id AS anon_1_users_id
FROM(SELECT users.id AS users_id FROM users WHERE users.name = ? ) AS anon_1 JOIN addresses ON anon_1.users_id = addresses.user_id
ORDER BY anon_1.users_id, addresses.id('jack', )# load the 'children'
collection using LEFT OUTER JOIN
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key = True)
children = relationship("Child", lazy = 'joined')# load the 'children'
collection using a second query which
# JOINS to a subquery of the original
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key = True)
children = relationship("Child", lazy = 'subquery')# set children to load lazily
session.query(Parent).options(lazyload('children')).all()
# set children to load eagerly with a join
session.query(Parent).options(joinedload('children')).all()
# set children to load eagerly with a second statement
session.query(Parent).options(subqueryload('children')).all()Posted on Feb 12, 2020
class User(db.Model):
id = db.Column(db.Integer, primary_key = True, autoincrement = True)
username = db.Column(
db.String(20),
nullable = False,
)
password_hash = db.Column(db.VARCHAR(130), nullable = False)
memberships = db.relationship('Membership',
back_populates = 'member')
class Company(db.Model):
id = db.Column(db.Integer, primary_key = True, autoincrement = True)
name = db.Column(db.String(120), nullable = False)
website = db.Column(db.String(), nullable = False)
address = db.Column(db.String(), nullable = False)
memberships = db.relationship('Membership',
back_populates = 'company')
class Membership(db.Model):
id = db.Column(db.Integer, primary_key = True, autoincrement = True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
company_id = db.Column(db.Integer,
db.ForeignKey('company.id'))
role = db.Column(db.String,
default = 'REGULAR_USER')
member = db.relationship("User", back_populates = "memberships")
company = db.relationship('Company', back_populates = "memberships")>>> membership = Membership.query.first()
INFO: sqlalchemy.engine.base.Engine: SELECT membership.id AS membership_id, membership.user_id AS membership_user_id, membership.company_id AS membership_company_id, membership.role AS membership_role FROM membership LIMIT % (param_1) s
>>> membership.member.username
INFO: sqlalchemy.engine.base.Engine: SELECT "user".id AS user_id, "user".username AS user_username, "user".password_hash AS user_password_hash
FROM "user"
WHERE "user".id = % (param_1) s
INFO: sqlalchemy.engine.base.Engine: {
'param_1': 1
}>>> membership.company.name
The loading of relationships falls into three categories; lazy loading, eager loading, and no loading. Lazy loading refers to objects are returned from a query without the related objects loaded at first. When the given collection or reference is first accessed on a particular object, an additional SELECT statement is emitted such that the requested collection is loaded.,"No" loading refers to the disabling of loading on a given relationship, either that the attribute is empty and is just never loaded, or that it raises an error when it is accessed, in order to guard against unwanted lazy loads.,Above, whenever a collection of Parent objects are loaded, each Parent will also have its children collection populated, using rows fetched by adding a JOIN to the query for Parent objects. See :ref:`joined_eager_loading` for background on this style of loading.,The special ordering requirements of subqueryload described at :ref:`subqueryload_ordering` also don't apply to selectin loading; selectin is always linking directly to a parent primary key and can't really return the wrong result.
The loader options applied to an object's lazy-loaded collections are "sticky" to specific object instances, meaning they will persist upon collections loaded by that specific object for as long as it exists in memory. For example, given the previous example:
session.query(Parent).options( lazyload(Parent.children).subqueryload(Child.subelements)).all()
if the children collection on a particular Parent object loaded by
the above query is expired (such as when a :class:`.Session` object's
transaction is committed or rolled back, or :meth:`.Session.expire_all` is
used), when the Parent.children collection is next accessed in order to
re-load it, the Child.subelements collection will again be loaded using
subquery eager loading.This stays the case even if the above Parent
object is accessed from a subsequent query that specifies a different set of
options.To change the options on an existing object without expunging it and
re-loading, they must be set explicitly in conjunction with the
:meth:`_query.Query.populate_existing` method:
# change the options on Parent objects that were already loaded session.query(Parent).populate_existing().options( lazyload(Parent.children).lazyload(Child.subelements)).all()
March 09, 2020Pauline Huguenel7 min read
relationship(ProjectModel, lazy = "select")class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key = True)
child_id = Column(Integer, ForeignKey('child.id'))
child = relationship("Child", lazy = "select")
# emits a SELECT statement to get the parent object
parent = session.query(Parent).first()
# emits a second SELECT statement to get the child object
child = parent.childclass Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key = True)
child_id = Column(Integer, ForeignKey('child.id'))
child = relationship("Child", lazy = "joined")
# emits a SELECT statement to get the parent object and its children
parent = session.query(Parent).first()
# does not emit a second SELECT statement as child object is already loaded
child = parent.childclass Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key = True)
manager_id = Column(Integer, ForeignKey('person.id'))
manager = relationship("Person", lazy = "joined", join_depth = 1)
person = session.query(Person).first()
person.manager # eager - loaded(one level deep)
person.manager.manager # lazy - loaded(two levels deep)class Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key = True)
firstname = Column(Text)
lastname = Column(Text)
address_id = Column(Integer, ForeignKey('address.id'))
address = relationship('Address')
manager_id = Column(Integer, ForeignKey(person.id))
manager = relationship('Person')
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key = True)
number = Column(Integer)
street = Column(Text)
city = Column(Text)
country = Column(Text)Person.query.options(joinedload("manager"))