SQLAlchemy教程

SQLAlchemy 预加载

预加载减少了查询的数量。 SQLAlchemy 提供了通过查询选项调用的预加载函数,这些函数为查询提供了额外的指令。这些选项决定了如何通过 Query.options() 方法加载各种属性。

子查询加载

我们希望 Customer.invoices 应该立即加载。 orm.subqueryload() 选项提供了第二个 SELECT 语句,该语句完全加载与刚刚加载的结果相关联的集合。名称"子查询"导致 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() 更适合多对一关系。
昵称: 邮箱:
Copyright © 2022 立地货 All Rights Reserved.
备案号:京ICP备14037608号-4