SQLAlchemy ORM - 急切載入

急切載入減少了查詢次數。SQLAlchemy 提供了透過查詢選項呼叫的急切載入函式，這些函式為 Query 提供了額外的指令。這些選項決定了如何透過 Query.options() 方法載入各種屬性。

子查詢載入

我們希望 Customer.invoices 能夠急切載入。orm.subqueryload() 選項提供了第二個 SELECT 語句，該語句完全載入與剛剛載入的結果關聯的集合。名稱“subquery”導致 SELECT 語句透過 Query 重新使用並嵌入到針對相關表的 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',)

要訪問兩個表中的資料，我們可以使用以下程式：

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

連線載入

另一個函式稱為 orm.joinedload()。這會發出一個 LEFT OUTER JOIN。引導物件以及相關物件或集合在一步驟中載入。

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 例項。這是因為 Query 基於物件標識對返回的實體應用了“唯一化”策略。連線急切載入可以在不影響查詢結果的情況下應用。

subqueryload() 更適合載入相關集合，而 joinedload() 更適合多對一關係。