如何在SQLAlchemy中更新PostgreSQL数组的单个元素？

我找不到SQLAlchemy官方文档里关于ARRAY字段单个元素更新的相关内容，目前只能更新整个数组，但实际需求是修改数组中指定位置的单个元素。

PostgreSQL原生SQL的操作示例如下：

先查询目标表数据：

SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}

更新数组第4个位置的元素：

UPDATE sal_emp SET pay_by_quarter[4] = 15000
WHERE name = 'Bill';

但在SQLAlchemy中，直接用.values()的关键字参数写法行不通：

# 错误写法（无法运行）：
update(SalEmp).values(SalEmp.pay_by_quarter[4]=15000).where(SalEmp.name=='Bill')

# 也不清楚.values(pay_by_quarter=???)里该如何填写

可行的实现方式 #

方式1：用字典传递数组索引表达式

.values()方法支持传入字典，其中键可以是数组列的索引表达式，值为要更新的内容：

from sqlalchemy import update

update_stmt = update(SalEmp).values(
    {SalEmp.pay_by_quarter[4]: 15000}
).where(SalEmp.name == 'Bill')

方式2：借助text()构造原生SQL片段

如果想更贴近原生SQL的写法，可以用text()来定义更新逻辑：

from sqlalchemy import update, text

update_stmt = update(SalEmp).values(
    pay_by_quarter=text("pay_by_quarter[4] = :new_val")
).params(new_val=15000).where(SalEmp.name == 'Bill')

或者直接构造完整的原生SQL语句：

from sqlalchemy import text

update_stmt = text("""
    UPDATE sal_emp 
    SET pay_by_quarter[4] = :new_val 
    WHERE name = :name
""").params(new_val=15000, name='Bill')

方式3：使用PostgreSQL的array_set函数

针对PostgreSQL 9.5及以上版本，可以用array_set函数结合SQLAlchemy的func来实现：

from sqlalchemy import update, func

update_stmt = update(SalEmp).values(
    pay_by_quarter=func.array_set(SalEmp.pay_by_quarter, 4, 15000)
).where(SalEmp.name == 'Bill')

注意：PostgreSQL数组默认是1-based索引，和Python的0索引逻辑不同，要注意位置对应。

内容的提问来源于stack exchange，提问作者Mastermind