Sqlalchemy 简明教程

SQLAlchemy Core - Using Joins

在本章中，我们将学习如何在 SQLAlchemy 中使用连接。

In this chapter, we will learn how to use Joins in SQLAlchemy.

连接的效果只需将两个表放在 select() 构造的 columns clause 或 where clause 中即可实现。现在，我们使用 join() 和 outerjoin() 方法。

Effect of joining is achieved by just placing two tables in either the columns clause or the where clause of the select() construct. Now we use the join() and outerjoin() methods.

join() 方法从一个表对象返回一个到另一个表对象的连接对象。

The join() method returns a join object from one table object to another.

join(right, onclause = None, isouter = False, full = False)

上面代码中提到的参数的功能如下：

The functions of the parameters mentioned in the above code are as follows −

right − the right side of the join; this is any Table object
onclause − a SQL expression representing the ON clause of the join. If left at None, it attempts to join the two tables based on a foreign key relationship
isouter − if True, renders a LEFT OUTER JOIN, instead of JOIN
full − if True, renders a FULL OUTER JOIN, instead of LEFT OUTER JOIN

例如，join() 方法的以下用法将自动导致基于外键的 join。

For example, following use of join() method will automatically result in join based on the foreign key.

>>> print(students.join(addresses))

这等效于以下 SQL 表达式 −

This is equivalent to following SQL expression −

students JOIN addresses ON students.id = addresses.st_id

您可以显式提及 join 条件，如下所示 −

You can explicitly mention joining criteria as follows −

j = students.join(addresses, students.c.id == addresses.c.st_id)

如果我们现在使用此 join 构建以下选择构建，则为 −

If we now build the below select construct using this join as −

stmt = select([students]).select_from(j)

这将导致以下 SQL 表达式 −

This will result in following SQL expression −

SELECT students.id, students.name, students.lastname
FROM students JOIN addresses ON students.id = addresses.st_id

如果此语句使用表示引擎的连接执行，则将显示属于所选列的数据。完整代码如下 −

If this statement is executed using the connection representing engine, data belonging to selected columns will be displayed. The complete code is as follows −

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
engine = create_engine('sqlite:///college.db', echo = True)

meta = MetaData()
conn = engine.connect()
students = Table(
   'students', meta,
   Column('id', Integer, primary_key = True),
   Column('name', String),
   Column('lastname', String),
)

addresses = Table(
   'addresses', meta,
   Column('id', Integer, primary_key = True),
   Column('st_id', Integer,ForeignKey('students.id')),
   Column('postal_add', String),
   Column('email_add', String)
)

from sqlalchemy import join
from sqlalchemy.sql import select
j = students.join(addresses, students.c.id == addresses.c.st_id)
stmt = select([students]).select_from(j)
result = conn.execute(stmt)
result.fetchall()

以下是在上个代码中输出的 −

The following is the output of the above code −

[
   (1, 'Ravi', 'Kapoor'),
   (1, 'Ravi', 'Kapoor'),
   (3, 'Komal', 'Bhandari'),
   (5, 'Priya', 'Rajhans'),
   (2, 'Rajiv', 'Khanna')
]