connecting sqlalchemy to MSAccess

Question

How can I connect to MS Access with SQLAlchemy? In their website, it says connection string is access+pyodbc. Does that mean that I need to have pyodbc for the connection? Since I am a newbie, please be gentle.

Mev

Answer 1

In theory this would be via create_engine("access:///some_odbc_dsn"), but the Access backend hasn't been in service at all since SQLAlchemy 0.5, and it's not clear how well it was working back then either (this is why it's noted as "development" at http://docs.sqlalchemy.org/en/latest/core/engines.html#supported-databases - "development" means, "a development version of the dialect exists, but is not yet usable"). There's just not enough interest/volunteers to keep this dialect running right now. (when/if it is, you'll see it at http://docs.sqlalchemy.org/en/latest/dialects/access.html).

Your best bet for Access right now would be to export the data into a SQLite database file (or of course some other database, though SQLite is file-based in a similar way at least), then use that.

Update, September 2019:

The sqlalchemy-access dialect has been resurrected. Details here.

Usage example:

engine = create_engine("access+pyodbc://@some_odbc_dsn")

Bellwort

@Gord Thompson Great project. I want to give it a try. It says I need a DSN for it to work. Is there a good guide on how to create such a DSN? The linked Microsoft articel does not hold much useful information. – Shing

@Shing - In Windows 10+ just type odbc into the search box and choose "ODBC Data Sources (64-bit)" or "ODBC Data Sources (32-bit)" depending on the flavour of Office/Access you have installed. – Whaleback

Answer 2

I primarily needed read access and some simple queries. The latest version of sqlalchemy has the (broken) access back end modules, but it isn't registered as an entrypoint.

It needed a few fixups, but this worked for me:

def fixup_access():
    import sqlalchemy.dialects.access.base
    class FixedAccessDialect(sqlalchemy.dialects.access.base.AccessDialect):
        def _check_unicode_returns(self, connection):
            return True
        def do_execute(self, cursor, statement, params, context=None, **kwargs):
            if params == {}:
                params = ()
            super(sqlalchemy.dialects.access.base.AccessDialect, self).do_execute(cursor, statement, params, **kwargs)

    class SomeObject(object):
        pass
    fixed_dialect_mod = SomeObject
    fixed_dialect_mod.dialect = FixedAccessDialect
    sqlalchemy.dialects.access.fix = fixed_dialect_mod

fixup_access()

ENGINE = sqlalchemy.create_engine('access+fix://admin@/%s'%(db_location))

Wein