时间戳

首页 > 解决方案 > 使用 pyodbc 过滤器 .in_(list) 出现“最大参数数”错误

python - 使用 pyodbc 过滤器 .in_(list) 出现“最大参数数”错误

问题描述

我们在 Python 2 + mxODBC 中运行的查询之一在 Python 3 + pyodbc 中不起作用；它会引发这样的错误：Maximum number of parameters in the sql query is 2100.连接到 SQL Server 时。由于两个打印的查询都有 3000 个参数，我认为它应该在两种环境中都失败，但显然这里似乎并非如此。在 Python 2 环境中，MSODBC 11 或 MSODBC 17 都可以工作，所以我立即排除了与驱动程序相关的问题。

所以我的问题是：

  1. 在 SQLAlchemy 中将a 作为多个参数发送是否正确，list因为参数列表将与 的长度成正比list？我觉得它看起来有点奇怪；我宁愿将它连接list成一个单一string的，因为数据库不理解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()

标签： pythonsqlalchemypyodbc

解决方案

当你做一个简单的.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))

推荐阅读