SQLAlchemy ORM - Utilisation des jointures

Maintenant que nous avons deux tables, nous allons voir comment créer des requêtes sur les deux tables en même temps. Pour construire une simple jointure implicite entre le client et la facture, nous pouvons utiliser Query.filter () pour assimiler leurs colonnes associées. Ci-dessous, nous chargeons les entités Client et Facture à la fois en utilisant cette méthode -

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

L'expression SQL émise par SQLAlchemy est la suivante -

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

Et le résultat des lignes de code ci-dessus est le suivant -

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

La syntaxe SQL JOIN réelle est facilement obtenue en utilisant la méthode Query.join () comme suit -

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

L'expression SQL pour la jointure sera affichée sur la console -

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 = ?

Nous pouvons parcourir le résultat en utilisant la boucle 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)

Avec 8500 comme paramètre de liaison, la sortie suivante est affichée -

4 Govind Kala 8 8500

Query.join () sait comment se joindre entre ces tables car il n'y a qu'une seule clé étrangère entre elles. S'il n'y avait pas de clés étrangères, ou plusieurs clés étrangères, Query.join () fonctionne mieux lorsque l'une des formes suivantes est utilisée -

query.join (Facture, id == Address.custid)	condition explicite
query.join (Factures.client)	spécifier la relation de gauche à droite
query.join (Facture, Factures.Client)	idem, avec cible explicite
query.join ('factures')	idem, en utilisant une chaîne

De même, la fonction externaljoin () est disponible pour réaliser une jointure externe gauche.

query.outerjoin(Customer.invoices)

La méthode subquery () produit une expression SQL représentant l'instruction SELECT incorporée dans un alias.

from sqlalchemy.sql import func

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

L'objet stmt contiendra une instruction SQL comme ci-dessous -

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

Une fois que nous avons notre instruction, elle se comporte comme une construction Table. Les colonnes de l'instruction sont accessibles via un attribut appelé c comme indiqué dans le code ci-dessous -

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)

La boucle for ci-dessus affiche le nombre de factures par nom comme suit -

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