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.

Solution 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")

Solution 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))