SQLAlchemy的SQL 表达式语言: select

在 SQLAlchemy 中，SQL 表达式语言（SQL Expression Language） 是介于原生 SQL 和 ORM 之间的核心层，而 select 是该层中用于构建 查询语句（SELECT） 的核心构造器。它提供了面向对象的方式来生成标准的 SQL SELECT 语句，支持列选择、过滤、排序、分组、连接、分页等所有 SQL 查询特性，且与数据库方言解耦，保证跨数据库的兼容性。

本文将从 select 的基础语法、核心特性、高级用法等方面，详细阐述 SQLAlchemy 表达式语言中的 select 构造器。

一、`select` 的核心概念

定位：select 是 SQLAlchemy 表达式语言的入口，对应 SQL 中的 SELECT 语句，用于从一个或多个表中检索数据。
特性：
- 面向对象：通过链式调用方法（如 where()、order_by()）构建查询，无需手写原生 SQL。
- 可组合性：select 对象可嵌套、拼接，支持子查询、联合查询等复杂场景。
- 方言适配：自动根据数据库引擎（如 SQLite、PostgreSQL、MySQL）生成适配的原生 SQL。
- 惰性执行：select 对象仅在执行（execute()）时才会发送 SQL 到数据库。

核心导入：

from sqlalchemy import select  # 核心 select 构造器
from sqlalchemy.sql import select  # 等价导入方式

二、基础准备：表定义与引擎初始化

首先定义测试用的表结构，并初始化数据库引擎和元数据，后续所有示例均基于此：

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, func
from sqlalchemy.sql import select, and_, or_, not_

# 初始化引擎（SQLite 内存数据库，方便测试）
engine = create_engine("sqlite:///:memory:")
# 元数据：用于管理表结构
metadata = MetaData()

# 定义用户表
users = Table(
    "users",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(50), nullable=False),
    Column("age", Integer),
    Column("email", String(100), unique=True)
)

# 定义订单表（外键关联用户表）
orders = Table(
    "orders",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("user_id", Integer, ForeignKey("users.id")),
    Column("amount", Integer, nullable=False),
    Column("status", String(20), default="pending")  # pending/completed/cancelled
)

# 创建所有表
metadata.create_all(engine)

# 插入测试数据（可选，用于验证查询结果）
with engine.connect() as conn:
    # 插入用户
    conn.execute(users.insert(), [
        {"name": "Alice", "age": 25, "email": "alice@example.com"},
        {"name": "Bob", "age": 30, "email": "bob@example.com"},
        {"name": "Charlie", "age": 35, "email": "charlie@example.com"}
    ])
    # 插入订单
    conn.execute(orders.insert(), [
        {"user_id": 1, "amount": 100, "status": "completed"},
        {"user_id": 1, "amount": 200, "status": "pending"},
        {"user_id": 2, "amount": 150, "status": "completed"},
        {"user_id": 3, "amount": 300, "status": "cancelled"}
    ])
    conn.commit()

三、`select` 的基础用法

1. 全列查询

查询表的所有列，对应 SQL 的 SELECT * FROM table。

# 构建全列查询
stmt = select(users)  # 等价于 select(*users.c)

# 执行查询：通过引擎连接执行，返回结果集
with engine.connect() as conn:
    result = conn.execute(stmt)
    # 遍历结果集：每行是 Row 对象，支持列名/索引访问
    for row in result:
        print(f"ID: {row.id}, Name: {row.name}, Age: {row.age}")

生成的原生 SQL：

SELECT users.id, users.name, users.age, users.email FROM users

2. 指定列查询

查询表的特定列，对应 SQL 的 SELECT col1, col2 FROM table。

# 方式一：传入列对象
stmt = select(users.c.name, users.c.age)

# 方式二：通过表的 c 属性（列集合）选择
stmt = select(users.c["name"], users.c["age"])

# 执行查询
with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f"Name: {row.name}, Age: {row.age}")

生成的原生 SQL：

SELECT users.name, users.age FROM users

3. 列别名（`label()`）

当列名冲突或需要自定义列名时，使用 label() 为列指定别名，对应 SQL 的 col AS alias。

# 为列指定别名
stmt = select(
    users.c.name.label("username"),
    users.c.age.label("user_age")
)

