SQLAlchemy ORM - 使用連線

現在我們有兩個表，我們將瞭解如何同時對這兩個表建立查詢。要構建 Customer 和 Invoice 之間的簡單隱式連線，我們可以使用 Query.filter() 將其相關列關聯起來。下面，我們使用此方法一次載入 Customer 和 Invoice 實體：

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

for c, i in session.query(Customer, Invoice).filter(Customer.id == Invoice.custid).all():
   print ("ID: {} Name: {} Invoice No: {} Amount: {}".format(c.id,c.name, i.invno, i.amount))

SQLAlchemy 發出的 SQL 表示式如下：

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email, invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount 
AS invoices_amount
FROM customers, invoices
WHERE customers.id = invoices.custid

以上程式碼行的結果如下：

ID: 2 Name: Gopal Krishna Invoice No: 10 Amount: 15000
ID: 2 Name: Gopal Krishna Invoice No: 14 Amount: 3850
ID: 3 Name: Govind Pant Invoice No: 3 Amount: 10000
ID: 3 Name: Govind Pant Invoice No: 4 Amount: 5000
ID: 4 Name: Govind Kala Invoice No: 7 Amount: 12000
ID: 4 Name: Govind Kala Invoice No: 8 Amount: 8500
ID: 5 Name: Abdul Rahman Invoice No: 9 Amount: 15000
ID: 5 Name: Abdul Rahman Invoice No: 11 Amount: 6000

可以使用 Query.join() 方法輕鬆實現實際的 SQL JOIN 語法，如下所示：

session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()

連線的 SQL 表示式將顯示在控制檯上：

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers JOIN invoices ON customers.id = invoices.custid
WHERE invoices.amount = ?

我們可以使用 for 迴圈遍歷結果：

result = session.query(Customer).join(Invoice).filter(Invoice.amount == 8500)
for row in result:
   for inv in row.invoices:
      print (row.id, row.name, inv.invno, inv.amount)

以 8500 作為繫結引數，將顯示以下輸出：

4 Govind Kala 8 8500  

Query.join() 知道如何在這些表之間連線，因為它們之間只有一個外部索引鍵。如果沒有外部索引鍵或多個外部索引鍵，當使用以下其中一種形式時，Query.join() 效果更好：

類似地，outerjoin() 函式可用於實現左外連線。

query.outerjoin(Customer.invoices)

subquery() 方法生成一個 SQL 表示式，表示嵌入別名中的 SELECT 語句。

from sqlalchemy.sql import func

stmt = session.query(
   Invoice.custid, func.count('*').label('invoice_count')
).group_by(Invoice.custid).subquery()

stmt 物件將包含如下 SQL 語句：

SELECT invoices.custid, count(:count_1) AS invoice_count FROM invoices GROUP BY invoices.custid

獲得語句後，它的行為類似於 Table 構造。語句上的列可以透過名為 c 的屬性訪問，如下面的程式碼所示：

for u, count in session.query(Customer, stmt.c.invoice_count).outerjoin(stmt, Customer.id == stmt.c.custid).order_by(Customer.id):
   print(u.name, count)

上面的 for 迴圈按名稱顯示發票計數，如下所示：

Arjun Pandit None
Gopal Krishna 2
Govind Pant 2
Govind Kala 2
Abdul Rahman 2