SQLAlchemy ORM: trabajo con combinaciones

Ahora que tenemos dos tablas, veremos cómo crear consultas en ambas tablas al mismo tiempo. Para construir una unión implícita simple entre Cliente y Factura, podemos usar Query.filter () para equiparar sus columnas relacionadas. A continuación, cargamos las entidades Cliente y Factura a la vez utilizando 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))

La expresión SQL emitida por SQLAlchemy es la siguiente:

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

Y el resultado de las líneas de código anteriores es el siguiente:

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 sintaxis de SQL JOIN real se logra fácilmente usando el método Query.join () de la siguiente manera:

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

La expresión SQL para unión se mostrará en la consola:

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 a través del resultado usando for loop -

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 como parámetro de vinculación, se muestra la siguiente salida:

4 Govind Kala 8 8500

Query.join () sabe cómo unirse entre estas tablas porque solo hay una clave externa entre ellas. Si no hubiera claves externas, o más claves externas, Query.join () funciona mejor cuando se usa una de las siguientes formas:

query.join (Factura, id == Address.custid)	condición explícita
query.join (Customer.invoices)	especificar la relación de izquierda a derecha
query.join (Factura, Customer.invoices)	lo mismo, con objetivo explícito
query.join ('facturas')	lo mismo, usando una cuerda

De manera similar, la función outsidejoin () está disponible para lograr la unión externa izquierda.

query.outerjoin(Customer.invoices)

El método subquery () produce una expresión SQL que representa la instrucción SELECT incrustada dentro de un alias.

from sqlalchemy.sql import func

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

El objeto stmt contendrá una declaración SQL como se muestra a continuación:

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

Una vez que tenemos nuestra declaración, se comporta como una construcción de tabla. Se puede acceder a las columnas de la declaración a través de un atributo llamado c, como se muestra en el siguiente código:

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)

El bucle for anterior muestra el recuento de facturas por nombre de la siguiente manera:

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