Working with an Access database in Python on non-W

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.

回答1:

"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: Jython, and JayDeBeApi.

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: Jython

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

Option 2: JayDeBeApi

You might prefer this option if you need to work with Python_3 or prefer to work with CPython. 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
ucanaccess_jars = [
    "/home/gord/Downloads/JDBC/UCanAccess/ucanaccess-4.0.2.jar",
    "/home/gord/Downloads/JDBC/UCanAccess/lib/commons-lang-2.6.jar",
    "/home/gord/Downloads/JDBC/UCanAccess/lib/commons-logging-1.1.1.jar",
    "/home/gord/Downloads/JDBC/UCanAccess/lib/hsqldb.jar",
    "/home/gord/Downloads/JDBC/UCanAccess/lib/jackcess-2.1.6.jar",
    ]
classpath = ":".join(ucanaccess_jars)
cnxn = jaydebeapi.connect(
    "net.ucanaccess.jdbc.UcanaccessDriver",
    "jdbc:ucanaccess:///home/gord/test.accdb;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()

回答2:

On Mac OSx and Ubuntu 18.04 you can 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

回答3:

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.

回答4:

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[]=@database.ext 'https://www.rebasedata.com/api/v1/convert?outputFormat=sqlite&errorResponse=zip' -o output.zip