Now that we've two tables, we are able to see the way to create queries on each tables on the equal time. To assemble a easy implicit join between Customer and Invoice, we will use Query.Filter out() to equate their associated columns together. Below, we load the Customer and Invoice entities at once the usage of this approach −
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))
The SQL expression emitted with the aid of SQLAlchemy is as follows −
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
And the end result of the above traces of code is as follows −
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
The actual SQL JOIN syntax is easily executed using the Query.Be a part of() approach as follows −
session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()
The SQL expression for be part of might be displayed on the 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 = ?
We can iterate via the end result the use of 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)
With 8500 because the bind parameter, following output is displayed −
4 Govind Kala 8 8500
Query.Be part of() is aware of a way to be a part of between those tables due to the fact there’s best one overseas key between them. If there have been no foreign keys, or extra foreign keys, Query.Be a part of() works better while one of the following bureaucracy are used −
query.join(Invoice, id == Address.custid) | explicit condition |
query.join(Customer.invoices) | specify relationship from left to right |
query.join(Invoice, Customer.invoices) | same, with explicit target |
query.join('invoices') | same, using a string |
Similarly outerjoin() characteristic is to be had to achieve left outer join.
query.outerjoin(Customer.invoices)
The subquery() method produces a SQL expression representing SELECT statement embedded inside an alias.
from sqlalchemy.sql import func
stmt = session.query(
Invoice.custid, func.count('*').label('invoice_count')
).group_by(Invoice.custid).subquery()
The stmt object will include a SQL announcement as beneath −
SELECT invoices.custid, count(:count_1) AS invoice_count FROM invoices GROUP BY invoices.custid
Once we've our declaration, it behaves like a Table construct. The columns on the announcement are accessible via an attribute referred to as c as shown in the underneath code −
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)
The above for loop presentations call-smart count of invoices as follows −
Arjun Pandit None
Gopal Krishna 2
Govind Pant 2
Govind Kala 2
Abdul Rahman 2