with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        # 通过别名访问列
        print(f"Username: {row.username}, Age: {row.user_age}")

生成的原生 SQL：

SELECT users.name AS username, users.age AS user_age FROM users

4. 过滤查询（`where()`）

添加查询条件，对应 SQL 的 WHERE 子句，支持 and_、or_、not_ 等逻辑运算符。

# 单条件过滤
stmt = select(users).where(users.c.age > 25)

# 多条件组合：and_（默认）
stmt = select(users).where(
    users.c.age > 25,
    users.c.name.like("B%")  # 多个条件默认用 AND 连接
)

# 显式逻辑运算符：and_/or_/not_
stmt = select(users).where(
    or_(
        users.c.age < 30,
        and_(users.c.name == "Charlie", users.c.age == 35)
    ),
    not_(users.c.email == "bob@example.com")
)

# 执行查询
with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(row)

生成的原生 SQL（最后一个 stmt）：

SELECT users.id, users.name, users.age, users.email 
FROM users 
WHERE (users.age < 30 OR (users.name = 'Charlie' AND users.age = 35)) AND (users.email != 'bob@example.com')

5. 排序（`order_by()`）

对结果集排序，对应 SQL 的 ORDER BY，支持升序（asc()）、降序（desc()）。

# 单列排序：默认升序
stmt = select(users).order_by(users.c.age)

# 多列排序：先按 age 降序，再按 name 升序
stmt = select(users).order_by(
    users.c.age.desc(),
    users.c.name.asc()
)

with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f"Age: {row.age}, Name: {row.name}")

生成的原生 SQL：

SELECT users.id, users.name, users.age, users.email 
FROM users 
ORDER BY users.age DESC, users.name ASC

6. 分页（`limit()`/`offset()`）

实现分页查询，对应 SQL 的 LIMIT 和 OFFSET。

# 分页：跳过前 1 条，取 2 条
stmt = select(users).order_by(users.c.id).offset(1).limit(2)

with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(row)

生成的原生 SQL：

SELECT users.id, users.name, users.age, users.email 
FROM users 
ORDER BY users.id 
LIMIT 2 OFFSET 1

7. 去重（`distinct()`）

去除重复记录，对应 SQL 的 DISTINCT。

# 对订单状态去重
stmt = select(orders.c.status).distinct()

with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(row.status)

生成的原生 SQL：

SELECT DISTINCT orders.status FROM orders

四、`select` 的高级用法

1. 聚合查询（`func`）

使用 SQLAlchemy 提供的 func 模块调用聚合函数（如 sum()、count()、avg()），对应 SQL 的聚合函数。

# 1. 统计订单总数
stmt = select(func.count(orders.c.id).label("total_orders"))

# 2. 计算订单总金额、平均金额
stmt = select(
    func.sum(orders.c.amount).label("total_amount"),
    func.avg(orders.c.amount).label("avg_amount")
)

# 3. 按用户分组，统计每个用户的订单数和总金额
stmt = select(
    users.c.name,
    func.count(orders.c.id).label("order_count"),
    func.sum(orders.c.amount).label("user_total")
).join(orders).group_by(users.c.id)  # 分组需配合 join

# 执行查询
with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f"User: {row.name}, Order Count: {row.order_count}, Total: {row.user_total}")

生成的原生 SQL（第三个 stmt）：

SELECT users.name, count(orders.id) AS order_count, sum(orders.amount) AS user_total 
FROM users JOIN orders ON users.id = orders.user_id 
GROUP BY users.id

2. 分组与过滤（`group_by()`/`having()`）

group_by() 用于分组，having() 用于对分组结果过滤（区别于 where()：where 过滤行，having 过滤分组）。

# 按订单状态分组，统计每组金额，且只保留总金额 > 200 的分组
stmt = select(
    orders.c.status,
    func.count(orders.c.id).label("count"),
    func.sum(orders.c.amount).label("total")
).group_by(orders.c.status).having(func.sum(orders.c.amount) > 200)

with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f"Status: {row.status}, Count: {row.count}, Total: {row.total}")

生成的原生 SQL：

SELECT orders.status, count(orders.id) AS count, sum(orders.amount) AS total 
FROM orders 
GROUP BY orders.status 
HAVING sum(orders.amount) > 200

