How to Convert Microsoft Access (.ACCDB) to PostgreSQL (.SQL) in Python

Dealing with legacy databases like Microsoft Access can be a huge pain.

While paid database migration services exist, I wanted an easy way to convert my .accdb files to PostgreSQL, without owning a Microsoft Access license or needing a Office 360 subscription.

This short tutorial will show you how to convert Microsoft Access database files (.MDB or .ACCDB) to PostgreSQL (`.sql).

I highly recommend Jakob Egger's MDB/ACCDB Viewer app if you work with Microsoft Access databases on a Mac (not affiliated, I just like the software)

Why convert .accdb to .sql?

Dealing with legacy Microsoft Access databases can be a pain, and there are many reasons why you may want to convert Access to SQL:

• Data migration projects
• Legacy system modernization
• Moving from desktop to web applications
• Creating backups of Access databases in a more standard format

Regardless of your reasoning, here's a simple way to programmatically export Microsoft Access to PostgreSQL (or MySQL, Oracle, SQLite).

Let's dive in.

Prerequisites

Before we start, here's the prerequisites for this tutorial.

• Mac/Linux environment
• PostgreSQL server and client tools (psql)
• Python

First, we'll need to install mdbtools to extract data from Microsoft Access files.

Install mdbtools

mdbtools is a fantastic library for accessing MDB and ACCDB files programmatically.

To install it on Mac:

Copy
brew install mdbtools

To install on Linux:

Copy
# Ubuntu/Debian
sudo apt-get install mdbtools

# Fedora
sudo dnf install mdbtools

Native Windows binaries of mdbtools are not available, but you can explore these workarounds to install mdbtools on Windows: 1, 2

Then confirm the installation:

Copy
mdb-tables --version

Create Python script to convert .accdb to SQL

We'll be using the mdb-schema tool to extract the table schemas from Access, which we'll then use to pass to another database (PostgreSQL in this case) to create a replica of the original table format.

The supported backend values are access, sybase, oracle, postgres, mysql and sqlite

Next we'll setup a simple python script to run mdbtools and migrate our .accdb to our PostgreSQL server.

Copy
import subprocess

def accdb_to_postgres(accdb_file, db_name, host, user, password):
    # get a list of tables
    tables = (
        subprocess.check_output(["mdb-tables", "-1", accdb_file])
        .decode()
        .strip()
        .split("\n")
    )

    # construct postgresql connection string
    pg_conn = f"postgresql://{user}:{password}@{host}/{db_name}"

    # iterate through tables
    for table in tables:
        print(f"Processing table {table}...")

        # get schema (need to convert Access types to PostgreSQL types)
        # supported backends: `access`, `sybase`, `oracle`, `postgres`, `mysql` and `sqlite`
        schema = subprocess.check_output(["mdb-schema", f"--table={table}", accdb_file, "postgres"]).decode()

        # temporarily store schema
        with open("temp.sql", "w") as f:
            f.write(schema)
        
        # create table in postgres
        subprocess.run(["psql", pg_conn, "-f", "temp.sql"])

        # export data and import to postgres
        data = subprocess.check_output(["mdb-export", accdb_file, table]).decode()
        process = subprocess.Popen(
            ["psql", pg_conn, "-c", f"\copy {table} FROM STDIN WITH CSV HEADER"],
            stdin=subprocess.PIPE,
        )
        process.communicate(data.encode())

This function uses mdb-tables to extract a list of Access tables, connects to PostgreSQL (or whatever database you want) and iterates through the tables.

For each table, the function copies and converts the Access schema, creates the table in SQL, and exports the data before importing it to your target database.

And that's it! Now you can run the function:

Copy
accdb_to_postgres(
    "/path/to/database.accdb", 
    "db_name"
    "127.0.0.1",
    "postgres",
    "password",
)

You should see output that looks like:

Copy
python3 ./main.py

Processing table users...
SET
CREATE TABLE
ALTER TABLE
COPY 2172

Processing table sessions...

And so on. While this process works seamlessly for many cases, there are some limitations you should be aware of.

Limitations of this approach

• Newer .accdb files might have compatibility issues with mdbtools
• Some Access-specific features (like forms, reports, macros) won't be converted
• Data type mappings between Access and PostgreSQL might need manual adjustment
• Large tables might need batch processing for better performance
• Binary data (BLOB) fields might need special handling

Wrapping up

I hope this tutorial helps you convert .accdb to PostgreSQL (.sql) programmatically.

If you have any questions, leave them down below.