SQLAlchemy ORM - Работа с объединениями

Теперь, когда у нас есть две таблицы, мы увидим, как создавать запросы к обеим таблицам одновременно. Чтобы создать простое неявное соединение между Customer и Invoice, мы можем использовать Query.filter () для приравнивания связанных столбцов вместе. Ниже мы загружаем сущности Customer и Invoice сразу, используя этот метод -

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

Выражение SQL, выдаваемое SQLAlchemy, выглядит следующим образом:

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

И результат приведенных выше строк кода выглядит следующим образом:

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

Фактический синтаксис SQL JOIN легко достигается с помощью метода Query.join () следующим образом:

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

Выражение SQL для соединения будет отображаться на консоли -

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

Мы можем перебрать результат, используя цикл 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)

С 8500 в качестве параметра привязки отображается следующий вывод -

4 Govind Kala 8 8500

Query.join () знает, как соединяться между этими таблицами, потому что между ними есть только один внешний ключ. Если внешних ключей не было или было больше, Query.join () работает лучше, если используется одна из следующих форм:

query.join (счет-фактура, id == Address.custid)	явное условие
query.join (Customer.invoices)	указать отношение слева направо
query.join (счет-фактура, Customer.invoices)	то же самое, с явной целью
query.join ('счета-фактуры')	то же самое, используя строку

Аналогичным образом доступна функция outerjoin () для левого внешнего соединения.

query.outerjoin(Customer.invoices)

Метод subquery () создает выражение SQL, представляющее инструкцию SELECT, встроенную в псевдоним.

from sqlalchemy.sql import func

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

Объект stmt будет содержать инструкцию SQL, как показано ниже -

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

Когда у нас есть оператор, он ведет себя как конструкция Table. Столбцы в операторе доступны через атрибут c, как показано в приведенном ниже коде:

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)

Вышеупомянутый цикл for отображает количество счетов-фактур по имени следующим образом:

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