SQLAlchemy ORM - praca z połączeniami

Teraz, gdy mamy dwie tabele, zobaczymy, jak tworzyć zapytania w obu tabelach w tym samym czasie. Aby skonstruować proste niejawne sprzężenie między klientem a fakturą, możemy użyć Query.filter (), aby zrównać ich powiązane kolumny. Poniżej wczytujemy jednocześnie podmioty Klient i Faktura za pomocą tej metody -

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

Wyrażenie SQL emitowane przez SQLAlchemy jest następujące -

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

Wynik powyższych wierszy kodu jest następujący -

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

Rzeczywistą składnię SQL JOIN można łatwo uzyskać za pomocą metody Query.join () w następujący sposób -

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

Wyrażenie SQL do łączenia zostanie wyświetlone na konsoli -

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

Możemy iterować wynik za pomocą pętli 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)

Z 8500 jako parametrem powiązania, wyświetlane są następujące dane wyjściowe -

4 Govind Kala 8 8500

Query.join () wie, jak łączyć te tabele, ponieważ między nimi jest tylko jeden klucz obcy. Jeśli nie było kluczy obcych lub więcej kluczy obcych, Query.join () działa lepiej, gdy używana jest jedna z następujących form -

query.join (Faktura, id == Adres.custid)	wyraźny warunek
query.join (Customer.invoices)	określ relację od lewej do prawej
query.join (faktura, faktury klienta)	to samo, z wyraźnym celem
query.join ('faktury')	to samo, używając ciągu

Podobnie, funkcja externaljoin () jest dostępna do uzyskania lewego zewnętrznego sprzężenia.

query.outerjoin(Customer.invoices)

Metoda subquery () generuje wyrażenie SQL reprezentujące instrukcję SELECT osadzoną w aliasie.

from sqlalchemy.sql import func

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

Obiekt stmt będzie zawierał instrukcję SQL, jak poniżej -

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

Kiedy już mamy naszą instrukcję, zachowuje się ona jak konstrukcja Table. Kolumny w instrukcji są dostępne za pośrednictwem atrybutu o nazwie c, jak pokazano w poniższym kodzie -

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)

Powyższa pętla for wyświetla liczbę faktur według nazwy w następujący sposób -

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