What do I need to read Microsoft Access databases using Python?

Question

How can I access Microsoft Access databases in Python? With SQL?

I'd prefere a solution that works with Linux, but I could also settle for Windows.

I only require read access.

Hampden

Answer 1

I've used PYODBC to connect succesfully to an MS Access db - on Windows though. Install was easy, usage is fairly simple, you just need to set the right connection string (the one for MS Access is given in the list) and off you go with the examples.

Waler

notice this question states linux, and you politely mentioned windows. The correct answer for linux (from a pyodbc dev) is below https://mcmap.net/q/330596/-what-do-i-need-to-read-microsoft-access-databases-using-python – Decomposer

Answer 2

On Linux, MDBTools is your only chance as of now. ^[disputed]

On Windows, you can deal with mdb files with pypyodbc.

To create an Access mdb file:

import pypyodbc
pypyodbc.win_create_mdb( "D:\\Your_MDB_file_path.mdb" )

Here is an Hello World script that fully demostate pypyodbc's Access support functions.

Disclaimer: I'm the developer of pypyodbc.

Eolian

According to your homepage it runs on linux. – Putnem

The mdb create and compact functions run only on windows, for it's ODBC interfacing functions, it's cross platform. – Eolian

on Linux mdbtools. Check codeenigma.com/community/blog/… and nialldonegan.me/2007/03/10/… – Populace

"On Linux, MDBTools is your only chance as of now." - That is no longer true. Jython and the UCanAccess JDBC driver are a feasible alternative under Linux (and Mac OS, I assume). Details in another question here – Amadis

Answer 3

You've got what sounds like some good solutions. Another one that might be a bit closer to the "metal" than you'd like is MDB Tools.

MDB Tools is a set of open source libraries and utilities to facilitate exporting data from MS Access databases (mdb files) without using the Microsoft DLLs. Thus non Windows OSs can read the data. Or, to put it another way, they are reverse engineering the layout of the MDB file.

Also note that I doubt they've started working on ACCDB files and there is likely not going to be much request for that capability.

Simulation

Actually none of the other solutions listed works on Linux (unless you want to shell out some $800 for a commercial driver). MDB Tools might be "close to the metal" but it includes a unixODBC driver that makes it possible to read an Access db using pyodbc onLinux. Thanks for saving my day! – Claque

A wonderful little program! A bit hard to find the names/docs for the individual utilities, though, so for reference: Linux users can use man mdb- and then tab to get the auto-complete list; and everyone can see the txt files in this Github repository. – Vanillic

Answer 4

How about pyodbc? This SO question demonstrates it's possible to read MS Access using it.

Compliment

Answer 5

On Ubuntu 12.04 this was what I did to make it work.

Install pyodbc:

$ sudo apt-get install python-pyodbc

Follow on installing some extra drivers:

$ sudo apt-get install mdbtools libmdbodbc1

Make a little test program which connects to the DB and displays all the tables:

import os
import pyodbc

db_path = os.path.join("path", "toyour", "db.mdb")
odbc_connection_str = 'DRIVER={MDBTools};DBQ=%s;' % (db_path)
connection = pyodbc.connect(odbc_connection_str)
cursor = connection.cursor()

query = "SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0"
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
    print row

I hope it helped.

Underthecounter

I believe libmdbodbc1 is now replaced with odbc-mdbtool. At least thats what I got working on Ubuntu 18.04. – Donets

@MartinThøgersen I believe it's called odbc-mdbtools (s at end) – Tibold

After installing pyobdc, mdbtools, on my Mac with Homebrew using a customized brew edit mdbtools and futzing around to install unicodbc, this test code worked for me (after updating the last like to print(row)) – Prieto

Answer 6

Old question, but I thought I'd post a pypyodbc alternative suggestion for Windows: ADO. Turns out, it's really easy to get at Access databases, Excel spreadsheets and anything else with a modern (as opposed to old-school ODBC) driver via COM.

Check out the following articles:

• http://www.mayukhbose.com/python/ado/index.php
• http://www.markcarter.me.uk/computing/python/ado.html
• http://www.ecp.cc/ado_examples.shtml

Dismay

Answer 7

Personally, I have never been able to get MDB Tools (along with related ODBC stuff like unixODBC) to work properly with Python or PHP under Linux, even after numerous attempts. I just tried the instructions in the other answer to this question here and all I got was "Segmentation fault (core dumped)".

However, I did get the UCanAccess JDBC driver to read both .mdb and .accdb files on Linux from either Jython or CPython+JayDeBeApi. For detailed instructions on how I set it up under Ubuntu 14.04 LTS see my other answer here.

Amadis

Answer 8

Most likely, you'll want to use a nice framework like SQLAlchemy to access your data, or at least, I would recommend it. Support for Access is "experimental", but I remember using it without too many problems. It itself uses pyodbc under the hood to connect to Access dbs, so it should work from windows, linux, os x and whatnot.

Littrell

I need it only to import data to my SQLite backed Storm model. :) – Putnem

SQLAlchemy + MS Access does not work for SQLAlchemy 0.5+, as the code for it has not been updated. – Ptolemaist

that uses odbc, which does not solve the problem of accessing the physical database files in the first place. – Sello

Answer 9

To read an Access database as a pandas dataframe (Windows).

This is a very quick and easy solution that I have used successfully for smaller databases.

You can read an Access database by making a permanent link to Excel and saving that file (it takes a couple of clicks), link here:

https://support.office.com/en-gb/article/Connect-an-Access-database-to-your-workbook-a3d6500c-4bec-40ce-8cdf-fb4edb723525

You can then simply read that Excel file as a pandas dataframe.

So, for example, save the linked Excel file as 'link_to_master.xlsx' in location \FileStore\subfolder1\subfolder.

Run the following in python:

import pandas as pd
import os
os.chdir('\\\\FileStore\\subfolder1\\subfolder') #sets the folder location
df = pd.read_excel('link_to_master.xlsx') # reads the Excel file
df

Consider frequency of the link refresh if you are re-visiting your python script. i.e. The link between Excel and Access is static.

Ingamar

Answer 10

If you sync your database to the web using EQL Data, then you can query the contents of your Access tables using JSON or YAML: http://eqldata.com/kb/1002.

That article is about PHP, but it would work just as well in Python.

Lahr

Answer 11

If you have some time to spare, you can try to fix and update this python-class that reads MS-Access DBs through the native COM32-client API: Extraction and manipulation class for Microsoft Access

Ruler

Answer 12

The way I connect Python to MS Access under Windows is by using this way: Connect to MS Access with Python. Maybe you can find some trouble on Win 7, so I found a solution: Solving a connection between MS Access and Python on Windows 7

I haven't tried connecting under Linux!

Helmick