Sqlalchemy 简明教程

SQLAlchemy Core - Using Set Operations

在上一个章节中，我们学习了各种函数，例如 max()、min()、count() 等，在这里，我们将学习集合运算及其用法。

In the last chapter, we have learnt about various functions such as max(), min(), count(), etc., here, we will learn about set operations and their uses.

UNION 和 INTERSECT 等集合运算由标准 SQL 及其大多数方言支持。SQLAlchemy 在以下函数的帮助下实现它们 −

Set operations such as UNION and INTERSECT are supported by standard SQL and most of its dialect. SQLAlchemy implements them with the help of following functions −

union()

在组合两个或多个 SELECT 语句的结果时，UNION 会从结果集中消除重复项。表中列和数据类型的数量必须相同。

While combining results of two or more SELECT statements, UNION eliminates duplicates from the resultset. The number of columns and datatype must be same in both the tables.

union() 函数从多个表中返回 CompoundSelect 对象。以下示例演示了它的用法 −

The union() function returns a CompoundSelect object from multiple tables. Following example demonstrates its use −

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

meta = MetaData()
conn = engine.connect()
addresses = Table(
   'addresses', meta,
   Column('id', Integer, primary_key = True),
   Column('st_id', Integer),
   Column('postal_add', String),
   Column('email_add', String)
)

u = union(addresses.select().where(addresses.c.email_add.like('%@gmail.com addresses.select().where(addresses.c.email_add.like('%@yahoo.com'))))

result = conn.execute(u)
result.fetchall()

联合结构转换为以下 SQL 表达式 −

The union construct translates to following SQL expression −

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?

根据我们的地址表，以下行代表联合运算 −

From our addresses table, following rows represent the union operation −

[
   (1, 1, 'Shivajinagar Pune', 'ravi@gmail.com'),
   (2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com'),
   (3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com'),
   (4, 5, 'MG Road Bangaluru', 'as@yahoo.com')
]

union_all()

UNION ALL 运算不能删除重复项，也不能对结果集中的数据进行排序。例如，在上面查询中，UNION 被 UNION ALL 替换以查看效果。

UNION ALL operation cannot remove the duplicates and cannot sort the data in the resultset. For example, in above query, UNION is replaced by UNION ALL to see the effect.

u = union_all(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))

相应的 SQL 表达式如下 −

The corresponding SQL expression is as follows −

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION ALL SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?

except_()

SQL EXCEPT 子句/运算符用于组合两个 SELECT 语句，并返回第一个 SELECT 语句中未通过第二个 SELECT 语句返回的行。except_() 函数生成带有 EXCEPT 子句的 SELECT 表达式。

The SQL EXCEPT clause/operator is used to combine two SELECT statements and return rows from the first SELECT statement that are not returned by the second SELECT statement. The except_() function generates a SELECT expression with EXCEPT clause.

在以下示例中，except_() 函数仅从地址表中返回那些 email_add 字段中具有“gmail.com”但排除 postal_add 字段中有“Pune”的记录。

In the following example, the except_() function returns only those records from addresses table that have ‘gmail.com’ in email_add field but excludes those which have ‘Pune’ as part of postal_add field.

u = except_(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))

上述代码的结果是以下 SQL 表达式 −

Result of the above code is the following SQL expression −

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? EXCEPT SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?

假设地址表包含前面示例中使用的数据，它将显示以下输出 −

Assuming that addresses table contains data used in earlier examples, it will display following output −

[(2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com'),
   (3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')]

intersect()

使用 INTERSECT 运算符，SQL 在两个 SELECT 语句中显示公共行。intersect() 函数实现此行为。

Using INTERSECT operator, SQL displays common rows from both the SELECT statements. The intersect() function implements this behaviour.

在以下示例中，两个 SELECT 结构是 intersect() 函数的参数。其中一个返回包含“gmail.com”作为 email_add 列一部分的行，另一个返回包含“Pune”作为 postal_add 列一部分的行。结果将是两个结果集中的公共行。

In following examples, two SELECT constructs are parameters to intersect() function. One returns rows containing ‘gmail.com’ as part of email_add column, and other returns rows having ‘Pune’ as part of postal_add column. The result will be common rows from both resultsets.

u = intersect(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))

实际上，这相当于以下 SQL 语句 −

In effect, this is equivalent to following SQL statement −

SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? INTERSECT SELECT addresses.id,
   addresses.st_id,
   addresses.postal_add,
   addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?

两个绑定参数“%gmail.com”和“%Pune”从地址表中的原始数据生成一行，如下所示 −

The two bound parameters ‘%gmail.com’ and ‘%Pune’ generate a single row from original data in addresses table as shown below −

[(1, 1, 'Shivajinagar Pune', 'ravi@gmail.com')]