SQLAlchemy ORM - Trabalhando com Joins

Agora que temos duas tabelas, veremos como criar consultas nas duas tabelas ao mesmo tempo. Para construir uma junção implícita simples entre Cliente e Fatura, podemos usar Query.filter () para igualar suas colunas relacionadas. Abaixo, carregamos as entidades Cliente e Fatura de uma só vez usando este método -

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

A expressão SQL emitida por SQLAlchemy é a seguinte -

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 o resultado das linhas de código acima é o seguinte -

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

A sintaxe SQL JOIN real é facilmente obtida usando o método Query.join () da seguinte forma -

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

A expressão SQL para junção será exibida no 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 = ?

Podemos iterar o resultado usando o loop 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)

Com 8500 como o parâmetro de ligação, a seguinte saída é exibida -

4 Govind Kala 8 8500

Query.join () sabe como unir essas tabelas porque existe apenas uma chave estrangeira entre elas. Se não houver chaves estrangeiras, ou mais chaves estrangeiras, Query.join () funciona melhor quando uma das seguintes formas é usada -

query.join (Invoice, id == Address.custid)	condição explícita
query.join (Customer.invoices)	especificar relação da esquerda para a direita
query.join (Invoice, Customer.invoices)	mesmo, com alvo explícito
query.join ('invoices')	mesmo, usando uma corda

Da mesma forma, a função outerjoin () está disponível para obter a junção externa esquerda.

query.outerjoin(Customer.invoices)

O método subquery () produz uma expressão SQL que representa a instrução SELECT embutida em um alias.

from sqlalchemy.sql import func

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

O objeto stmt conterá uma instrução SQL conforme abaixo -

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

Assim que tivermos nossa declaração, ela se comportará como uma construção de Tabela. As colunas na instrução são acessíveis por meio de um atributo chamado c, conforme mostrado no código a seguir -

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)

O loop for acima exibe a contagem por nome de faturas da seguinte forma -

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