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?
Add a comment
|
7 Answers
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)
12 Comments
Cerin
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)"
NumenorForLife
@bernie is there a way to do this in linux?
Gord Thompson
@jsc123 For a solution that works in Linux, see my answer.
mechanical_meat
@VigneshRajendran: change the mode from
'wb' to 'w'. Answer edited.
Robin Dinse
In more recent Access versions it should be
Microsoft Access Driver (*.mdb, *.accdb). |
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
…
7 Comments
serfer2
works fine with Linux (tested in CenOs 7 and Ubuntu with python 2.7), very easy way to read data from .mdb files. Thanks ;-)
karnataka
is this free and open sources?
jnns
@karnataka It uses the MIT license, so yes. (github.com/reubano/meza/blob/master/LICENSE)
karnataka
I have a large MDB file and its giving error read: Is a directory Couldn't read the first page. Couldn't open database.
jnns
@karnataka You should create a separate question in which you lay out your steps that produce the error you mention.
|
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.
Comments
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())
Comments
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)
Comments
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
1 Comment
Community
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.
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')
4 Comments
borgr
what is mdb? what did you import? is it a third party library?
Malik Mussabeheen Noor
its basically the Microsoft database file and after importing data in it you can convert it to the data frame using the above method
Fee
without an
import mdb or some line like mdb = ..., this code snippet is very unclear...
kris
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 - ***