3. 多表连接（`join()`/`outerjoin()`）

通过 select 的 join() 或 outerjoin() 实现多表连接，详细连接类型可参考前文《SQLAlchemy Joins》，此处重点说明 select 中的连接语法。

# 内连接：用户表 + 订单表，查询用户名和订单金额
stmt = select(users.c.name, orders.c.amount).join(orders)  # 自动通过外键推断连接条件

# 左外连接：用户表 + 订单表，包含无订单的用户
stmt = select(users.c.name, orders.c.amount).outerjoin(orders)

# 显式指定连接条件（非外键关联时使用）
stmt = select(users.c.name, orders.c.amount).join(
    orders,
    users.c.id == orders.c.user_id  # 手动指定连接条件
)

with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f"Name: {row.name}, Amount: {row.amount or '无订单'}")

4. 子查询（嵌套 `select`）

select 对象可作为子查询嵌入到另一个 select 中，通过 subquery() 方法将其转换为子查询对象。

# 子查询：统计每个用户的订单数
subq = select(
    orders.c.user_id,
    func.count(orders.c.id).label("order_count")
).group_by(orders.c.user_id).subquery("user_order_count")  # 转换为子查询并指定别名

# 主查询：关联用户表和子查询，查询用户信息和订单数
stmt = select(
    users.c.name,
    subq.c.order_count
).outerjoin(subq, users.c.id == subq.c.user_id)

with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f"Name: {row.name}, Order Count: {row.order_count or 0}")

生成的原生 SQL：

SELECT users.name, user_order_count.order_count 
FROM users 
LEFT OUTER JOIN (
    SELECT orders.user_id, count(orders.id) AS order_count 
    FROM orders 
    GROUP BY orders.user_id
) AS user_order_count ON users.id = user_order_count.user_id

5. 联合查询（`union()`/`union_all()`）

将多个 select 结果集合并，对应 SQL 的 UNION（去重）和 UNION ALL（不去重）。

# 第一个查询：已完成的订单
stmt1 = select(orders.c.id, orders.c.amount, orders.c.status).where(orders.c.status == "completed")

# 第二个查询：金额 > 200 的订单
stmt2 = select(orders.c.id, orders.c.amount, orders.c.status).where(orders.c.amount > 200)

# 联合查询：UNION（去重）
union_stmt = stmt1.union(stmt2)

# 联合查询：UNION ALL（不去重）
# union_stmt = stmt1.union_all(stmt2)

with engine.connect() as conn:
    result = conn.execute(union_stmt)
    for row in result:
        print(f"Order ID: {row.id}, Amount: {row.amount}, Status: {row.status}")

生成的原生 SQL：

SELECT orders.id, orders.amount, orders.status FROM orders WHERE orders.status = 'completed'
UNION
SELECT orders.id, orders.amount, orders.status FROM orders WHERE orders.amount > 200

6. 条件表达式（`case()`）

使用 case() 实现 SQL 的 CASE WHEN 条件逻辑，用于动态生成列值。

from sqlalchemy import case

# 按订单金额分级：高/中/低
stmt = select(
    orders.c.id,
    orders.c.amount,
    case(
        [(orders.c.amount > 200, "high")],
        [(orders.c.amount > 100, "medium")],
        else_="low"
    ).label("amount_level")
)

with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(f"Order ID: {row.id}, Amount: {row.amount}, Level: {row.amount_level}")

生成的原生 SQL：

SELECT orders.id, orders.amount, 
CASE 
    WHEN orders.amount > 200 THEN 'high' 
    WHEN orders.amount > 100 THEN 'medium' 
    ELSE 'low' 
END AS amount_level 
FROM orders

五、`select` 的执行与结果处理

1. 执行方式

select 对象的执行依赖于 SQLAlchemy 的连接（Connection）*或*会话（Session）：

Connection：核心层常用，通过 engine.connect() 获取连接，执行后手动提交（如需修改数据）。
Session：ORM 层常用，通过 sessionmaker 创建会话，自动管理事务。

2. 结果集类型

执行 select 后返回的 Result 对象是可迭代的，支持多种结果处理方式：

