使用SQLAlchemy-Access删除Access数据时遇Pyodbc.OperationalError HY001错误求助

尝试删除MS Access数据库Data表中**「Dummy Rows Indicator」**列标记为"Y"的所有记录时触发错误，相关信息如下：

所用Python脚本 #

import sqlalchemy_access as saa
import sqlalchemy as sa
from sqlalchemy import create_engine
import pyodbc

def removedummies(): 
    try: 
        engine = create_engine('access+pyodbc://@MS Access Database', echo = True)
        textbox('Removing dummy rows in MS Access...\n')
        
        ##Remove dummy rows added previously
        engine.execute("DELETE * FROM Data WHERE [Dummy Rows Indicator] = 'Y';")
        
        textbox('Dummy rows have been deleted sucessfully. \n')
    except Exception as e:
        textbox(e)

错误信息 #

(pyodbc.OperationalError) ('HY001', '[HY001] [Microsoft][ODBC Microsoft Access Driver] The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result. (-1812) (SQLExecDirectW)')
[SQL: DELETE * FROM Data WHERE [Dummy Rows Indicator] = 'Y';]

补充信息 #

• 临时文件夹无超过2GB的文件；
• MS Access文件(.accdb)大小为313MB，待删除数据为20列共30万行；
• 尝试分批删除（如下语句）可执行，但无法一次性清除所有目标行：

engine.execute("DELETE * FROM (SELECT TOP 10000 * FROM Data WHERE [Dummy Rows Indicator] = 'Y')") 

方案1：循环分批删除 #

基于已验证的单批删除逻辑，通过循环重复执行操作，直到无符合条件的行：

import sqlalchemy_access as saa
import sqlalchemy as sa
from sqlalchemy import create_engine
import pyodbc

def removedummies(): 
    try: 
        engine = create_engine('access+pyodbc://@MS Access Database', echo = True)
        textbox('Removing dummy rows in MS Access...\n')
        
        # 循环执行分批删除
        while True:
            result = engine.execute("DELETE * FROM (SELECT TOP 10000 * FROM Data WHERE [Dummy Rows Indicator] = 'Y')")
            if result.rowcount == 0:
                break
            textbox(f"Deleted {result.rowcount} dummy rows...\n")
        
        textbox('Dummy rows have been deleted successfully. \n')
    except Exception as e:
        textbox(e)

方案2：直接使用pyodbc连接操作 #

跳过SQLAlchemy的封装，直接通过pyodbc执行Access本地查询，减少中间层的临时存储消耗：

import pyodbc

def removedummies():
    try:
        # 替换为你的数据库文件路径
        conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=你的数据库路径\文件名.accdb;')
        cursor = conn.cursor()
        textbox('Removing dummy rows in MS Access...\n')
        
        while True:
            cursor.execute("DELETE TOP 10000 FROM Data WHERE [Dummy Rows Indicator] = 'Y'")
            deleted_count = cursor.rowcount
            if deleted_count == 0:
                break
            conn.commit()
            textbox(f"Deleted {deleted_count} dummy rows...\n")
        
        textbox('Dummy rows have been deleted successfully. \n')
        conn.close()
    except Exception as e:
        textbox(e)

方案3：添加索引优化查询 #

给**「Dummy Rows Indicator」**列创建索引，帮助Access快速定位目标行，减少临时存储需求：
在Access中执行以下SQL（可通过Access界面或代码执行）：

CREATE INDEX idx_dummy_indicator ON Data ([Dummy Rows Indicator]);

添加索引后，可尝试重新执行原一次性删除语句，大概率能成功。

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