how to deal with .mdb access files with python

Can someone point me in the right direction on how to open a .mdb file in python? I normally like including some code to start off a discussion, but I don't know where to start. I work with mysql a fair bit with python. I was wondering if there is a way to work with .mdb files in a similar way?

Below is some code I wrote for another SO question.
It requires the 3rd-party pyodbc module.

This very simple example will connect to a table and export the results to a file.
Feel free to expand upon your question with any more specific needs you might have.

import csv, pyodbc

# set up some constants
MDB = 'c:/path/to/my.mdb'
DRV = '{Microsoft Access Driver (*.mdb)}'
PWD = 'pw'

# connect to db
con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))
cur = con.cursor()

# run a query and get the results 
SQL = 'SELECT * FROM mytable;' # your query goes here
rows = cur.execute(SQL).fetchall()
cur.close()
con.close()

# you could change the mode from 'w' to 'a' (append) for any subsequent queries
with open('mytable.csv', 'w') as fou:
    csv_writer = csv.writer(fou) # default field-delimiter is ","
    csv_writer.writerows(rows)

There's the meza library by Reuben Cummings which can read Microsoft Access databases through mdbtools.

Installation

# The mdbtools package for Python deals with MongoDB, not MS Access. 
# So install the package through `apt` if you're on Debian/Ubuntu
$ sudo apt install mdbtools
$ pip install meza

Usage

>>> from meza import io

>>> records = io.read('database.mdb') # only file path, no file objects
>>> print(next(records))

Table1
Table2
…

This looks similar to a previous question:

• What do I need to read Microsoft Access databases using Python?
• http://code.activestate.com/recipes/528868-extraction-and-manipulation-class-for-microsoft-ac/

Answer there should be useful.

In addition to bernie's response, I would add that it is possible to recover the schema of the database. The code below lists the tables (b[2] contains the name of the table).

con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,MDB,PWD))
cur = con.cursor()

tables = list(cur.tables())

print 'tables'
for b in tables:
    print b

The code below lists all the columns from all the tables:

colDesc = list(cur.columns())