Sqlalchemy 简明教程

SQLAlchemy ORM - Eager Loading

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

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.

Subquery Load

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

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()

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

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

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

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 output of the above program is as follows −

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 对象以及相关对象或集合。

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

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

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.

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

The subqueryload() is more appropriate for loading related collections while joinedload() is better suited for many-to-one relationship.