BUG: read_sql_table fails on any Oracle view and/or materialized view.

[SirBenJammin]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine(
    "oracle+oracledb://...",
    echo=True
)

pd.read_sql_table(con=engine, schema="schema_name", table_name="view_name")

Issue Description

Hey team,

Since upgrading Pandas from 1.5.3 to 2.0.1, the function read_sql_table has started failing when attempting to retrieve data from a Oracle view or materialized view. The following error is raised:

sqlalchemy.exc.InvalidRequestError: Could not reflect: requested table(s) not available in Engine

This behaviour only affects Pandas >=2.0.0 and is directly related to the inclusion of the SQLAlchemy self.meta.reflect routine within pandas/io/sql.py. This routine did not exist in previous versions of Pandas.

    def read_table(
        self,
        table_name: str,
        index_col: str | list[str] | None = None,
        coerce_float: bool = True,
        parse_dates=None,
        columns=None,
        schema: str | None = None,
        chunksize: int | None = None,
        dtype_backend: DtypeBackend | Literal["numpy"] = "numpy",
    ) -> DataFrame | Iterator[DataFrame]:

        self.meta.reflect(bind=self.con, only=[table_name])
        table = SQLTable(table_name, self, index=index_col, schema=schema)
        if chunksize is not None:
            self.returns_generator = True
        return table.read(
            self.exit_stack,
            coerce_float=coerce_float,
            parse_dates=parse_dates,
            columns=columns,
            chunksize=chunksize,
            dtype_backend=dtype_backend,
        )

Expected Behavior

The SQLAlchemy reflect function (lib/sqlalchemy/sql/schema.py) has a parameter to include views but the default value is false.

    @util.preload_module("sqlalchemy.engine.reflection")
    def reflect(
        self,
        bind: Union[Engine, Connection],
        schema: Optional[str] = None,
        views: bool = False,
        only: Optional[_typing_Sequence[str]] = None,
        extend_existing: bool = False,
        autoload_replace: bool = True,
        resolve_fks: bool = True,
        **dialect_kwargs: Any,
    ) -> None:

Adding the views parameter to the routine or including a new "include_views" parameter within read_sql_table would fix this bug.

self.meta.reflect(bind=self.con, only=[table_name], views=True)

Many thanks,

Installed Versions

INSTALLED VERSIONS

commit : 37ea63d
python : 3.11.3.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19045
machine : AMD64
processor : Intel64 Family 6 Model 61 Stepping 2, GenuineIntel
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : English_United Kingdom.utf8

pandas : 2.0.1
numpy : 1.24.3
pytz : 2023.3
dateutil : 2.8.2
setuptools : 67.7.2
pip : 23.1.2
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : 4.12.2
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
snappy : None
sqlalchemy : 2.0.11
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

[DavideCanton]

Hi, is this going to be fixed in the 2.0.3?

[DavideCanton]

This is not related to oracle by the way, it happens with postgresql too, probably it happens on any database that supports views.

[lithomas1]

Hi,
I think I can reproduce with SQLite, the suggested fix SGTM.

[lithomas1]

Pushing to 2.0.4 as I'm planning on releasing tomorrow. I'll see if I can include the fix and a test soonish, though.