[Python] Why does pandas read_sql throw an error and how to prevent it?

I have a database (Microsoft Access) that i am accessing through my python file, inorder for my program to work effeciently i need to have a cached copy of the whole database. I am able to connect to the database no problem, i was hoping to then import this database into a pandas format. I am doing this by :

cached_database = pandas.read_sql('select * from myTable', database_connection)


problem with this is this line throws an error:

UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
cached_database = pandas.read_sql('select * from myTable', database_connection)
pyodbc.Error: ('HY109', '[HY109] [Microsoft][ODBC Microsoft Access Driver] Record is deleted. (-1017) (SQLGetData)')


Here i thought maybe pandas is just broken so i investigated a bit, and have thought using raw pyodbc commands:

cursor = database_connection.cursor()
cursor.execute('select * from myTable')

print(cursor.fetchone) # this works and returns the first row
print(cursor.fetchmany(100000)) # this also works and return me 100 thousand row
print(cursor.fetchmany(1000000)) # this does NOT work and throws the error like above
print(cursor.fetchall()) # this does NOT also work and throws the same error above


So this leads me to believe that a row in the database is causing the code to fail, its hard to find though given the sheer number of rows the database have.

Is this any work around to this? i would like to import my data to pandas so i can do some processing with them.

Thanks so much

Continue reading...