使用 pyodbc 的过滤器.in_(列表)"Maximum number of parameters"错误

Question

我们在Python2+mxODBC中工作的一个查询在Python3+pyodbc中不工作；它会在连接到SQL Server时引发类似以下错误：CCD_ 1。由于两个打印的查询都有3000个params，我认为它在两种环境中都应该失败，但很明显，这里的情况并非如此。在Python 2环境中，MSODBC 11或MSODBC 17都可以工作，所以我立即排除了与驱动程序相关的问题。

所以我的问题是：

1. 在SQLAlchemy中将list作为多个参数发送是否正确，因为参数列表将与list的长度成比例？我觉得这看起来有点奇怪；我更喜欢将list连接成一个单独的string，因为DB不理解list数据类型
2. 有没有任何迹象表明为什么它会在mxODBC中工作，而不是在pyodbc中工作？mxODBC优化了pyodbc没有的东西吗？如果有任何提示，请告诉我-我可以尝试在这里粘贴更多信息。(我还是调试SQLAlchemy的新手。(

脚注：我看到了很多建议大块数据的答案，但由于1和2，我想知道我一开始做的事情是否正确。

(由于它似乎与pyodbc有关，我在官方存储库中提出了一个内部问题。(

import sqlalchemy
import sqlalchemy.orm
from sqlalchemy import MetaData, Table
from sqlalchemy.ext.declarative import declarative_base
from  sqlalchemy.orm.session import Session
Base = declarative_base()
create_tables = """
CREATE TABLE products(
idn NUMERIC(8) PRIMARY KEY
);
"""
check_tables = """   
SELECT * FROM products;
"""
insert_values = """
INSERT INTO products
(idn)
values
(1),
(2);
"""
delete_tables = """
DROP TABLE products;
"""
engine = sqlalchemy.create_engine('mssql+pyodbc://user:password@dsn')
connection = engine.connect()
cursor = engine.raw_connection().cursor()
Session = sqlalchemy.orm.sessionmaker(bind=connection)
session = Session()
session.execute(create_tables)
metadata = MetaData(connection)
class Products(Base):
__table__ = Table('products', metadata, autoload=True)
try:
session.execute(check_tables)
session.execute(insert_values)
session.commit()
query = session.query(Products).filter(
Products.idn.in_(list(range(0, 3000)))
)
query.all()
f = open("query.sql", "w")
f.write(str(query))
f.close()
finally:
session.execute(delete_tables)
session.commit()

Answer 1

当您执行简单的.in_(list_of_values)时，SQLAlchemy会呈现以下SQL。。。

SELECT team.prov AS team_prov, team.city AS team_city 
FROM team 
WHERE team.prov IN (?, ?)

其中in子句中的每个值都被指定为单独的参数值。pyodbc将此作为…发送到SQL Server。。。

exec sp_prepexec @p1 output,N'@P1 nvarchar(4),@P2 nvarchar(4)',N'SELECT team.prov AS team_prov, team.city AS team_city, team.team_name AS team_team_name 
FROM team 
WHERE team.prov IN (@P1, @P2)',N'AB',N'ON'

因此，如果您的列表很长，那么您将达到2100个参数的限制。据推测，mxODBC在将参数值发送到SQL Server(例如(之前以内联方式插入了参数值

SELECT team.prov AS team_prov, team.city AS team_city 
FROM team 
WHERE team.prov IN ('AB', 'ON')

你可以让SQLAlchemy用为你做这件事

provinces = ["AB", "ON"]
stmt = (
session.query(Team)
.filter(
Team.prov.in_(sa.bindparam("p1", expanding=True, literal_execute=True))
)
.statement
)
result = list(session.query(Team).params(p1=provinces).from_statement(stmt))