CODEWITHSUNDEEP
pythonsql-deletems-access-2016sqlalchemy-access
To_nY
To_nY

Reputation: 1

Pyodbc.OperationalError HY001 when trying to delete rows from MS Access through SQLAlchemy-Access in Python

May I seek your help on an error I have met when I tried to delete all records marked "Y" in "Dummy Rows Indicator" column in Data table of one MS Access file?

The python script is as below.

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)

The error is as following:

(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';]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Have tried below that works, but not able to delete all rows in one time.

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

Have checked the temp folder and no file is larger than 2GB. The MS Access file (.accdb) is 313MB of size. The data to be deleted are of 20 columns and 300,000 rows in the accdb file.

Appreciate you can help.

Thank you!

Upvotes: 0

Views: 201

Answers (1)

To_nY
To_nY

Reputation: 1

I have to delete rows by batches in a loop as below to avoid the error. For your reference. Thanks.

def removedummies(): 
    try: 
        textbox('Removing dummy rows in MS Access...\n')

        metaData = sa.MetaData(bind=engine)
        sa.MetaData.reflect(metaData)
        tblData = metaData.tables['Data']
        col = tblData.c['Dummy Rows Indicator']
        
        ##Remove dummy rows added previously  
        rowsToDelete = sa.select([sa.func.count()]).select_from(tblData).where(col == 'Y').scalar()
        
        while rowsToDelete > 80000:
            engine.execute("DELETE * FROM (SELECT TOP 80000 * FROM Data WHERE [Dummy Rows Indicator] = 'Y')") 
            #Delete 100000 rows or more has caused error due to the maximum size of an access database (2 GB)
            rowsToDelete = rowsToDelete - 80000
        else:
            engine.execute("DELETE FROM Data WHERE [Dummy Rows Indicator] = 'Y'")

        textbox('Dummy rows have been deleted sucessfully. \n')
    except Exception as e:
        textbox(e)

Upvotes: 0

Related Questions