SQLAlchemy - MS Access connection failure

Question

I am trying to connect to MS Access using sql alchemy. My access is 64 bit. I have tried with both 32-bit python and 64-bit python. The results are the same.

It works using pyodbc:

import pyodbc
from sqlalchemy import create_engine

class MSAccessConnector:

    def __init__(self, **kwargs):
        self.kwargs = kwargs

    def test_connection(self):
        try:

            conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C://Users//Gautam Sardana//Documents//gautam.accdb;')

            cursor = conn.cursor()
            return cursor


    except Exception as e:
        print(e)
    pass

MSAccessConnector().test_connection()

But fails with sqlalchemy:

import pyodbc 
from sqlalchemy import create_engine

class MSAccessConnector:

    def __init__(self, **kwargs):
        self.kwargs = kwargs

    def test_connection(self):
        try:

            engine = create_engine(r'access:///C:/Users/Gautam Sardana/Documents/gautam.accdb')
            return engine

        except Exception as e:
            print(e)
        pass

    def connect(self):
        pass

MSAccessConnector().test_connection()

I get the error:

Can't load plugin: sqlalchemy.dialects:access

Gord Thompson · Answer 1 · 2020-05-14T01:30:27.370

1

The sqlalchemy-access dialect uses a connection URI of the form

access+pyodbc:// ...

The previous access:// ... form is no longer supported.

You also cannot simply supply the path to the Access database file, e.g.

access+pyodbc:///C:/Users/Gautam Sardana/Documents/gautam.accdb

Instead, you must supply the name of an ODBC System/User DSN or a pass-through ODBC connection string. For example, for a System or User DSN named "accessDatabase1" that points to a normal, unprotected Access database file you would use

access+pyodbc://@accessDatabase1

See the "Getting Connected" wiki page for details.

edited May 14 '20 at 01:30

answered Dec 09 '19 at 15:06

Gord Thompson

116,920
32
215
418

For this, I am getting the error "Can't load plugin: sqlalchemy.dialects:access.pyodbc" – Gautam Sardana Dec 10 '19 at 06:01
Did you `pip install sqlalchemy-access` ...? – Gord Thompson Dec 10 '19 at 11:12
Yeah, I did. I have a question, will it work using sqlite? – Gautam Sardana Dec 10 '19 at 11:18
Yes, SQLAlchemy will work with SQLite. You don't need an external dialect for that. – Gord Thompson Dec 10 '19 at 12:51
The link included says you can in fact connect with a connection string instead of a DSN. I this functionality new or is the info deprecated? – johnDanger Aug 12 '23 at 01:01

SQLAlchemy - MS Access connection failure

1 Answers1