Sqlalchemy 简明教程

SQLAlchemy ORM - Eager Loading

渴望加载减少了查询的数量。SQLAlchemy 提供了渴望加载函数，是通过查询选项调用的，这些选项对查询提供了附加说明。这些选项通过 Query.options() 方法来确定如何加载各种属性。

Subquery Load

我们想要 Customer.invoices 能够渴望加载。orm.subqueryload() 选项提供了一个第二个 SELECT 语句，该语句完全加载了与刚刚加载的结果相关联的集合。名称“子查询”导致 SELECT 语句直接通过重复利用并作为子查询嵌入到针对相关表的 SELECT 中来构建。

from sqlalchemy.orm import subqueryload
c1 = session.query(Customer).options(subqueryload(Customer.invoices)).filter_by(name = 'Govind Pant').one()

这会导致以下两个 SQL 表达式：

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

要访问来自两个表的 data，我们可以使用下面的程序：

print (c1.name, c1.address, c1.email)

for x in c1.invoices:
   print ("Invoice no : {}, Amount : {}".format(x.invno, x.amount))

以上程序的输出如下：

Govind Pant Gulmandi Aurangabad gpant@gmail.com
Invoice no : 3, Amount : 10000
Invoice no : 4, Amount : 5000

Joined Load

另一个函数称为 orm.joinedload()。这会发出一个 LEFT OUTER JOIN。在一步中加载了 Lead 对象以及相关对象或集合。

from sqlalchemy.orm import joinedload
c1 = session.query(Customer).options(joinedload(Customer.invoices)).filter_by(name='Govind Pant').one()

这发出了以下表达式，给出了与上面相同的输出：

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

OUTER JOIN 生成了两行，但它返回了一个 Customer 实例。这是因为查询对返回的实体应用了一种基于对象标识的“唯一化”策略。连接渴望加载可以在不影响查询结果的情况下应用。

subqueryload() 更适合加载相关集合，而 joinedload() 更适合多对一关系。