SQLAlchemy操作SQLite批量更新时出现参数绑定类型不支持错误

现有SQLite表结构 #

CREATE TABLE BatchData (
    pk INTEGER PRIMARY KEY AUTOINCREMENT,
    batchid TEXT NOT NULL,
    status TEXT NOT NULL,
    strategyname TEXT NOT NULL,
    createdon DATETIME
);

数据库数据快照 #

pk,batchid,status,strategyname,createdon    
1,a3eaa908-dbfc-4d9e-aa2a-2604ee3fdd95,FINISHED,OP_Ma,2023-02-15 06:20:21.924608
2,8813d314-4548-4c14-bd28-f2775fd7a1a7,INPROGRESS,OP_Ma,2023-02-16 06:01:19.335228
3,d7b0ef19-97a9-47b1-a885-925761755992,INPROGRESS,OP_CL,2023-02-16 06:20:52.748321
4,e30e2485-e62c-4d3c-9640-05e1b980654b,INPROGRESS,OP_In,2023-02-15 06:25:04.201072

问题 #

直接在控制台执行批量更新SQL语句完全正常，但用SQLAlchemy绑定tuple参数执行时，触发sqlite3.InterfaceError错误。已经确认参数类型为str和tuple(str)，需要排查参数绑定的问题。

涉事代码 #

import sqlalchemy as sa
sqlite_eng = sa.create_engine('blah.db')
...
status = 'FINISHED'
tuple_data = tuple(batchids)
STMT = sa.text("""UPDATE BatchData SET status = :stat WHERE batchid IN (:bids)""")
STMT_proxy = sqlite_eng.execute(STMT, stat=status, bids=tuple_data)

错误信息 #

InterfaceError: (sqlite3.InterfaceError) Error binding parameter 1 - probably unsupported type.
        [SQL: UPDATE BatchData SET status = ? WHERE batchid IN (?)]
        [parameters: ('FINISHED', ('e30e2485-e62c-4d3c-9640-05e1b980654b', ...))]

解决办法 #

问题根源是SQLite不支持直接把tuple作为IN子句的单个参数绑定，SQLAlchemy需要特殊处理才能展开tuple中的元素。

方法1：使用SQLAlchemy的expanding参数（推荐） #

修改SQL语句的参数绑定逻辑，添加expanding=True标记，让SQLAlchemy自动处理tuple的展开：

STMT = sa.text("""UPDATE BatchData SET status = :stat WHERE batchid IN :bids""").bindparams(
    sa.bindparam('bids', expanding=True)
)
STMT_proxy = sqlite_eng.execute(STMT, stat=status, bids=tuple_data)

注意这里IN :bids不需要加括号，expanding会自动补全括号并将tuple中的每个元素作为单独参数绑定。

方法2：手动生成占位符（适配旧版SQLAlchemy） #

如果你的SQLAlchemy版本不支持expanding，可以手动生成与tuple长度一致的占位符：

# 生成和tuple元素数量匹配的占位符
placeholders = ', '.join(['?'] * len(tuple_data))
STMT = sa.text(f"""UPDATE BatchData SET status = :stat WHERE batchid IN ({placeholders})""")
# 直接传递参数列表
STMT_proxy = sqlite_eng.execute(STMT, status, *tuple_data)

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