Skip to content

SQL Expression Language

SQL Statements and Expressions Language即 SQL 语句和表达式语言，通俗讲就是通过方法来构造 SQL 语句。SQL 语句的核心就是对表的 CRUD 操作。对应的就是构建以下四个对象:

  • Delete: 删除语句，使用 delete(table) 来构造 Delete 对象
  • Insert: 插入语句，使用 insert(table) 来构造 Insert 对象
  • Update: 更新语句，使用 update(table) 来构建 Update 对象
  • Select: 选择语句，使用 select(table) 来构建 Select 对象

而为了构造这三个语句通常都需要用到一个核心的 Where 子句，而 Where 的核心在于各种 Operator and Expression 的使用，而操作数通常还需要使用 Function 来处理，他们共同构成了 SQL Expression Language。

构造方法

要构造对应的 SQL 语句，最简单的就是通过 SQLAlchemy 公开的方法来实现:

Python
from sqlalchemy import insert, delete, update, select

stem = insert(table).values(xxx)

实际上每一个 Table 也绑定了这三个方法，他唯一的区别在于不需要传入对应 table 的名称了。

Insert

Insert表示 INSERT 语句:

SQL
-- 基本插入语句
INSERT INTO table_name (column_name1, column_name2...) VALUES (value1, value2...)

-- ON CONFLICT 来实现 upsert
INSERT INTO t1 VALUES (0,0,3,0) ON CONFLICT (a) DO UPDATE SET c = EXCLUDED.c;

-- RETURNING
INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
   RETURNING did;

在 SQLAlchemy 中使用insert()方法来构建 Insert 对象。它具有几个核心的对象，即 Insert 语句的子句:

  • values(): VALUES 子句
  • returning(): RETURNING 子句
  • from_select(): 从 SELECT 语句来获取 VALUES

values()

他为 INSERT 语句指定 VALUES 子句。有多种类型的参数来插入 value:

Python
# 通过关键字参数
users.insert().values(name="some name")
users.update().where(users.c.id==5).values(name="some name")

# 通过单一字典
users.insert().values({"name": "some name"})
users.update().values({"name": "some new name"})

# 通过元组实现
users.insert().values((5, "some name"))

# 批量插入
users.insert().values([
    {"name": "some name"},
    {"name": "some other name"},
    {"name": "yet another name"},
])

Tips

UPDATE 中的 SET 子句也是通过 values() 方法来实现的

returning()

用于添加 RETURNING 子句，他决定了最终的执行结果会返回什么字段:

Python
stmt = table.insert().returning(
    (table.c.first_name + " " + table.c.last_name).label("fullname")
)
# 等价于
# INSERT INTO some_table (first_name, last_name)
# VALUES (:first_name, :last_name)
# RETURNING some_table.first_name || :first_name_1 || some_table.last_name AS fullname

stmt = (
    table.update()
    .where(table.c.data == "value")
    .values(status="X")
    .returning(table.c.server_flag, table.c.updated_timestamp)
)

# 等价于
# UPDATE some_table SET status=:status
# WHERE some_table.data = :data_1
# RETURNING some_table.server_flag, some_table.updated_timestamp

Upsert

INSERT 语句包含一个 ON CONFLICT 子句来实现 upsert 操作，即在插入数据时，如果行已存在，则更新该行；如果不存在，则插入新行。这是一种结合了更新和插入的操作方式。需要注意这并不是所有数据库都支持，因此只有通过特定 dialects中的 insert 方法才能够实现:

Python
# 特定 dialects 的 insert
from sqlalchemy.dialects.postgresql import insert

# 如果存在 do nothing
insert_stmt = insert(my_table).values(
    id='some_existing_id',
    data='inserted value')
do_nothing_stmt = insert_stmt.on_conflict_do_nothing(
    index_elements=['id']
)

"""
等价于
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT (id) DO NOTHING
"""

# 如果存在 do update
do_update_stmt = insert_stmt.on_conflict_do_update(
    constraint='pk_my_table',
    set_=dict(data='updated value')
)

"""
等价于
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT ON CONSTRAINT pk_my_table DO UPDATE SET data = %(param_1)s
"""

Select

