如何为SQLAlchemy text()查询的IN子句传递值列表？

问题重现 #

尝试通过SQLAlchemy将列表作为参数传入IN子句，代码如下：

args = [112210104, 112012523]
raw_sql = "SELECT * FROM table WHERE id IN :values" 
query = sqlalchemy.text(raw_sql).bindparams(values=tuple(args))
cnxn_gw.engine.execute(query)

遇到错误：

DBAPIError: (pyodbc.Error) ('HY004', '[HY004] [Microsoft][ODBC SQL Server Driver]Invalid SQL data type (0) (SQLBindParameter)')
[SQL: SELECT * FROM table WHERE id IN ?]
[parameters: ((112210104, 112012523),)]

解决方案 #

方法1：使用expanding参数（推荐，SQLAlchemy 1.3+） #

利用SQLAlchemy提供的bindparam并设置expanding=True，它会自动处理列表参数，将其展开为IN子句所需的多个占位符：

import sqlalchemy

args = [112210104, 112012523]
raw_sql = "SELECT * FROM table WHERE id IN :values" 
# 用bindparam指定expanding参数
query = sqlalchemy.text(raw_sql).bindparams(
    sqlalchemy.bindparam('values', expanding=True, value=args)
)
cnxn_gw.engine.execute(query)

方法2：动态生成占位符 #

如果使用的SQLAlchemy版本较低不支持expanding，可以根据列表长度动态生成对应数量的占位符，再直接传递列表参数：

import sqlalchemy

args = [112210104, 112012523]
# 根据列表长度生成N个占位符
placeholders = ', '.join(['?' for _ in args])
raw_sql = f"SELECT * FROM table WHERE id IN ({placeholders})"
query = sqlalchemy.text(raw_sql)
# 用*args将列表元素作为多个参数传递
cnxn_gw.engine.execute(query, *args)

原因说明 #

原代码将tuple作为单个参数传递给:values，但SQL Server的ODBC驱动无法识别这种复合数据类型作为IN子句的参数，因此抛出"Invalid SQL data type"错误。上述两种方法都是将列表元素拆分为独立的参数，符合SQL Server驱动的参数绑定要求。

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