Python查询SQL Server超长IN列表触发8632错误的解决方法
问题描述
执行SQL语句时遇到IN语句取值列表体量过大的问题,需要遍历列表获取合并结果集。
收到的报错信息如下:
[SQL Server]Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them. (8632) (SQLExecDirectW)')
报错触发原因是IN列表长度达到7.5万条,超出SQL Server表达式服务限制。
初步实现思路
拟对ID列表做分块后循环查询,伪代码逻辑如下:
List = 75k items Chunk_Size = 10000 For i in first Chunk_Size of List df.append = SQL results WHERE IN List of 10k items
按该思路会循环8次,将每次查询结果追加到DataFrame中,支持自定义分块大小便于性能测试。
现有代码与遇到的问题
现有实现代码
import pyodbc, pandas as pd conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};' 'SERVER=server;' 'DATABASE=db;' 'UID=Me;' 'PWD=Secret;' 'Trusted_Connection=yes;') list_of_ids = ['1', 'Hello', '3.4', 'xyz5000'] # 总长度7.5万 params= "', '".join(list_of_ids) sql = ("SELECT [ItemNumber],[Name],[Part] FROM table1 WHERE Part IN ('{}');".format(params)) sql_query = pd.read_sql_query(sql,conn)
已知用format拼接SQL的方式不规范,尝试改用参数化查询时出现报错:
sql_query2 = cursor.execute("SELECT [ItemNumber],[Name],[Part] FROM table1 WHERE Part IN ?;",list_of_ids)
报错信息:
pyodbc.ProgrammingError: ('The SQL contains 1 parameter markers, but 4 parameters were supplied', 'HY000')
问题原因与优化方向
报错原因
参数化查询报错的核心逻辑是:IN 子句后的单个参数标记?只能对应1个参数值,传入长度为N的列表时,pyodbc会识别到N个待传入参数,但SQL语句中仅声明了1个参数位,参数数量不匹配因此触发报错。
安全优化建议
如果使用场景存在SQL注入风险,建议将分块查询改为动态生成参数标记的参数化写法,避免字符串拼接的安全隐患,示例逻辑如下:
for i in range(0, len(list_of_ids), Chunk_Size): chunk = list_of_ids[i:i+Chunk_Size] # 动态生成和分块长度一致的参数占位符 placeholders = ','.join(['?'] * len(chunk)) sql = f"SELECT [ItemNumber],[Name],[Part] FROM table1 WHERE Part IN ({placeholders})" # 直接传入分块列表作为查询参数 cursor.execute(sql, chunk) list1 = cursor.fetchall() list2.append(list1)
最终实现方案
内部使用无注入风险的场景下,可使用以下代码实现需求:
Chunk_Size = 10000 list2 = [] cursor = conn.cursor() for i in range(0, len(list_of_ids), Chunk_Size): params= "', '".join(list_of_ids[i:i+Chunk_Size]) sql = "SELECT [ItemNumber],[Name],[Part] FROM table1 WHERE Part IN ('{}');".format(params) cursor.execute(sql) list1 = cursor.fetchall() list2.append(list1) from pandas.core.common import flatten list3 = list(flatten(list2)) match_df = pd.DataFrame.from_records(list3, columns=['Item Number','Name','Part'])
内容的提问来源于stack exchange,提问作者Steve

