[Python] Reading a table from SQL Server into a DataFrame using SQLAlchemy

I have a table named "products" on SQL Server. I would like to read the table into a DataFrame in Python using SQLAlchemy.

The pandas.read_sql function has a "sql" parameter that accepts two types of SQLAlchemy "selectable" objects:

• select object
• text object

I succeeded in using the text object but failed in using the select object. What corrections are needed?

successful code using text object:

import pandas as pd

from sqlalchemy import text
from sqlalchemy.engine import URL
from sqlalchemy import create_engine


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)
stmt = text("SELECT * FROM products")
df = pd.read_sql(sql=stmt, con=engine)


not successful code using select object:

import pandas as pd

from sqlalchemy import select
from sqlalchemy.engine import URL
from sqlalchemy import create_engine

from sqlalchemy import Table, 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())
stmt = select(products)
df = pd.read_sql(sql=stmt, con=engine)


sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'FROM'. (156) (SQLExecDirectW)")
[SQL: SELECT
FROM products]
(Background on this error at: https://sqlalche.me/e/20/f405)

Continue reading...