Connect sqlalchemy to MSAccess

Question

How to connect to MS Access using SQLAlchemy? On its website it says the connection string is + pyodbc access. Does this mean that I need to have pyobbc to connect? Since I am a beginner, please be careful.

Answer 1

Theoretically, this would be via create_engine ("access: /// some_odbc_dsn"), but the Access backend has not worked at all since SQLAlchemy 0.5, and it is unclear how well it worked then (that's why it is marked as "development" in http: // docs.sqlalchemy.org/en/latest/core/engines.html#supported-databases - "development" means "a dialect development version exists but is not yet usable"). There is simply not enough interest / volunteers to support this dialect right now. (when this happens, you will see it at http://docs.sqlalchemy.org/en/latest/dialects/access.html ).

The best option for Access at this point would be to export the data to a SQLite database file (or, of course, to some other database, although SQLite is at least like a file), and then use this.

Update, September 2019:

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

Usage example:

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

Answer 2

First of all, I need read access and some simple queries. The latest version of sqlalchemy has (broken) reverse access modules, but it is not registered as an entry point.

He needed a few fixes, but it 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))