Working with an Access database in Python on non-Windows platform (Linux or Mac)

Question

I want to access the data in a Microsoft Access database. I have some .accdb and .mdb files and want to read them in Python.

From my research, pyodbc can only be used on Windows platform, but I am working on Mac OS X. I am new to Python.

The other option is if I could export the data from the database to a csv and then use in python.

Any help or starting would be highly appreciated.

Licha

Answer 1

On Mac OSx and Ubuntu 18.04 you can use pandas_access

From the documentation:

import pandas_access as mdb

db_filename = 'my_db.mdb'

# Listing the tables.
for tbl in mdb.list_tables(db_filename):
  print(tbl)

# Read a small table.
df = mdb.read_table(db_filename, "MyTable")

On Ubuntu you may need to run:

sudo apt install mdbtools

Bernardinebernardo

I am getting error: [Errno 2] No such file or directory: 'mdb-schema': 'mdb-schema' – Dougherty

That's because pandas_access is just a subprocess calling wrapper around an old and unmaintained CLI utility: mdbtools. mdbtools.sourceforge.net . Not super helpful for modern uses. – Braun

Pandas_access + mdbtools worked out of the box! I hope this will be maintained in the future. I struggled with pyodbc+unixodbc and had to give up due to missing driver. – Conclave

Ubuntu 20.04 and Python 3.8: works perfectly! Thanks – La

On OS X 13.2.1 Venture, brew install mdbtools was necessary prior to using pandas_access successfully. – Monsignor

this worked for bookworm w/python 3.9 after apt-get install -yq mdbtools unixodbc-dev g++ – Lissome

Answer 2

"From my research, pyodbc can only be used on Windows platform"

Not true. The main pyodbc page says

Precompiled binary wheels are provided for most Python versions on Windows and macOS. On other operating systems [pip install pyodbc] will build from source.

However, it is certainly true that using ODBC to manipulate an Access database is mainly done on Windows. "MDB Tools", along with "unixODBC", is often mentioned as a way to work with Access databases on non-Windows platforms, but in my limited experience I have found that it really just doesn't work very well (when it works at all).

Of course, you can always purchase a third-party MS Access ODBC driver for your non-Windows platform, but if you want a free open-source solution you can use the UCanAccess JDBC driver. There are two ways to accomplish that: JayDeBeApi, and Jython.

In both cases you will need to download the latest version of UCanAccess (available for download here) and unpack the "bin.zip" file to a convenient location, making sure to preserve the folder structure:

(In the following examples I unpacked it to ~/Downloads/JDBC/UCanAccess.)

 

Option 1: JayDeBeApi

This is the preferred option since it should work with your existing Python setup. You can install JayDeBeApi with pip.

If you don't already have a JRE (Java Runtime Environment) installed then you'll need that, too. (I used sudo apt install default-jre on Ubuntu.)

Once the required components are in place you should be able to use code like this:

import jaydebeapi

db_path = "/home/gord/test.accdb"
## check your jar file version numbers
ucanaccess_jars = [
    "/home/gord/Downloads/JDBC/UCanAccess/ucanaccess-5.0.1.jar",
    "/home/gord/Downloads/JDBC/UCanAccess/lib/commons-lang3-3.8.1.jar",
    "/home/gord/Downloads/JDBC/UCanAccess/lib/commons-logging-1.2.jar",
    "/home/gord/Downloads/JDBC/UCanAccess/lib/hsqldb-2.5.0.jar",
    "/home/gord/Downloads/JDBC/UCanAccess/lib/jackcess-3.0.1.jar",
]
classpath = ":".join(ucanaccess_jars)
cnxn = jaydebeapi.connect(
    "net.ucanaccess.jdbc.UcanaccessDriver",
    f"jdbc:ucanaccess://{db_path};newDatabaseVersion=V2010",
    ["", ""],
    classpath
    )
crsr = cnxn.cursor()
try:
    crsr.execute("DROP TABLE table1")
    cnxn.commit()
except jaydebeapi.DatabaseError as de:
    if "user lacks privilege or object not found: TABLE1" in str(de):
        pass
    else:
        raise
crsr.execute("CREATE TABLE table1 (id COUNTER PRIMARY KEY, fname TEXT(50))")
cnxn.commit()
crsr.execute("INSERT INTO table1 (fname) VALUES ('Gord')")
cnxn.commit()
crsr.execute("SELECT * FROM table1")
for row in crsr.fetchall():
    print(row)
crsr.close()
cnxn.close()

 

Option 2: Jython

(Note that Jython is a separate implementation of Python, it only supports Python 2.7, and is apparently no longer under active development.)

Important: The following instructions are for UCanAccess version 3.0.5 or later.

