SQLAlchemy ORM - Lavorare con i join

Ora che abbiamo due tabelle, vedremo come creare query su entrambe le tabelle contemporaneamente. Per costruire un semplice join implicito tra Customer e Invoice, possiamo usare Query.filter () per equiparare tra loro le colonne correlate. Di seguito, carichiamo le entità Cliente e Fattura contemporaneamente utilizzando questo metodo:

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'espressione SQL emessa da SQLAlchemy è la seguente:

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

E il risultato delle righe di codice precedenti è il seguente:

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

L'attuale sintassi SQL JOIN può essere facilmente ottenuta utilizzando il metodo Query.join () come segue:

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

L'espressione SQL per il join verrà visualizzata sulla 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 = ?

Possiamo iterare attraverso il risultato usando il ciclo 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)

Con 8500 come parametro bind, viene visualizzato il seguente output:

4 Govind Kala 8 8500

Query.join () sa come unire queste tabelle perché tra di loro c'è solo una chiave esterna. Se non c'erano chiavi esterne, o più chiavi esterne, Query.join () funziona meglio quando viene utilizzata una delle seguenti forme:

query.join (Fattura, id == Address.custid)	condizione esplicita
query.join (Customer.invoices)	specificare la relazione da sinistra a destra
query.join (Invoice, Customer.invoices)	stesso, con target esplicito
query.join ("fatture")	stesso, usando una stringa

Allo stesso modo la funzione outerjoin () è disponibile per ottenere il join esterno sinistro.

query.outerjoin(Customer.invoices)

Il metodo subquery () produce un'espressione SQL che rappresenta l'istruzione SELECT incorporata in un alias.

from sqlalchemy.sql import func

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

L'oggetto stmt conterrà un'istruzione SQL come di seguito:

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

Una volta che abbiamo la nostra dichiarazione, si comporta come un costrutto di tabella. Le colonne dell'istruzione sono accessibili tramite un attributo chiamato c come mostrato nel codice seguente -

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)

Il ciclo for precedente mostra il conteggio delle fatture in base al nome come segue:

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