[tip] running Access stored queries that expect parameters

Access ODBC treats a saved SELECT query without parameters as a View, so we can use it just like a table. For example, if our saved query named [number_subset] is

SELECT Numbers.i
FROM Numbers
WHERE (((Numbers.i)<=3));

we can simply do

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine("access+pyodbc://@your_dsn")

df = pd.read_sql_query("SELECT * FROM number_subset", engine)

However, if the saved query in Access takes parameters, e.g.,

PARAMETERS highest_number Short;
SELECT Numbers.i
FROM Numbers
WHERE (((Numbers.i)<=[highest_number]));

then Access ODBC treats it as a stored procedure and we need to use the ODBC {CALL …} syntax:

# with pyodbc parameter placeholders
#
df = pd.read_sql_query("{CALL number_subset(?)}", engine, params=(3,))

# or with SQLAlchemy text() placeholders
#
df = pd.read_sql_query(
    sa.text("{CALL number_subset(:highest_number)}"),
    engine,
    params=dict(highest_number=3),
)