how to deal with .mdb access files with python

Question

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?

Answer 1

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)

Answer 2

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
…

Answer 3

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.

Answer 4

For a solution that works on any platform that can run Java, consider using Jython or JayDeBeApi along with the UCanAccess JDBC driver. For details, see the related question

Read an Access database in Python on non-Windows platform (Linux or Mac)

Answer 5

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())

Answer 6

This code will convert all the tables to CSV.

Happy Coding

for tbl in mdb.list_tables("file_name.MDB"):
    df = mdb.read_table("file_name.MDB", tbl)
    df.to_csv(tbl+'.csv')