BUG: ObjectNotExecutableError reading from MySQL with read_sql and SQL string after Sqlalchemy 2.0.0 release

[moritzmeister]

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
from sqlalchemy import create_engine

sql_alchemy_engine = create_engine(sql_alchemy_conn_str, pool_recycle=3600)
sql_query = "select * from capital_fg_602.transactions_1"

with sql_alchemy_engine.connect() as mysql_conn:
    result_df = pd.read_sql(sql_query, mysql_conn)

Issue Description

Ever since SQLalchemy released version 2.0.0 it's not possible to read dataframe using read_sql using a simple SQL string.

I am trying to read from a mysql database, but end up with the following exception

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
/srv/hops/anaconda/envs/theenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py in execute(self, statement, parameters, execution_options)
   1409         try:
-> 1410             meth = statement._execute_on_connection
   1411         except AttributeError as err:

AttributeError: 'str' object has no attribute '_execute_on_connection'

The above exception was the direct cause of the following exception:

ObjectNotExecutableError                  Traceback (most recent call last)

/srv/hops/anaconda/envs/theenv/lib/python3.8/site-packages/hsfs/engine/python.py in _jdbc(self, sql_query, connector, dataframe_type, read_options, schema)
    115             )
    116         with self._mysql_online_fs_engine.connect() as mysql_conn:
--> 117             result_df = pd.read_sql(sql_query, mysql_conn)
    118             if schema:
    119                 result_df = Engine.cast_columns(result_df, schema, online=True)

/srv/hops/anaconda/envs/theenv/lib/python3.8/site-packages/pandas/io/sql.py in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)
    588         )
    589     else:
--> 590         return pandas_sql.read_query(
    591             sql,
    592             index_col=index_col,

/srv/hops/anaconda/envs/theenv/lib/python3.8/site-packages/pandas/io/sql.py in read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype)
   1558         args = _convert_params(sql, params)
   1559 
-> 1560         result = self.execute(*args)
   1561         columns = result.keys()
   1562 

/srv/hops/anaconda/envs/theenv/lib/python3.8/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1403     def execute(self, *args, **kwargs):
   1404         """Simple passthrough to SQLAlchemy connectable"""
-> 1405         return self.connectable.execution_options().execute(*args, **kwargs)
   1406 
   1407     def read_table(

/srv/hops/anaconda/envs/theenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py in execute(self, statement, parameters, execution_options)
   1410             meth = statement._execute_on_connection
   1411         except AttributeError as err:
-> 1412             raise exc.ObjectNotExecutableError(statement) from err
   1413         else:
   1414             return meth(

ObjectNotExecutableError: Not an executable object: 'select * from capital_fg_602.transactions_1'

Expected Behavior

As the documentation states, one should be able to pass a SQL string but also Sqlalchemy text, however, strings don't seem to work anymore.

Wrapping the string in a sqlalchemy.text object, works:

from sqlalchemy import sql
sql_query = sql.text("select * from capital_fg_602.transactions_1")

with sql_alchemy_engine.connect() as mysql_conn:
    result_df = pd.read_sql(sql_query, mysql_conn)

Installed Versions

INSTALLED VERSIONS

commit : 2e218d1
python : 3.8.11.final.0
python-bits : 64
OS : Linux
OS-release : 4.15.0-96-generic
Version : #97-Ubuntu SMP Wed Apr 1 03:25:46 UTC 2020
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : None
LOCALE : en_US.UTF-8

pandas : 1.5.3
numpy : 1.20.3
pytz : 2021.3
dateutil : 2.8.2
setuptools : 58.0.4
pip : 21.2.4
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : 1.0.2
psycopg2 : 2.8.6
jinja2 : 2.11.3
IPython : 7.31.0
pandas_datareader: None
bs4 : 4.11.1
bottleneck : None
brotli : None
fastparquet : None
fsspec : 2022.7.1
gcsfs : 2022.7.1
matplotlib : 3.1.3
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 10.0.1
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.6.3
snappy : None
sqlalchemy : 2.0.0
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
zstandard : None
tzdata : 2022.7

[moritzmeister]

I believe the DeprecationWarning in the docs here is what causes this:
https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Connection.execute

Passing a string to exectute has been deprecated in 2.0.0. But pandas passes down the string without wrapping it in text().

[benrutter]

I'm guessing the fact that sqlalchemy isn't the only sql connection object pandas let's you use means not just wrapping the sql string in:

sqlalchemy.text(sql_string)

but is there a reason not to just chuck something like this into the start of the function?

if 'sqlalchemy' in str(type(con)):
    sqlalchemy = import_optional_dependency("sqlalchemy", errors="ignore")
    sql = sqlalchemy.text(sql)

I don't know too much about pandas direction on compatibility with sqlalchemy's version 2 bump, but seems like this would be a semi-acceptable quick fix? (probably a lot more acceptable if there's a nice way of not converting the type to a string, without importing sqlalchemy)

I'm happy to put in a pull request with something like this, but it seems to obvious a solution to have not already been put in place?

[benrutter]

Just came across this pull request for overall compatibility, which should solve this issue and other potential problems. So my above sugggested hack probably isn't a good idea.

Is it worth pinning the requirements file to flag the current incompatibility in the meantime with that PR's being reviewed?

[Jakobhenningjensen]

This is still an issue tho

[mroeschke]

pandas fully supports sqlalchemy 2.0 as of the upcomming pandas 3.0 release so I suspect this is no longer an issue so closing. Happy to have a new issue if this isn't the case