Skip to content

connecting sqlalchemy to MSAccess

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.

Answer

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