As @AlwaysLearning pointed out, the option to load some or all table information from an existing database is called reflection (https://docs.sqlalchemy.org/en/20/tutorial/metadata.html#table-reflection).

from sqlalchemy import select, Table, MetaData, create_engine, URL
import pandas as pd

metadata_obj = MetaData()
url_object = URL.create(
    "mssql+pyodbc",
    host="abgsql.xx.xx.ac.uk",
    database="ABG",
    query={
        "driver": "ODBC Driver 18 for SQL Server",
        "TrustServerCertificate": "yes",
    },
)
engine = create_engine(url_object)
products = Table("products", metadata_obj, autoload_with=engine)
stmt = select(products)
df = pd.read_sql(sql=stmt, con=engine)