SQLAlchemy lazy load和eager load

lazy load 和 eager load

SQLAlchemy支持lazy load, eager load和no load 三种关联对象的查询方式。默认的是lazy load。
lazy load 将会返回一个对象，先不会对关联对象进行查询。直到第一次访问其关联对象，才会进行关联对象查询
eager load 会在返回对象前进行关联对象的查询。

lazy load

例如：

我们先创建user和address两张表，并插入数据：

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, String, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.orm import selectinload, joinedload, contains_eager

engine = create_engine('sqlite:///:memory:', echo=True)

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

    def __repr__(self):
       return "<User(name='%s', fullname='%s', nickname='%s')>" % (
                            self.name, self.fullname, self.nickname)

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))

    user = relationship("User", back_populates="addresses")

    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address

User.addresses = relationship(
     "Address", back_populates="user")


Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

jack = User(name='jack', fullname='Jack Bean', nickname='gjffdd')
jack.addresses = [Address(email_address='jack@google.com'),
                  Address(email_address='j25@yahoo.com')]

ed = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
ed.addresses = [Address(email_address='ed@google.com')]

session.add(jack)
session.add(ed)
session.commit()

对其进行默认的lazy load:

users = session.query(User).join(Address).filter(or_(User.name=='jack', User.name=='ed')).all()

log显示它只运行了SQL:

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.nickname AS users_nickname 
FROM users JOIN addresses ON users.id = addresses.user_id 
WHERE users.name = 'jack' OR users.name = 'ed'

直到访问user的addresses：

for user in users:
    print(user.addresses)
    
# 输出：
# [<Address(email_address='j25@yahoo.com')>, <Address(email_address='jack@google.com')>]
# [<Address(email_address='ed@google.com')>]

才查询其addresses:

SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE 1 = addresses.user_id

SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses 
WHERE 2 = addresses.user_id

eager load

SQLAlchemy根据实现方式，有selectinload, joinedload和Join + Eagerload三种eager load的实现方式

selectinload

users = session.query(User)
        .options(selectinload(User.addresses))
        .filter(or_(User.name=='jack', User.name=='ed')).all()

将会执行：

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.nickname AS users_nickname 
FROM users 
WHERE users.name = 'jack' OR users.name = 'ed'

SELECT addresses.user_id AS addresses_user_id, addresses.id AS addresses_id, addresses.email_address AS addresses_email_address 
FROM addresses 
WHERE addresses.user_id IN (1, 2)

在访问时就可以直接获得数据

for user in users:
    print(user.addresses)
# 输出：
# [<Address(email_address='j25@yahoo.com')>, <Address(email_address='jack@google.com')>]
# [<Address(email_address='ed@google.com')>]

需要注意的是以下这种多对一的情况：

addresses = session.query(Address)
            .options(selectinload(Address.user))
            .filter(User.name=='jack').all()
for address in addresses:
    print(address)
# 输出：
# <Address(email_address='j25@yahoo.com')>
# <Address(email_address='jack@google.com')>
# <Address(email_address='ed@google.com')>

对addresses实际上是没有filter的

SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses, users 
WHERE users.name = 'jack'

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.nickname AS users_nickname 
FROM users 
WHERE users.id IN (1, 2)

这种情况需要使用Join + Eagerload的方法

joinedload

会直接进行left out join

users = session.query(User)
        .options(joinedload(User.addresses))
        .filter(or_(User.name=='jack', User.name=='ed')).all()

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.nickname AS users_nickname, 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 
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id 
WHERE users.name = 'jack' OR users.name = 'ed'

在访问时就可以直接获得数据

for user in users:
    print(user.addresses)
# 输出：
# [<Address(email_address='j25@yahoo.com')>, <Address(email_address='jack@google.com')>]
# [<Address(email_address='ed@google.com')>]

同样需要注意的是多对一的情况：

addresses = session.query(Address)
            .options(joinedload(Address.user))
            .filter(User.name=='jack').all()
for address in addresses:
    print(address)
# 输出：
# <Address(email_address='j25@yahoo.com')>
# <Address(email_address='jack@google.com')>
# <Address(email_address='ed@google.com')>

SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id, users_1.id AS users_1_id, users_1.name AS users_1_name, users_1.fullname AS users_1_fullname, users_1.nickname AS users_1_nickname 
FROM users, addresses LEFT OUTER JOIN users AS users_1 ON users_1.id = addresses.user_id 
WHERE users.name = 'jack'

Join + Eagerload

addresses = session.query(Address)
            .join(Address.user)
            .filter(User.name=='jack')
            .options(contains_eager(Address.user)).all()

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.nickname AS users_nickname, addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id 
FROM addresses JOIN users ON users.id = addresses.user_id 
WHERE users.name = 'jack'

for address in addresses:
    print(address)
# 输出：
# [<Address(email_address='j25@yahoo.com')>
# <Address(email_address='jack@google.com')>]