Select用于构造 SELECT 语句，一个完整的 SELECT 语句的运行流程如下:

  1. 首先根据 FROM 子句来计算出一个关系
  2. 如果出现了 Where 子句，根据后面的布尔表达式从 FROM 子句的结果上运行选择运算来获取一个新的关系
  3. 如果存在 GROUP BY 子句，通过 WHERE 选择的新关系将被放入分组中。当然没有 GROUP BY 子句实际上就是将整个关系当做一个分组
  4. 如果存在 GROUP BY 子句，且出现了 HAVING 子句。他将应用到每个分组上，不满足的 HAVING 子句的分组将被去掉
  5. SELECT 子句利用剩下的分组产生查询结果中的元组，即在每个分组上应用聚集行数来得到单个结果元组

Select 对象指代一个 SELECT 语句对象，通常由 select() 工厂函数来构建。该工厂函数接受一系列 ColumnElement 对象或者是 FromClause 对象(通常就是一个表对象或者映射类)，在内部会循环 FromClause.c 来获取所有的 ColumnElement 对象。

Python
# 等价于 
# SELECT * FROM PatentChinaPip 
#   WHERE publication_number == xxx
stem = select(PatentChinaPip).where(PatentChinaPip.publication_number == id)

# 等价于
# SELECT publication_number,google_patent_id 
#  FROM PatentChinaPip 
#  WHERE publication_number == xxx
stem = select(
  PatentChinaPip.publication_number, PatentChinaPip.google_patent_id
).where(PatentChinaPip.publication_number == id)

Select.select_from(FromClause)

接受一个 FromClause 对象，他对应的就是 SELECT 语句中的 FROM 子句。FromClause 通常有三种情况:

  1. 直接就是一个 Table 对象
  2. 通过 join 来执行内连接
  3. 通过 outerjoin 来执行外连接

上面三中情况的结果返回的依然是一个 FromClause 对象，实际上 FromClause 对象的表象主要有以下几点:

  1. 具有一个 c 集合，其中的元素是 ColumnElement 对象
  2. 具有一个 primary_key 属性，他是所有 primary_key 标志的 ColumnElement 集合
  3. 具有 foreign_keys 属性，表示外键

sqlalchemy.sql.expression.join(left, right, onclause, isouter=false, full= False)

该方法能够构建外连接和内连接，其中 left right 都需要一个 Table 对象(或者其他 FromClause 对象)，onclause 表示 ON 子句即连接条件，isouter 表示是否是外连接(默认内连接)，full 表示是否全连接默认是左连接(要想实现有连接就是 left right 调换下位置)。

该方法会返回一个 FromClause 的子类 Join 对象:

Python
j = join(user_table, address_table,
         user_table.c.id == address_table.c.user_id)
stmt = select(user_table).select_from(j)

"""
SELECT user.id, user.name FROM user
JOIN address ON user.id = address.user_id
"""

还有一个sqlalchemy.sql.expression.outerjoin(left, right, onclause, full=False)方法实际上就是默认isouter=True的 join 方法.

FromClause.join(right, onclause, full=False)

实际上就是省略了一个 left 表，将 FromClause 作为 left 表。同样返回 Join 对象。

Python
from sqlalchemy import join

j = user_table.join(address_table,
                user_table.c.id == address_table.c.user_id)
stmt = select(user_table).select_from(j)

Select.join(right, onclause, full=False)

将传入 Select 对象的 FromClause 来作为 left 表，并且他还自动组装成一个新的 Select 对象。

Python
j = user_table.join(address_table,
                user_table.c.id == address_table.c.user_id)
stmt = select(user_table).select_from(j)

# 等价于
stmt = select(user_table).join(address_table,
                user_table.c.id == address_table.c.user_id)

"""
SELECT user.id, user.name FROM user
JOIN address ON user.id = address.user_id
"""

Select.where(whereclause)

构造 Where 子句。whereclause 就是一条条的 Operator and Expression 他们可以通过逻辑运算符来组合他们。

Select.group_by()

他们要配合聚合函数来使用，聚合函数需要传入到 select() 方法中:

Python
stmt = select(table.c.name, func.max(table.c.stat)).group_by(table.c.name)

其他方法

其他 SELECT 子句通常都有对应的 select().xxx() 方法，大部分也都比较简单：

  • select().limit(int): 限制返回的数量
  • select().offset(int): 分页
  • select().order_by(ColumnElement): 排序
  • select().having(OperatorExpression): having 子句

Update

Update用于构造 UPDATE 语句:

SQL
-- 基础 UPDATE 语句就是 SET 和 WHERE
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

