Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 231
Jack_of_All_Trades Avatar asked Feb 10 '12 19:02

Jack_of_All_Trades


People also ask

Does SQLAlchemy use ODBC?

Connecting to Microsoft SQL Server from a Python program requires the use of ODBC driver as a native data access API.

Can SQLAlchemy create database?

Creating and Inserting Data into TablesBy passing the database which is not present, to the engine then sqlalchemy automatically creates a new database.


2 Answers

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")
like image 153
zzzeek Avatar answered Sep 21 '22 12:09

zzzeek


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))
like image 21
EB. Avatar answered Sep 19 '22 12:09

EB.