SQLAlchemy ORM-結合の使用

2つのテーブルができたので、両方のテーブルに同時にクエリを作成する方法を確認します。 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

実際のSQL JOIN構文は、次のようにQuery.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（）は、これらのテーブル間に外部キーが1つしかないため、これらのテーブルを結合する方法を知っています。外部キーが存在しない場合、またはより多くの外部キーが存在しない場合、Query.join（）は、次の形式のいずれかが使用された場合により良く機能します-

query.join(Invoice, id == Address.custid)	explicit condition
query.join(Customer.invoices)	specify relationship from left to right
query.join(Invoice, Customer.invoices)	same, with explicit target
query.join('invoices')	same, using a string

同様に、outerjoin（）関数を使用して、左外部結合を実現できます。

query.outerjoin(Customer.invoices)

subquery（）メソッドは、エイリアス内に埋め込まれたSELECTステートメントを表すSQL式を生成します。

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

Sqlalchemy-orm-working-with-joins

SQLAlchemy ORM-結合の使用