-- 多字段 set 以及携带 RETURNING 子句
UPDATE weather SET temp_lo = temp_lo + 1, temp_hi = temp_lo + 15, prcp = DEFAULT
  WHERE city = 'San Francisco' AND date = '2003-07-03'
  RETURNING temp_lo, temp_hi, prcp;

-- 多表
UPDATE accounts SET contact_first_name = first_name,
                    contact_last_name = last_name
  FROM employees WHERE employees.id = accounts.sales_person;

在 SQLAlchemy 中使用update()方法来构建 Update 对象。它具有几个核心的对象，即 Update 语句的子句:

  • values(): SET 子句，与 Insert 对象的 values() 一样
  • returning(): RETURNING 子句，与 Insert 对象的 returning() 一样
  • where(whereclause): 最核心的子句，用于筛选要更新的行

Delete

Delete用于构造 Delete 语句:

SQL
-- 基本删除
DELETE FROM films WHERE kind <> 'Musical';

-- 清空表
DELETE FROM films;

-- 携带 RETURNING
DELETE FROM tasks WHERE status = 'DONE' RETURNING *;

在 SQLAlchemy 中使用delete()方法来构建 Delete 对象。它具有几个核心的对象，即 Delete 语句的子句:

  • returning(): RETURNING 子句，与 Insert 对象的 returning() 一样
  • where(whereclause): 最核心的子句，用于筛选要删除的行

Where

WHERE 子句用于从表中筛选数据他是 CRUD 操作中非常核心的部分。他通常由 Operator and Expression 以及 Function 来组合实现。

Operator and Expression

Operator表示运算符，他与算子共同构成了Expression主要用于构建 Where 子句。

算子

所谓的算子就是操作符的操作对象，他与操作符共同构建了表达式。SQLAlchemy 提供了三种形式的算子:

  • Column: Column 对象
  • ColumnElement: ColumnElement 对象类似 Column 不过是从其他源包装而来: - Python 文本对象，例如字符串、整数、浮点数、布尔值、datetime 等几乎所有的 Python 对象 - None 被翻译为 NULL
  • InstrumentedAttribute: ORM 级别的映射属性，说白了就是Mapped Class中定义的字段

上面的三种形式实际上对应了数据库中的表:

Python
# Column
column("x")
table.c.x

# InstrumentedAttribute
Table.x

比较运算符

Python
column("x") == 5
column("x") != 5
column("x") > 5
column("x") < 5
column("x") >= 5
column("x") <= 5

# 区间
column("x").between(5, 10)
# x BETWEEN :x_1 AND :x_2

in/not_in

Python
column("x").in_([1, 2, 3])
column("x").not_in([1, 2, 3])
# not_in 还等价于
~column("x").in_([1, 2, 3])

# 可以是子查询
column("x").in_(select(user_table.c.id))

NULL 判断

最好使用 == 来进行 NULL 判断:

Python
column("x").is_(None)
column("x").is_(null()) # 更规范的写法

# 使用 == 的方式
a = None
column("x") == a

column("x").is_not(None)
# 等价于
column("x") != None

字符串相关

模糊匹配:

Python
column("x").like("word")    # like x LIKE :x_1
column("x").ilike("word")   # 不区分大小写 like  lower(x) LIKE lower(:x_1)
column("x").notlike("word")
column("x").notilike("word")

字符串包含实际上就是对 like 的改写:

Python
column("x").startswith("word")  # x LIKE :x_1 || '%'
column("x").endswith("word")    # x LIKE '%' || :x_1
column("x").contains("word")    # x LIKE '%' || :x_1 || '%'

字符串修改:

Python
column("x").concat("some string")   # x || :x_1

算术运算符

Python
column("x") + 5
column("x") - 5
column("x") * 5
column("x") / 5
column("x") // 5
column("x") % 5

逻辑运算符

他用于连接多个表达式，即与或非:

Python
column("x") == 5) & (column("y") == 10)
(column("x") == 5) | (column("y") == 10)
~(column("x") == 5)

Function

Function以及SQLAlchemy 全局会暴露一些 SQL 函数供用户使用。

func

func是一个非常特殊的对象实例，可以根据名称属性来生成 SQL 函数:

Python
func.count(1)   # -> count(:param_1)
func.current_timestamp() # -> CURRENT_TIMESTAMP
func.extract('year', Foo.Date) # -> extract('year', foo.date)

# 有些 SQLALchemy 是内置了的
from sqlalchemy import extract

stmt = select(logged_table.c.id).where(
    extract("YEAR", logged_table.c.date_created) == 2021
)