with engine.connect() as conn:
    result = conn.execute(select(users))

    # 1. 遍历行：Row 对象，支持列名/索引/属性访问
    for row in result:
        print(row.id, row["name"], row[2])  # 索引从 0 开始

    # 2. 转换为列表
    result = conn.execute(select(users))
    rows = result.fetchall()  # 获取所有行
    # rows = result.fetchone()  # 获取单行
    # rows = result.fetchmany(2)  # 获取指定行数

    # 3. 转换为字典
    result = conn.execute(select(users))
    dicts = result.mappings().all()  # 每行是字典（列名 -> 值）
    for d in dicts:
        print(d["name"], d["age"])

六、注意事项

惰性执行：select 对象仅在调用 execute() 时才会生成并发送 SQL，此前的链式调用仅构建对象。
列名冲突：多表连接时若列名相同，需通过表别名或列别名区分，避免结果访问歧义。
数据库兼容性：部分特性（如全外连接、match 子句）仅支持特定数据库，需根据方言适配。
性能优化：
- 避免 SELECT *，仅查询需要的列；
- 大表查询使用 limit()/offset() 分页，或添加索引；
- 复杂子查询可通过视图或 CTE（公共表表达式）简化。
CTE（公共表表达式）：SQLAlchemy 也支持 with_cte() 实现 SQL 的 WITH 子句，适合复杂的嵌套查询，用法与子查询类似但更易读。

七、总结

SQLAlchemy 表达式语言中的 select 是构建查询的核心工具，它以面向对象的方式封装了 SQL SELECT 语句的所有特性，从简单的全列查询到复杂的子查询、联合查询、聚合查询均能覆盖。

其核心价值在于：

脱离原生 SQL：避免手写 SQL 带来的语法错误和注入风险；
跨数据库兼容：自动适配不同数据库的方言差异；
可组合性：支持查询的嵌套、拼接，满足复杂业务的查询需求。

掌握 select 的用法是 SQLAlchemy 核心层开发的基础，也是理解 ORM 层查询（如 session.query()）的关键，因为 ORM 层的查询最终会转换为核心层的 select 对象执行。

posted @ 2025-12-18 15:14 gugucai 阅读(87) 评论(0) 收藏举报

刷新页面返回顶部

cai-wy

SQLAlchemy的SQL 表达式语言: select

一、`select` 的核心概念

二、基础准备：表定义与引擎初始化

三、`select` 的基础用法

1. 全列查询

2. 指定列查询

3. 列别名（`label()`）

4. 过滤查询（`where()`）

5. 排序（`order_by()`）

6. 分页（`limit()`/`offset()`）

7. 去重（`distinct()`）

四、`select` 的高级用法

1. 聚合查询（`func`）

2. 分组与过滤（`group_by()`/`having()`）

3. 多表连接（`join()`/`outerjoin()`）

4. 子查询（嵌套 `select`）

5. 联合查询（`union()`/`union_all()`）

6. 条件表达式（`case()`）

五、`select` 的执行与结果处理

1. 执行方式

2. 结果集类型

六、注意事项

七、总结

公告

cai-wy

SQLAlchemy的SQL 表达式语言: select

一、select 的核心概念

二、基础准备：表定义与引擎初始化

三、select 的基础用法

1. 全列查询

2. 指定列查询

3. 列别名（label()）

4. 过滤查询（where()）

5. 排序（order_by()）

6. 分页（limit()/offset()）

7. 去重（distinct()）

四、select 的高级用法

1. 聚合查询（func）

2. 分组与过滤（group_by()/having()）

3. 多表连接（join()/outerjoin()）

4. 子查询（嵌套 select）

5. 联合查询（union()/union_all()）

6. 条件表达式（case()）

五、select 的执行与结果处理

1. 执行方式

2. 结果集类型

六、注意事项

七、总结

公告

一、`select` 的核心概念

三、`select` 的基础用法

3. 列别名（`label()`）

4. 过滤查询（`where()`）

5. 排序（`order_by()`）

6. 分页（`limit()`/`offset()`）

7. 去重（`distinct()`）

四、`select` 的高级用法

1. 聚合查询（`func`）

2. 分组与过滤（`group_by()`/`having()`）

3. 多表连接（`join()`/`outerjoin()`）

4. 子查询（嵌套 `select`）

5. 联合查询（`union()`/`union_all()`）

6. 条件表达式（`case()`）

五、`select` 的执行与结果处理