Sqlalchemy 简明教程

SQLAlchemy ORM - Working with Joins

现在我们有两个表，我们将了解如何同时对这两个表创建查询。要构建客户和发票之间的简单隐式连接，我们可以使用 Query.filter() 将它们的相关列等同起来。在下面，我们使用此方法一次性加载客户和发票实体 -

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

SQLAlchemy 发出的 SQL 表达式如下 -

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(Invoice, id == Address.custid)

explicit condition

query.join(Customer.invoices)

从左到右指定关系

query.join(Invoice, Customer.invoices)

same, with explicit target

query.join('invoices')

same, using a string

同样，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