CODEWITHSUNDEEP
pythonmacosms-accessodbcpyodbc
user2948166
user2948166

Reputation: 629

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

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.

Upvotes: 27

Views: 33767

Answers (5)

Gord Thompson
Gord Thompson

Reputation: 123509

"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 it has its limitations.

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.

Go to UCanAccess on Maven Central, Browse the latest version, and download the uber.jar file.

Using JayDeBeApi

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"
classpath = "/home/gord/Downloads/ucanaccess-5.1.2-uber.jar"
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()

Upvotes: 22

Jason Sundram
Jason Sundram

Reputation: 12524

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

Upvotes: 0

Vipassana Vijayarangan
Vipassana Vijayarangan

Reputation: 8859

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

Upvotes: 29

mattmc3
mattmc3

Reputation: 18325

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

Upvotes: 0

gravmatt
gravmatt

Reputation: 428

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.

Upvotes: -1

Related Questions