Can't connect SQLAlchemy with pyodbc to Microsoft Access database

Question

I have a Microsoft Access .accdb database which I am trying to access using sqlalchemy and pyodbc. When I create a connection and cursor using pyodbc alone, everything works fine, however I would prefer to use sqlalchemy, which throws an error and I am not sure why.

The below code works fine:

import pandas as pd
import pyodbc

database_connector = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=<database_file_path>"
    
with pyodbc.connect(database_connector) as con:
    query = "SELECT TOP 100 * FROM Table"
    df = pd.read_sql_query(query, con)

However, the below code does not:

from sqlalchemy import create_engine, text
from sqlalchemy.engine.url import URL
import pandas as pd

database_connector = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=<database_file_path>"

url = URL.create(
    drivername="mssql+pyodbc",
    query={
        "odbc_connect": database_connector,
    },
)

with create_engine(url).connect() as con:
    query = text("SELECT TOP 100 * FROM Table")
    df = pd.read_sql_query(query, con)

When I run this code, I get the following error when create_engine(url).connect() is executed:

Exception has occurred: ProgrammingError (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Undefined function 'schema_name' in expression. (-3102) (SQLExecDirectW)") [SQL: SELECT schema_name()] (Background on this error at: https://sqlalche.me/e/20/f405)

Answer 1

mssql+pyodbc is for Microsoft SQL Server. You need to install sqlalchemy-access and use access+pyodbc.

(I maintain the sqlalchemy-access dialect.)