After ...

• installing Jython (via sudo apt-get install jython on Ubuntu) and
• downloading UCanAccess and unpacking it as described above

I created the following Jython script named "dbTest.py"

from com.ziclix.python.sql import zxJDBC

jdbc_url = "jdbc:ucanaccess:///home/gord/Documents/test.accdb"
username = ""
password = ""
driver_class = "net.ucanaccess.jdbc.UcanloadDriver"

cnxn = zxJDBC.connect(jdbc_url, username, password, driver_class)
crsr = cnxn.cursor()
crsr.execute("SELECT AgentName FROM Agents")
for row in crsr.fetchall():
    print row[0]
crsr.close()
cnxn.close()

and ran it with the following shell script

#!/bin/bash
export CLASSPATH=.:/home/gord/Downloads/JDBC/UCanAccess/loader/ucanload.jar
jython dbTest.py

Mendel

can you please help me on Jython setup, i am getting the error - No module named com.ziclix.python.sql. – Anticlastic

@ShravyaShetty Run it with using jython instead of 'python'.. It didn't solve my problem though. But your error is because of running with python. – Antechoir

How would you open a password encrypted database using jackcess-encrypt-2.x.x with this method? – Moneylender

pip install JPype1==0.6.3 JayDeBeApi==1.1.1 this is not working on my mac. I get below error: #include_next <limits.h> /* recurse down to the real one */ ^ compilation terminated. error: command 'gcc' failed with exit status 1 I even tried below steps to fix this error but it is not working #54016817 – Tagalog

UCanAccess is version 5.0.1 now so make sure to update this code if you copy/paste ucanaccess_jars – Lissome

Answer 3

For a one time conversion of an old .mdb file to .sqlite, I found this site helpful: https://www.rebasedata.com/convert-mdb-to-sqlite-online . I'm not affiliated with it in any way, it's just what I wound up with when no answers here worked for me. They offer a curl command:

curl -F files[][email protected] 'https://www.rebasedata.com/api/v1/convert?outputFormat=sqlite&errorResponse=zip' -o output.zip

Braun

The service wasn't free. See this answer for an open source solution. – Gestation

Did you even try it before downvoting? It was absolutely free for me for my small MS Access DB. There's probably a tier that requires payment, but I found this question while trying to do a conversion myself and none of the existing answers got me there which is why I posted what actually worked for me. I don't pay for this kind of stuff. – Braun

I did try it. Twice. From different browsers. My database had 300 rows. Maybe they've changed their pricing scheme since you used, or maybe the difference arises from some other reason. But this wasn't free when I tried it, so it's hard for me to recommend your answer. I would have far preferred to use your solution than to have to waste time coming up with an alternative; I'm sorry it didn't work for me. – Gestation

How dare you suggest a paid solution to this! I'd rather spend 6 hours debugging mdb installations. LOL - paid solutions are underrated on SO. I wasted so much time trying to get this to work and rebasedata just did the trick. Protip - access the site from a Windows machine if you can, otherwise visiting from a Mac costs more (they don't let you buy the 7 day access) – Interrupter

Answer 4

I had problems with pandas-access, which is recommended by the top answer.

The problem with using pyodbc, as many of the other answers point out, is that getting an mdb driver for pyodbc on macos is not super quick, easy, or cheap.

And of course, the online tools require you to upload your database, which may contain data you would prefer not to share.

So, I ended up wrapping mdbtools on my own, which is simple enough, and in case you find yourself here, I decided I'd share it in case it's useful. It takes an mdb file and exports csv, excel, or sqlite, depending on your needs. The full code and documentation is in this gist.

I essentially just wrapped the following 3 commands:

1. mdb-tables: mdb-tables -d ", " MDB_FILE
2. mdb-export: importantly, this can export csv (default) but also can export to other backends, such as sqlite via -I, e.g. mdb-export -I sqlite MDB_FILE TABLE_NAME
3. mdb-schema: this can also take a backend (e.g. sqlite): mdb-schema --indexes --relations --default-values --not-null MDB_FILE -T TABLE_NAME sqlite

Kilometer

Answer 5

This question is old but the documentation says:

The easiest way to install is using pip. Windows binaries will be downloaded but other operating systems will need to compile from source.

So it should be possible. There is also a example for linux machines.

http://mkleehammer.github.io/pyodbc/#connecting

But check out this part in the source.

https://github.com/mkleehammer/pyodbc/blob/master/tests2/accesstests.py#L630,L636

It shows you how the connection string for MS Access files looks like.

Braeunig

It is certainly possible to install pyodbc on non-Windows machines, but you'd still need an ODBC driver for the Access database. That's the difficulty. – Mendel