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')>]
