SQLAlchemy Core - 使用集合操作

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

标准 SQL 及其大多数方言都支持集合操作，例如 UNION 和 INTERSECT。SQLAlchemy 借助以下函数实现它们 −

union()

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

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

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

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

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 ?

从我们的地址表中，以下行代表联合操作 −

[
   (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 即可看到效果。

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

对应的 SQL 表达式如下 −

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 表达式。

在以下示例中，except_() 函数仅返回地址表中 email_add 字段中包含"gmail.com"的记录，但排除 postal_add 字段中包含"Pune"的记录。

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

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

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 ?

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

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

intersect()

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

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

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

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

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'从地址表中的原始数据生成一行，如下所示−

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