Liberate data from Access databases.

access_horror.py

"""
A script to run on a Windows 7 host after installing the Access redistributable:

http://www.microsoft.com/en-nz/download/details.aspx?id=13255

Install Python 2.7, and then pyodbc from:

http://code.google.com/p/pyodbc/

The script dumps all tables to a dictionary and pickles it. The dictionary has a key for each table name, each value is a dictionary that has the following keys.

* "columns" - a list of column names.
* "column_details" - a list of dictionaries describing each column, e.g. the data type and text description.
* "rows" - a list of lists. Each sublist is a table row.

"""
import pyodbc
import pickle

in_file = r"Z:\horrible_access.accdb"
out_file = r"Z:\lovely.pickle"

cnxn = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=%s;Uid=Admin; Pwd=;' % in_file)
cursor = cnxn.cursor()
t = cursor.tables()
my_t = [x[2] for x in t.fetchall() if x[3] == u'TABLE']

data = {}

for t_name in my_t:
    data[t_name] = {}

    cols = cursor.columns(table=t_name)
    data[t_name]['column_detail'] = [list(c) for c in cols.fetchall()]
    data[t_name]['columns'] = [x[3] for x in data[t_name]['column_detail']]

    t_cursor = cursor.execute('select * from "%s"' % t_name)
    data[t_name]['rows'] = [list(r) for r in t_cursor.fetchall()]

f = open(outfile, 'w')
pickle.dump(data, f)
f.close()