Importing Data from ms access into Python as List of dict

Question

I've been able to insert records from python into a ms access database but having trouble reading it back into Python. I require it to be in the format of a List of Dictionarys in Python.

So far I have tried using pyodbc to do this with the following code;

db_path = ("path/tomy/db/mine.mdb")
odbc_connection_str = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%s;' % (db_path)
connection = pyodbc.connect(odbc_connection_str)
cursor = connection.cursor()

I then tried the following but i get a no read permission error.

query = "SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0"
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
    print row

Anyone have experience in extracting from a ms access database and then able to store it in python as a list of dicts?

Answer 1

To solve the "no read permission error" you need to GRANT permission to that system table as explained here:

Record cannot be read; no read permission on 'MSysObjects'

As for returning the query results as a list of dictionaries, you can find the solution here:

Output pyodbc cursor results as python dictionary