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?

Rigadoon

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)

Cati

This doesn't seem to work from Linux, since there's no driver included by default with pyodbc for reading Access. "Data source name not found, and no default driver specified (0) (SQLDriverConnect)" – Bender

@Cerin: thanks for the update. Since the question didn't specify "from Linux" I did not address that concern. Best regards. – Cati

@bernie is there a way to do this in linux? – Camelback

@jsc123 For a solution that works in Linux, see my answer. – Rubbery

The 'with open' line here didn't work for me (using Python 3.51). I changed it to 'with open('mytable.csv', 'w', newline='') as fou:' and all was ok :) – Phosphorism

@Cati i am getting the following error in writing the csv files. csv_writer.writerows(rows) TypeError: a bytes-like object is required, not 'str' Line 22 – Pervasive

@VigneshRajendran: change the mode from 'wb' to 'w'. Answer edited. – Cati

this works till half of the database after that this error comes. UnicodeEncodeError: 'charmap' codec can't encode character '\x90' in position 707: character maps to <undefined> – Pervasive

@Cati is this have the read-only option? because I am accessing a network shared file, while reading it it prevents other users from opening the file. – Pervasive

In more recent Access versions it should be Microsoft Access Driver (*.mdb, *.accdb). – Sandoval

If you are getting the "Data source name not found, and no default driver specified (0) (SQLDriverConnect)" error, make sure you have the MS Access driver installed on your machine: microsoft.com/en-us/download/details.aspx?id=13255. The x86 version worked for me. Make sure to restart your python environment as well. – Clypeate

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
…

Dashing

works fine with Linux (tested in CenOs 7 and Ubuntu with python 2.7), very easy way to read data from .mdb files. Thanks ;-) – Halothane

is this free and open sources? – Ellata

@Ellata It uses the MIT license, so yes. (github.com/reubano/meza/blob/master/LICENSE) – Dashing

I have a large MDB file and its giving error read: Is a directory Couldn't read the first page. Couldn't open database. – Ellata

@Ellata You should create a separate question in which you lay out your steps that produce the error you mention. – Dashing

I have tried it for lmdb file but I am getting KeyError: 'lmdb' error – Retrogression

@Retrogression please create a separate question with a detailed description of what you are doing and the error message you get. – Dashing

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.

Rosaline

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)

Rubbery

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

Rendition

Answer 6

Have found very simple code to read .MDB

Actually, I don't know, how it works :)), but it really works! You can get a list of tables, get data from a table, convert it to Pandas or save to CSV. For me converting to Pandas was the best option:

https://github.com/solieman/python-mdbtools/blob/master/read_mdb.py

Descriptive

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Bills

Answer 7

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

Trophoblast

what is mdb? what did you import? is it a third party library? – Valvular

its basically the Microsoft database file and after importing data in it you can convert it to the data frame using the above method – Trophoblast

without an import mdb or some line like mdb = ..., this code snippet is very unclear... – Momentary

most probably forgot the library : 'pandas_access' <code>import pandas_access as mdb </code> more information : # pypi.org/project/pandas_access - plugin # pydigger.com/pypi/pandas_access - *** – Whyte