[程序]sqlalchemy文档资料翻译(七) -- 使用关联对象
现在当我们创建一个 User 对象,将会生成一个空的 addresses 集。像集合和 map 这样的各种各样的数据类型都有可能在此用到(详细参考 Alternate Collection Implementations),但通常来说该数据集返回 python 链表。
>>> jack = User('jack', 'Jack Bean', 'gjffdd')
>>> jack.addresses
[]
我们现在可以随意的为 User 对象添加 Address 对象。此处,我们直接为其赋值一个链表:
>>> jack.addresses = [Address(email_address='jack@google.com'), Address(email_address='j25@yahoo.com')]
当使用了双向链接,在一方添加元素时另一方会自动获得该链接。这是 backref 关键字的作用,他负责在内存而非通过 SQL 维护链接关系:
>>> jack.addresses[1]
<Address('j25@yahoo.com')>
>>> jack.addresses[1].user
<User('jack','Jack Bean', 'gjffdd')>
此时我们添加并提交 Jack Bean 到数据库中。jack 和他的 addresses 表中的 Address members 在本次会话中通过一种被称为 cascading(级联) 的方法一起提交:
>>> session.add(jack)
>>> session.commit()
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('jack', 'Jack Bean', 'gjffdd')
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('jack@google.com', 5)
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('j25@yahoo.com', 5)
COMMIT
此时查询 Jack 的信息,我们只获得 Jack,并没有生成 SQL 去获取 Jack 的 addresses:
>>> jack = session.query(User).filter_by(name='jack').one() >>> jack <User('jack','Jack Bean', 'gjffdd')>
接下来我们来查询 addresses,看看 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 ORDER BY addresses.id
(5,)
[<Address('jack@google.com')>, <Address('j25@yahoo.com')>]
当我们访问 addresses 集合时,才生成SQL语句。这是一个延迟加载关系的实例。 addresses 集合此时就像对待一个原始列表一样载入之。
如果想减少查询的次数(戏剧的是,大多数情况均是如此),我们可以在查询操作时通过使用 joinedload() 函数立即检索。该函数是一项用于指定查询如何载入的额外选项,此例中,我们希望 addresses 立即载入。SQLAlchemy 此时创建一个用于联接 users 和 addresses 表的外联接,并立即载入他们,并利用 addresses 集合填充每一个 User 对象:
>>> from sqlalchemy.orm import joinedload
>>> jack = session.query(User).\
... options(joinedload('addresses')).\
... filter_by(name='jack').one()
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname,
users.password AS users_password, 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 = ? ORDER BY addresses_1.id
('jack',)
>>> jack
<User('jack','Jack Bean', 'gjffdd')>
>>> jack.addresses
[<Address('jack@google.com')>, <Address('j25@yahoo.com')>]
更多 joinedload() 及其兄弟函数 subqueryload() 的信息参考 Configuring Loader Strategies: Lazy Loading, Eager