Fork me on GitHub

Project Notes

#076 MDB Files

Reading Microsoft Access database files on a Mac with Python

Notes

I have some old Microsoft Access databases lying around … I wonder if there’s anything useful in them? And can I read them from my Mac without installing a whole suite of Microsoft tools?

There’s at least one way to do this that I’ve found to work: mdbtools for *nix MDB support and pandas_access for a tidy python wrapper.

I ran these tests with python 3..

Installing

$ brew install mdbtools
...
Installing dependencies for mdbtools: gettext, sqlite, python and glib
...
$ pip3 install -r requirements.txt

Some pandas_access Basics

Loading a table as a pandas.DataFrame

import pandas_access as mdb

df = mdb.read_table('quotes.mdb', 'Quotations')

Getting column names. df.columns returns a pandas.Index. and df.columns returns a numpy.ndarray

print('Columns: {}'.format(list(df.columns.values)))
=> Columns: ['AuthorLast', 'AuthorFirst', 'Quote', 'QuoteDate', 'Circumstance']

Some Examples

Listing tables in a database…

$ ./show_tables.py quotes.mdb
Listing the tables in quotes.mdb..
Quotations

Listing records in a table…

$ ./show_records.py quotes.mdb Quotations
Listing the records in quotes.mdb::Quotations..
Table has 2945 rows
Columns: ['AuthorLast', 'AuthorFirst', 'Quote', 'QuoteDate', 'Circumstance']
--------------

AuthorLast: Edison
AuthorFirst: Thomas
Quote: ...I have not failed. I've just found 10,000 ways that won't work.
QuoteDate: nan
Circumstance: nan
--------------

AuthorLast: Asimov
AuthorFirst: Isaac
Quote: [John] Dalton's records, carefully preserved for a century, were destroyed during the World War II bombing of Manchester. It is not only the living who are killed in war.
QuoteDate: nan
Circumstance: nan
--------------

AuthorLast: Gates
AuthorFirst: Bill
Quote: 640K ought to be enough for anybody.
QuoteDate: nan
Circumstance: In 1981
--------------

AuthorLast: Berra
AuthorFirst: Yogi
Quote: 95% of this game is half mental.
QuoteDate: nan
Circumstance: nan
--------------

AuthorLast: Dirksen
AuthorFirst: Everett
Quote: A billion here, a billion there, and pretty soon you're talking about real money.
QuoteDate: nan
Circumstance: nan
--------------

AuthorLast: Marx
AuthorFirst: Groucho
Quote: A child of five would understand this. Send someone to fetch a child of five.
QuoteDate: nan
Circumstance: nan
...(etc)...

Credits and References

About LCK#76 python

This page is a web-friendly rendering of my project notes shared in the LittleCodingKata GitHub repository.

Project Source on GitHub Return to the LittleCodingKata Catalog
About LittleCodingKata

LittleCodingKata is my collection of programming exercises, research and code toys broadly spanning things that relate to programming and software development (languages, frameworks and tools).

These range from the trivial to the complex and serious. Many are inspired by existing work and I'll note credits and references where applicable. The focus is quite scattered, as I variously work on things new and important in the moment, or go back to revisit things from the past.

This is primarily a personal collection for my own edification and learning, but anyone who stumbles by is welcome to borrow, steal or reference the work here. And if you spot errors or issues I'd really appreciate some feedback - create an issue, send me an email or even send a pull-request.

Follow the Blog follow projects and notes as they are published in your favourite feed reader